0

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?

  • 2
    For things that require mass deployment, you simply have to automate it or purchase software that has the alerting that you need. A little bit of PowerShell goes a long way to automate deployment. If you are doing something custom on each server (which is what it kind of sounds like), then your first step is to standardize on what is deployed to your servers, and then you could disable things that are not needed on certain servers if needed. This question will likely be closed due to being too broad or opinion-based as there are 50,000 correct answers to this question. – Tony Hinkle Apr 25 at 13:43
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.