Blog: Developing Always On Availability Group checklist with reduced configuration time and costs

One of the largest transportation authorities was running the mission-critical system on SQL Server platform and any down times could harmfully affect on their business. So, they decided to modernize their system and implement high availability and disaster recovery solutions to keep their system running without interruptions. To meet the customer’s needs, we performed technical support and delivered detailed instructions and automated scripts to implement the highly available architecture in compliance with Always On Availability Group checklist. Our solution allowed the customer to build perfectly configured highly available system by themselves while saving configuration time and money.

Customer’s problem

Our customer maintains one of the busiest toll roads in the United States. To ensure non-stop 24/7 operation and satisfy the needs of millions of travelers, they needed to implement high availability and disaster recovery approach into their system.

After reviewing the current customer system, we outlined Always On Availability Group checklist implementing high availability requirements. It included the proper configuration of the following elements:

• SQL Server;
• Windows Server configuration;
• Always On Availability Groups;
• Windows Server Failover Cluster.

Operation system configuration

At the preliminary stage we reviewed current customer system and prepared instructions how to ensure high performance when running SQL Server on Windows Server. We specified Windows server setting and prerequisites essential for SQL Server performance.

Here is the checklist for the Windows Server settings:

• Power. We pointed out how to increase the performance of SQL Server up to 30% by adjusting power settings. This is achieved by preventing the CPUs from stepping down in a number of cycles and enabling all stepping and CPU boost features. The server BIOS should also be adjusted to disable CPU power restrictions.
• Antivirus. We indicated how to prevent performance issues while using antivirus. For this purpose, certain SQL Server directories and file-name extensions must be excluded from virus scanning.
• Firewall. We also indicated which ports needed to be opened in Windows Firewall to allow SQL Server access.
• Windows update. It’s important to configure Windows updates on your schedule by disabling it on your servers. You can then use the Windows Server Update Services (WSUS). Then use the guidance on rolling upgrades for SQL Server Always On Availability Groups.
• Page file. We completed this stage by setting the paging file size to the expected maximum commit charge. Related to page file size is getting the NTFS Allocation Unit Size correct. The default is 4k and the recommeded guideance for SQL Server data, logs, and tempdb file drives is typically 64k.

SQL Server installation and configuration

Once we implemented all the prerequisites, we were ready to install SQL Server. We prepared a set of instructions that covered each step of SQL Server installation, paying special attention to service account permissions.

After successful installation, we moved on to SQL Server configuration. We provided our customer with values to set in SQL Server Management Studio for optimal configuration of their system.

SQL Server has an option of setting Alerts to collect and report occurring system issues, and then write the resulting data to the log. We developed a script for recommended Alerts that automated this procedure.
We also specified Model database that served as a template for all new databases on this server. The default settings for this database were non-optimal that could lead to fragmentation in future. So, we modified those settings and set the correct values for Initial Size and Autogrowth.

We set the Maximum number of SQL Server error logs and ensured a reasonable size of SQL Server Agent History log by increasing the retention periods.

Then we provided the script that automatically configured the database mail. The customer had to perform only one manual action: specify SMTP server address and email address.

We also configured SQL Server logs and created a process that manages the cycling of these logs. We developed a script with a job regularly creating a new log at midnight.

Temporary database

Temporary database (tempdb) settings are critically important for overall SQL Server database performance. Basing on the current system performance, we decided to create one temporary database data file for each two CPU cores on the server and set their size. Moreover, we provided our customer with a script that allowed to add more data files and adjust their size in case of growing workloads in future.

SQL Server uses Trace Flags to temporarily set specific server characteristics in order to diagnose performance issues. We created a job that starts specific traces related to the tempdb performance and deadlocks logging.

Once we finished configuring Windows Server and SQL Server, we could move on to the next item in our Always On Availability Group checklist.

Preparing Always On Availability Group checklist

Here is the Always On Availability Group checklist that we were implementing to build a highly available system:

• Create and configure Windows Server Failover Cluster;
• Configure Active Directory accounts;
• Create and configure availability group;
• Configure the availability group listener.

At first, we created the failover cluster and configured different settings including cluster quorum and Active Directory accounts. After that, we were able to configure an Availability group. We prepared instructions on how to create the availability group, configure database replicas, set correct backup preferences, select initial data synchronization. We also prepared a PowerShell script to configure the availability group listener in order to connect to the secondary read-only replica. Once all applied settings passed the validation checks, Always On Availability Group checklist was fulfilled and the whole system was ready to go live.

Benefits

• Highly effective solution with scripts automating the routine;
• Reduced configuration time, efforts, and costs needed to set up failover environment;
• Turnkey solution capable of being applied remotely;
• Implemented SQL Server best practices for flawless performance.