August 25, 2014 at 5:53 am
August 25, 2014 at 5:56 am
ramana3327 (8/25/2014)
Hi,If we want to move SQL Sever 2008R2 to SQL 2102,
In placemigration (Upgradation) or side by side migration is better? How can we decide which one is better option to us?
This article showing that it is always better to avoid in place migration
Thanks,
Quick thought, my preferred method is (when possible) to do a side by side installation of SQL2012 and then copy everything over, test it and drop the source.
😎
August 25, 2014 at 6:47 am
Eirikur Eiriksson (8/25/2014)
ramana3327 (8/25/2014)
Hi,If we want to move SQL Sever 2008R2 to SQL 2102,
In placemigration (Upgradation) or side by side migration is better? How can we decide which one is better option to us?
This article showing that it is always better to avoid in place migration
Thanks,
Quick thought, my preferred method is (when possible) to do a side by side installation of SQL2012 and then copy everything over, test it and drop the source.
😎
+1. Side by side is safer. It completely avoids the risk of in-place upgrade. Your rollback plan is to simply revert back to using the original.
August 25, 2014 at 7:41 am
Side-by-side is absolutely the best and safest. I've never really seen any major issues during upgrades, but if you had any, the ability to instantly just bring the old server online is the prefect safety mechanism.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 25, 2014 at 8:15 am
Side by side for me, but always make sure you take backups of all user databases, master, model, and msdb before you do either.
August 25, 2014 at 8:16 am
Thanks for the replies.
One more doubt about the downtime
"Downtime is the time to take the backup and restore into the new server and changing the compatibility levels and fixing the orphan users issues"
which option is better for databases recovery in side by side migration. If both sql 2008 r2 & 2012 will be in the same network and we need less downtime
1)Back up the databases from the original server and then restore them to the SQL Server 2012 instance. 2)Manually detach your database from the old instance and reattach it to the new instance
3)Use log shipping, or database mirroring.
4)Using the Copy Database Wizard to migrate databases to the new server.
August 25, 2014 at 8:23 am
1 or 3. But you should do the backups even if you do #3. The backups should just be how you protect yourself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 25, 2014 at 4:32 pm
Hi All,
Thanks for the replies.
I read that for migration we can schedule log backup or differential backup to reduce the downtime. Anyone did this? I need the downtime as less as possible.
August 25, 2014 at 9:10 pm
The way you do this is to schedule full, then diffs, and then log backups. You take a full and restore it to the new instance. Once that is done, you can take a diff, and restore that to the new instance. Then logs.
If you time this right, you can get the downtime as low as a log or two of backup/copy/restore.
August 25, 2014 at 11:33 pm
If you have another server for migration and the server has the same or better hardware, you can do the side by side migration. and then you can control the migration progress.
August 26, 2014 at 4:19 am
ramana3327 (8/25/2014)
Hi All,Thanks for the replies.
I read that for migration we can schedule log backup or differential backup to reduce the downtime. Anyone did this? I need the downtime as less as possible.
Yeah, that's just a manual log shipping process as Steve described.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2014 at 6:30 am
Thanks all,
Correct me if I am wrong,
If we have 15 databases in 2008 and we want to upgrade to 2012 with minimal downtime. First we have to run the upgrade adviser to know the potential issues. Then we can take the full backups of all databases and apply those in 2012 then apply all databases transactional logs with recovery into the new environment then run the dbcc check db, creating the logins & users and changing the compatibility and update the statistics and connect the application then create the jobs
If we have TDE enable in any of the databases then we have to backup the master key & service key then restore those on the target environment.
Thanks,
August 26, 2014 at 8:28 am
ramana3327 (8/26/2014)
First we have to run the upgrade adviser to know the potential issues.
Yes, this is a basic requirement, you want to know where compatibility issues will lie.
ramana3327 (8/26/2014)
Then we can take the full backups of all databases and apply those in 2012 then apply all databases transactional logs with recovery into the new environment then run the dbcc check db, creating the logins & users and changing the compatibility and update the statistics and connect the application then create the jobs
Since you have a new cluster you'll need to change connections strings in your apps, unless you also migrate the Virtual IPs and network names too.
ramana3327 (8/26/2014)
If we have TDE enable in any of the databases then we have to backup the master key & service key then restore those on the target environment.Thanks,
No, no, no, no, no!!
You only require a backup of the certificate and its private key from the source server, these are then restored to the target server. The SMK and DMK are totally separate and not required. If the target server does not have a DMK then create one before restoring the certificate, but it's not required from the source server and indeed probably best if it's not for security reasons 😉
See my guide at this link[/url] for moving a TDE protected database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply