Always On Availability Groups are a fundamental component of the availability story for SQL Server, and provide a robust disaster recovery solution as well. Availability Groups were first introduced in SQL Server 2012, replacing the prior Database Mirroring technologies. They enable a DBA to configure two SQL instances to host replicas of a set of databases, which can be kept exactly in sync, giving zero data loss guarantees, or near exactly in sync (async replication, which is optimal for geographically remote replicas). This technology has become the standard which the majority of critical production SQL Server instances leverage.<\/p>\n
SQL Server 2016 is making some significant improvements to the Always On Availability Groups set of features. There are a number of features, such as:<\/p>\n
These each deserve their own blog post, and I hope to get to them all. However, there is one feature area that I\u2019d like to focus on today because it opens up some interesting scenarios, and has been requested for a very long time.<\/p>\n
When Always On Availability Groups was introduced in SQL Server 2012, it represented a substantial advancement in the HA story over the previous technology, Database Mirroring (DBM). By adopting certain layers of Windows Server Failover Cluster (WSFC) functionality to use as infrastructure, the new solution is able to be much more performant, and to scale much better than the original DBM solution. Where DBM was limited to only two replicas, the Principal and the Mirror, Availability Groups could initially support five replicas (up to eight as of SQL Server 2014). Perhaps more significantly, where DBM established a relationship between one database and a copy of that database, Availability Groups, as the name implies, establishes a relationship between a set or group of databases, and replicas of that group of databases on one or more replicas. This means that for applications which access more than one database such as a SharePoint farm, we can now have all of the databases in the group move as a unit. We no longer need to worry about implementing complex scripting solutions to cause multiple databases to move as a unit.<\/p>\n
One of the additional restrictions that came with Availability Groups was the restriction that all nodes in the Availability Group must reside in the same Active Directory Domain.<\/p>\n
While this works well for a majority of our customers, there are some deployment patterns which cannot be met with current Always On Availability Groups\/Windows Server Failover Cluster solutions:<\/p>\n
In order to relieve this situation, SQL Server had two options: We could (once again) re-implement the functionality that we are currently getting from WSFC, or we could collaborate with the Windows Server Failover Cluster team to eliminate the single-domain restriction. The two teams met and worked for over a year to understand what the technical restrictions were that led to this limitation, and to envision what a solution that did not rely on AD for authentication and security would look like, and how it could be made usable.<\/p>\n
The result is that with the release of Windows Server 2016, Windows Server Failover Clusters will no longer require that all nodes in a cluster reside in the same domain. In fact, they will no longer require the nodes to live in any domain at all. You can now form a WSFC cluster from machines which are in workgroups. Because of this change, SQL Server 2016 is now able to deploy Always On Availability Groups in environments with:<\/p>\n
This opens up a great number of new scenarios to customers, and removes previous blocks preventing migration from the deprecated Database Mirroring technology to AlwaysOn Availability Groups.<\/p>\n
Although many customers will see this change as a great step forward in openness and flexibility, the AD infrastructure does make many operations simpler, so in order to operate without it we must compensate for these changes.<\/p>\n
In order to set up Windows Server Failover Clustering in an environment without AD security, we must provide a means to securely configure the cluster and provide for secure communications within the cluster. WSFC as of Windows Server 2016, creates self-signed certificates and uses these to secure intra-cluster authentication. In environments with AD security, we leverage that security infrastructure in order to establish the secure communication links. Without AD, we must take an extra step. In order to establish the cluster in environments without AD, WSFC requires you to set up synchronized admin accounts on all proposed cluster nodes. That means that there must be an account with the same name and the same password, that is in the Administrators group, on each node in the cluster. You must also be able to resolve the hostnames of all nodes in the cluster so that they can find each other.<\/p>\n
As of now, WSFC cannot be managed using the UI in environments without AD security, so you will need to form and manipulate the cluster using PowerShell cmdlets. Fortunately, the minimum of commands for our purposes is very simple.<\/p>\n
PS C:\\> New-Cluster -Name \u201cMyCluster\u201d -Nodes Node1,Node2,Node3,Node4 -AdministrativeAccessPoint DNS<\/span><\/p>\n That\u2019s it! It really is that simple once the basics are set up.<\/p>\n Node A<\/span><\/b><\/p>\n <\/span>\n<\/td>\n Node B<\/span><\/b><\/p>\n <\/span>\n<\/td>\n Node C<\/span><\/b><\/p>\n <\/span>\n<\/td>\n<\/tr>\n EXEC CreateEndpointCert \u2018\\\\NodeB\\MyShare\u2019 ‘1R3@llyStr0ngP@ssw0rd!’<\/span><\/p>\n <\/span>\n<\/td>\n EXEC CreateEndpointCert \u2018\\\\NodeB\\MyShare\u2019 ‘1R3@llyStr0ngP@ssw0rd!’<\/span><\/p>\n <\/span>\n<\/td>\n EXEC CreateEndpointCert \u2018\\\\NodeB\\MyShare\u2019 ‘1R3@llyStr0ngP@ssw0rd!’<\/span><\/p>\n <\/span>\n<\/td>\n<\/tr>\n EXEC InstallEndpointCert \u2018NodeB\u2019 ‘D1ff3rentStr0ngP@ssw0rd!’<\/span><\/p>\n <\/span>\n<\/td>\n EXEC InstallEndpointCert \u2018NodeA\u2019 ‘D1ff3rentStr0ngP@ssw0rd!’<\/span><\/p>\n <\/span>\n<\/td>\n EXEC InstallEndpointCert \u2018NodeA\u2019 ‘D1ff3rentStr0ngP@ssw0rd!’<\/span><\/p>\n <\/span>\n<\/td>\n<\/tr>\n EXEC InstallEndpointCert \u2018NodeC\u2019 ‘D1ff3rentStr0ngP@ssw0rd!’<\/span><\/p>\n <\/span>\n<\/td>\n EXEC InstallEndpointCert \u2018NodeC\u2019 ‘D1ff3rentStr0ngP@ssw0rd!’<\/span><\/p>\n <\/span>\n<\/td>\n EXEC InstallEndpointCert \u2018NodeB\u2019 ‘D1ff3rentStr0ngP@ssw0rd!’<\/span><\/p>\n <\/span>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n If you have set up the instances with synchronized service accounts, from this point on, you can create and manipulate the Availability Group much like any other. You can script the creation, or use the New AG wizard to create the AG. You manipulate it like any other, and monitor it using the Dashboard, DMVs or other SQL tools.<\/p>\n CreateEndpointCert<\/strong><\/p>\n ————————————————————————————————–<\/span> CREA<\/span>TE PROCEDURE<\/span> CreateEndpointCert<\/span> \u00a0\u00a0\u00a0 DECLARE<\/span> @DynamicSQL varchar<\/span>(<\/span>1000);<\/span><\/span> \u00a0— Only create a master key if it doesn’t already exist<\/span><\/p>\n \u00a0SELECT<\/span> @HasMasterKey =<\/span> is_master_key_encrypted_by_server from<\/span> sys.databases<\/span> where<\/span> name =<\/span> ‘master’<\/span><\/span> \u00a0–Create the certificate to authenticate the endpoint<\/span><\/span> \u00a0\u00a0\u00a0 –Create the database mirroring endpoint authenticated by the certificate.<\/span><\/span> \u00a0\u00a0\u00a0 –Back up the certificate to a common network share for import into other nodes in the cluster<\/span><\/span> InstallEndpointCert<\/strong><\/p>\n ————————————————————————————-<\/span> CREATE PROCEDURE<\/span> InstallEndpointCert<\/span>\n\n
\n \n <\/span><\/p>\n \n <\/span><\/p>\n \n <\/span><\/p>\n \n \n <\/span><\/p>\n \n <\/span><\/p>\n \n <\/span><\/p>\n \n \n <\/span><\/p>\n \n <\/span><\/p>\n \n <\/span><\/p>\n \n \n <\/span><\/p>\n \n <\/span><\/p>\n \n <\/span><\/p>\n Finishing the Setup<\/h1>\n
Scripts<\/em><\/h1>\n
— This procedure automates the creation of a local certificate and the endpoints required for a domainless AG.<\/span>
— Parameters are the strong password for the cert, and the location of a share which receives the backup of the cert.<\/span>
— The share should be accessible to all nodes in the AG, as they will need to read the certs for each other.<\/span>
— The procedure also creates the endpoint based upon the newly created cert.<\/span>
—<\/span>
— EXEC CreateEndpointCert ‘\\\\Myserver\\Myshare’ ‘1R3@llyStr0ngP@ssw0rd!’<\/span>
—————————————————————————————————<\/span><\/p>\n
\u00a0@ShareName SYSNAME<\/span> ,<\/span><\/span>
\u00a0@StrongPassword SYSNAME <\/span><\/span>
AS BEGIN<\/span>
\u00a0<\/span>
\u00a0–This must be executed in the context of Master<\/span><\/span>
\u00a0IF<\/span> (<\/span>DB_NAME<\/span>() <><\/span> ‘master’<\/span>)<\/span><\/span>
\u00a0BEGIN<\/span><\/span>
\u00a0\u00a0PRINT<\/span> N’This SP must be executed in master.\u00a0 USE master and then retry.’<\/span><\/span>
\u00a0\u00a0RETURN<\/span> (<\/span>-1)<\/span><\/span>
\u00a0END<\/span><\/span><\/p>\n
\u00a0\u00a0\u00a0 DECLARE<\/span> @CompName varchar<\/span>(<\/span>250);<\/span><\/span>
\u00a0DECLARE<\/span> @HasMasterKey INT<\/span>;<\/span><\/span>
\u00a0\u00a0\u00a0 SELECT<\/span> @CompName = CONVERT(<\/span>SysName<\/span>,<\/span> SERVERPROPERTY(<\/span>‘MachineName’<\/span>));<\/span><\/span><\/p>\n
\u00a0IF<\/span> (<\/span>@HasMasterKey = 0)<\/span><\/span>
\u00a0BEGIN<\/span><\/span>
\u00a0–Create a MASTER KEY to encrypt the certificate.<\/span>
\u00a0\u00a0SET<\/span> @DynamicSQL =<\/span> CONCAT<\/span>(<\/span>‘CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<\/span> ,<\/span>\u00a0 QUOTENAME<\/span>(<\/span>@StrongPassword,<\/span> ””<\/span>));<\/span><\/span>
\u00a0\u00a0EXEC<\/span> (<\/span>@DynamicSQL)<\/span><\/span>
\u00a0END<\/span><\/span><\/p>\n
\u00a0\u00a0\u00a0 SET<\/span> @DynamicSQL =<\/span> CONCAT<\/span>(<\/span>‘CREATE CERTIFICATE ‘<\/span>,<\/span> QUOTENAME<\/span>(<\/span>@CompName +<\/span> ‘-Cert’<\/span>),<\/span> ‘ WITH SUBJECT = ‘<\/span>,<\/span> QUOTENAME<\/span>(<\/span>@CompName,<\/span> ””<\/span>)) ;<\/span><\/span>
\u00a0\u00a0\u00a0 EXEC<\/span> (<\/span>@DynamicSQL);<\/span><\/span><\/p>\n
\u00a0\u00a0\u00a0 SET<\/span> @DynamicSQL =<\/span> <\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONCAT<\/span>(<\/span>‘CREATE ENDPOINT Endpoint_Mirroring<\/span><\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STATE = STARTED<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) <\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE ‘<\/span>,<\/span>QUOTENAME<\/span>(<\/span>@CompName +<\/span> ‘-Cert’<\/span>),<\/span> ‘ , ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)’<\/span>);<\/span><\/span>
\u00a0\u00a0\u00a0 EXEC<\/span> (<\/span><\/span>@DynamicSQL);<\/span><\/span><\/p>\n
\u00a0\u00a0\u00a0 SET<\/span> @DynamicSQL =<\/span> CONCAT<\/span>(<\/span>‘BACKUP CERTIFICATE ‘<\/span>,<\/span>QUOTENAME<\/span>(<\/span>@CompName +<\/span> ‘-Cert’<\/span>),<\/span>‘ To FILE = ‘<\/span>,<\/span> QUOTENAME<\/span>(<\/span> @ShareName +<\/span> ‘\\SQL-‘<\/span> +<\/span> @CompName +<\/span> ‘.cer’<\/span>,<\/span> ””<\/span>));<\/span><\/span>
\u00a0\u00a0\u00a0 EXEC <\/span>(<\/span>@DynamicSQL);<\/span><\/span>
END<\/span>
GO<\/span><\/p>\n
—\u00a0 This procedure assumes that a certificate has been created on another node in the AG, and backed up to a common network share.<\/span>
—\u00a0 Parameters:<\/span>
—\u00a0\u00a0\u00a0 @CompName – The name of the computer whose certificate needs to be installed here.\u00a0 i.e. the other replica that this node needs to communicate with.<\/span>
—\u00a0\u00a0\u00a0 @ShareName – A common network share to which certificates were backed up from each machine in the cluster\/AG.<\/span>
—\u00a0\u00a0\u00a0 @StrongPassword – A strong password to be used for the login created to log in on behalf of the endpoint on the other node.<\/span>
—<\/span>
—\u00a0 This procedure assumes that each node has run CreateEndpointCert and that all cert backup files reside on the share pointed to by the second parameter.<\/span>
—\u00a0 The procedure creates a login and a user for the remote machine, and then created a certificate to authorize the user when the certificate is used as authentication from the remote endpoint.<\/span>
—————————————————————————————<\/span><\/p>\n
\u00a0\u00a0\u00a0 @CompName SYSNAME<\/span>,<\/span><\/span>
\u00a0@ShareName SYSNAME<\/span>,<\/span><\/span>
\u00a0@StrongPassword SYSNAME<\/span><\/span>
AS BEGIN<\/span>
\u00a0\u00a0\u00a0