In this installment we will perform several tasks needed to successfully configure a SQL 2017 Always-On Availability Group (AAG). Tasks include Windows firewall rules (if you use the Windows firewall), configuring a replication folder, creating a small sample database, and finally configuring the SQL AAG.
We will need to create two new AD-based computer objects for our AAG. One is for the AAG itself, and the other is for the AAG listener (this is what applications use for SQL connections). SQL supports multiple AAGs per instance, so it's important to establish a naming standard for the required computer objects. You can use whatever you want, but for this series I'm using:
Availability Group Name: <Cluster Name>-AG1
Availability Group Listener: <Cluster Name>-AG1L
Windows Firewall
If you are using the built-in Windows firewall and don't already have rule sets applied for a SQL AAG server, then follow this procedure. If you don't have the Windows firewall active, you can skip this section. If you are using a third-party firewall or micro-segmentation, then take a peek at the script to see what ports/protocols need to be open on both SQL nodes.
1. Download my SQL 2017 Always-on firewall script from here on each SQL node. Open the script with your favorite text editor and validate the SQL binary paths match your configuration. If you are following on exactly with this series, no changes are needed.
2. On the first SQL VM open an elevated command prompt and type:
SQL2017-AAG-Firewall.cmd <Second SQL server IP>
3. Repeat the process on the second SQL server, using the IP address of the first SQL server.
Create Test Database
Note: If you already have a database you want to use with an AAG, you can use that instead of creating a dummy database. However, you MUST perform a full backup of that database at this point, or the AAG wizard won't let you use it.
1. Launch SQL Server Manager, right click on Databases and select New Database. Create a new database called SeedDB and leave all other parameters at their default.
2. Right click on the SeedDB, select Tasks, then Backup.
3. Leave all defaults and click OK.
SQL 2017 AAG Configuration
Note: If you are using a production database and know what you are doing, modify these steps as needed. This is just one example of how to configure an AAG.
1. Open SQL Server Configuration Manager and locate the SQL Server service. Select the AlwaysOn High Availability tab. Check the box to enable AlwaysOn.
2. Restart the SQL service and exit out of SQL Server Studio. Repeat this configuration on the other SQL server node.
3. Open Active Directory Users and Computers (ADUC) and create a new computer object using the cluster name with a suffix of –AG1 (e.g. SQL2017CLA-AG1). Change the description of the object to SQL Cluster <Cluster Name> Availability Group.
4. On the first SQL server cluster node in SQL Server Management Studio navigate to AlwayOn High Availability, right click and select New Availability Group Wizard.
Note: If you get a message about needing to enable AAGs, you probably didn't close SQL studio after you enabled it or you didn't restart the SQL service.
3. For the availability group name use the format of: <Cluster Name>-AG1 (e.g. SQL2017CLA-AG1). This should match the AD object name you just created. Leave the rest of the defaults on this page.
4. On the Select Databases screen the SeedDB database should be listed, and it should meet the prerequisites. Check the box next to the database and click Next.
5. On the Specify Replicas screen add the second SQL server cluster node. Configure the automatic failover and synchronous commit options as shown below.
6. On the Listener tab create an availability group listener. For the DNS name, use the same name as the AG (e.g. SQL2017CLA-AG1) but with an “L” suffix added (e.g. SQL2017CLA-AG1L), using port 1433. Assign an IP address on the same subnet as the SQL server, or use DHCP mode like I did.
7. On the Select Data Synchronization step select Automatic Seeding,
8. Validate that all checks passed and proceed with the configuration.
9. In SQL Server Management Studio right click on the AG and select Show Dashboard. Verify all objects are in a healthy state, as shown below.
Summary
This installment we finally got to the process of setting up our AlwaysOn Availability Group (AAG). As you can see, the process is fairly straight forward if you have a database that's ready to be replicated. A single AAG can support multiple databases, so you don't need a new AAG for every database.
SQL 2017 Installation Series Index
SQL 2017 Always-on AG Pt. 1: Introduction
SQL 2017 Always-on AG Pt. 2: VM deployment
SQL 2017 Always-on AG Pt. 3: Service Accounts
SQL 2017 Always-on AG Pt. 4: Node A SQL Install
SQL 2017 Always-on AG Pt. 5: Node B SQL Install
SQL 2017 Always-on AG Pt. 6: Cluster Configuration
SQL 2017 Always-on AG Pt. 7: File Share Witness
SQL 2017 Always-on AG Pt. 8: AAG Setup
SQL 2017 Always-on AG Pt. 9: Kerberos (Coming)
SQL 2017 Always-on AG Pt. 10: SSL Certificates (Coming)
SQL 2017 Always-on AG Pt. 11: Max Mem & Email Alerts (Coming)
SQL 2017 Always-on AG Pt. 12: Maintenance Jobs (Coming)
One issue I encountered when configuring the listener was a lack of permissions on the OU. My first attemp failed and I had to add the cluster computer object (SQL2017CLA$ in the case of this tutorial) with “Create Computer objects” permissions on the same OU that contained all of my cluster objects. Right-click OU -> Properties -> Security -> Advanced -> Add -> Select a principal (again, making sure to include Computers in the object types) -> Select ‘Create Computer objects’, leaving all other pre-selected alone. With a vanilla AD deployment, there probably won’t be any issues for the majority… Read more »
Meaning Web applications has to connect to AAG listener to get their application connected to SQL DB ?
Hope you are still monitoring this board… In your discussion above you mention a firewall script, however, the link isn’t working. https://www.dropbox.com/s/cnkjys2z7a16jp5/SQL2017-AAG-Firewall.cmd?dl=0
Is this script available or would you list what is in the script?
thanks for the great information.
It should work…I checked the link and it opened in a private browser window. I did refresh the link just in case though.
Hi Derek, thank you very much for all your work!
There’s a typo in the Firewall-script, double Binn, 3rd rule:
\Binn\Binn\sqlservr.exe
Great article. Any chance you could extend this series of articles to about the design and implementation of an availability group extended into cloud or distributed availability groups across on-prem and cloud?