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.
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.
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.
Specify Availability Group Listener
On the next screen we’ll setup the listener. I’ll take defaults and choose Next.
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:
Validation
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:
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
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.
To add a database, replica or view the dashboard right click on the Availability Group Name.
The dashboard will help you determine if your databases are Synchronized and Healthy.
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.
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.
Within seconds if I switch over to my secondary server, the NatlChamps table is already created.
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.
