SQL2012 AlwaysOn Availability Groups and SQLBackup

ChrisAVWood

Hi,

We are just setting up two SQL2012 Servers that will be clustered to use Availability Groups. Because some of the databases are large we still want to use Red Gate SQLBackup as we use split backups and SQLBackup gives us uniquely named backup files that maintenance plans will not do. From what I understand if we create a database on one node or restore to it and then take a full backup it would automatically replicate to the other node(s) in the Availability Group. This could be a long process for large databases.

Is anybody using SQLBackup with SQL2012 Availability Groups that could give advice on best practices?

Thanks

Chris

Manfred.Castro

SQL Backup provides limited support for AlwaysOn Availability Groups introduced in SQL Server 2012. SQL Backup must be installed on each SQL Server instance separately; you cannot use the cluster installation. In the event of a failover, any scheduled jobs from the old primary server must be created manually on the new primary server.

ChrisAVWood

Thanks Manfred. As I read more about AlwaysOn Availability Groups I see that most things have to be duplicated.

Chris

ChrisAVWood

I am also interested in the permissions. Our current method involves using the windows account that runs SQL Server to be the account used to run SQLBackup. To install SQLBackup I had made this account a sysadmin. I then changed that to db_creator when I wanted to restore. We use a proxy to run the actual backup jobs. Now I believe that I would have to give the proxy account the availability groups permissions.

Chris

Tony Barnsley

What I've done on our Always On Availability groups is to modify our 'standard' Backup stored procedure to determine if the database is a member of the Availability group and if it is on the replica do a COPY Only Backup. (We don't back up on the 'live' replica . . . one benefit of the Always On Feature is to be able to take backups from one of the replicas). Our databases are of a reasonable size (~150GB) and backup times are very reasonable.

Of course actually creating the replica in the first place takes a bit of time.

What's good for us is that we have 3 SQL servers with several availability groups, and independent Databases, and this approach ensures that all the databases are backed up. The only issue is collecting together all the Full DIff and TLOG backups in the backup chain if you have to do a restore . . . Hence a COPY_TO to a common network location.

Writing your own Backup Stored procedure gives you a lot more flexibility to use SQL Backup as a tool to ensure your data is protected, rather than relying on the required functionality being added into the GUI