Sql-server – SQL Server 2008-R2/2012 with Failover Cluster – minimizing downtime of program updates

high-availabilitysql-server-2012

I have SQL Server 2008-R2 with Failover Cluster like this. Planning to upgrade to SQL Server 2012.

Problem –

  • The downtime of my program updates (not Microsoft updates/patches).
    Both of my cluster nodes have separate sets of connected IIS and App servers.
  • I need to upgrade all of them: IIS + App server + SQL.
  • Cannot run old IIS+App against upgraded SQL or the other way around (causes version conflicts).So the minimum downtime is the time of SQL upgrade running (can at least prepare one node's IIS+App in advance and then switch to it when ready).

Now I hear there is something clever in SQL Server 2012 to minimize the downtime even further in this kind of situations. But what exactly might it be?

(please be specific, I know they have this big AlwaysOn Availability Groups banner 🙂
PS basically can change the entire approach if needed (like dropping Failover Cluster in favor of something better)

Best Answer

I am not sure if you are proposing an in-place or migration upgrade. As this answer indicates, I prefer migration upgrades when possible.

Assuming a migration - you shouldn't need to rely on any SQL Server 2012 features to assist in the migration and reduce the downtime. For your new environment planning, you might look into AlwaysOn Availability Groups and some of the benefits and differences from using failover clustering instances. Failover Cluster Instances are still a fine option in SQL Server 2012, though. Especially when you want to use shared storage, keep a copy of the data, have the DBs, jobs, logins, etc. all just work the same on the passive node as the active node.

As to how to make the migration faster - I talk about it a bit in the linked answer above - but basically you could do a plan like this if migrating:

  1. Build the new environment and test it/prep it for readiness
  2. Do a test migration to it, verify apps look good and work. Hopefully you can freeze jobs and logins at this point so you have the jobs and logins you need moved over
  3. For the actual live cutover - restore full databases to it specifying with norecovery (this is assuming your DBs are already in full recovery mode on the source, if not they'll need to be properly there.) from your latest full backup.
  4. Using a combination of differential and transaction log backups, restore the backlog of diffs and transaction logs (again specifying with norecovery) until you get to
  5. When you are "caught up" on your restores and have your "cutover window" - you can hold activity, do your last log restores - finally specifying with recovery on the last ones and ensure all apps are good.

That is an approach to minimize the downtime when doing a migration style. When you do an in-place it's a different story and there frankly isn't a whole heck of a lot to be done to change or speed up processing.

Obviously this is a decision you would have to have made well ahead of the upgrade - in place doesn't need new hardware.. Migration does.

Related Question