Implement a SQL Server 2016 Availability Group without Active Directory Part 2
By: Edwin Sarmiento | Comments (7) | Related: 1 | 2 | More > Availability Groups
In a previous tip on Implementing Database Mirroring in SQL Server 2005 across domains, we have seen how we can configure Database Mirroring to achieve local high availability for SQL Server databases that are not joined to an Active Directory domain. We need to upgrade our SQL Server 2008 R2 databases before extended support ends. However, we do not have an Active Directory domain in our environment. How do we go about it?
To continue this series on Implement a SQL Server 2016 Availability Group without Active Directory, we will look at creating the SQL Server Availability Group. In Part 1, you learned how to configure the security requirements for authenticating the replicas of the Availability Group. This tip will walk you through the creation of the Availability Group and its corresponding listener name.
Implementing SQL Server Availability Groups without Active Directory
For reference, a review of the high-level steps is shown below. This tip will continue on Step #12.
- Install SQL Server 2016 using the built-in service account
- Enable SQL Server AlwaysOn Availability Groups feature
- Create a database master key on the primary replica
- Create the certificate that will be used to encrypt the Availability Group endpoint
- Create the Availability Group endpoint for the primary replica using the certificate for authentication
- Export the certificate to a file
- Copy the certificate file to the secondary replicas
- Create a login on the primary replica for use by the secondary replicas
- Create a user for the login
- Associate the certificate created in Step #4 with the user
- Grant the CONNECT permission on the login
- Create the Availability Group using T-SQL
- Create an Availability Group listener name
Step #12: Create the Availability Group using T-SQL
It’s tempting to use the New Availability Group Wizard to create the Availability Group. However, if you do so, you won’t be able to modify the parameter for the login used to connect to the replicas. By default, the wizard will use the SQL Server service account credentials to connect to the replicas. But since the service accounts are all local user accounts (and that they are built-in accounts that Windows manages), you won’t be able to know what the password is. You can work around this limitation by creating a local user account on all of the failover cluster nodes with the same name and password as described in this previous tip. You can then use the local user account as the SQL Server service account credential.
For this example, the login created in Step #8 will be used.
Use the T-SQL script below to create the Availability Group on the SQL Server instance that you want to configure as the primary replica. It is assumed that you already have a database named Northwind in your SQL Server instance configured in FULL recovery model with at least one full database backup.
--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS :CONNECT WSFC2016-WG1 USE [master] GO CREATE AVAILABILITY GROUP [Workgroup_AG] WITH ( AUTOMATED_BACKUP_PREFERENCE = PRIMARY, DB_FAILOVER = ON, DTC_SUPPORT = NONE ) FOR DATABASE [Northwind] REPLICA ON N'WSFC2016-WG1' WITH ( ENDPOINT_URL = N'TCP://WSFC2016-WG1.TESTDOMAIN.COM:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE ( ALLOW_CONNECTIONS = NO ) ), N'WSFC2016-WG2' WITH ( ENDPOINT_URL = N'TCP://WSFC2016-WG2.TESTDOMAIN.COM:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE ( ALLOW_CONNECTIONS = NO) ), N'WSFC2016-WG3' WITH ( ENDPOINT_URL = N'TCP://WSFC2016-WG3.TESTDOMAIN.COM:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SECONDARY_ROLE ( ALLOW_CONNECTIONS = NO) ); GO
The following parameters and their corresponding values are used to create the Availability Group
- Workgroup_AG: the name of the Availability Group
- AUTOMATED_BACKUP_PREFERENCE = PRIMARY: specifies preference on the primary replica when performing automated database backups
- DB_FAILOVER = ON: specifies database-level health detection action; this will automatically failover the Availability Group if one of the databases on the primary replica is offline
- DTC_SUPPORT = NONE: no DTC support
- FAILOVER_MODE = AUTOMATIC: specifies automatic failover of the Availability Group
- AVAILABILITY_MODE = SYNCHRONOUS_COMMIT: specifies synchronous mode Availability Group replication
- SECONDARY_ROLE (ALLOW_CONNECTIONS = NO): specifies secondary replica databases to only be on standby and not used for read-only workloads
After creating the Availability Group on the primary replica, proceed to join the secondary replicas to the Availability Group.
--Run this on the secondary replica/WSFC2016-W2. Just to be sure, enable SQLCMD mode in SSMS --Join secondary replica to the Availability Group :Connect WSFC2016-WG2 ALTER AVAILABILITY GROUP [Workgroup_AG] JOIN; GO --Run this on the secondary replica/WSFC2016-W3. Just to be sure, enable SQLCMD mode in SSMS --Join secondary replica to the Availability Group :Connect WSFC2016-WG3 ALTER AVAILABILITY GROUP [Workgroup_AG] JOIN; GO
After joining the secondary replicas to the Availability Group, proceed to add the database on the secondary replicas to the Availability Group. This is the Join Only option in the Select Initial Data Synchronization dialog box when you run the New Availability Group Wizard.
At this point, it is assumed that you’ve already taken a full and log backup of the database on the primary replica and restored them in the NORECOVERY mode on the secondary replicas. If you haven’t done so, go ahead and do that now before proceeding.
--Run this on the secondary replica/WSFC2016-W2. Just to be sure, enable SQLCMD mode in SSMS -- Add database to Availability Group on secondary replicas :Connect WSFC2016-WG2 ALTER DATABASE [Northwind] SET HADR AVAILABILITY GROUP = [Workgroup_AG]; GO --Run this on the secondary replica/WSFC2016-W3. Just to be sure, enable SQLCMD mode in SSMS --Add database to Availability Group on secondary replicas :Connect WSFC2016-WG3 ALTER DATABASE [Northwind] SET HADR AVAILABILITY GROUP = [Workgroup_AG]; GO
You can view the Availability Group dashboard for the state and configuration information.
A few things to consider when implementing Availability Groups – be it for those with Active Directory or without.
- Dedicated network adapter for Availability Group replication traffic. Most of the examples available online use a single network adapter configuration. Some might say that they use multiple physical network adapters that are teamed (using NIC teaming) and presented to the operating system as a single network adapter. Remember that production traffic will go thru the same network adapter. You also have to consider the cluster heartbeat communication and, for this case, the Availability Group replication traffic. This is the reason why all of the examples I use for failover clustering have a minimum of two (2) network adapters. A future tip will cover how to configure a dedicated network adapter for Availability Group replication traffic.
- Production workload on secondary replicas. Reporting and read-only workloads are not the only ones considered as production workloads on secondary replicas. Database backups are also included. DBCC CHECKDB, too. Running maintenance routines on secondary replicas require that you license those instances. Now, I am not going to dispute the arguments around running consistency checks on all Availability Group replicas. They are all valid. But valid arguments should be evaluated against the cost incurred to the business, in this case, licensing cost.
- Quorum configuration. This is something that needs to be emphasized every time a SQL Server failover clustered instance or Availability Group is implemented. I consider the quorum to be the most complicated, most confusing yet the most important aspect of a Windows Server Failover Cluster. This tip covered the supported quorum configuration for an Availability Group without an Active Directory domain. The goal is to always have majority of votes. If quorum is lost, the Availability Group goes offline. This may not be obvious if the replicas are standalone instances since the system databases are not part of the Availability Group. Configure properly and monitor extensively.
- Synchronous versus Asynchronous. The decision to use either synchronous or asynchronous replication should be based on recovery objectives (RPO/RTO) and service level agreements (SLA). The choice to use asynchronous over synchronous is often made based on performance SLAs, not recovery objectives. Clearly and realistically define recovery objectives and SLAs and let them dictate the appropriate configuration.
Step #13: Create an Availability Group listener name
Once the Availability Group has been created and validated, proceed to create the listener name. Unlike when creating the Availability Group, you can create the listener name using SQL Server Management Studio.
- From within Object Explorer, expand the AlwaysOn High Availability node and the Availability Groups node
- Right-click the Availability Group Listeners node and select the Add Listener command. This opens the New Availability Group Listener dialog box
- In the new Availability Group Listener dialog box, specify the listener name and the port number. In the example below, port 1433 is used for simplicity.
- In the Network Mode: drop-down list, select Static IP and click the Add button.
- In the Add IP Address dialog box, select the appropriate subnet and provide a virtual IP address for the corresponding listener name. Click OK.
- Click OK to create the listener name.
Alternatively, you can use the T-SQL script below to create the Availability Group listener name.
--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS :CONNECT WSFC2016-WG1 USE [master] GO ALTER AVAILABILITY GROUP [Workgroup_AG] ADD LISTENER N'Workgroup_AG_LN' ( WITH IP ( ) , PORT=1433); GO
Implementing a SQL Server Availability Group with an Active Directory domain requires proper planning and thorough documentation. Use this tip series as a guide to help you successfully provide high availability to your SQL Server databases using Availability Groups.
- Read the previous tips – Part 1 and Part 2 - on deploying a Windows Server 2016 Failover Cluster without Active Directory
- Review the previous tips on SQL Server AlwaysOn Availability Group Configuration
- Review the previous tip on Implementing Database Mirroring in SQL Server 2005 across domains
About the author
View all my tips