June 5, 2013 at 9:28 am
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?
June 6, 2013 at 6:32 am
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
June 6, 2013 at 8:42 am
Is backing up the databases and restoring the best way, including Master and Model, or once the old server is shut down, can I just copy the files over? MDF etc?
In the cluster, can I just delete the cluster resources for the IP and computer name but leave SQL running?
What about moving over the SQL Integration Services components?
June 6, 2013 at 8:49 am
joshua.post (6/6/2013)
Is backing up the databases and restoring the best way, including Master and Model, or once the old server is shut down, can I just copy the files over? MDF etc?In the cluster, can I just delete the cluster resources for the IP and computer name but leave SQL running?
What about moving over the SQL Integration Services components?
Unless you have some custom modifications, you may forget about Master and Model. By moving the jobs via script, you eliminate one big reason for moving MSDB. If I remember correctly, MSDB will not restore to another instance unless they are running exactly the same version of SQL, right down to service pack and hotfix (i.e., cumulative update).
The SQL cluster resources should have dependancy on the IP and Name, so SQL can not be left running (that I know of).
I'm sorry, but I don't know specifically about the SSIS components. I'll ask a colleague who is knowledgable to chime in here.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
June 6, 2013 at 9:15 am
joshua.post (6/6/2013)
Is backing up the databases and restoring the best way, including Master and Model, or once the old server is shut down, can I just copy the files over? MDF etc?
I apologize for overlooking your first question. I prefer to use backup/restore to move databases. The files to be moved are usually smaller, and I believe it is somewhat more reliable. Copying the MDF and LDF will work, but I recommend using detach on each database before shutting the database down. Whether you use backup/restore, or copy mdf/ldf, you should perform a log backup first (if you are running Full or Bulk-Logged, but not Simple).
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
June 6, 2013 at 9:20 am
joshua.post (6/6/2013)
Is backing up the databases and restoring the best way, including Master and Model, or once the old server is shut down, can I just copy the files over? MDF etc?In the cluster, can I just delete the cluster resources for the IP and computer name but leave SQL running?
What about moving over the SQL Integration Services components?
All of your agent jobs will come over when you restore msdb. You can also script the jobs out on the old server and re-create on the new.
For integration services you can export the packages on the old server first by connecting to Integration Services via SSMS and right clicking on the package and choosing export.
On the new server connect to Integration Services via SSMS and if they packages were stored in msdb right click on it and choose import package. If you didn't export them you can connect directly to the old instance and import them. If not point to the file you export previously.
If the packages were on the file system on the old server you should just be able to copy them to the same path on the new server.
Since you are upgrading to 2008 I'd open the packages in BIDS and save them so they get upgraded. If the server names stay the same you shouldn't have to modify the connections in the package. If they changed and the old name was hard coded you may have to edit the package.
If you were using maintenance plans on the old server I would re-create them from scratch on the new 2008 server.
June 6, 2013 at 9:20 am
Thanks. We use Simple recovery today due to our backup software solution we are utilizing. I'll plan on doing that method of backing up and restoring, although it involves moving the data twice, once to the backup file, and then back to a new server. But I could see it being more reliable and if I don't detach the database from the original SQL, then it still leaves me in a better position for failback.
June 6, 2013 at 9:22 am
webdave (6/6/2013)
Since you are upgrading to 2008 I'd open the packages in BIDS and save them so they get upgraded.
Can you explain futher on this BIDS section? I'm not familiar with that.
June 6, 2013 at 9:25 am
joshua.post (6/6/2013)
webdave (6/6/2013)
Since you are upgrading to 2008 I'd open the packages in BIDS and save them so they get upgraded.
Can you explain futher on this BIDS section? I'm not familiar with that.
Business Intelligence Development Studio. It is the app used to create SSIS packages. It is a feature available during the SQL install wizard. I'm not 100% sure but you may be able to upgrade the package by launching SSMS connecting to Integration Services right clicking on the package and click upgrade package.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply