SQL Server Always On
Prerequisites
Before implementing your AlwaysOn Availability Group (AG), make sure you have everything in your environment ready to go. There are several prerequisites that need to be addressed to ensure a successful deployment.
Windows
- Do not install AlwaysOn on a domain controller
- The operating system must be Windows 2012 or later
- Windows Server Failover Cluster (WSFC) must be installed on every replica
SQL Server
- Each server (replica) must be a node in the WSFC
- No replica can run Active Directory services
- Each replica must run on comparable hardware that can handle identical workloads
- Each instance must run the same version of SQL Server, and have the same SQL Server collation
- The account that runs SQL Services should be a domain account
Network
- It is recommended to use the same network links for communication between WSFC nodes and AlwaysOn replicas
- Give full Permission your sql host machine.
Add Windows Failover Cluster (WSFC) to each replica
On each replica, open Server Manager > click Add Roles & Features > select Add Failover Clustering > click Install. Proceed through the wizard, and when you get to the Select Features page, select the Failover Clustering checkbox.
If you do not already have .NET Framework 3.5.1 or greater installed on your server, select that checkbox as well to install. (If you do need to install the .NET Framework, you will need to reboot the server after installing).
Proceed next through the wizard and click Install to finish the wizard. You will need to do this on every replica in your AG.
Configure WSFC on primary replica
From Administrative Tools, open Failover Cluster Manager and click on Validate Configuration
Add the names of all the SQL Servers you want to configure as replicas in your AlwaysOn group.
On the Testing Options page, click Run all tests (recommended). It is normal to see some warning messages. Make sure to review the warnings and correct anything necessary.
After the validation and summary is complete, the Create Cluster Wizard will open. In the Access Point for Administering the Cluster dialog box, enter the virtual cluster name (not the server or instance name), and the virtual IP address of your cluster.
Proceed next through the wizard, and your cluster will be created. The secondary nodes will be added to the cluster, and your cluster should now show up on all replicas (through Failover Cluster Manager). You do not have to go through these steps on the other replicas…you’re all done with setting up the cluster.
Install AlwaysOn
To install an AG on SQL Server 2019, we need to configure the SQL Server instance and then add the AG.
Configure SQL Server
Assuming you are installing a traditional AlwaysOn Availability Group (and not a Basic Availability Group), make sure you have installed Enterprise Edition of SQL Server onto each replica, and install it as stand-alone instances. On each replica, open the SQL Server Configuration Manager. Right click on SQL Server Services and open the Properties dialog box. Navigate to the AlwaysOn High Availability tab and select the Enable AlwaysOn Availability Groups checkbox. Both side sql server
Restart the SQL Server Service after making these changes. Complete these steps on all your replicas.
Create an Availability Group
First, make sure all databases are in Full Recovery mode. Remove these databases from any transaction log backup maintenance during the installation of the Availability Group. You can add them back later. You do not want log backups happening on these databases while the AG is being created.
Next, take full and log backups of all databases you want added to the AG:
On your primary replica, open SQL Management Studio (SSMS) and expand the AlwaysOn High Availability folder. Right click on Availability Groups and select New Availability Group Wizard… to open the wizard:
First, specify your AlwaysOn group name. Name it something descriptive and unambiguous. Also, select the checkbox for Database Level Health Detection. Starting in 2016, failover will occur not only if the instance is in trouble, but also if one or more of your databases is in trouble. This is not a default setting; however, you must specify this when creating your Availability Group.
Next, you will select the databases you want to include in your AG. All the databases in your instance will show up in this list…you don’t have to include all of them in your group… select only the ones to be included in the AG.
Next to each database is a blue link that signifies whether your database is ready to be included into your group or not. If the link does not say ‘Meets prerequisites’, then you can click on the link to get a more in-depth explanation of what you need to do. Correct any discrepancies, and then select the databases to include in the AG.
Next, is the Specify Replicas page where you will add the replicas to be included in your AG. Add and connect the replicas by clicking the Add Replica… button.
For each replica, you will need to specify whether you want Automatic or Manual Failover, Synchronous or Asynchronous Data Replication, and what type of connections you will allow your end users to connect with.
On this Specify Replicas page, there are several tabs at the top. The second tab is the Endpoints tab. On this tab verify that the port number is 5022. If you have more than one instance on your server, you might need to create another endpoint.
Next tab is the Backup Preferences tab. This is where you will choose where you want your backups to occur, and how you prioritize which replica will run your backups.
Listener
- Add dns record as per image
- Create host in ad and give full permission with cluster member in security
The next tab in the page is the Listener tab. Here you will select the Create an availability group listener button. Enter the DNS name, which is the name that will be used in your application connection string. Enter port number 1433, and enter the IP address for your listener. This should be an unused IP address on your network.
The last tab in the Specify Replicas page is Read-Only Routing. This is used when you want SQL Server to direct read-only connections to a secondary replica. This feature must have a routing URL and a read-only routing list. Within the wizard you can specify the routing URL, however you cannot specify the routing list at this point. To specify the routing list, you must open the properties of the AlwaysOn group after it has been created
Fix Error: Job for mysql.service failed
The next page in the wizard is the Select Initial Data Synchronization page. Here is where you will join your databases to the AG. Select the Automatic seeding option for SQL to automatically create the databases on your secondary replicas. Make sure your data and log file paths are identical on all replicas.
Next, ensure that your Validation checks return successful results. If you get any errors, you need to stop and correct these before proceeding.
In the Summary page, verify that all your configuration settings are correct, and click Finish. The Results page will show the progress of the installation. Verify that all tasks have completed successfully.
After the results are complete, and everything has finished successfully, you can now see the Availability Group created in SSMS. Based on my entries, I see these values:
- The Availability Group Name: SQLITF
- All the Replicas, and whether they are primary or secondary
- All the Databases included in the AlwaysOn group
- The Listener created for the group.
This AG will also be visible on all the secondary replicas as well.
SQL Management Studio (SSMS) provides a dashboard tool to monitor the current state and health of your Availability Groups. Simply right click on the AG and select Show Dashboard to get an overview of the state of your AG.
In the Dashboard, you can see which replicas are primary/secondary, the databases in the AG, the failover mode, and if they are online and connected to the AG. If there are any issues, a link will appear in the Issues column which will help you troubleshoot.
