SQL 2012 Always on and mirroring questions


  • Hi,

    We are planning to upgrade our current SQL 2005 cluster to SQL 2012 cluster. The SQL 2005 primary cluster has 4 instances on 3 nodes (2 instances on an Active, 2 instances on an active and passive), the DR cluster has 4 instances to do mirroring for primary 4 instances (4 instances on active and passive). Our plan is to build a new SQL 2012 cluster with 4 nodes (2 instances on an Active, 2 instances on an active,passive and DR always on node). The first 3 nodes will have shared storage, the DR always on node has its dedicated storage. My questions are:

    1. Does the design have any issue? Windows cluster can be failed over automatically in the first 3 nodes, for example, we do patching on any of nodes, or any node has some issues. SQL always on can be failed manually, for example we lost shared storage. 

    2. Do we still need to build another server/cluster for DB mirroring?

    3. do we need extra license cost if we do not want to do backup/reporting on always on node?



    Sunday, September 08, 2013 11:33 PM


All replies

  • What DR scenarios are you trying to mitigate? Is the 4th node of the new solution in a second data centre?

    1. You will need to think carefully about quorum in this situation. For example if nodes 1-3 are in data centre 1 and it goes offline, node 4 will also go offline because the cluster now doesn't have quorum (so you'll need to manually restart the cluster - there are some useful features in W2012 R2 failover cluster that may help).

    2. Availability Groups is the replacement for DB Mirroring - so, no, you'd typically replace DBM with AG rather than have to build / retain a DB Mirroring component in your new solution. You'll also need to confirm that all applications support AG (even some MS apps don't).

    3. Typically, if you're using SQL Server Enterprise Edition with Software Assurance you'd only license 2 of the 4 nodes (minimum of 50% rule) in the solution you've described (All Windows Servers need to be fully licensed). However, you should always check with the Microsoft before proceeding.

    Monday, September 09, 2013 12:57 AM
  • Hi Stephen,

    Thank you very much for your reply. Yes, the 4th node is in a second data center. 

    1. if we lost whole data center 1 including Quorum, can we still bring all always on DBs online on node4 and all applications can talk to these online DBS?


    Monday, September 09, 2013 1:33 AM
  • Yes, but it would potentially require a force cluster start because you'd most likely lose cluster quorum which would make node4 also go offline. In your scenario I'd recommend deploying on W2012 R2.

    See the following for more details:

    Monday, September 09, 2013 1:58 AM