Problem solve Get help with specific problems with your technologies, process and projects.

Firing up AlwaysOn for high availability in SQL Server Denali

Microsoft was aiming for high availability in SQL Server Denali, and SQL Server AlwaysOn is the company’s latest design. Get an expert take on the steps necessary to configure AlwaysOn, available in the first community technology preview of the upcoming database release.

The upcoming release of Microsoft SQL Server, code-named Denali, has an exciting new feature called High Availability Disaster Recovery (HADR), also known as AlwaysOn. This feature, introduced in a limited fashion in the recently released community technology preview (CTP), takes the best components of database mirroring and failover clustering and adds some additional features for high availability in SQL Server Denali.

Configuring HADR in SQL Server Denali

Before you can configure HADR on a SQL Server database, there are some prerequisites. The first is that the server running the database must be set up as a cluster node. Because of this requirement, the operating system running under an HADR database must be Windows Server Enterprise edition or higher. The SQL Server instance itself does not need to be clustered; only the operating system needs to be. For this article, two virtual machines (VMs) were used, with each VM set up as a node in the same cluster with no shared storage.

After setting up a Windows cluster, install a normal non-clustered instance of Microsoft SQL Server Denali on each node of the cluster, then launch the SQL Server Configuration Manager. Select “SQL Server Services” from the menu on the left.  Right-click on the SQL Server service on the service list on the right and select “Properties.” In the pop-up window, select the “SQL HADR” tab and check the box reading “Enable SQL HADR service,” as shown in Figure 1. If the Windows operating system is not clustered, this check box won’t be available. In the screenshot in Figure 1, you’ll see the Windows failover cluster name of “denali-1-root” shown. This is the name given to the cluster by the clustering wizard.

Figure 1

After enabling HADR, an “availability group” can be created. Availability groups are a selection of databases on an instance as well as the instances that will be hosting the databases. Connect to the object explorer on the primary server, which has the primary database copies you want to protect, navigate to the “Management” folder and locate the “Availability Groups” menu. Right-click on “Availability Groups” and select “New Availability Group” from the context menu.

When the wizard opens, click “Next” to pass the initial information screen to the second screen, which asks you to name the availability group. This name should follow normal object-naming standards.  Clicking “Next” takes you to a screen that allows you to select the databases you will be protecting on the second server. When adding a database to an availability group, make sure the database or databases are in full recovery mode, and that a full backup of the database has been made.  If the database isn’t available, it will not be shown in the list; in this case select the “Show user databases not meeting requirements” checkbox to show all the user databases on the server. If a database isn’t available the reason will be shown in the Comments column. In Figure 2 you can see the database called “AlwaysOn,” which will be replicated to the secondary server.

Figure 2

When you see the screen shown in Figure 2, check the box next to the database or databases you want to protect and click “Next.” The next screen allows you to specify the instances hosting the replicas. By default, only the current instance will be shown. To add the second replica (the instance that keeps the copies of the databases), click the “Add” button and fill out the SQL connection dialog. Assuming that the installer can access to the second instance, it will be added to the list. There you’ll see which replica will be the primary and which will be the secondary. The current release of SQL Server Denali allows for only a single secondary replica; the final release (or, hopefully, before that) will allow for up to three secondary replicas.

After adding the second instance to the list of replicas, select the read mode the replica will support as a secondary replica. The default is to “Disallow Connections,” much like database mirroring does today.  You can also allow “Read Intent Connections” or “All Connections,” which will allow users to read from the secondary database for reporting purposes. For the purposes of this article, the secondary replica hosted on the “DENALI-2” instance will allow all connections, while when the replica hosted on the “DENALI-1” instance will not allow connections when it is the secondary, as shown in Figure 3.

Figure 3

Like database mirroring, HADR uses a dedicated endpoint to handle communication between the instances. These endpoints will be configured by default on TCP port 5022 unless there’s another endpoint already configured to use that TCP port number. If you want to make changes to the default endpoint configuration, click the Endpoints tab on the screen, as shown in Figure 4.

Figure 4

Scrolling to the right of the endpoint list presents two additional options, “Encrypt Data” and “SQL Server Service Account.” The “Encrypt Data” option lets you specify whether the SQL Server instances should encrypt data as it is transferred over the network, while the SQL Server Service Account Option shows the account running the instance. If the instances are running under a non-domain account (local accounts), then the instance needs to be configured to run under a domain account. If your instances are not running under a domain account, close the wizard, change the SQL Service account and restart the wizard.

After configuring the replicas, you’ll see the screen summarizing the configuration that will be used. On this screen, in the lower right side there is a “Script” button that will allow you to create a Transact-SQL script of the changes that you need to make. Click “Finish” to make the changes to the instance.

After the availability group has been fully configured, click the “Start Data Synchronization” button in the lower left of the screen, as shown in Figure 5.


Figure 5

Clicking this button opens the “Start Data Synchronization” window. This asks you for a single setting, a network location that you can store the full backup on. After entering the network path -- it must be a network share -- click the test button to verify that both servers have access to the network share, then click “OK” to start the process. When the process has completed, click “OK” on the popup window; that will close the “Start Data Synchronization” window. Then click “Close” to complete the new availability group wizard.

Failing over the database in SQL Server Denali

The database can be failed over from one instance to another using the Cluster Administrator. Each SQL Server availability group will create an entry under “Services and Applications” (called “Resource Group” in Windows 2003) allowing each availability group to be failed over from one instance to another independently of the other availability groups.

Configuration Notes

Keep in mind when configuring large databases for HADR that when large databases are copied over the network, memory will be taken away from the SQL Server database engine, and that will impact the active database. This includes if the network share is set up on a different server, such as a file server.

Currently SQL Server Denali only supports two replicas. If you were in Seattle for the Professional Association for SQL Server (PASS) conference, you may remember that in the keynote speech on Denali, demonstrators used four replicas for high availability in SQL Server Denali. The build used in the demo, however, is not the same as the one released in CTP1. A future build of SQL Server Denali will support four replicas. 

Denny Cherry has over a decade of experience managing SQL Server, including's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Check out his blog at SQL Server with Mr. Denny.


Dig Deeper on SQL Server High Availability, Scalability and Reliability