May 7, 2012 at 2:36 pm
Hi -
Planning on upgrading from SQL 2008 R2 to SQL 2012...
Has anyone performed one way SQL Mirroring for the upgrade process?
Simple example - add SQL 2012 as a mirror node to a SQL 2008 R2 principal.. Then simply failover the database...
Should be pretty basic - just want to see if anyone has actually done it yet.
Thanks.
May 7, 2012 at 3:28 pm
mirroring requires both partners to be the same version of SQL so this will not work.
Whats the size of the database? I presume you want to do this to reduce the cutover time of the upgrade? Use log shipping with the standby in norecovery mode, bringing it online with the very last transaction log at cutover time, the database will get upgraded at that point.
---------------------------------------------------------------------
May 7, 2012 at 4:57 pm
I haven't tried it, but I don't see a restriction on versions. The docs mention any compatibility mode.
The issue with doing this continuously is that if you fail over, you cannot fail back. That kills the HA/DR purpose for many people. However there are notes about installing SPs. Usually as long as the mirror db is higher than the primary db, you are fine.
There are docs about upgrades with rolling them through the mirror session (http://msdn.microsoft.com/en-us/library/bb677181.aspx). I suspect in the worst case, you could install a new R2 instance, make it a mirror partner, then upgrade the R2 instance to 2012 and fail over. Then once it's failed, upgrade the original box to 2012, or remove mirroring.
May 7, 2012 at 5:22 pm
george sibbald (5/7/2012)
mirroring requires both partners to be the same version of SQL so this will not work.
It will work, it is a documented way of doing a rolling upgrade.
Mirroring with two different versions is not supported except as part of a rolling upgrade, the principal must be the lower version to start with then once failed over, the mirroring is suspended until the mirror is also upgraded.
http://msdn.microsoft.com/en-us/library/bb677181.aspx
http://sqlcat.com/sqlcat/b/msdnmirror/archive/2009/02/09/minimize-downtime-with-db-mirroring.aspx
The database mirroring GUI may give errors if the versions are different, but it's perfectly allowed with T-SQL scripts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply