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?