Sql-server – Monitoring DB activity in a large server environment

sql serversql-server-2016

Apologies if this is ambiguous. In an environment with a small number of servers it is relatively easy to install and maintain stored procedures, etc. I normally build up some stored procedures to notify by DB mail when there are events that I should be aware of, maybe build some triggers to notify when there are new databases in the system.

How do I build, deploy and maintain these kind of objects in an environment with a large number of servers where it becomes impossible to do so individually.

Secondly, we use log forwarding on all individual servers except for the central management server, and only have DB mail active on the central management server. How can I return relevant results to the DBAs when I can't use DB Mail?

Best Answer

A couple months ago I wrote this script that can execute through SQLCMD your dynamic SQL on a comma-delimited-list of servers.

Note that you have to copy/paste the result into a new window in SSMS, then change it to SQLCMD mode, remove any non-SQL lines, and then execute.

I've used this a few times and it works. It's not a shrink-wrapped, commercial deployment tool, but it will work.

My first use case that inspired this script is that I inherited a bunch of servers from another DBA, but their email address was still in some of the Agent Operators. Instead of searching through every server for that person's email address and updating it to my own, I wrote this script to automate the process. The dynamic SQL can be adapted to any purpose.

Be sure to test this very carefully.

/*
    This script will create a TSQL script that you can run separately in SQLCMD mode. 

    Steps:
    1. Skip this:  Create the function SplitString with the commented-out script below.
    2. Edit @Servers with a comma delimted list of your servers.
    3. Edit @SearchVariable1 and @SearchVariable2 with the search variables. 
        If you want to search for only 1 string, then make both variables that string.
        For example 'kanjana', which is part of the name of the DBA who had this set of servers before me. 
        You can add more SearchVariableN variables. Edit the dynamic SQL statement. 
4.  Edit the dynamic SQL to do whatever you need.
5. Copy and paste the result into a new window, specify Query | SQLCMD mode, and execute.
    Remove any non-TSQL lines, such as '(1 row affected)'
6. If logical, you can re-run the script to check the results.


*/

USE MSDB
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString
-- SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
GO

CREATE FUNCTION [dbo].[SplitString]
(      
      @Input NVARCHAR(MAX),  
      @Character CHAR(1)=','  
)  
RETURNS @Output TABLE (  
      Item NVARCHAR(1000)  
)  
AS  
BEGIN  
      DECLARE @StartIndex INT, @EndIndex INT  

      SET @StartIndex = 1  
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character  
      BEGIN  
            SET @Input = @Input + @Character  
      END  

      WHILE CHARINDEX(@Character, @Input) > 0  
      BEGIN  
            SET @EndIndex = CHARINDEX(@Character, @Input)  

            INSERT INTO @Output(Item)  
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)  

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))  
      END  

      RETURN  
END
GO


-- User configuration variables:
DECLARE @Servers VARCHAR(MAX) = ''
DECLARE @SearchVariable1 VARCHAR(MAX) = ''
DECLARE @SearchVariable2 VARCHAR(MAX) = ''

-- ************ Edit these variables:
SET @Servers = @Servers +
    'server1,server2,server3,server4' 

SET @SearchVariable1 = 'person1'
SET @SearchVariable2 = 'person2'
-- ************ 

-- other variables:
DECLARE @ThisServer VARCHAR(100)
DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @B CHAR(2) = CHAR(13) + CHAR(10) 


DECLARE MyCursor CURSOR
    FOR SELECT * FROM dbo.SplitString(@Servers,',')
OPEN MyCursor 

FETCH NEXT FROM MyCursor INTO @ThisServer

WHILE @@FETCH_STATUS = 0
BEGIN
    -- PRINT @ThisServer
    SET @SQL = @SQL + ':CONNECT ' + @ThisServer + @B
    SET @SQL = @SQL + 'SELECT ''' + @ThisServer + ''' AS [Server] ' + @B 
    SET @SQL = @SQL + 'SELECT * FROM msdb.dbo.sysoperators ' + @B +
        'WHERE email_address LIKE ''%' + @SearchVariable1 + '%''' + @B + 
        'OR email_address LIKE ''%' + @SearchVariable2 + '%'''
    SET @SQL = @SQL + @B + 'GO' + @B + @B

    -- Use this to update the operator. This only works when there is only one email address for the operator.
    -- ToDo: adapt this to replace the email address within a list.
    --SET @SQL = @SQL + 
    --  'USE [msdb] ' + @B +
    --  'GO ' + @B +
    --  'EXEC msdb.dbo.sp_update_operator @name=N''DFK_Team'', @enabled=1, @email_address=N''me@myemail.com''' + @B  +
    --  'GO '
    --SET @SQL = @SQL + @B + @B

    FETCH NEXT FROM MyCursor INTO @ThisServer
END

PRINT @SQL

CLOSE MyCursor
DEALLOCATE MyCursor



IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString
Related Question