joshua.post (6/5/2013)
We have a SQL 2005 Standard cluster with 2 nodes on Server 2003 R2 Enterprise. There are two SQL instances and two File Server resources on this cluster. The SQL instances have 4-8 databases, multiple jobs, and some SQL Integration Services pakcages. We are wanting to migrate to a VMware VM with Server 2008 R2 and SQL 2008 R2 to get off the physical hardware but keep the same SQL names so the applications do not need to be updated.Do you have any guides or posts on doing this? My strategy so far would be do backup the databases, uninstall SQL to free up the names and IPs, install the new servers with the same name and IP, and then restore/reattach the databases.
How do I also bring over the SQL Agent jobs and logins?
How does Integration Services fall into this? Someone else created all of the packages and I am very green to that aspect.
Thoughts?
Overall you have thought out a safe plan of attack. For safety sake, I recommend that you don't uninstall SQL instances until later. This will leave you a fairly quick backout, which you likely won't need (unless they are uninstalled!).
Like you said, you would first take full backups of all user databases, and then copy the backup output files to where you can access them from the destination server.
For the SQL Agent jobs (hopefully there aren't hundreds), Right-Click | Script Job As | CREATE TO | File... . I'd use the job name along with the instance name as the filename for your job scripts. Save these in a folder near your database backups.
To move the SQL Logins, KB918992 has a method that has worked well for me.
http://support.microsoft.com/kb/918992
This will create a script to recreate all logins, complete with (hashed) password if they are native SQL logins, to your destination instances. Edit it first to get rid of the logins that are created by the SQL installation, such as NT AUTHORITY\SYSTEM, and the domain cluster service account groups, which will not be needed in SQL 2008R2 (or a non-clustered server).
Take the SQL resources offline in Cluster Administrator, and remove the DNS entries for the SQL Name and IP, as well as Active Directory. (I admit I can't do that, but I always have my system engineers and network admins take care of it.)
When the new server is up and running with the desired name and IP, install the SQL instances. Then run the Create Login scripts before you restore the databases, to avoid orphaning the database users. Restore the databases, make sure the database users have resolved to the server logins, and then you can run the SQL Agent Job Create scripts on the correct instances.
I hope this helps your efforts.
- Regards, Mike
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS