SQL 2012 AlwaysOn: Configuring 2 Synchronous and 1 Asynchronous Replicas

To configure highly available and disaster resilient  databases with SQL 2012 AlwaysOn, 3 servers will be used as show below

In the Primary Data Center SQLDEV1 and SQLDEV2 will host synchronous replica of the databases and in the Secondary Data Center on SQLDEV3 an asynchronous replica will reside.

To enable AlwaysOn, SQL Server must be installed on a node in the Windows Failover Cluster (WFC) (how to install 3-Node WFC on Windows Server 2012  see here ).

SQL 2012 needs .NET 3.5. So the first step is to add .NET Framework 3.5 on each of the three servers.

The next step is to install SQL 2012 on each of the servers. Even if these servers are part of the WFC the stand-alone installation is required (this WFC has no shared storage and this won't allow you to install SQL failover cluster).

When SQL 2012 is installed we need to enable AlwaysOn and Named Pipes.
In SQL Server Configuration Manager right-click on the SQL Server and on the "AlwaysOn High Availability" tab check off "Enable AlwaysOn Availability Group" (this check box is available only if SQL Server is installed on a node in WFC).

Then enable named pipes protocol for this SQL Instance and restart the SQL Server service.

To verify that we have enabled AlwaysOn on all 3 server execute this script

sqlcmd -s sqldev1 -E -Q "SELECT CONVERT(varchar(5), SERVERPROPERTY ('IsHadrEnabled'))"

sqlcmd -s sqldev2 -E -Q "SELECT CONVERT(varchar(5), SERVERPROPERTY ('IsHadrEnabled'))"

sqlcmd -s sqldev3 -E -Q "SELECT CONVERT(varchar(5), SERVERPROPERTY ('IsHadrEnabled'))"

If it returns 1 for each server then we're good to proceed.

The next step is to open Firewall ports. We will need the following TCP ports:
-   445      for Named Pipes
- 7022      for the mirroring end points
- 5555      for Availability Group Listener

To create end point this scrip has to be executed on each of the server

CREATE ENDPOINT endpoint_mirroring

    STATE = STARTED

    AS TCP ( LISTENER_PORT = 7022 )

    FOR DATABASE_MIRRORING (ROLE=PARTNER);

GO

Then we need to grant permissions to access the end points. Because the SQL Server 2012 was installed to run under virtual accounts (e.g., NT Service\MSSQLSERVER) the permissions need to be granted to the corresponding computer accounts. For example, to access the end point on SQLDEV1 permissions need to be granted to SQLDEV2$ and SQLDEV3$ (the dollar sign indicates computer account).

On SQLDEV1 execute this script

use [master]

GO

CREATE LOGIN [TESTDOMAIN\SQLDEV2$] FROM WINDOWS

GO

GRANT CONNECT ON ENDPOINT::[endpoint_mirroring] TO [TESTDOMAIN\SQLDEV2$]

GO

CREATE LOGIN [TESTDOMAIN\SQLDEV3$] FROM WINDOWS

GO

GRANT CONNECT ON ENDPOINT::[endpoint_mirroring] TO [TETSDOMAIN\SQLDEV3$]

GO

On SQLDEV2 execute this script

use [master]

GO

CREATE LOGIN [TESTDOMAIN\SQLDEV1$] FROM WINDOWS

GO

GRANT CONNECT ON ENDPOINT::[endpoint_mirroring] TO [TESTDOMAIN\SQLDEV1$]

GO

CREATE LOGIN [TESTDOMAIN\SQLDEV3$] FROM WINDOWS

GO

GRANT CONNECT ON ENDPOINT::[endpoint_mirroring] TO [TETSDOMAIN\SQLDEV3$]

GO

And on SQLDEV3 the script looks like this

use [master]

GO

CREATE LOGIN [TESTDOMAIN\SQLDEV2$] FROM WINDOWS

GO

GRANT CONNECT ON ENDPOINT::[endpoint_mirroring] TO [TESTDOMAIN\SQLDEV2$]

GO

CREATE LOGIN [TESTDOMAIN\SQLDEV1$] FROM WINDOWS

GO

GRANT CONNECT ON ENDPOINT::[endpoint_mirroring] TO [TETSDOMAIN\SQLDEV1$]

GO

To verify that we have all end points created run the following script

sqlcmd –s sqldev1 –E –Q “select CONVERT(varchar(20), Name) AS [Name], CONVERT(varchar(22),type_desc) AS [Desc] from sys.endpoints where type_desc like  data%’”

sqlcmd –s sqldev2 –E –Q “select CONVERT(varchar(20), Name) AS [Name], CONVERT(varchar(22),type_desc) AS [Desc] from sys.endpoints where type_desc like  data%’”

sqlcmd –s sqldev3 –E –Q “select CONVERT(varchar(20), Name) AS [Name], CONVERT(varchar(22),type_desc) AS [Desc] from sys.endpoints where type_desc like  data%’”

And it returns results as shown

Now we need to prepare our test databases (TESTDB1 and TESTDB2) for adding to availability groups. These databases must be in FULL recovery mode.

The New Availability group wizard allows you to restore the databases on new replicas automatically but I prefer to do it myself.

So I did a full backup of both TESTDB1 and TESTDB2, and then transaction log backup.

I copied the files to SQLDEV2 and SQLDEV3 and restored both databases (both full and transaction log backups) WITH NORECOVERY. 

And now we launch the NewAvailability Group Wizard from SSMS

We enter the group name AG1

On the next screen we select databases to be a part of this availability group 

Both our test databases meet the requirements (Full recovery mode and full backup has been taken). 

On the next screen we need to configure and to add replicas (SQLDEV1 was added automatically because this is the server we were connected to in SSMS when launched the Wizard)

We'll add SQLDEV2 and SQLDEV3 and configure SQLDEV1 and SQLDEV2 for automatic failover (and it will check synchronous commit automatically for us).

The third replica (on SQLDEV3) will be used for a disaster recovery and the databases on this replica will be in asynchronous-commit mode. If we enable synchronous-commit mode for this server the performance impact will be ....see it here ).

On the Endpoints tab review the settings. Nothing to change there.

And on the backups tab we also won't change anything for this exercise (but it might be very useful in production to be able to take backups on secondary replicas)

And on the Listener tab we enter the DNS name (SqlDevCluster1), the port (5555 in this case) and virtual IP addresses assigned to the listener name. Because we have WFC on two subnets (see here) we need to enter two IP addresses. We'll use 192.168.78.219 (on 192.168.78.x) and 192.168.69.135 (on 192.168.69.x)

 Because we've already restored the databases on both replicas we'll select 'Join Only' on this screen

 The validation completes successfully

On the Summary page review all the information and click Finish to create the availability group

In SSMS on SQLDEV1 we'll see this (when expanded all subnodes beneath AG1)

Finally we need to configure the failover policy for our availability group.

ALTER AVAILABILITY GROUP AG1 SET (FAILURE_CONDITION_LEVEL = 5);

The Conditional Level values mean the following:

1. On server down. The SQL Server service stops because of a failover or restart.
2. On server unresponsive. Any condition of lower value is satisfied, the SQL Server service is connected to the cluster and the health check timeout threshold is exceeded, or the current primary replica is in a failed state.
3. On critical server error. Any condition of lower value is satisfied or an internal critical server error occurs. This is the default level.
4. On moderate server error. Any condition of lower value is satisfied or a moderate Server error occurs.
5. On any qualified failure conditions. Any condition of lower value is satisfied or a qualifying failure condition occurs.

Now if the SQL Server service stops on SQLDEV1 or the server gets shut down the AG1 availability group will automatically fail over to SQLDEV2.

When both servers SQLDEV1 and SQLDEV2 go down this constitutes a disaster and we need to execute a couple of manual steps (they can be scripted) to fail AG1 over to SQLDEV3.

We'll review all these steps in the next post.