Steps to configure SQL server Availability Groups in DENALI

In this blog post I will drive you through configurations steps required (with GUI screens) to successfully implement SQL server’s new high availability option called “ALWAYSON” aka HADR .

If you haven’t read more about Always ON or Availability groups in SQL server code named “Denali” version, here are some articles that I would recommend you read before using this blog post to configure SQL server’s new and cool feature.

http://technet.microsoft.com/en-us/library/cc645581%28SQL.110%29.aspx , http://technet.microsoft.com/en-us/library/cc645581%28SQL.110%29.aspx#TF9532 and http://technet.microsoft.com/en-us/library/ff877931%28SQL.110%29.aspx

In a nutshell, AlwaysON combines the working principle of database mirroring for making the database DR’ed in secondary site(s) in three different modes and extensively uses Windows Failover Clustering services for enhancing the failover capabilities thus creating AVAILABILITY GROUPS which can perform fail over for a group of databases. As like any other DR/HA solutions , AlwaysON has got its own set of DMVs that can be queried for management , maintenance and administrated by a SQL server DBA.

Note: This blog articles covers the AlwaysON features and functionalities as at CTP3 release which could be changed in the final RTM release.

Step 1: Configure Windows cluster and add nodes.

I have two VMs with Windows server 2008 R2 Enterprise edition 64 bit and SQL server Denali CTP3 x64 and I’m going to create a windows cluster just by adding 2 nodes and a virtual name for the cluster . Starting from Windows server 2008 failover cluster manager has to be enabled from roles and features applet. The cluster required for host Always on / HADR database doesn’t need to have shared storage or instance virtual name, the cluster can just be created and validated with a virtual name for it, I am using the virtual name for my cluster as SQLAlwaysON and the nodes are SQLCLU2 and SQLCLU3.

The configured windows cluster SQLAlwaysON looks as below

alwayson_hadr_1

Nodes SQLCLU2 and SQLCLU3 are part of the cluster SQLAlwaysON

alwayson_hadr_2

Step 2:  Enable AlwaysON high availability for SQL services

Now the windows cluster SQLAlwaysON is setup with 2 nodes that has SQL server Denali CTP3 installed in it, the SQL server services in SQL server configuration manager will have the cluster name detected automatically.

Go to SQL server configuration manager and right click SQL server engine services and tab to the ‘AlwaysOnHighAvailability’ page, then tick the ‘enable AlwaysON high availabilities group’ on both the instances on both nodes.

alwayson_hadr_3

This change requires SQL server services to be restarted on both the nodes/instances.

Step 3: Configure AlwaysON / HADR

Before continuing to configure the high availability group, decide the group of databases that you will be including. I have chosen Adventure Works database for this test purpose and I will configure HADR for these databases from SQLCLU2(Primary) to SQLCLU3(Secondary). I am seeing this feature as a perfect fit of SHAREPOINT server databases and all other pre written or custom apps that has multiple databases to work on

Image below shows instances I will be working on to configure AlwaysON for 4 adventure works database

alwayson_hadr_4

Drill down the management folder and right click the Availability groups folder and choose New Availability group Wizard ,

alwayson_hadr_5

The wizard opens up and looks like

alwayson_hadr_6

Specify a name for availability group in the next window

alwayson_hadr_7

Specify / Select the list of databases in the next screen .Only databases with FULL recovery model will be shown in this wizard .This page will exclude system databases and simple recovery database. Also to remember is , a database can either be configured for database mirroring or AlwaysON

alwayson_hadr_8

In the next screen we must specify the Replica properties( SQL Servers instances that are part of the windows cluster SQLAlwaysON , in my case)

Options for Replica mode: Automatic failover, High Performance and High Safety – I’m choosing Automatic failover in my test case and each options here decides what purpose you are using AlwaysON for !

Options for Connection mode in secondary state: Disallow connection, allow all connections and allow only read-intent connections – I’m choosing allow only read-intent connections for the current secondary set of databases

alwayson_hadr_9

Other tab on the same page is for specifying the ENDPOINT properties and the connect permissions for SQL service accounts same as Database mirroring

alwayson_hadr_10

The next page lets us to create a DNS entry for this availability group which is the virtual name for this AG , eventually this will be data source name that App or client tools will use to connect to these group of databases

alwayson_hadr_11

Now that all the properties , requirements are entered for AG creation the last screen is for starting the data synchronization

alwayson_hadr_12

alwayson_hadr_13

Once all the details are entered, the wizard configured the AlwaysON for us . As you can see only one database failed to be part of our HADR / Always group due to the filestream option not being enabled on the secondary instance SQLCLU3. We are not going to bother about it at least for now.

alwayson_hadr_14

This wizard has given all summary/steps it has done to successfully accomplish the task. The failed items can be fixed later with the help of this screen.

Step 4: Look what’s happened

On the primary server SQLCLU2, open the availability group folder in management node of SSMS and look what’s newly included

alwayson_hadr_15

As you can see the replicas are our 2 nodes and databases are Adventure works, the last item is interesting though. Group listener called MY_FIRST_ALWAYSON_LISTENER is configured as DNS entry to connect to the current PRIMARY SQL server and this is how its looks on secondary server SQLCLU3

alwayson_hadr_16

AdventureWorks2008R2 database seems to be suspended as the restore did not succeed during AG configuration. Availability groups are now a server scoped objects ,any existing or newly created logins can be granted permissions on AG similar to an end point\server\login and server roles(only in Denali).

Now let’s have a look at the Windows failover cluster manager and see what’s interesting there

alwayson_hadr_17

Successfully creating the availability group in SSMS creates an entry for service/application for our AG with the AG name we used during the configuration. This looks similar to our SQL server clustered instances on Windows clusters but not for the whole instance rather for just a group of databases.

Looking in to the MY_FIRST_ALWAYSON service/application for further details from the cluster manager

 alwayson_hadr_18

As you can see, the cluster resource is currently owned by SQLCLU2 which is our primary now and the DNS and IP address that we created for connecting to SQL server is also created as a dependent resource for this availability group.

From now it can be managed as similar to SQL server cluster but only for group of databases. You can flick them across nodes etc., and still use the same virtual name to connect to databases.

What Next?

Now that we have successfully configured AlwaysON with “ONE” read only secondary, we should be able to force a manual failover or automatic failover for availability groups by shutting down the current primary server or SQL services. For configuring multiple replicas with CTP3 books online says to add the start up trace flag 9532.

You could use your apps to use the name my_first_alwayson_listener to connect to the AG databases and the AlwaysON resolves the current primary node and connects to that instance. Apart from this high availability option, we could use standalone instance named sqlclu3 (current secondary) to act as reporting database as it’s configured to be read only as per my test setup.I’m sure there’s lot more to test this before the final RTM release ,I will try all possible scenarios and come up with my latest findings. I would like to test with having both copies of replica to be read/write

I really love this feature as it gives high available option using Windows cluster failover and virtual naming methods, DR options with sticking on to principles of database mirroring and having multiple replicas to work on for various purpose there by segregating the load on your OLTP databases.

I’m AlwaysON, are you??


Posted

in

by

Comments

3 responses to “Steps to configure SQL server Availability Groups in DENALI”

  1. SQL-Articles » Steps to configure SQL server Availability Groups in DENALI…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. FANOFHADR avatar
    FANOFHADR

    Can we use the name of primary instead of using listener? what advantage does it gives other the name is different? Could someone pls explain?

  3. Lekss avatar
    Lekss

    Hi FANOFHADR,

    Yes , indeed you can use both the names ( primary and listener) to connect to the databases. But it makes more sense to include your listener name as data source in your connection string to make your apps listen to the current primary group of databases if a disaster has occurred.

    If you are using your primary instance name in your connection string it means that everytime a failover occurs you need to update your connection string.

Leave a Reply

Your email address will not be published. Required fields are marked *