We are looking at spending some money next year on a new Dev/UAT server environment. Current we have a number of physical servers on which SQL Server is installed to match what is in production. As an option, we're considering two possible approaches: -
1. Automate the uninstall/install of SQL Server 2005 instances on a given Windows server
Currently, I have to manually install, configure and maintain the Dev and UAT instances of SQL Server. The problem is that these environments degrade and diverge from what is in production. We would like to be able to be able to quickly trash and rebuild SQL Server instances, configure server level objects, restore databases and so on.
2. Automate VMWare build of a Windows Server 2003 environment + same as (1) above.
The advantage of VMWare will be that we can, if we can automate the process, quickly create/destroy new Dev or UAT environments including a standard Windows build. Another advantage might be that a virtualised server resource is more efficient that separate physical boxes - most of the time, DEV and UAT servers are not busy.
The question is, what is the best way to automate given the two options above? I know WMI is there but I would have to learn it from the ground up. I also know I can do SMO coding via VB for example (have done some DMO coding in the SQL Server 2000 world) but I am not an expert. I used FinalBuilder currently for automating database and application build tasks. I have seen there are some VMWare actions in FinalBuilder - how complete/useful are these actions? Maybe Automise would offer a solution but how much WMI coding and SMO or T-SQL scripting would still be required? What are the elements (eg. FinalBuilder, Automise, etc) are required to complete the picture?
Clive