Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2012 – Always On

SQL Server 2012 – Always On

  • Comments 14
  • Likes

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
  • Just wondering if it also allows for taking backups off the secondaries as to not load the primary one?

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

  • An active secondary can be used for backups or production workloads such as real-time reporting

  • 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?

  • 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

  • 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)?

  • 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!

  • Hi Andrew,

    It is really a nice article explaining everything that I need to know.

    In SharePoint 2010, We can specify failover mirroring server name, when creating a new web application. In the above scenario, I understand that we don't really need SQL Clustering and Mirroring; instead we can simply use Always On (Availability Group) to decide whether we need high availability or DR, if both then we will create two separate secondary nodes. One with Always On automatic failure (i.e. Synchronous) and 2nd with manual option.

    What about licensing? Do we need SQL Server 2012 Enterprise license both Primary and all secondary nodes.

    Thanks

    Adnan Ahmed

    www.sp-blogs.com

  • yes you will need all instances of ReadOnly replicas to be licensed.

  • yes you will need all instances of ReadOnly replicas to be licensed.

  • 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.

  • Pankaj.  No you cant' do that and actually that's the point of Availability Groups you are keep the group in Sync.  We still have mirroring on a database by database basis and you could put one of the twelve into its own availability group if that's what you need

    Glad you found this useful

    Andrew

  • We use ADO, COM+ components, which use DTC transactions.  we tried using Always on, but found that MS DTC is not supported with Always on.  is this going to change, or does this mean, Always On is completely out of scope.

  • I have a team trying to set up Always on over a layer 2 long haul connection that spans frome east to west coasts of the US. Anytime maintenance is done anywhere along those layer 2 circuits spanning tree and firewall fail overs. These failovers generally take 30 seconds and breaks the Always On feature. This causes the team to have to wake up (because maintenance is done in the middle of the night) and restart the Always On feature. Is there a tolerance level that can be set to greater than 30 seconds so the Always on feature doesn't break over the WAN?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment