Share via

SQL Server 2012 RC0, A New AlwaysOn Experience

  • Article

Last week RC0 for SQL Server 2012 was released. If you missed it you can find it here: https://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: https://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.

Comments

  • Anonymous
    January 01, 2003
    @pieter I am unable to reproduce this on my system. Can you post the output of the following query, replacing AppGroup1 with the name of your availability group. Thx. select automated_backup_preference_desc from sys.availability_groups where name = 'AppGroup1' select availability_mode_desc, backup_priority from sys.availability_replicas where group_id = ( select group_id from sys.availability_groups where name = 'AppGroup1')

  • Anonymous
    January 01, 2003
    Just to get it clear. You must supply the databasename with the sys.fn_hadr_backup_is_preferred_replica function, not the availability group name. Which makes sense because the backup is on the database level, not the availability group level. However, specifying a non existing databasename or null as parameter for sys.fn_hadr_backup_is_preferred_replica results in a 1.

  • Anonymous
    December 04, 2011
    Robert, could you post the checksums for SQLFULL_ENU.iso please?

  • Anonymous
    December 06, 2011
    Hi Robert, I was playing around with the new backup pref. options. But when I try the function sys.fn_hadr_backup_is_preferred_replica, I always get a result 1. This is for each replica the same, even if I change the priorities. Any ideas? Regards Pieter

  • Anonymous
    December 06, 2011
    Here are the checksums for SQLFULL_ENU.iso if anyone needs it. CRC-32: 5c939061 MD4: bfb3aef4281ea3677583ca2e07dbc091 MD5: 24e3f7e57c81f53f84f8ff4c773b2e96 SHA-1: 56f201082b9270a1d4db0190a97becb2f22c6144

  • Anonymous
    November 27, 2013
    Hi Robert, question about readable secondary option - does it impact on result of sys.fn_hadr_backup_is_preferred_replica function? in your example you use SQL-05 replica to check backup . but if it was SQL-03 or SQL-02 for example, having 'no' connection option? does it impact on backup ability?