SQL2012 AlwaysOn Availability Groups and SQLBackup

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

SQL2012 AlwaysOn Availability Groups and SQLBackup

Postby ChrisAVWood » Tue Mar 26, 2013 5:19 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby Manfred.Castro » Wed Mar 27, 2013 6:39 pm

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.
Manfred Castro
Product Support
Red Gate Software
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm

Postby ChrisAVWood » Wed Mar 27, 2013 6:46 pm

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

Chris
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby ChrisAVWood » Sun Mar 31, 2013 4:06 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby Tony Barnsley » Wed May 22, 2013 1:47 pm

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
Tony Barnsley
 
Posts: 2
Joined: Wed May 22, 2013 11:22 am


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 1 guest