Last week RC0 for SQL Server 2012 was released. If you missed it you can find it here: http://www.microsoft.com/download/en/details.aspx?id=28145.

One area where you will find improvements or changes is AlwaysOn Availability Groups. In this post I will step through the wizard to show you the improvements that were made. The basis is a 4 node Windows Server 2008 R2 cluster with a stand alone SQL Server 2012 RC0 installation on each of them. All instances use the same domain account. On my first instance, SQL-02, I have two databases; AlwaysOnDb1 and AlwaysOnDb2. I will create an availability group for these databases and sync these to three replica’s.

  • In Management Studio connect to the first instance, SQL-02 and navigate to the new AlwaysOn High Availability tree item. Right click it and choose New Availability Group Wizard.
  • Skip the Welcome Screen by clicking Next.
  • First specify the name for the availability group. I’m using AG-AlwaysOn-1. Click Next to proceed.
  • In the next screen you can specify the databases you can use in your availability group. Choose all that apply and click Next to proceed. If for some reason the database cannot be chosen check the status column.
    image
  • The next screen is where the real magic happens. This screen has changed a lot since CTP3. Let’s go through each tab. The first tab is Replicas. This is where you will specify the replicas. Add an replica by clicking Add Replica and choose your favorite option. I’m using all four instances.
    image
  • The second tab is Endpoints. This is where you will specify the endpoints used by the mirroring sessions. By default these are encrypted.
  • The third tab is Backup Preferences. This is where you specify where the backups are taken. But remember, this is not forced. You have to create the logic yourself. Read more about backups on replicas here: http://msdn.microsoft.com/en-us/library/hh245119(v=sql.110).aspx. To demonstrate this later on I will set the top priority on SQL-04, then SQL-03 and last SQL-02. I will configure SQL-05 as a machine where no backups are allowed.
    image
  • The final tab on this screen is the Listener tab. The listener is the connection between the availability group and the application. The name specified in this tab, is the name you use for your connection. Provide a name and a port and set the network mode to Static or DHCP according to your network setup.
    image
  • Click Next to continue and specify how you want the data to be initialized. There are three options here:
    1. Full – If you want the wizard to take care of the synchronization.
    2. Join Only – If you have already placed the databases on the replica’s.
    3. Skip – If you plan to place the databases on another time.
  • Clicking Next will validate your configuration and the greener the better.
    image
  • Next followed by Finish will create the availability group.
    image
  • The warning is related to the Windows Server cluster configuration. In my case this is not the most desired configuration. The warning provides a link to the KB article that describes the quorum models and what the preferred one is.

Now that the availability group is created we can open up the dashboard to view the health. You can open the dashboard by right clicking the AlwaysOn High Availability item in management studio and choose Show Dashboard. This is what it looks like.

image

To quickly check if I can perform backups on a replica I connect to SQL-05 using management studio, open a new query window and create a COPY_ONLY backup. This will work, although I specified during the wizard that this replica was not available for backups. So remember, you can always create a COPY_ONLY full or a log backup on the replicas. For log backups remember that these will also be part of the log chain. You WILL need these to restore your database to a point in time. You can create backup logic with the new sys.fn_hadr_backup_is_preferred_replica function. Here is an example.

  1. if sys.fn_hadr_backup_is_preferred_replica('AlwaysOnDb1') = 0
  2. begin
  3.     -- This is not the preferred replica
  4.     raiserror( 'Backup is not preferred on this replica',10,1)
  5. end
  6. else
  7. begin
  8.     -- Backup this database
  9.     backup database AlwaysOnDb1 to disk = '\\dc-01\temp\AlwaysOnDb1_FULL.bak' WITH COPY_ONLY
  10. end

SQL Server 2012 RC0 provides an even better experience for the availability groups but you still have to be in control. Know what you are doing. Where you want backups to happen. If log backups are made on replicas. With greater powers come greater responsibilities.