SQL 2014 features?

Category: sql server mirroring

Question

kimdav111 on Thu, 12 Apr 2018 22:18:37


SQL 2014 standard has AlwaysOn feature?

What are the advantages of AlwaysOn feature for standard version other than the failover is FASTER?

We are planning a two node SQL cluster for HA at the primary site then a single node at the DR site.

Is the synchronization of DBs allow for any additional protection over have a DB which can failover to the other node in the HA scenario?

And is it correct that AlwayOn for SQL 2016 allows for a witness server at the primary site?  Does this imply there is automatic failover of the mirror?



dsk

Replies

Teige Gao on Fri, 13 Apr 2018 02:12:27


Hi kimdav111,

SQL Server 2014 Standard edition has limited support on Always-on, it only supports two-node Alwayson Failover Cluster Instances. It cannot support AlwaysOn Availability Groups, for more information, please refer to this document: https://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx, if what you mentioned is specified as Availability Group, it is not support.

>>What are the advantages of AlwaysOn feature for standard version other than the failover is FASTER?

SQL Server Always-on FCI provides instance level failover, but for standard edition, I think fast failover can be the main advantage. If it is enterprise edition which supports AG, we can set the read-only routine, which is extremely useful.

>>Is the synchronization of DBs allow for any additional protection over have a DB which can failover to the other node in the HA scenario?

We can use Database Mirroring combined with FCI, please refer to this document: https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-and-sql-server-failover-cluster-instances, but for standard edition, Database Mirroring only supports Safety Full mode.

>>And is it correct that AlwayOn for SQL 2016 allows for a witness server at the primary site?  Does this imply there is automatic failover of the mirror?

Which disaster recovery solution did you want to use? For database mirroring, when witness server is broken, it will not automatically fail-over.

Best Regards,

Teige