SQL Server AlwaysOn Availability Groups Terminology….!

In SQL Server 2012, with AlwaysOn being introduced there are lot of new terms/words which we need to get used to as we support SQL Sever 2012. Well, Just getting used to those terms is not enough…we’ve to understand the terminology. In this short Post let me define what I’ve understood so far with these new fancy terms.

Availability Groups(AG) : – Group of Databases which move together from one Instance to other. Each Instance can have multiple AGs, where each AG contains multiple Databases.

Listener :- It’s a virtual entity which moves around to the current Primary Server for a given AG.

Replicas :- All the SQL Servers involved in your AG are considered as Replicas. Even current Primary Server is treated as a Replica, not just the secondaries…! So, replicas are not Just secondary SQL Instances. They are differentiated by using Primary Replica and Secondary Replica.

Note: AGs run on top of Windows Clustering. So is it a new clustering?? Nope! Same old Windows Clustering, but with a flavor of no Shared Storage. FYI, SQL Instances in my lab are 3 Standalones which are built on top of a 3 Node Windows Cluster with no shared Storage!

Note: your SQL Server Instances can be clustered as well, which adds more complexity, but is needed for some customers based on their own business needs. Typically, MSFT calls this scenario as AlwaysOn Failover Clustered Instance.

AlwaysOn Failover Cluster is not same as AlwaysOn Availability Groups!


How to add a new Database to an existing AlwaysOn Availability Group?

In this post, let us see how to add a new database to an existing AlwaysOn Availability Group. Let’s assume we’ve a sales AG already in place and application team requested us(DBA) team to add one more Database to this Group as they need them to be available all together. For this Example let’s assume the new Database Name is “Sales_Q1” – Of Course this would be more realistic Name in your Real world deployments!

Please see my current Availability Grp Status Below:

At a glance, you can see 3 Databases currently Sales_1, Sales_2 and Sales_3 participating in my AG. Also you can see only 2 nodes out of 3 are Powered Up(See SreeSQLDR status  as Down) and the Primary Replica is SreeSQLA.

Now, let me create a new Database named “Sales_Q1”. See below

As you can see I’ve just created a new Database(Simple Recovery Model) which is not yet added to my AG. Now, let’s try to add this Database and see how SQL behaves by default.

Step1: Right Click on Availability Databases and select Add Database.


As you can see Wizard is smart enough to recognize your Database needs Full Recovery Model to participate in your AG.

Step3: I changed the Recovery Model to Full and refreshed the Wizard, now….See below for what it says

Cool…now it says you need a Full backup to be taken.

Step4: Now I tool full backup and placed on a share where all the nodes have access to. Now it says you are good to go as you can see below 🙂

Step 5:  Now it’s time to choose how do you want to start synch. I’ve selected Full by providing the share(I placed by backup here) where all my nodes participating in my AG have access to.

Step 6:  Time to Join all other Nodes by connecting. Notice I can’t proceed further(Next button is greyed out)

As you can see in the above Screenshot I’m not allowed to proceed any further. (Remember from my first screenshot? I didn’t powered UP my DR Node…) Just wanted to show you how this wizard behaves if any one of the nodes participating in AG is down. Once, I powered up my DR machine, I was able to connect to that Instance and was able to proceed Next.

Step7: It validated all my configurations and did it’s magic behind the scenes 🙂

Note: You can script out the entire process while you are at summary Screen.
Finally, see below for how it looks once I added new DB to our AG.

You can see Sales_q1 added to the AG successfully. Perfect! Hope this helps.