June 14, 2015 at 4:33 am
Hi All,
We are currently migrating our SQL Server 2005 to 2008 r2. We have decided on the below approach :
1. Take full backup of current DB
2. Copy the backup file on new server. -- Need feedback on this
3. Restore on the new server (2008 r2)
I have below queries:
1. Do we need to do any other steps apart from the above ones.
2. How do we migrate all the login access data.
3. Also, one of the major challenges is couple of our DB's are huge like 450 GB, 200 GB. Now we are told to do this activity with minimal outage (2-3 hours).
What is the best/fastest way to copy the full backups on new servers and restore them.
Need feedback on these please.
June 14, 2015 at 7:24 am
For the logins, you would just want to script them all out prior to the migration and save the script. Then you can just run the script on the destination server once the restores are complete.
sp_help_revlogin can help a lot with that: https://support.microsoft.com/en-us/kb/918992. Note that that will only preserve the SID and password, so you would still want to script out permissions and user mappings.
As far as the window goes, is there any reason the entire backup/restore process has to take place in the window specified for the migration?
If there isn't, then you could copy over and restore the full at your leisure before the migration, and either configure mirroring (my favorite method when feasible) or just keep the destination DB up to date with tlog restores.
Then for the actual migration all you would have to do is failover the mirror if you went the mirroring route, or if you were keeping the destination up to date with tlog restores, take a tail-log backup and copy that over.
Cheers!
June 14, 2015 at 3:09 pm
I second setting up mirroring (or log shipping if you have to).
Remember to check for other items you might need to bring over as well:
SQL Agent jobs
Maintenance plans
Server-level triggers
etc.
Joie Andrew
"Since 1982"
June 14, 2015 at 9:20 pm
Does anyone have any info on whether or not it's possible to mirror a 2005 server to a 2008 server? I was pretty sure that they had to be at the same revision but could be wrong.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2015 at 12:55 am
You can mirror from an older version to a newer version (like 2005->2008), but it's a one way trip. You can fail over to the 2008 instance, but you can't fail back.
That's fine for migrations, but not so much for HA or DR.
https://technet.microsoft.com/en-us/library/ms189053.aspx#Requirements%5B/url%5D points that out, albeit with confusing wording.
The principal and mirror server instances must be running on the same version of SQL Server. While it is possible for the mirror server to have a higher version of SQL Server, this configuration is only recommended during a carefully planned upgrade process. In such a configuration, you run the risk of an automatic failover, in which data movement is automatically suspended because data cannot move to a lower version of SQL Server.
(emphasis mine)
Cheers!
June 15, 2015 at 7:28 am
Jacob Wilkins (6/15/2015)
You can mirror from an older version to a newer version (like 2005->2008), but it's a one way trip. You can fail over to the 2008 instance, but you can't fail back.That's fine for migrations, but not so much for HA or DR.
https://technet.microsoft.com/en-us/library/ms189053.aspx#Requirements%5B/url%5D points that out, albeit with confusing wording.
The principal and mirror server instances must be running on the same version of SQL Server. While it is possible for the mirror server to have a higher version of SQL Server, this configuration is only recommended during a carefully planned upgrade process. In such a configuration, you run the risk of an automatic failover, in which data movement is automatically suspended because data cannot move to a lower version of SQL Server.
(emphasis mine)
Cheers!
Excellent. Thanks for that great feedback and the link, Jacob.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2015 at 7:48 am
I have had a few people I know use mirroring to minimize the downtime. You can do this as well, but be aware that you need to account for the time to move clients from one server to the next, or the rename time on the second server once the first is turned off.
As mentioned, be aware you have no real fallback here for data that's entered on the R2 instance if you need to bring the old one back. You'd be manually reconciling the differences.
June 15, 2015 at 8:35 am
You will have to script out all of your SQL Agent jobs. Also, if you have any Linked servers you will need to recreate those as well.
June 23, 2015 at 2:22 pm
If the nightly DIFF backups are not too big:
Take a FULL backup earlier in the day
Restore the FULL backups on the new server WITH NORECOVERY
Keep running your TLOG backups as normal
Right before cutover time:
Run a final TLOG backup then disable the TLOG backup job.
At cutover time (for each user DB):
Alter the DB to SINGLE_USER mode, take a DIFF backup, alter the DB to OFFLINE
Restore the DIFF backups on the new server WITH RECOVERY
(note: you can stripe the diff backup to speed it up)
If you need to backout then alter the OFFLINE DB's back ONLINE.
You can test this ahead of time to have your jobs/scripts ready to roll and know how long it will take (just don't alter the live DBs OFFLINE).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply