Archive for the ‘Automation’ Category

How to get notified on SQL Server Agent and/or SQL Server Service restarts in addition to identifying the current active node for the failover cluster?

Unlike your TV, SQL Server can’t be turned off or on as per your desire. For mission critical SQL Servers, the uptime is extremely critical as one or more applications are connected to the databases to support the application users at all times throughout the day and possibly throughout many regions around the world. Planned maintenance downtime required for version upgrades, service pack upgrades, hot fixes, and production deployments or releases of which will require services or host reboots must be coordinated, planned, and approved by the management team. In case of unexpected service interruptions such as SQL Server Agent and SQL Server Service restarts and/or failover for the clusters, you must be notified and find out and document the root cause to prevent future surprises. How can we do that?

As we all know, when we schedule a SQL Server Agent job, one of the possible schedule types is “Start automatically when SQL Server Agent starts.” We can use this feature to notify us via database mail on SQL Server Agent and/or SQL Server Service restarts. When a failover on a cluster occurs, SQL Server service will also get restarted. We will identify the current active node for the failover clusters.

Here are the overview steps and concepts:

  1. Develop the T-SQL code to detect SQL Server Agent and/or SQL Server Service restarts in addition to finding the current active node in the cluster.
  2. Schedule a SQL Server Agent job to run the T-SQL code whenever SQL Server Agent starts.

Here are the implementation steps:

  1. Develop the T-SQL code to detect SQL Server Agent and/or SQL Server Service restarts in addition to finding the current active node in the cluster.
  2. -- Alert on SQL Server Agent and/or SQL Server service restarts
    -- Replace your recipients in line # 14
    
    -- declare the required variables
    DECLARE @Message VARCHAR(MAX)
    , @Subject VARCHAR(123)
    , @Recipients VARCHAR(123)
    , @IsDBMailEnabled BIT
    , @MailProfile VARCHAR(123)
    , @IsClustered VARCHAR(1) -- this is because SERVERPROPERTY('IsClustered') is a sql_variant data type
    , @CurrentNodeName VARCHAR(123)
    , @InstanceName VARCHAR(123)
    , @RestartTime VARCHAR(123)
    
    set @Recipients = 'email@company.com' -- specify the recipients here, separate with ; 
    
    SELECT @InstanceName = CONVERT(VARCHAR, SERVERPROPERTY('ServerName'))
    , @IsClustered = CONVERT (VARCHAR, SERVERPROPERTY('IsClustered'))
    , @CurrentNodeName = CONVERT (VARCHAR, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
    
    -- SQL Server service has been restarted
    IF (
    	SELECT DATEDIFF(MI, CREATE_DATE,GETDATE() ) 
    	FROM SYS.DATABASES 
    	WHERE NAME = 'TEMPDB'
    	) <= 3 -- SQL Server service restarted in the past 3 minutes
    	BEGIN
    		SELECT @RestartTime = CONVERT(VARCHAR, create_date, 13)
    		FROM SYS.databases
    		WHERE NAME = 'TEMPDB'
    		
    		SET @Message = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted at: ' +@RestartTime
    		SET @Subject = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted!'
    
    		
    		IF @IsClustered = '1'
    			BEGIN
    				SET @Message = @Message + ' and the current active node is: ' +@CurrentNodeName+ '.'
    				SET @Subject = @Subject + ' The current active node is: ' +@CurrentNodeName+ '.' 
    			END
    	END
    ELSE 	
    	BEGIN
    		SET @Message = @InstanceName + ' SQL Server Agent service has been restarted!'
    		SET @Subject = @InstanceName + ' SQL Server Agent service restarted!'
    	END 
    
    SET @Message = @Message + CHAR(10)
    SET @Message = @Message + 'If this is not a planned maintenace. Please verify the status of the databases before restarting any application services and review the log files to find the possible root causes!'	
    
    -- Find the database mail profile name
    create table #dbmail_profile
    (
    profileid int null,
    profilename varchar(125) null,
    accountid int null,
    accountname varchar(125) null,
    sequencenumber int null
    )
    insert #dbmail_profile
    EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;
    
    select @MailProfile = profilename
    from #dbmail_profile
    where sequencenumber = 1
    
    drop table #dbmail_profile
    
    EXEC msdb.dbo.sp_send_dbmail
    	@profile_name = @MailProfile,
    	@recipients = @Recipients,
    	@subject = @Subject,
    	@body_format = 'TEXT',
    	@body = @Message;
    
  3. Schedule a SQL Server Agent job to run the T-SQL code whenever SQL Server Agent starts.
  4. Create a SQL Server Agent job the way as you have always been doing, then on the schedule page when choosing a “schedule type,” select the “Start automatically when SQL Server Agent starts” option.
    New Job Schedule

If you need to also identify the previous active node for the failover cluster in addition to the current active node, you will need to create a table in a “utility” database or the master database to store the cluster nodes information, then on SQL Server service restarts make a comparison of the previous active node and the current active node and embed the information on the notification email.

If you wish to check if other instances of SQL Server are running and you don’t have a third party monitoring system, you can schedule a SQL Server Agent job to ping the host of the other instances or check for their SQL Server Agent and SQL Server services statuses.

 

REFERENCES:

SERVERPROPERTY
http://msdn.microsoft.com/en-us/library/ms174396(v=sql.105).aspx

DATEDIFF
http://msdn.microsoft.com/en-us/library/ms189794(v=sql.105).aspx

How to create an operator and configure SQL Server Agent to notify the operator in case of job failures?

Wouldn’t it be nice if you can get SQL Server to notify you of an event that has occurred or only if there are any job failures so that you can enjoy your Monday Night Football game without constantly checking your laptop to see the latest job executions? Yes. It would be nice and SQL Server does have that feature – operators. Operators are accounts that can receive notifications when an event occurs or a job fails via email, pager, or net send. There’s a caveat that you will need to be aware of when enabling job failure alerts, you must enable the “mail profile” or else no email notification can be sent. The following documents the steps on how to:

  1. Create an operator to receive event or job failure notifications.
  2. Configure the SQL Server Agent to enable the mail profile and define the fail-safe operator as a catch-all operator so that you won’t miss out a thing. SQL Server Agent service restarted required.
  3. Update a SQL Server Agent job to enable job failure notification.

Here are the scripts for each step:

  1. Create an operator.
  2. -- create an operator
    USE [msdb] 
    GO 
    EXEC msdb.dbo.sp_add_operator 
    		@name=N'007' -- name the operator
    		,@enabled=1
    		,@weekday_pager_start_time=0
    		,@weekday_pager_end_time=235959
    		,@saturday_pager_start_time=0
    		,@saturday_pager_end_time=235959 
    		,@sunday_pager_start_time=0
    		,@sunday_pager_end_time=235959
    		,@pager_days=127
            ,@email_address=N'emailaddress@company.com' -- modify the email address here
    GO
    
  3. Configure the SQL Server Agent to enable the mail profile and define the fail-safe operator as a catch-all operator so that you won’t miss out a thing. SQL Server Agent service restarted required.
  4. -- find and write down your database mail profile
    SELECT name as DatabaseMailProfile
    FROM [msdb].[dbo].[sysmail_profile]
    
    -- Enable the database mail profile in the SQL Server Agent Alert System
    -- Restart SQL Server Agent service for this to take effect
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_set_sqlagent_properties 
    @email_save_in_sent_folder=0 -- option to save copies of the sent messages in the Sent Items folder
    GO
    EXEC master.dbo.xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
    N'UseDatabaseMail', 
    N'REG_DWORD', 
    1
    GO
    EXEC master.dbo.xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
    N'DatabaseMailProfile', 
    N'REG_SZ', 
    N'YourDatabaseMailProfile' -- database mail profile
    GO
    
    -- Define a fail-safe operator
    EXEC master.dbo.sp_MSsetalertinfo 
    @failsafeoperator=N'007', -- modify to your operator here
    @notificationmethod = 1; -- notify using email
    
  5. Update a SQL Server Agent job to enable job failure notification.
  6. -- Update a job to notify on failure
    USE [msdb] 
    GO 
    EXEC msdb.dbo.sp_update_job 
    	@job_name=N'syspolicy_purge_history' -- change the job name here
    	,@notify_level_email=2 -- notify on failed job
        ,@notify_email_operator_name=N'007' -- change to your operator here
    GO
    

REFERENCES:

sp_add_operator
http://msdn.microsoft.com/en-us/library/ms186747(v=sql.105).aspx

sp_update_job
http://msdn.microsoft.com/en-us/library/ms188745(v=sql.105).aspx