Refresh database from PROD to Dev server in existing Alway-on configuration automatically

 

PowerShell Script to copy from PROD server to Dev03 and from Dev03(AG Primary) to Dev04(AG secondary) server. 

You can schedule a job in task scheduler to automatic copy file. 

# Delete all existing backup from below location

Remove-Item D:\ProdBackup\*.bak

Write-Output "Deleted all old existing backup from Dev03 server : TASK COMPLETED"

# Source and Destination backup file location

$_sourcePath ="\\ABC-PROD01\e$\CopyOnlyBackup"

$_destinationPath = "D:\ProdBackup";

# Pick latest .bak file and Copying file Des to Source and rename

$Datetime =Get-Date

Write-Output ("PROD Latest backup file copying started at " + $Datetime + " ...COPY TASK RUNNING....." )

@(Get-ChildItem $_sourcePath -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$_destinationPath\testDB.bak") -force} 

$Datetime1 =Get-Date

Write-Output ("Latest backup file copy Completed on Dev03 server and renamed database file name at " + $Datetime1 + "...COPY TASK COMPLETED..." )

################# Copy backup From Dev03 to Dev04 ######################

# Delete all existing backup from below location

Remove-Item \\ABC-DEV04\d$\ProdBackup\*.bak

Write-Output "Deleted all old existing backup from Dev04 server : TASK COMPLETED"

# Source and Destination backup file location

$_sourcePath ="D:\ProdBackup"

$_destinationPath = "\\ABC-DEV04\d$\ProdBackup";

# Pick latest .bak file and Copying file Des to Source and rename

$Datetime =Get-Date

Write-Output ("PROD Latest backup file copying started at " + $Datetime + " ...COPY TASK RUNNING....." )

@(Get-ChildItem $_sourcePath -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$_destinationPath\testDB.bak") -force} 

$Datetime1 =Get-Date

Write-Output ("Latest backup file copy Completed on Dev server and renamed database file name at " + $Datetime1 + "...COPY TASK COMPLETED..." )

Once Copy done by above Script. 

Create below job on AG primary server Dev03. 

Note: This will contain total 6 step to Restore DB on Primary and Secondary server and will configure AG between Dev03 and and Dev04 automatically.

USE [msdb]

GO

/****** Object:  Job [Monthly_Test_DB_Refresh_From_PROD]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC msdb.dbo.sp_delete_job @job_id=N'8c70a241-323d-48e5-80ce-f97190c07661', @delete_unused_schedule=1

GO

/****** Object:  Job [Monthly_Test_DB_Refresh_From_PROD]    Script Date: 7/6/2021 7:45:32 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 7/6/2021 7:45:32 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Monthly_Test_DB_Refresh_From_PROD', 

@enabled=1, 

@notify_level_eventlog=0, 

@notify_level_email=0, 

@notify_level_netsend=0, 

@notify_level_page=0, 

@delete_level=0, 

@description=N'No description available.', 

@category_name=N'[Uncategorized (Local)]', 

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [1-Remove AG from Dev Primary]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1-Remove AG from Dev Primary', 

@step_id=1, 

@cmdexec_success_code=0, 

@on_success_action=4, 

@on_success_step_id=2, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RemoveDBFromAGgroup.sql

', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [2-DeleteDBOnSecondary]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'2-DeleteDBOnSecondary', 

@step_id=2, 

@cmdexec_success_code=0, 

@on_success_action=4, 

@on_success_step_id=3, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV04 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\DeleteDBOnSecondary.sql', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [3-DeleteDBOnPrimary]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'3-DeleteDBOnPrimary', 

@step_id=3, 

@cmdexec_success_code=0, 

@on_success_action=4, 

@on_success_step_id=4, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\DeleteDBOnPrimary.sql', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [4-RestoreDBOnPrimary]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'4-RestoreDBOnPrimary', 

@step_id=4, 

@cmdexec_success_code=0, 

@on_success_action=4, 

@on_success_step_id=5, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RestoreDBOnPrimary.sql', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [5-RestoreDBOnSecondary]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'5-RestoreDBOnSecondary', 

@step_id=5, 

@cmdexec_success_code=0, 

@on_success_action=4, 

@on_success_step_id=6, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RestoreDBOnSecondary.sql', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [6- Add DB in AG]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'6- Add DB in AG', 

@step_id=6, 

@cmdexec_success_code=0, 

@on_success_action=1, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\AddDBInAG.sql', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Now the below SQL script of code divided in 6 step for your above created SQL Job.

Save each steps of code on Dev03(AG primary server) at D:\ProdBackup\AG_Refresh_SQL_Scripts\*.sql format

Note: File name refer from created job steps.

All 6 *.sql file will access by above job.

Note: Before/while saving the .sql file go to Query --> and SQLCMD option.

-----------File name 1 -Remove DB from AG group-------------------------

:connect ABC-DEV03

USE [master]

GO

/****** Object:  AvailabilityDatabase testdb    Script Date: 7/1/2021 2:45:39 AM ******/

ALTER AVAILABILITY GROUP [ABC-DV-AWO]

REMOVE DATABASE testdb;

GO

-----------File name 2 Delete DB on Secondary ------------------------------

:connect ABC-DEV04

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'testdb '

GO

USE [master]

GO

/****** Object:  Database testdb    Script Date: 7/1/2021 2:48:58 AM ******/

DROP DATABASE testdb

GO

----------File name 3 Delete DB on Primary------------------------------

:connect ABC-DEV03

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'testdb '

GO

use testdb

GO

use [master]

GO

USE [master]

GO

ALTER DATABASE testdb SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

GO

USE [master]

GO

/****** Object:  Database testdb    Script Date: 7/1/2021 2:51:08 AM ******/

DROP DATABASE testdb

GO

-------File name 4 Restore DB on Secondary -------------------------------

:connect ABC-DEV04

RESTORE DATABASE TestDB 

FROM DISK = N'D:\ProdBackup\testdb.bak' 

WITH FILE = 1,  

     MOVE N'testdb' TO N'E:\Data\testdb.mdf',  

     MOVE N'testdb_log' TO N'L:\DBLogs\testdb.ldf',

NORECOVERY, 

     NOUNLOAD, REPLACE, STATS = 5

-------File name 5 Restore DB on Primary-------------------------------

:connect ABC-DEV03

RESTORE DATABASE TestDB 

FROM DISK = N'D:\ProdBackup\testdb.bak' 

WITH FILE = 1,  

     MOVE N'testdb' TO N'E:\Data\testdb.mdf',  

     MOVE N'testdb_log' TO N'L:\DBLogs\testdb.ldf',

     NOUNLOAD, REPLACE, STATS = 50

---------------File name 6 - add DB in AG -----------------------

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:Connect ABC-DEV03

USE [master]

GO

ALTER AVAILABILITY GROUP [ABC-DV-AWO]

MODIFY REPLICA ON N'ABC-DEV04' WITH (SEEDING_MODE = MANUAL)

GO

USE [master]

GO

ALTER AVAILABILITY GROUP [ABC-DV-AWO]

ADD DATABASE testdb;

GO

:Connect ABC-DEV04

-- Wait for the replica to start communicating

begin try

declare @conn bit

declare @count int

declare @replica_id uniqueidentifier 

declare @group_id uniqueidentifier

set @conn = 0

set @count = 30 -- wait for 5 minutes 

if (serverproperty('IsHadrEnabled') = 1)

and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)

and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)

begin

    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'ABC-DV-AWO'

select @replica_id = replicas.replica_id from 

master.sys.availability_replicas as replicas 

where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

while @conn <> 1 and @count > 0

begin

set @conn = isnull((select connected_state from

master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

if @conn = 1

begin

-- exit loop when the replica is connected, or if the query cannot find the replica status

break

end

waitfor delay '00:00:10'

set @count = @count - 1

end

end

end try

begin catch

-- If the wait loop fails, do not stop execution of the alter database statement

end catch

ALTER DATABASE testdb SET HADR AVAILABILITY GROUP = [ABC-DV-AWO];

GO

GO

Create listener name SQLAWSDBLSTNR in Active Directory for Always On in SQL Server

Create listener name SQLAWSDBLSTNR in Active Directory as mentioned in below steps:

•       Open the Active Directory Users and Computers Snap-in (dsa.msc).

•       In Menu > View -> Advanced Features. (Otherwise, we would not see option explained in next steps)

•       Right click the OU/Container where we want the VCO to be created and click “New” -> “Computer”

•       Provide a name for the object (SBAWSFARDBLSTNR) and click “OK”:

•       Right click on the on the VCO which we just created and select “Properties”. Click the security tab and then click “Add”:

•       Enter the CNO (SQLAWSAG01) (Make sure to select “Computers” option in the “Object Types” window) and click “OK”. The CNO is a Cluster Name Object.

•        Give CNO “Full Control” over the VCO.

Always-On Monitoring using Datadog

Always-On Monitoring

The below three error code which need to monitor for Always-on

Query:

SELECT * FROM SYS.SYSMESSAGES WHERE MSGLANGID=1033 AND DESCRIPTION LIKE '%AVAILABILITY%' AND ERROR IN (1480, 35264, 35265)

error	Description
1480	The %S_MSG database "%.*ls" is changing roles from "%ls" to "%ls" because the mirroring session or availability group failed over due to %S_MSG. This is an informational message only. No user action is required.
35264	Always On Availability Groups data movement for database '%.*ls' has been suspended for the following reason: "%S_MSG" (Source ID %d; Source string: '%.*ls'). To resume data movement on the database, you will need to resume the database manually. For info
35265	Always On Availability Groups data movement for database '%.*ls' has been resumed. This is an informational message only. No user action is required.

Note: As we do not have DB mail option so that we need to use Datadog alerting.

Let’s begin….

Error: 1480: The Always on Failover occurred. The below steps need to perform on Primary and DR server.

Step 1: Create an Alert in SQL Server to capture the error event.

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'AG_FailOver',

              @message_id=1480,

              @severity=0,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]'--,

              --@job_id=N'6f93aabb-a0ba-4e57-8695-1febaebe6c7c'

GO

Step 2: Create a Job in SQL server to send the error details to table.

USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 11/12/2018 4:44:24 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AG_FailOver_Job',

              @enabled=1,

              @notify_level_eventlog=0,

              @notify_level_email=0,

              @notify_level_netsend=0,

              @notify_level_page=0,

              @delete_level=0,

              @description=N'No description available.',

              @category_name=N'[Uncategorized (Local)]',

              @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [AG_FailOver]    Script Date: 11/12/2018 4:44:24 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'AG_FailOver',

              @step_id=1,

              @cmdexec_success_code=0,

              @on_success_action=1,

              @on_success_step_id=0,

              @on_fail_action=2,

              @on_fail_step_id=0,

              @retry_attempts=0,

              @retry_interval=0,

              @os_run_priority=0, @subsystem=N'TSQL',

              @command=N'Insert into [CSMonitor].[dbo].[AG_ERROR_FailOver] (Error_code,Error_desc,date) values(1480,''AG Failover'',getdate())',

              @database_name=N'master',

              @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Step 3: Create a table in CSMonitor database to log the error details.

USE [CSMonitor]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[AG_ERROR_FailOver](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [Error_code] [int] NULL,

       [Error_desc] [varchar](max) NULL,

       [Date] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Step 4: Open the property of the created Alert (AG_FailOver), Click on Response à  check the Execute Job check box à Select the create job(AG_FailOver_Job) from drop down list and Press OK button.

Error: 35264: The Data Moment suspended. The below steps need to perform on Primary and DR server.

Step 1: Create an Alert in SQL Server to capture the error event.

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'AG_Data_Moment_Suspended',

              @message_id=35264,

              @severity=0,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]'--,

              --@job_id=N'6f93aabb-a0ba-4e57-8695-1febaebe6c7c'

GO

Step 2: Create a Job in SQL server to send the error details to table.

USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 11/12/2018 4:44:24 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AG_Data_Movment_Suspended_Job',

              @enabled=1,

              @notify_level_eventlog=0,

              @notify_level_email=0,

              @notify_level_netsend=0,

              @notify_level_page=0,

              @delete_level=0,

              @description=N'No description available.',

              @category_name=N'[Uncategorized (Local)]',

              @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [AG_Data_Movment_Suspended]    Script Date: 11/12/2018 4:44:24 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'AG_Data_Movment_Suspended',

              @step_id=1,

              @cmdexec_success_code=0,

              @on_success_action=1,

              @on_success_step_id=0,

              @on_fail_action=2,

              @on_fail_step_id=0,

              @retry_attempts=0,

              @retry_interval=0,

              @os_run_priority=0, @subsystem=N'TSQL',

              @command=N'Insert into [CSMonitor].[dbo].[AG_ERROR_Data_Moment_suspended] (Error_code,Error_desc,date) values(35264,''Data Moment suspended'',getdate())',

              @database_name=N'master',

              @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Step 3: Create a table in CSMonitor database to log the error details.

USE [CSMonitor]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[AG_ERROR_Data_Moment_suspended](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [Error_code] [int] NULL,

       [Error_desc] [varchar](max) NULL,

       [Date] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Step 4: Open the property of the created Alert (AG_Data_Moment_Suspended), Click on Response à  check the Execute Job check box à Select the create job() from drop down list and Press OK button.

Error: 35265: The Data Moment resumed. The below steps need to perform on Primary and DR server.

Step 1: Create an Alert in SQL Server to capture the error event.

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'AG_Data_Moment_Resumed',

              @message_id=35265,

              @severity=0,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]'--,

              --@job_id=N'6f93aabb-a0ba-4e57-8695-1febaebe6c7c'

GO

Step 2: Create a Job in SQL server to send the error details to table.

USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 11/12/2018 4:44:24 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AG_Data_Movment_Resumed_Job',

              @enabled=1,

              @notify_level_eventlog=0,

              @notify_level_email=0,

              @notify_level_netsend=0,

              @notify_level_page=0,

              @delete_level=0,

              @description=N'No description available.',

              @category_name=N'[Uncategorized (Local)]',

              @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [AG_Data_Movment_Resumed]    Script Date: 11/12/2018 4:44:24 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'AG_Data_Movment_Resumed',

              @step_id=1,

              @cmdexec_success_code=0,

              @on_success_action=1,

              @on_success_step_id=0,

              @on_fail_action=2,

              @on_fail_step_id=0,

              @retry_attempts=0,

              @retry_interval=0,

              @os_run_priority=0, @subsystem=N'TSQL',

              @command=N'Insert into [CSMonitor].[dbo].[AG_ERROR_Data_Moment_Resumed] (Error_code,Error_desc,date) values(35265,''Data Moment Resumed'',getdate())',

              @database_name=N'master',

              @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Step 3: Create a table in CSMonitor database to log the error details.

USE [CSMonitor]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[AG_ERROR_Data_Moment_Resumed](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [Error_code] [int] NULL,

       [Error_desc] [varchar](max) NULL,

       [Date] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Step 4: Open the property of the created Alert (AG_Data_Moment_Resumed), Click on Response à  check the Execute Job check box à Select the create job(AG_Data_Moment_Resumed_Job) from drop down list and Press OK button.

·         Modify DataDog stored procedure to add the script for Datadog agent. Copy below SP and execute on Primary and DR server.

USE [msdb]

GO

/****** Object:  StoredProcedure [dbo].[SP_datadogtest]    Script Date: 11/12/2018 5:20:16 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SP_datadogtest]

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

declare @AA int

declare @bb int

declare @cc int

declare @dd int

declare @ee int

declare @ff int

declare @gg int

declare @hh int

declare @ii int

declare @jj int

declare @kk int

declare @ll int

DECLARE @DBCOUNT INT

-- If the job got failed, success or cancel by anyone.

set @aa=( SELECt COUNT(*) FROM MSDB.DBO.SYSJOBSERVERS SJS

LEFT OUTER JOIN MSDB.DBO.SYSJOBS SJ ON (SJ.JOB_ID = SJS.JOB_ID)

where SJS.LAST_RUN_OUTCOME in (0,2) and enabled =1

and convert(varchar(10), last_run_date, 126) = (convert(varchar(10), GETDATE(), 112) )) -- 0 failed 2 cannaclled

-- If the log file got full more than 80 percente

create table #TmpLOGSPACE(

  DatabaseName varchar(100)

  , LOGSIZE_MB decimal(18, 9)

  , LOGSPACE_USED decimal(18, 9)

  ,  LOGSTATUS decimal(18, 9))

insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS)

exec ('DBCC SQLPERF(LOGSPACE);')

set @bb= (select count(*) from #TmpLOGSPACE where DatabaseName not in ('Model','master','msdb')

and LOGSPACE_USED > 90) -- This is value which can be change as per our requirement.

-- Drop table #TmpLOGSPACE

-- If the blocking is more than 10 minutes

set @cc=(SELECT count (*) FROM SYS.DM_EXEC_REQUESTS REQ

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQLTEXT

WHERE TOTAL_ELAPSED_TIME >=600000 -- 10 MINUTES

and blocking_session_id <>0)

-- If query running more than 1 hour

set @dd=(SELECT count(*) FROM SYS.DM_EXEC_REQUESTS REQ

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQLTEXT

WHERE TOTAL_ELAPSED_TIME >=3600000) -- 1 hour

-- If DB status is not online

set @ee =( select COUNT(*) FROM sys.databases where state_desc in ('Offline','EMERGENCY', 'SUSPECT'))

-- If any login created on instance level

set @gg=( select count(*) from sys.syslogins nolock

where convert(varchar(10), createdate, 126)  >= convert(varchar(10), GETDATE(), 126) )

--If backup not done since 1 day

SET @HH = '0'

-- If job got modified by anyone.

set @ii =(select count(*) from  MSDB.DBO.SYSJOBS nolock where convert(varchar(10), date_modified, 126)  >= convert(varchar(10), GETDATE(), 126) )

--declare @timeTORUN int

--set @timeTORUN =(select SUBSTRING ( CONVERT(VARCHAR(8),GETDATE(),108),1,2))

--if (@timeTORUN =2 or @timeTORUN =4 or @timeTORUN =6 or @timeTORUN =8 or @timeTORUN =12 or @timeTORUN =14 or @timeTORUN =16 or @timeTORUN =18 or @timeTORUN =20 or @timeTORUN =22 or @timeTORUN =24)

--begin

--if database created in last one day

create table #Tmpdatabasecreation(

  databaseName varchar(100)

  , db_size nvarchar(100)

  , [owner] varchar(50)

  ,  [dbid] int

  , created date

  , [status] varchar(2000)

  ,compatiblity_level int )

insert #Tmpdatabasecreation(databaseName, db_size, [owner], [dbid],created,[status],compatiblity_level)

exec ('Sp_helpdb')

set @ff= (select count(*) from #Tmpdatabasecreation where DatabaseName not in ('tempdb')

and created >= convert(varchar(10), GETDATE(), 126) )

-- Drop table #Tmpdatabasecreation

 -- If AG data moment suspended

 set @jj= (select count(*) from CSMonitor.[dbo].[AG_ERROR_Data_Moment_suspended] where  [Date] >= convert(varchar(10), GETDATE(), 126) )

  -- If AG data moment Resumed

 set @kk= (select count(*) from CSMonitor.[dbo].[AG_ERROR_Data_Moment_Resumed] where  [Date] >= convert(varchar(10), GETDATE(), 126) )

  -- If AG Fail over

 set @ll= (select count(*) from CSMonitor.[dbo].[AG_ERROR_FailOver] where  [Date] >= convert(varchar(10), GETDATE(), 126) )

--end

CREATE TABLE #Datadog (

[metric] varchar(255) not null,

[type] varchar(50) not null,

[value] float not  null,

[tags] varchar(255))

insert into #Datadog values

('sqlserver.Agent.failedJob','gauge',@aa,'tag:FailedJob'),

('sqlserver.DB.LogSpace','gauge',@bb,'tag:LogSpace'),

('sqlserver.DB.Blocking','gauge',@cc,'tag:Blocking'),

('sqlserver.DB.LRQ','gauge',@dd,'tag:LRQ'),

('Sqlserver.DB.DBstatus','gauge',@ee,'tag:DBstatus'),

('Sqlserver.DB.NewDBCreated','gauge',@ff,'tag:NewDBCreated'),

('Sqlserver.DB.NewLoginCreated','gauge',@gg,'tag:NewLoginCreated'),

('Sqlserver.DB.BackupStatus','gauge',@hh,'tag:BackupStatus'),

('Sqlserver.DB.JobModified','gauge',@ii,'tag:JobModified'),

('Sqlserver.DB.Data_Moment_suspended','gauge',@jj,'tag:Data_Moment_suspended'),

('Sqlserver.DB.Data_Moment_Resumed','gauge',@kk,'tag:Data_Moment_Resumed'),

('Sqlserver.DB.FailOver','gauge',@ll,'tag:FailOver')

select * from #Datadog

END

Created a matrix in Datadog console to send the alert notification mail to DBA team.