SQL Server 2017 introduces a new feature that eliminates the need for a Windows cluster to enable the Always On feature. In this blog post, we will guide you through the process of configuring an Always On availability group with SQL Server 2017.
Before we begin, please ensure that you have SQL Server installed on two servers as standalone instances, as this is a requirement for this configuration.
Enabling AlwaysOn
To enable AlwaysOn, open SQL Server Configuration Manager and navigate to SQL Server Services. Locate the MSSQLSERVER instance and open its properties. In the properties window, you will find the “AlwaysOn High Availability” option. If you are using an older version of SQL Server, you will need to make the machine part of a Windows cluster before this option becomes available. However, with SQL Server 2017, you can enable AlwaysOn without a Windows cluster. Check the “Enable AlwaysOn Availability Groups” checkbox and click apply and OK. Please note that enabling this feature requires a service restart. Repeat this step for all SQL instances that will be part of the Availability Group.
Configuring the Availability Group
Open SQL Management Studio and connect to any of the SQL instances that will be part of the group. Expand the “Always On High Availability” folder and right-click on the “Availability Groups” folder. Select “New Availability Group Wizard” to begin the configuration process.
In the availability group wizard, provide a name for your availability group. In SQL Server 2017, you have two options for the cluster type: External or None. If you choose External, it means that you will be joining this availability group to an external cluster, such as a Windows cluster in a different network or a Linux cluster. For this scenario, choose None.
Next, select the databases that will be part of the availability group. These databases must be in the Full recovery model and have a full backup.
Now, you will choose the replicas that you want to add to your group. Set the initial role for each replica and choose the failover mode and availability mode. You can also configure the readable secondary option, which allows you to query the databases in your replica. The endpoints tab will show you the URL and ports for each replica. Additionally, you can configure backup preferences and the availability group listener later.
Automatic Seeding
Starting from SQL Server 2016, you have the option of automatic seeding. With this type, SQL Server will handle everything for you, using the default folders for storage. If you prefer more control, you can choose the full database and log backup option, which allows you to choose the backup location. The join only option is useful for large databases, as it allows you to prepare everything in your replica. Finally, the skip initial data synchronization option requires you to restore everything just before initializing the synchronization.
Setting up the Listener
The listener will serve as the single point of entry for your availability group. To configure the listener, expand the “Always On High Availability” folder, locate your availability group, and right-click on the “Availability Group Listener” option. In the configuration screen, specify the domain name for your listener and the port it should listen to. Make sure the IP you choose is available in the domain and belongs to the same subnet as one of your replicas.
Once you have completed these steps, you can start configuring your applications to use the listener and take advantage of the high availability provided by Always On.
With SQL Server 2017, configuring an Always On availability group has become easier and more flexible. You no longer need a Windows cluster, and the process is similar to previous versions. By following the steps outlined in this blog post, you can ensure that your SQL Server environment is highly available and resilient.