Free SQL Help for Devs, DBAs, and the Curious

In the world of databases, ensuring high availability and data resilience is paramount. One of the key technologies that can help achieve this is SQL Server Always On. Introduced in SQL Server 2012 and improved in subsequent versions, Always On provides a high-availability and disaster-recovery solution. This post will provide a guide on how to set up SQL Server Always On High Availability groups using SQL Server 2016 and 2017.

Before we begin, it's important to note that setting up Always On requires a Windows Server Failover Clustering (WSFC) cluster. Always On Availability Groups is not a standalone technology, but an integral part of the WSFC. To start, ensure that your WSFC cluster is set up correctly and the SQL Server instances are installed on each of the WSFC nodes.

Once the WSFC cluster is set up, you can proceed to enable the Always On feature for each SQL Server instance. This can be done by changing the SQL Server configuration. This is done by right-clicking on the SQL Server instance in SQL Server Management Studio, choosing properties, and then navigating to the 'Always On High Availability' tab. Here's a T-SQL command that achieves the same:

EXEC sp_configure 'always on', 1; 
RECONFIGURE; 

Next, create a new Availability Group from the SQL Server instance. You can do this by right-clicking on the 'Always On High Availability' node in SQL Server Management Studio and selecting 'New Availability Group Wizard'. This will launch a wizard that will guide you through the process of creating a new Availability Group. Alternatively, this can also be done using T-SQL commands. Here's an example:

CREATE AVAILABILITY GROUP [AG1]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR
REPLICA ON
N'Instance1' WITH (ENDPOINT_URL = N'TCP://Instance1:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC),
N'Instance2' WITH (ENDPOINT_URL = N'TCP://Instance2:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC);
GO

Now, we add a database to the Availability Group. This can be done through SQL Server Management Studio or via T-SQL. Remember to ensure that the database is in full recovery model before adding it to the Availability Group. Here's how to do it using T-SQL:

ALTER DATABASE [YourDatabase] SET RECOVERY FULL; 
BACKUP DATABASE [YourDatabase] TO DISK = N'YourBackupLocation'; 
ALTER AVAILABILITY GROUP [AG1] ADD DATABASE [YourDatabase]; 

While this guide focuses on SQL Server 2016 and 2017, it's worth mentioning that similar high availability solutions are available for other database systems. MySQL 5.7 offers replication and clustering features, and DB2 11.1 provides HADR (High Availability Disaster Recovery). Cloud databases like Azure SQL also offer built-in high availability options.

Setting up a high availability solution is a critical task that requires careful planning and testing. This guide provides a basic introduction to setting up SQL Server Always On Availability Groups. However, every environment is unique, and you should tailor your solution to your specific needs and constraints. Always test your setup thoroughly before deploying it to a production environment.