February 25, 2016 at 2:07 pm
Greetings,
I am a Systems Engineer and have just recently taken over the role of a DBA at my company. We have 2 servers running 30 AAG's and 168 databases. I just purchases SQL Backup Pro. The issue I am running into is being able to backup only the preferred secondary and not the primary. Red gate provided me with a link but I just cant seem to get it to work properly.
The link is http://documentation.red-gate.com/display/SBU7/AlwaysOn+Availability+Groups
They state that if I add the code below prior to the execution of the sproc. It should work and all is good.
IF (sys.fn_hadr_backup_is_preferred_replica(@DBNAME)<>1)
BEGIN
Select 'This is not the preferred replica, exiting with success';
RETURN -- This is a normal, expected condition, so the script returns success
END
When I attempt to use this in my backup job. I get an error that @DBNAME needs to be declared. After further research, it seems I need to create a line like the above for every database. That is a lot lines to write. Does anyone know a way to do that dynamically?
February 26, 2016 at 9:53 am
First, sorry about that. I work for Redgate, and Backup Pro has lagged back a bit in development as MS has added many of our features to the native product.
That being said, this should work:
SELECT DB_NAME()
Not @dbname.
In terms of doing this dynamically, you could write a script that generates these scripts for the jobs. That's not too hard, though it is still work. Ideally you'd generate the script that builds the jobs. Really the resverse of this: http://sqlgator.com/2012/12/28/creating-a-red-gate-backup-pro-virtual-restore-job-automatically/
I don't have time today, but I'll ask around if anyone has done this already.
Steve
February 26, 2016 at 12:07 pm
We don't have a script handy, but if you give some details on how you're setting up backup jobs and what you want, I'll see if I can knock something out tonight or tomorrow that will help. I suspect it's not too difficult to check if a db is in an AG, and then whether this is the primary or not and decide to back things up.
February 26, 2016 at 3:53 pm
Hi Steve,
I cant thank you enough for taking the time to assist a newbie. My goal is to be able to use the the newest version of SQL backup pro. After I bought the product. I was not aware of the short coming of backups within an AAG.
So with that in mind. I have my AAG's configured for Secondary preferred. I need to ensure that the backup software will honor that rule. As I found out it will not without some kind of check to know if its the preferred or not.
If its the preferred, backup the databases to X location else end.
February 27, 2016 at 2:23 pm
OK, are these run from the Backup Pro scheduler, just SQL agent jobs? Something else?
February 27, 2016 at 3:30 pm
I will be setting up the backup jobs through the backup pro scheduler. Which then created a backup job on the server (see example below). Their documentation states that I need to edit the job to make the check for the backup preferences prior to their sproc running. Its getting the check to work that is causing me the confusion. I cant seem to figure out how to make the check work within their backup job.
--Their documentation
SQL Backup ignores Availability Group backup preferences. If you want a scheduled job to use these preferences, you need to directly modify the job and precede the SQL Backup Pro stored proc call with a check on the backup preferences. For example:
--what they say I need to add to make it work
IF (sys.fn_hadr_backup_is_preferred_replica(@DBNAME)<>1)
BEGIN
Select 'This is not the preferred replica, exiting with success';
RETURN -- This is a normal, expected condition, so the script returns success
END
--Backup job created by backup pro
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [MydataBase] TO DISK = ''D:\Backup\<database>\<AUTO>.sqb'' WITH ERASEFILES = 3, PASSWORD = ''<ENCRYPTEDPASSWORD>Passwordhere</ENCRYPTEDPASSWORD>'', CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 2, COMPRESSION = 2, COPYTO = ''MyAzureStorage\'', KEYSIZE = 256, THREADCOUNT = 2"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
February 29, 2016 at 8:35 am
First, my apologies. I was researching some of the AG stuff over the weekend, but never really got back to it.
I think you can edit the jobs in SQL Backup Pro and the changes will carry through. That isn't a lot of help if you've got many jobs, and I apologize for that. Most people add the backup jobs piecemeal as they go, so I think this is a bit of a corner case.
The AG check certainly isn't, but what I'd lean towards is something like this:
- check if this database is in an AG and not backup preferred.
- if so, stop
- otherwise, backup.
What I'd look to do is implement the first check like this:
declare @stop tinyint = 0;
SELECT @db =
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id;
if @dbname is not null
Select @stop = 1;
if (@stop = 1) and (sys.fn_hadr_backup_is_preferred_replica(@DBNAME)<>1)
BEGIN
Select 'This is not the preferred replica, exiting with success';
RETURN -- This is a normal, expected condition, so the script returns success
END
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [MydataBase] TO DISK = ''D:\Backup\<database>\<AUTO>.sqb'' WITH ERASEFILES = 3, PASSWORD = ''<ENCRYPTEDPASSWORD>Passwordhere</ENCRYPTEDPASSWORD>'', CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 2, COMPRESSION = 2, COPYTO = ''MyAzureStorage\'', KEYSIZE = 256, THREADCOUNT = 2"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
Note, I don't have an AG handy, but I'd try this in SQL Server to test. Skip the backup part and put in a message there. You should be able to then add the first part (query + IF) to the SQL Backup Pro job.
February 29, 2016 at 11:14 am
One other note, if you add logic to the job step, and then you use SQL Backup to change something, you'll lose your logic. The only way I see to do this practically, is to use a separate step for the logic. Put in the checks there, and then if they meet the criteria for stopping, return a -1. Then alter the step to say that on failure, you quit the job.
You can learn a bit more about multiple steps here: http://www.sqlservercentral.com/articles/Stairway+Series/72457/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply