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.
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.
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.
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.
Robert, could you post the checksums for SQLFULL_ENU.iso please?
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.
Here are the checksums for SQLFULL_ENU.iso if anyone needs it.
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.
where name = 'AppGroup1'
where group_id = (
where name = 'AppGroup1')
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.
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?