Step-by-Step MSSQL Always On Install & Availability Group Config (Failover Cluster + Listener)
Hello everyone, in this video I will show you how to install MSSQL Always On and configure an Availability Group on the servers
· In this video, we will install MSSQL Server on two servers: MSSQL1 and MSSQL2. These are our servers. I have already added them to the domain and created a specific Organizational Unit, placing these servers under that Organizational Unit.
· This is MSSQL1.
· This is MSSQL2.
· Before installing SQL on these servers, I need to activate the Failover Clustering feature on them.
· First, activate the Failover Clustering feature on the MSSQL1 server.
· Click on Add Roles and Features.
· Click Next.
· Next.
· Next.
· Select the Failover Clustering feature.
· Click Add Features.
· Next.
· Install.
· Activate Failover Clustering on MSSQL2 in the same way.
· Okay, wait for a few minutes to complete activating Failover Clustering on both servers.
· To use the Failover Clustering feature, a dedicated network interface is used on both servers to handle cluster traffic. Using a dedicated interface on the servers is recommended.
· Rename this interface to ‘Cluster’.
· Rename the interface on the MSSQL2 server to ‘Cluster’ as well.
· Ethernet0 is the management interface we use to handle server traffic.
· If you plan to use MSSQL Always On, the SQL service must run on the servers using domain user accounts instead of service accounts. Therefore, I created a user in Active Directory.
· Uncheck this box and check that box.
· Alright, Failover Clustering has been enabled on MSSQL1.
· Close.
· Open Run and type control userpasswords2.
· Add the user created for the MSSQL service to the local administrators group on both servers.
· Finish.
· Add that user to the local administrators group on MSSQL2 in the same way.
· Failover Clustering has also been enabled on MSSQL2.
· Now, it’s time to configure the Failover Cluster on these servers.
· Open the Failover Cluster Manager.
· Click on ‘Validate Configuration’ to check and verify the servers’ prerequisites and requirements before creating a cluster between these servers.
· Next.
· Here, we select the servers that we want to join the cluster and work as part of the cluster.
· Check name.
· Select.
· Select both of the servers.
· Ok.
· Next.
· Run All Tests.
· Next.
· The checking process is currently ongoing.
· There is no need to run validation and cluster configuration on another server.
· We received some errors and warnings, but because this is a test environment, they are not critical. These issues are typically related to network interfaces and server connections. In a production environment, you should ideally not encounter any warnings or errors.
· After validating the configuration, it’s time to create the cluster.
· Click on ‘Create Cluster’.
· Next.
· Select the servers to be part of the cluster.
· Next.
· Next, we need to assign a name and IP address to the cluster. This is for the Windows Cluster service and is not related to MSSQL.
· Next.
· Next.
· When you look in Active Directory Users and Computers, you’ll find the cluster name object created in the Computers container, within the same Organizational Unit as your servers.
· Okay, our cluster has been created.
· Check the status of the cluster nodes.
· Both servers are online.
· As you can see, there is no witness configured.
· Right-click the cluster, then go to More Actions and select ‘Configure Cluster Quorum Settings’.
· Choose ‘Select the quorum witness’ and click Next.
· Choose ‘Configure a File Share Witness’ and then click Next.
· Here, choose a shared folder. I prefer to use a shared folder located on the Active Directory server.
· Click ‘Browse’ to choose the Active Directory server.
· Click ‘Browse’ again.
· Since there is no shared folder, click on ‘New Shared Folder’.
· The local path of the shared folder points to a folder on the Active Directory server.
· As you can see, I am using a root folder on the C drive.
· Specify the name of the shared folder to be created on the Active Directory server’s C drive, and assign read and write permissions to all users.
· Ok.
· Ok.
· Next.
· Finish.
· Okay, the witness has been created.
· The Windows Failover Cluster configuration is now complete and functioning correctly. Both servers monitor each other, so if one fails, the other takes over seamlessly. Services continue to run without interruption.
· In the next step, we are going to install the MSSQL service on the servers.
· Run the setup as an administrator to start installing SQL on MSSQL1.
· Go to Installation and choose ‘New SQL Server stand-alone installation’.
· Begin installing SQL on MSSQL2 at the same time as on MSSQL1. While this isn’t required, I run the setup on both servers simultaneously to save time.
· The SQL Server installation steps are the same for both servers.
· Enter your product key and click Next.
· Accept.
· Next.
· Select the features you want to install based on your needs, but make sure to select Database Engine Services to install the SQL service on the server.
· Next.
· Repeat the same steps on MSSQL2.
· Keep the default instance or modify it according to your requirements.
· At this point, change the service account to the domain account we created earlier, which is already added as a local administrator on MSSQL1 and MSSQL2.
· Enter the account password and set the startup type to Automatic.
· Applying the same changes to the Database Engine service.
· Leave the settings for SQL Server Browser as they are.
· Select Mixed Mode from the authentication section and enter a strong password. This is your SQL Server SA account.
· Also, click on ‘Add Current User’ to add the logged-in user as a SQL administrator. These settings can be adjusted based on your requirements.
· Next.
· Install.
· Continue the installation on the MSSQL2 server.
· SQL installation on both servers is complete. Next, we’ll configure SQL Server to work as Always On.
· Open SQL Server Configuration Manager.
· In SQL Server Services, right-click on SQL Server and choose Properties.
· Select ‘Always On Availability Groups’ and check ‘Enable Always On Availability Groups’.
· Ok.
· You need to restart the SQL Server service for the change to take effect.
· Repeat the same steps on the other server.
· Ok.
· Restart.
· Alright, now open SQL Server Management Studio.
· Connect to MSSQL1.
· Now, we need to assign permissions to the Organizational Unit containing the MSSQL servers. Provide full permissions to the user running the SQL services, and also grant permissions to the computers and cluster objects to manage the Organizational Unit. This allows the SQL service to manage the cluster computers properly.
· As you can see, the Security tab is not visible. To fix this, enable Advanced Features from the View menu.
· Return to Management Studio.
· In Always On High Availability, right-click on Availability Groups and choose ‘New Availability Group Wizard’.
· Next.
· Enter a name for the Availability Group.
· Next.
· Since we don’t have any database, I can’t proceed with the wizard. Let’s create a test database before we start.
· Right-click on Databases and select ‘New Database’.
· Enter a name for the database.
· Go to the Options section and verify that the Recovery Model is set to Full.
· To add any database to an Availability Group, the database recovery model must be set to Full.
· Ok.
· Additionally, before adding a database to the Availability Group, a full backup of the database must be taken.
· Right-click the database, go to Tasks, and click Backup.
· Set the backup type to Full.
· Ok.
· Ok.
· Open the New Availability Group Wizard again.
· Next.
· Write the name of Availability Group.
· Next.
· Select Test database.
· Next.
· Here is MSSQL1. Click ‘Add Replica’ to add MSSQL2 as a replica and configure the availability group to synchronize data between both servers.
· These are the availability modes, and here you can see the differences between them.
· The mode you choose depends on your requirements.
· I prefer to use synchronous commit.
· And here you can see the endpoints.
· Backup preferences are also shown here.
· At this step, the Listener section is the most important.
· Choose ‘Create an Availability Group Listener’.
· Type the DNS name for the listener.
· Specify the port to be used for SQL connections from clients.
· Select the interface subnet that will listen for SQL connections.
· Specify the virtual IP address that will be used for SQL Always On.
· Since we granted permissions to the SQL service account and server computers, the listener DNS name and IP address will be created automatically.
· Next.
· Choose Automatic Seeding, then click Next.
· Next.
· Finish.
· Alright, all tasks have been completed successfully.
· As you can see, both servers are listed under Available Replicas.
· Under Available Databases, you can find the databases added to Always On. They will function correctly during server failures, with data maintained on all replicas.
· You can add more databases to this list later.
· Databases added to Always On appear as synchronous.
· Let’s verify the listener and try connecting to the database through the listener name.
· Enter the listener name.
· Alright, as you can see, we connect to the database via the listener name. Even if one server fails, our connection remains intact.
· To check the replication status, right-click the Availability Group name and select ‘Show Dashboard’.
· MSSQL1 is currently the primary server. I will do a failover to switch the primary server to MSSQL2.
· Next.
· Choose the new primary server.
· Next.
· Connect to the new primary MSSQL server instance.
· Next.
· Finish.
· Alright, as you can see, the failover happened, and after a few seconds, the sync status will show green. During this time, the database remains fully functional.
· Performing failover again to change the primary server back to MSSQL1.
· Next, create a new database on MSSQL1 and include it in Always On.
· The recovery model is set to Full.
· Take a full backup.
· Right-click on the available database.
· Add database.
· Choose the database you want to add to Always On.
· Next.
· Connect to MSSQL2 server instance.
· Next.
· Next.
· Finish.
· You can see that both databases are marked as synchronous.