Freigeben über

SQL Server 2012 – Always On

  • Artikel

There have always been several ways to do high availability in SQL Server, but choosing the right one has always been difficult as each approach has obvious benefits coupled with unavoidable limitations:

Clustering looks after a whole instance of SQL Server containing many databases and is completely transparent to an application. However shared storage adds cost and complexity and there is only the one copy of the database(s) on that shared storage.

Mirroring creates a continuously updating replica of a given database, failover is really fast and it’s easy for a DBA to setup. However mirroring has several significant limitations:

  • A special connection (SQL Server native client )is needed to mirroring so not all applications can work with it
  • Protecting multiple databases so that if one fails they all fail over is not really possible.
  • There is only one mirror of the database
  • The mirror is not directly usable it just sits there unless you are prepared to work with snapshots.

Log shipping is sort of manual mirroring which allows more than one replica to be kept; perhaps a local one and a remote one.  This is more difficult to setup and failover is not automatic you have to reset all of this yourself.

To build a better SQL Server mousetrap, you would want a solution that:

  • Looks like a cluster to any application i.e. there is a DNS entry to the cluster to which the application connects without ‘knowing’ which node SQL Server is running on
  • You would want to treat a group of databases as an object so that they can be failed over etc. as needed in one go. 
  • As with log shipping, there wouldn’t just be one other node behind the primary there would be multiple mirrors/secondaries
  • The mirror could be read only and therefore available for reporting
  • You could opt to have some nodes connecting asynchronously and thus have a remote replica of your databases without needlessly slowing down the primary.

Up until know that meant that we would have use more than one feature in concert e.g. mirroring and clustering together to achieve the high availability we wanted. What SQL Server 2012  AlwaysOn  does is to provide this combination in one single feature:

It uses the Windows Failover Cluster feature in Windows Server but doesn’t use any shared storage. A normal install of SQL Server 2012 is then done on each node and the SQL Server 2012 service is then configured to use the cluster..

image

Having done that you then tell the SQL Server service on each node to use the cluster the new AlwaysOn High Availability tab in the properties for the service..

image

However AlwaysOn is actually doing something very similar to mirroring under the covers, in that there are replicated copies of the databases being protected not just one copy on shared storage as there is for clustering – and AlwaysOn doesn’t need to use shared storage. You’ll also notice that for databases to be protected by AlwaysOn they need to be in full recovery mode and backed up (preferably to a share that’s visible from the other nodes). However with AlwaysOn you can have multiple secondaries and you create availability groups, which are sets of the databases you want to keep together.

There’s a wizard in SQL Server Management Studio for this where you can specify the nodes, the databases and the options for accessing each node. Note this uses TCPIP ports like mirroring does (so port 5022 by default) and these need to be opened in the firewall for this all to work.

There’s a dashboard to confirm all is well ..

image

There is also an option to create a TCPIP listener which provides an address and DNS entry for the cluster.  If you set this up you can  connect directly to the cluster from any tool that can connect to SQL Server, in this case I have connected to the TechNet cluster from management studio in the same way I would connect to any other instance or cluster..

 image

However you can also connect directly to the primary or secondary as well and for a read only secondary that’s how you would do reporting.

I have a short (8 min) AlwaysOn screen cast if you want to know more or have a guide to help you try it yourself.

 

Finally be aware that this is not replacing clustering, mirroring or log shipping but it is only going to be available in SQL Server 2012 Enterprise edition.

Comments

  • Anonymous
    December 13, 2011
    Just wondering if it also allows for taking backups off the secondaries as to not load the primary one?

  • Anonymous
    December 20, 2011
    Priit - Yes, you can take Database & T-Log backups from the Secondaries. The backups must be COPY_ONLY.

  • Anonymous
    December 22, 2011
    An active secondary can be used for backups or production workloads such as real-time reporting

  • Anonymous
    March 02, 2012
    Quote:  “Databases and is completely transparent to an application”. Isn't this an overstatement? Applications even using SQL Server Native Client 11.0 (with Multisubnet Failover) will have to re-establish there sessions themselves no?

  • Anonymous
    April 05, 2012
    Cassings what I meant by this is that you don't have to change your application to use Native client as with mirroring, not that you won't loose a connection.  Also modern app need to be more tolerant of connection changes as app move further away form the datacentre where the database is. so sorry for the confusion Andrew

  • Anonymous
    April 17, 2012
    Hi Andrew, Can we use a Developer Edition SQL Server as a member of the Availabilty Group in order to provide our developers with an up to date read-only copy of our production server (without the Enterprise Licensing hit)?

  • Anonymous
    April 24, 2012
    Hey Andrew, This is probably the best article i have seen... mainly to overcome and understand the "no need for shared storage" Amazing! Thank you so much for sharing!

  • Anonymous
    June 25, 2012
    The comment has been removed

  • Anonymous
    March 21, 2013
    yes you will need all instances of ReadOnly replicas to be licensed.

  • Anonymous
    March 21, 2013
    yes you will need all instances of ReadOnly replicas to be licensed.

  • Anonymous
    July 07, 2013
    In Always-on, suppose i have 12 databases in Always on group and want to fail over only one database from primary to secondary. Is that possible in Always on ?? Thanks for such nice and very useful article.

  • Anonymous
    July 10, 2013
    The comment has been removed

  • Anonymous
    December 03, 2013
    The comment has been removed

  • Anonymous
    February 20, 2014
    The comment has been removed