Agent Jobs

Create Standard AlwaysOn Jobs

It’s always a good idea to create some Standard AlwaysOn jobs for management purposes whenever you’re first learning, or experimenting with the AlwaysOn configurations. You’ll find times when you need to remove databases, or all databases from the HADR configurations, or to completely remove them from Availability Groups.

Below you’ll find some quick logic to create these jobs. You can see some typical AlwaysOn Jobs for this purpose.

In this case we’ll be focusing the 2 of them.
ALWAYSON – REMOVE DATABASES FROM AG

ALWAYSON – SET HADR OFF FOR DATABASES

You can take the logic below and throw them into a quick Job if needed.

ALWAYSON – REMOVE DATABASES FROM AG

use master;

set nocount on
declare @remove_from_ag         varchar(max)
declare @availability_group     varchar(255)
set     @remove_from_ag         = ''
set     @availability_group     = (select name from sys.availability_groups)
select distinct @remove_from_ag = @remove_from_ag +
'alter availability group [' + @availability_group + '] remove database [' + database_name + '];' + char(10)
from   sys.dm_hadr_database_replica_cluster_states
exec   (@remove_from_ag)

ALWAYSON – SET HADR OFF FOR DATABASES

use master;
set nocount on
declare @set_hadr_off          varchar(max)
declare @availability_group    varchar(255)
set     @set_hadr_off          = ''
set     @availability_group    = (select name from sys.availability_groups)
select  distinct @set_hadr_off = @set_hadr_off +
'alter  database [' + database_name + '] set hadr off;' + char(10)
from    sys.dm_hadr_database_replica_cluster_states
exec    (@set_hadr_off)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s