One of the things I had problems with was job synchronisation, to this end I came up with a rather odd method of ensuring that scheduled jobs were run on the boxes that we needed them running on even after fail-overs. Lots of posts suggested altering every job to determine whether it needed to run within the first step, but with others writing jobs and approximately 100 to be moved across I felt this was impractical.
But, first I needed to know when a fail-over occurred and also enable or disable jobs as necessary when that happened - preferably with the minimum amount of effort on my part!!!
To do this I created a new administration database and added it to the availability group, then I created a table for tracking the primary replica.
With this I approached the somewhat more awkward thought of how quickly did we need to be able to identify a fail-over for job purposes, after a lot of discussion with members of the company, it was decided that a 5 minute notification period would be ok.
Next to create a SProc that would check, now I imagine plenty of people will pick holes with this , and please do I always want to improve it
CREATE PROCEDURE dbo.Checkagstate
AS
DECLARE @LastPrimary VARCHAR(80)
DECLARE @CurrentPrimary VARCHAR(80)
DECLARE @JobName VARCHAR(200)
DECLARE @jobID UNIQUEIDENTIFIER
DECLARE @ssql NVARCHAR(500)
SELECT TOP 1 @LastPrimary = primaryagserver FROM tblagstate ORDER BY id DESC
SELECT @CurrentPrimary= primary_replica FROM sys.dm_hadr_availability_group_states WHERE group_id = (SELECT group_id FROM sys.availability_groups WHERE NAME = '<Availability Group Name Here>')
IF (((@LastPrimary <> @CurrentPrimary) AND (@CurrentPrimary = @@SERVERNAME)))
BEGIN
INSERT INTO [dbo].[tblagstate] ([primaryagserver],[changenoted])
VALUES (@CurrentPrimary ,Getdate())
END
Then I added a job to both replicas scheduled for 5 minute intervals. now I know 'roughly' when fail overs have occurred
I'll cover how I expanded on this approach to do job and user management in my next posts! Probably in a day or two
User Management
AS
DECLARE @LastPrimary VARCHAR(80)
DECLARE @CurrentPrimary VARCHAR(80)
DECLARE @JobName VARCHAR(200)
DECLARE @jobID UNIQUEIDENTIFIER
DECLARE @ssql NVARCHAR(500)
SELECT TOP 1 @LastPrimary = primaryagserver FROM tblagstate ORDER BY id DESC
SELECT @CurrentPrimary= primary_replica FROM sys.dm_hadr_availability_group_states WHERE group_id = (SELECT group_id FROM sys.availability_groups WHERE NAME = '<Availability Group Name Here>')
IF (((@LastPrimary <> @CurrentPrimary) AND (@CurrentPrimary = @@SERVERNAME)))
BEGIN
INSERT INTO [dbo].[tblagstate] ([primaryagserver],[changenoted])
VALUES (@CurrentPrimary ,Getdate())
END
Then I added a job to both replicas scheduled for 5 minute intervals. now I know 'roughly' when fail overs have occurred
I'll cover how I expanded on this approach to do job and user management in my next posts! Probably in a day or two
User Management
No comments:
Post a Comment