Microsoft SQL Server Always-On

What is the Microsoft SQL Server Always-On Functionality?

SQL Server Always-On is a High-Availability feature of SQL Server that was introduced in SQL Server 2012 – Enterprise Edition.

Now Always-On might be a bit of a misleading term in this context, as it is not really guaranteeing to be always on, merely reducing the risk of being off! So how does it do this.  Put simply, it allows you to have other copies of your database on servers elsewhere, such that if you server goes pop, then within a few seconds the other servers take the strain.

SQL Server Always-On is a High-Availability feature of SQL Server

Synchronous or Asynchronous Replication?

Before we answer this question, let’s first explain the difference. With a synchronous always on replication node, the primary SQL server does not tell the application that it is done until both the primary and secondary databases are updated. In contrast, with asynchronous replication, the primary tells the application it’s done when the primary is done, and assumes the secondary will just catch up when it can.

With synchronous replication, there can be a performance hit, especially if the secondary is on a different site, as it needs to effectively write to and update both the primary and secondary databases before the application can continue, however it does ensure that the 2 are kept in sync. With asynchronous, the is no performance hit because the primary does not care if the secondary is ready, however potentially if the primary falls over, then the secondary could be slightly out of date.

Synchronous or Asynchronous Replication? What is the Difference?

Always-On Availability Groups

There are instances where if one database fails (failover), then you would want a number of related datasets to also failover to their secondaries. This is because the databases may get written too by the same set of applications and therefore you need the complete set of databases to be in a coherent state (e.g. a NAV database and connected Dynamics CRM database or webshop). To ensure this happens, you put the related databases in the same Always-On Availability Group. Databases can only be a member of one availability group.

SQL always on Availability groups like peas in a pod

SQL Server Enterprise Always-On

The Always-on functionality with introduced with SQL Server 2012 Enterprise Edition, allows always on with up to 4 secondary servers of which 2 can be synchronous and of those 2 one can be set for automatic failover. Additionally, the secondaries can be setup to be active, thus allowing them to be used for read transactions, particularly useful for reporting against the asynchronous secondaries, and you can perform backups against the secondary, further reducing the load on the primary.

SQL Server Standard Always-On

Until SQL Server 2016, the Always-On functionality was an enterprise only feature, this made it prohibitively expensive for most. SQL Server 2016 saw database mirroring deprecated, and this was replaced in the Standard Version with Basic Availability Groups. Basic Availability Groups have the following limitations:
•All instances must be SQL Server 2016 or later
•Only supports a single database (although you can have multiple Basic Availability Groups)
•The secondary must be passive … so you can’t read from it or back it up.

Licensing Always-On

If you think the above is fabulous, sounds too good to be true, where’s the catch? Well the catch is with the licensing, the secondaries need to be licensed too! Which if you are running a SQL Server Enterprise license, could cost you more than a few pounds. However, there is a silver lining. If the secondary is passive, in that it has no connections to it other than for the replication, then it does not need to be separately licensed. This however means that you cannot use the secondaries for reporting purposes or to take the read-load.

However, for SQL Server Standard Always-On, the secondary has to be passive, therefore there is no extra licensing cost involved. Additionally, if required, the secondary can be configured to be a SQL Azure database. These 2 factors mean that an offsite high available solution is now affordable for the SME market.

Please talk to us if you would like to find out how we can help set this up for your Dynamics NAV or Dynamics CRM database.

Contact Us

Tom Jenkins

Tom is one of the founding directors of Dynamics Consultants. He has worked on ERP / CRM systems since 1995, initially as an end user and later as a developer/consultant. Before founding Dynamics Consultants, Tom worked in a management role for a machinery importer / reseller, where his work included inventory management and purchase control, systems development, and IT project management.