Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Best Practices for Migrating SQL Server to Azure - free webinar
 

SQL Server AlwaysOn Availability Groups - Part 2 Availability Groups Setup


By:   |   Last Updated: 2011-11-10   |   Comments (5)   |   Related Tips: 1 | 2 | 3 | More > Availability Groups

Problem

SQL Server has produced some excellent High Availability options, but I was looking for an option that would allow me to access my secondary database without it being read-only or in restoring mode. I need the ability to see transactions occur and query the secondary database.

Solution

In Part 1 we configured prerequisites for SQL Server 2012 new AlwaysOn and in Part 2 we'll go over the fun stuff...configuring the High Availability Groups.

Create Sample Database and Create Backups

First thing we need to do is connect to the primary server using SSMS and create two databases and back them up to the network share we created. In this example I'll create database RammerJammer and RollTide and then create database backups.

ssms object explorer

Specify Name

In SSMS go to Management, right click Availability Groups and click New Availability Group Wizard. Once the wizard appears click Next on the main screen and create a unique Availability Group name on the Specify Availability Group Name screen. I'll name my group AG-Bama and then click Next.

Select Databases

On the next screen we will need to select our databases that we want added to our availability groups. This screen also has a status column that will let us know ahead of time if our databases meet the prerequisites. I'll select both databases and click Next.

new availability group in sql

Specify Replicas

On the next screen click Add Replica... and connect to the other server (Denali2) Replica Mode can be set to Automatic Failover, High Performance, or High Safety.

  • Automatic Failover: This replica will use synchronous-commit availability mode and support both automatic failover and manual failover.
  • High Performance: This replica will use asynchronous-commit availability mode and support only forced failover (with possible data loss).
  • High Safety: This replica will use synchronous-commit availability mode and support only manual failover.

Connection Mode in Secondary Role can be set to Disallow connections, Allow only read-intent connections, or Allow all connections.

  • Disallow connections: This availability replica will not allow any connections.
  • Allow only read-intent connections: This availability replica will only allow read-intent connections.
  • Allow all connections: This availability replica will allow all connections for read access, including connections running with older clients. For this example, I'll choose Automatic Failover and Disallow connections to my secondary role and click Next.
sql server replicas

Specify Availability Group Listener

On the next screen we'll setup the listener. I'll take defaults and choose Next.

specify availability group listner

Select Data Synchronization

The next screen we will need to specify the Perform initial data synchronization option and type the UNC path of the network share we created earlier and click the Next. Hopefully we'll see SUCCESS for our validations:

select initial data synchronization

Validation

validate sql server alwayson setup

Summary

Click Next to view the summary and Finish to configure or you can click the Script button if you'd like to perform this action at a later time:

verify always setup choices

Results

During the configuration you can see the wizard complete the following: Configures endpoints Create Availability Group Create Availability Group Listener Join secondary replica to the Availability Group Create a full backup of DB1 Restore DB1 to secondary server Backup log of DB1 Restore DB1 log to secondary server Join DB1 to Availability Group on secondary server Create a full backup of DB2 Restore DB2 to secondary server Backup log of DB2 Restore DB2 log to secondary server Join DB2 to Availability Group on secondary server.  When done click Close to close the wizard. To

alwayson installation results

View the Availability Group in SSMS

In SSMS, drill down to Management and Availability Groups. Here you will see your Availability Group. Drill down one farther and you'll see Availability Replicas, Databases, and Group Listeners.

ssms availability groups

To add a database, replica or view the dashboard right click on the Availability Group Name.

add database replica

The dashboard will help you determine if your databases are Synchronized and Healthy.

alwayson dashboard

For demo purposes I'll change my Connection mode in Secondary Role from Disallow all connections to Allow all connections (In SSMS, right click Availability Group and go to Properties) This will allow me to connect to my secondary server and open the databases.

ssms alwayson databases

If we had left this set to disallow all connections when we try to open the secondary servers databases we would have received a database is inaccessible error. I am going to create a table called NatlChamps on my primary server in the RammerJammer database.

ssms always on sql server databases

Within seconds if I switch over to my secondary server, the NatlChamps table is already created.

sql server alwayson database list

AlwaysOn is an excellent new feature of SQL Server and I can't wait to use this in my production environments.

Next Steps
  • View all High Availability products and tools here
  • Review these other tips related to SQL Server 2012
  • When configuring the listener make sure you open the port in the firewall on the other server. If this port is not open they will not be able to communicate and you will receive a connection error.
  • Learn more about AlwaysOn in this webcast


Last Updated: 2011-11-10


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, June 16, 2015 - 1:29:49 PM - Phani Back To Top

Hi Brady,

Thank you for the artciler.. it is very useful.

 

I am trying to automate alwayson errors i.e. i am trying to send an email whenever a failover happend, database removed, added.

I know we can use alerts, operators, but it will send alert for each database failed over / back

 

Can you please help me to prepare a email alerts for all the failed over databases in one email instead individual email.

Thanks

 


Thursday, August 14, 2014 - 8:35:35 AM - ananda Back To Top

What are the pre requisites required for configure High availability solutions always on in SQL Server 2012?

Before I want to test that ALWAYS ON feature after implement at production setup.

I have two HV server (HyperV server with OS: windows 2008R2 enterprise editions)

As per your screenshots, there is no shard storage and virtual IP etc..,  so how application will re-direct another database if primary database down? Pls. clarify  

 


Thursday, May 08, 2014 - 7:28:11 AM - Nandish Back To Top

Hi,

I have installed 2 SQL servers [SQL01 & SQL02] with failover clustering and have enabled alwayson feature.

when I try to connect to the database after stopping SQL service on server SQL01, I'm unable to connect to the database.

But when I shutdown SQL01 completely, I'm able to connect to the database which is on high availability.


Tuesday, April 15, 2014 - 1:49:48 PM - John Pluchino Back To Top

I am about to begin a project to implement a SaaS application based on a SQL Server 2012 Enterprise Edition database that needs to physically reside on multiple geographically dispursed data centers.  I want to establish the best possible implementation with high availability and uninterrupted operations in the event of an out-of-service condition at any given data center.  Is SQL Server "High Availability Groups" the best approach? ...OR... Is SQL Server "Transactional Replication" the way to go?


Wednesday, September 12, 2012 - 11:58:06 PM - vahid Back To Top

I have a question though. ihave 2 node and sync and automatic failover seting. if i want reset primary server what happen? that switch on secondary server or no?


Learn more about SQL Server tools