How to setup SQL active/active cluster to achieve Blue / Green instance switching?

Question

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:

• http://blogs.msdn.com/b/dbrowne/archive/2012/05/21/how-to-add-a-hostname-alias-for-a-sql-server-instance.aspx
• https://stackoverflow.com/questions/9313358/connecting-to-a-multiple-instance-sql-server-without-specifying-instance

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

Answer 1

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.

Answer 2

The one issue I see with that you are proposing is that when using a cluster you can only have 1 default instance and the rest of the clustered instances would require a unique instance name. This will cause issues if you are trying to route you application to the "active/live" database with DNS as the DNS entry will be unaware of the port/instance name causing issues when trying the SQL Client connection is initiating.

If you wanted to use DNS to direct the connections to the instance that is server - both instances would need to either be configured as default instances or using the same port\instance name. If you want to use a failover cluster, this would require two separate clusters - again due to the limitation in that the instance name must be unique among the instances on that cluster.

Alternatively you could look at SQL Alias's (http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/) to achieve the same idea you are looking at with the DNS entries. The challenge with this option is that the alias must be set on each client machine connecting directly to the database. In order to simplify the administration time spent directing the client over to the new version of the database then a way to programatically change the alias on each client machine. Which I believe the alias is stored within the registry so it should be possibly to change that alias with a powershell script.