Sql-server – How to setup SQL active/active cluster to achieve Blue / Green instance switching

clusteringdeploymentsql server

I am wondering if anyone has ever used a multi-instance cluster (nee 'Active/Active') to achieve blue/green (or A/B) deployment scenarios, and what the best way of configuring it is (using SQL 2012 / Windows 2008 R2)?

To be specific, the scenario I want to achieve is to be able to switch between which cluster instance is being connected to by clients without either the clients or the SQL instances knowing (I stress I'm not talking about node failover here). I'm envisaging that the best way to achieve this is something like:

  • Setup 2 node cluster, each of which has InstanceA and InstanceB instances
  • Configure both InstanceA and InstanceB to listen as if they were the default instance on their cluster address (given each instance on a cluster has it's own unique IP)
  • Use DNS to switch which virtual address clients actually connect to.

This should hopefully enable me to do the following:

  • Deploy database to instance A, and have clients connect to it via DNS
    alias as if default instance
  • Deploy new version of database to
    instance B
  • Vet new version of database (connecting explicitly to cluster\InstanceB)
  • Redirect DNS alias to point to instance B's cluster name
  • Clients now connect to InstanceB without realising anything's changed
  • Both instances can still failover to the other node in a true outage

Joining the dots, it seems like this should be possible:

… but I've never seen a full example. Has anyone done it? Will what's proposed above work? What have I missed?

Best Answer

What you are looking to do is basically have two instances running that you can redirect clients between. There's no need to do this within Failover clustering as this won't help you any. Doing this simply requires that you setup two standalone instances of SQL Server, each on a separate Windows server. Then setup a CNAME in DNS and use that to point people to the correct SQL Instance.

Now for some of the problems.

  1. The data won't be in sync between the two instances.
  2. DNS takes time to replicate and update on the client machine's local cache, so you'll end up with users connected to both systems.

A better solution would be to take a snapshot of the database before doing the schema change release. If the release is successful delete the snapshot. If the release fails and you have to rollback you'll need to restore the database from the snapshot. This will be pretty quick, but there will be an outage while the rollback happens.

What you are looking for basically won't work.

Related Question