As I am rebuilding my lab from scratch, I decided to test and use SQL BAG. BAG stands for Basic Availability Groups.
I will use Windows Server 2019 and SQL Server 2017 Standard Edition.
The main difference with Always On Availability Groups is that you can only “protect” 1 database with a BAG, but on the other hand it only requires SQL Standard and not Enterprise.
For details regarding limitation, it’s here
Prerequisites
You will need :
- At least 2 domain-joined Windows Server 2019 (one for each Node) up to date
- Windows Server Enterprise or DataCenter Edition for Windows Failover Clustering Role
- SQL Server 2017 Standard Edition
- A Windows service account for SQL
- Azure Storage Account (for Cloud Witness)
Implementation
1. Install Failover Clustering Feature
- Within Server Manager, click on Manage > Add Roles and Features
- Click on Next
- Click on Next
- Click on Next
- Click on Next
- Select Failover Clustering and click on Add Features
- Click on Next
- Click on Install
- Click on Close and restart the server if needed
Note: The above steps need to be done on all other Nodes
2. Create Cluster
- Within Server Manager, click on Tools > Failover Cluster Manager
- Click on Create Cluster
- Click on Next
- Add your Nodes and click on Next
- Provide a name for the Cluster and an IP, then click Next
- Verify information and click on Next
- Click on Finish
3. Configure Computer account
The SQL Cluster will need special permissions on AD, here are the steps
- On a domain controller open Active Directory Users and Computers mmc
- Click on View > Advanced Features
- Make a right click on the OU where the SQL servers reside and select Properties
- Click on Security tab
- Click on Add
- Click on Object Types…
- Select Computers and click on OK
- Provide name of the previously created Cluster and click on OK
- Click on Advanced
- Select the added computer and click on Edit
- Select Create Computer objects permission and click on OK
- Click on OK
- Click on OK
Note: The SQL cluster computer account needs rights to create DNS records. The process of creating the Basic Availability Groups creates DNS “A” records for the Listeners.
- On your local domain, make a right click and select Properties
- Click on Security tab
- Click on Add
- Click on Object Types…
- Select Computers and click on OK
- Provide the name of the Cluster and click on OK
- Check the box Create all child objects and click on OK
4. Install SQL Server Standard 2017
- Launch Setup for source DVD
- Within Installation section, click on New SQL Server stand-alone installation or add features to an existing installation
- Click on Next
- Accept the license terms and click on Next
- Check the box Use Microsoft Update to check for updates and click on Next
- Select SQL Server Replication (this will include Database Engine Services) and click on Next
- Click on Next
- For SQL Server Database Engine Account Name, select the drop box and click on Browse
- Provide the name of the service Account and click on OK
- Provide the password for the service account and click on Collation tab
- Click on Customize
- Select option as above and click on OK
- Click on Next
- I added my Current User and the service account. Click on Next
- Click on Install
- Click on Close
- Click on Install SQL Server Management Tools
- Download and install the latest version of SQL Server Management Studio
- Click on Install
5. Configure AlwaysOn High Availability
- Open SQL Server 2017 Configuration Manager
- Select SQL Server, make a right click and select Properties
- Click on AlwaysOn High Availability tab
- Check the box Enable AlwaysOn Availability Groups and click on OK
- Click on OK
Note: The above steps need to be done on each Node
6. Configure Quorum
- Within Failover Cluster Manager console, select the Cluster, make a right click and select More Actions > Configure Cluster Quorum Settings
- Click on Next
- Select radio button Select the quorum witness and click on Next
- Select radio button Configure a cloud witness and click on Next
- Provide the account name, the account key and click on Next
- Click on Next
- Click on Finish
- Ensure Witness shows Cloud Witness
Our SQL Basic Availability Group is now created we will see in the next article how to create a BAG for Citrix WorkSpace Environment Management.
Stay tuned …
