PBM Log Backup Policy and Preferred Replicas

Question

PBM Log Backup Policy and Preferred Replicas

macdonl01

Valued Member

Points: 63
More actions
October 27, 2017 at 10:43 am

#326029

Hello,
I am using EPM to run a policy to check if log backups have happened on databases in the last four hours. It works great with the exception of databases that are in a AG with preferred backups on the secondary. I don't want to leave a server out in case the roles change so right now the primary always fails the policy. I would like it to check if the database is the preferred replica and to not check the policy if it's not.
Besides writing a query from scratch to check everything, the only thing I have found is to put the custom condition in the database facet. However some reason it does not work in combination with my other check @RecoveryModel = Full. Error is 'user database full recovery cannot be used for filtering' but it can be because without the custom check there is no error.
ExecuteSql('Numeric','IF(SERVERPROPERTY(''IsHadrEnabled'')=1)
SELECT sys.fn_hadr_backup_is_preferred_replica(DB_NAME()')
I have also tried to exclude servers by server restriction option using instance name. However it seems to be ignoring the list and I would like a dynamic check anyway. Just looking for new ideas I guess, I appreciate the help, or thoughts if any.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

macdonl01 - Friday, October 27, 2017 10:43 AM
Hello,
I am using EPM to run a policy to check if log backups have happened on databases in the last four hours. It works great with the exception of databases that are in a AG with preferred backups on the secondary. I don't want to leave a server out in case the roles change so right now the primary always fails the policy. I would like it to check if the database is the preferred replica and to not check the policy if it's not.
Besides writing a query from scratch to check everything, the only thing I have found is to put the custom condition in the database facet. However some reason it does not work in combination with my other check @RecoveryModel = Full. Error is 'user database full recovery cannot be used for filtering' but it can be because without the custom check there is no error.
ExecuteSql('Numeric','IF(SERVERPROPERTY(''IsHadrEnabled'')=1)
SELECT sys.fn_hadr_backup_is_preferred_replica(DB_NAME()')
I have also tried to exclude servers by server restriction option using instance name. However it seems to be ignoring the list and I would like a dynamic check anyway. Just looking for new ideas I guess, I appreciate the help, or thoughts if any.

It's hard to say specifically what's wrong without know more about the facets and conditions involved, if you wrote your own, etc. Take a look at the following article and see if it helps tracking things down. It's doing very much the same thing:
Policy Based Management â€“ Tricks & Tips

Sue

macdonl01 Valued Member Points: 63 More actions · Answer 2

Thanks, that's the original post I was using to get the query but wasn't able to get it to work. Trying something different now, grouping just HA servers in a folder and running a different condition on them.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

macdonl01 - Friday, October 27, 2017 2:20 PM
Thanks, that's the original post I was using to get the query but wasn't able to get it to work. Trying something different now, grouping just HA servers in a folder and running a different condition on them.

Your ExecuteSQL is not the same - you may want to revisit that section of the post.

Sue

macdonl01 Valued Member Points: 63 More actions · Answer 4

Fixed the syntax so now the policy does run but still doesn't work. I forget the exact issue but I need another way.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

macdonl01 - Wednesday, November 22, 2017 12:57 PM
Fixed the syntax so now the policy does run but still doesn't work. I forget the exact issue but I need another way.

That is the way to extend or customize what's available with the facets, conditions - using ExecuteSQL().
Not much anyone can help with when you don't know the issue or error. There are several other things that may help or could be the issue but I'm not going there are some unknown issue. Those ending up being too much time on wild guesses. You should also be executing the same statements yourself manually to verify the results.

Sue

macdonl01 Valued Member Points: 63 More actions · Answer 6

To add more color, the issue appears to be that no targets are found on the server that is the secondary replica. I'm not really filtering on databases, I have tried using the default all databases option and just filtering on ones that where not system objects. I am starting to think that because it's a database policy it needs to execute the command "ExecuteSql('Numeric', 'SELECT sys.fn_hadr_backup_is_preferred_replica(DB_NAME())')" within each database. However the secondary replica database is not accessible since by default it's not set to allow read. The policy works as expected on the primary where the databases are accessible. So I'm fairly sure it can't be done without creating a good amount of custom code. Sorry for being lazy about my last comment. I'm not really sure how to close posts. Happy Thanksgiving.