| Author |
Topic |
|
lebedev
Posting Yak Master
126 Posts |
 Posted - 2012-09-21 : 15:32:42
|
| Hello,Our company is hosting multiple customer databases in SQL Server 2008 R2. The total size of databases is about 2TB. We are planning to move a new data center (in a different state) which has SQL Server 2012 installed and would like to minimize the downtime.What would be the recommended way to move 2TB from 2008 to 2012 with little or no downtime?Thanks,Alec |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-21 : 16:02:09
|
| Typically you'd establish database mirroring with the principal in the old location and the mirror in the new. Once your mirrors are fully synchronized you perform a manual failover to the new server location. If you can modify your application's connection strings to include the "Failover Partner" key, then they'll automatically handle the failover as well, with no downtime. Once you get the new location running, you can break mirroring and change the connection string to just use the new server.Since you're changing versions from 2008 R2 to 2012, you won't be able to fail back once the mirror fails over. You'll want to test and practice this process several times to work out all the necessary steps and look for hiccups. I've done it several times and it works very well, we had maybe 5 seconds of downtime across 10 databases moved using this method. (and by downtime I mean existing DB connections were dropped, but not busy at the time; new connections waited until the failover completed) |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-22 : 02:31:55
|
| Are you switching straight into SQL Server 2012? Is there any provision for testing?This could save you downtimeJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2012-09-23 : 14:50:09
|
| Thanks for the mirroring suggestion, robvolk. We are definitely planning to test the migration process a couple of times in QA lab. Manual failover will work for us. Fortunately we don't have any custom database types, which may simplify switching to a different version of SQL Server.We will be switching from 2008 R2 directly to 2012. I am assuming that if connection is lost and the reestablished during the initial mirroring process, SQL Server will be smart enough to retry to resync the mirrors, right? Robvolk, you mentioned that you performed this sort of migration several times. Do you have any notes that you could share or point me to some helpful docs? I will, of course, do my own share of googling :)Thanks,Alec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2012-10-17 : 17:32:21
|
| It turns out that we are currently on the Standard edition on 2008 R2 and the new data center is using the Enterprise edition of 2012. According to http://msdn.microsoft.com/en-us/library/ms366349.aspx#Prerequisites mirroring partners should be using the same edition. Is it better to upgrade 2008 R2 to Enterprise and perform mirroring or install Standard 2012, perform mirroring and after that upgrade 2012 to Enterprise? Are there any other ways to fix this?Thanks,Alec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-17 : 18:39:31
|
| Did you try mirroring with the current setup (2008 Standard->2012 Enterprise)? If it lets you establish the mirrors, then you can ignore the edition upgrade. I'm pretty sure I've mirrored from Standard to Enterprise before, but not between different versions. It may not be recommended or supported, but if you're planning to go straight to 2012 you can test it to be safe and then perform a failover as soon as possible when going live. The key thing is to test it beforehand, on different hardware wherever possible. |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2012-10-24 : 13:44:33
|
| Thanks, we are definitely going to test this thoroughly before deploying in production.We have an internal debate here about using Replication for this move instead of Mirroring. Moreover, this article http://msdn.microsoft.com/en-us/library/ms175883.aspx says that mirroring will be deprecated in the future versions on SQL Server (or maybe it's just mirroring with Windows Authentication).Why do you think mirroring and not replication is the preferred technology for this migration?Thanks,Alec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-24 : 14:32:23
|
| You'll have more downtime with replication, i.e. can't guarantee all the data is synchronized without making the publication database read-only. With mirroring you can keep everything running and have downtime of only a few seconds, with no loss of data or need to post-synchronize.Mirroring is deprecated but will not be dropped until 1 or 2 more versions of SQL Server. SQL 2012 introduces Availability Groups as a replacement for mirroring, but mirroring is still valid. You're only using mirroring for doing a server migration coupled with version upgrade. |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2012-10-24 : 16:56:00
|
| I just confirmed that mirroring between Standard and Enterprise editions is not supported. SSMS shows me an error message on the Mirroring tab of the principle database properties: "This mirroring configuration is not supported. Because the principalserver instance, localhost, is Standard Edition, the mirror serverinstance must also be Standard Edition."So, now I have to chose one of the following options:1. Upgrade principle to 2012 Enterprise and mirror it with 2012 Enterprise mirror2. Install 2012 Standard on the mirror, mirror the data from 2008 Standard and the upgrade mirror to 2012 Enterprise3. Use replication. Can replicate from 2008 Standard directly to 2012 Enterprise?Which option would you recommend?Thanks,Alec |
|
|
|
|
Moving to a new data center with SQL Server 2012