SQL Server AlwaysOn - Part 2 - Availability Groups Setup

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.