How many times have you wished you could just get some information from all your SQL servers? Things like job failures, versions, list of users, etc? When you have a few SQL servers it's not as bad. Since I was working in over 70 servers/instances, getting this information could take all day or longer. Here I will show you how you can remotely execute code on all your SQL servers regardless of version or platform. Currently I'm using this process against SQL 7.0, SQL 2000, SQL 2005, SQL 2008 both x32 and x64 servers. It works with default and named instances.
Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DOERVNXdyeW9NNlE/edit?usp=sharing
What is Remote Execution?
Here is my definition of remote execution. Executing a stored procedure or a T-SQL statement on many servers from one location and viewing the results on one monitor.So for example, let's say you want to execute the "sp_who" command on all your production servers. Normally you would manually connect to each server/instance and execute the stored procedure. Then you would want to copy/paste the output for final review. With a remote execution setup, you would just pick the servers/instances and have the code execute the "sp_who" command on all the production servers/instances with all the outputs delivered to one screen.
The above example talks about production servers, but it could have been any of the following , or more.
- All production servers/instances that are only SQL 2000 or only SQL 2005
- Only test servers/instance that have SQL 2005 and owned by Finance
- All servers/instances regardless of version, type, owner, etc
How to Get Started
Getting started is easy and doesn't take a veteran DBA to setup. Here are the basics you will need.Note that all the code is provided so you just have to copy, paste and execute.
First is where you want to store and execute the code. I recommend a server/instance that you have control over. Here you would create the databases and tables needed and execute the remote code from. This can be just about anywhere but I would recommend a server/instance that is not development, test or UAT environment as someone could modify or delete your code and or table(s).
The version of SQL server to use is SQL 2005 with SP2 and the Hotfix for SP2. You can search the Microsoft web page( http://support.microsoft.com/) to get the service packs and patches you need, if you don't already have them.
You will need to set this SQL server installation to be "Trusted" by all you SQL servers. To do this you will need access to Active Directory or have someone in the Network Department with "Domain Admin" rights to make the changes.
What needs to be done is make your server "trusted" by all the other SQL servers in your network. Once the other servers trust yours you can easily use "link connections" to execute your code remotely. To get more information on link connections, please refer to Books On Line (BOL) or Microsoft's web page ( http://support.microsoft.com/).
To make your server trusted (as you not make your instance trusted) perform the following
- Open "Active Directory Users and Computers" from your PC (if domain user) or log into a servers with a user that has domain admin rights.
- Click on "Actions" and select "Find"
- Change the "Find" pull down and select "Computers". Now in the "Computer Name" box type in the name of your server and click " Find Now"
- Your server should now be displayed in the "Search results" window. Right click on your server's name and select "Properties"
- A new window will open. Click on the "Delegation" tab and finally click the radial button that says "Trust this computer for delegation to any service (Kerberos only).
- Click the "Ok" button to close the windows and give the network some time to propagate this information to all the other servers.
Security Note! By allowing your server to be trusted by all the other servers you need to ensure that you guard this server as if someone hacks into it they could gain access to all the other servers. Please discuss with your Security Analyst before setting this feature.
If using trusted connections is allowed you can modify as follows.- Add to additional columns to theAllSQLInstancesTabletable. One would be user id and the other would be the password.
- Modify main code to look up these two columns so that it would populate the link connection login information and thus connect using the id provided.
Open a command window (CMD) and execute : osql -L or isql -L which will display a list of SQL servers on your network. Now you will have a list of most of the servers/instances. For some reason not all servers/instances are displayed.
You could use the pipe command to create a file and import the data to your table. Here is an example.
osql -L >srvlist.txt
Using notepad or any editor, open the file and remove anything you don't what so that all you have is a list of only SQL servers/instances. Next you can use the Import Data wizard to load the data into your table.
The next step is to create the databases and tables that will hold all you server/instance information and more.
Creating the Remote Execution Environment
Now it's time to create the databases and tables needed to make this all work. The databases you will create are calledDatabase 1 - SQLSrvOps - SQL Server Operations is there the main work is done
Table 1 - AllSQLInstancesTable - Stores the server/instance names
Database 2 - SQLSrvReports - SQL Server Reports is where data is saved for reporting
Table 2 - GeneralError - Stores errors that may occur during execution.
Creating SQLSrvOps Database
Here is the code used to create the database called SQLSrvOps. If you want to change the name just do a search and replace throughout all the code provided.Before executing change the location of where you want the data (.MDF) and log (.LDF) files to be located. Here I'm installing on to the D:\MSSQL2005\MSSQL\DATA location.
USE [master]
GO
CREATE DATABASE [SQLSrvOps] ON PRIMARY
( NAME = N'SQLSrvOps',FILENAME =
N'D:\MSSQL2005\MSSQL\DATA\SQLSrvOps.mdf', SIZE = 6144KB , MAXSIZE =UNLIMITED, FILEGROWTH= 1024KB )
LOG ON
( NAME = N'SQLSrvOps_log',FILENAME = N'D:\MSSQL2005\MSSQL.8\MSSQL\DATA\SQLSrvOps_log.ldf', SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'SQLSrvOps', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SQLSrvOps].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
Now you should see the database called SQLSrvOps.
Creating Table for SQLSrvOps Database
Next, you will need to create the table called "AllSQLInstanceTable" to hold all your server/instance information. Below is the code to execute.USE [SQLSrvOps]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AllSQLinstancesTable](
[SQLInstanceName] [varchar](75)NOT NULL,
[SQLlvl] [varchar](1)NOT NULL,
[Owner] [varchar](50)NOT NULL,
[SQLVer] [nchar](10)NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
If you view the table created you should see the following columns.
SQLInstanceName - this is where all your SQL server/instance names are located
SQLlvl - type of system. I use P for Production and D for development, test and UAT
Owner - Person or department that owner the server/instance
SQLVer - Version of SQL . Currently I use the following
- 70 = SQL 7.0
- 2000 = SQL 2000
- 2005 = SQL 2005
- 2008 = SQL 2008
Creating SQLSrvReports Database
Within this database we will record errors that have occurred during execution of the remote code and hold data from the results (if you like) which can be used with SQL Server Reporting Services (SSRS) to display information. Let's make the reporting database.Here is the code to create the "SQLSrvReports database"
Before executing change the location of where you want the data (.MDF) and log (.LDF) files to be located. Here I'm installing on to the D:\MSSQL2005\MSSQL\DATA location.
USE [master]
GO
CREATE DATABASE [SQLSrvReports] ON PRIMARY
( NAME = N'SQLSrvReports',FILENAME =
N' D:\MSSQL2005\MSSQL\DATA \SQLSrvReports.mdf' , SIZE= 595968KB , MAXSIZE= UNLIMITED,FILEGROWTH = 1024KB )
LOG ON
( NAME = N'SQLSrvReports_log', FILENAME=
N' D:\MSSQL2005\MSSQL\DATA \SQLSrvReports_log.ldf' , SIZE= 833024KB , MAXSIZE= 2048GB , FILEGROWTH= 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'SQLSrvReports', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SQLSrvReports].[dbo].[sp_fulltext_database] @action = 'disable'
end
The reporting database is now created.
Creating Table for SQLSrvReports Database
To create the table called "GeneralError", execute the following.USE [SQLSrvReports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GeneralError](
[ErrMsg] [nvarchar](2048)NULL,
[ErrInstance] [varchar](255)NULL,
[ErrDate] [datetime] NULL,
[ExecFrom] [varchar](255)NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
- ErrMsg - Holds the error message itself
- ErrInstance - Shows which remote server produced the error
- ErrDate - Date and time of the error
- ExecFrom - The function or job that when executed caused the error
With all the databases created and tables created/populated you are now ready to execute your code remotely.
Remote Execution Code
Let's go through the code and I will show you an example of how to use it.First the code starts off by declaring variables that will be used throughout the execution
USE master
GO
PRINT '***************** Start of Remote Query Excution *******************'
PRINT ' '
SET NOCOUNT ON
-- Create variables used by script
DECLARE@InstanceName VARCHAR (75), -- Server Instance Name
@lvl VARCHAR (1), -- Production or Test server
@ver VARCHAR (4), -- SQL version number
@Err int, -- Errors detected. For future use
@PreLinked VARCHAR (75) -- Used for removing any old linked
-- Create cursor to cycle thru servers in the SQLinstanceTable table
DECLARE myInstances CURSOR FOR SELECT SQLInstanceName, SQLlvl, SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable
The variable descriptions is show in the remarks in green. Next we create a cursor where the code reads in the contents of the "AllSQLInstanceTable". Remember the table you had to populate?
The next section is remarked out. If you want to remotely execute your code on all servers/instances regardless of type, version, owner, etc . you would leave this section as is.
----------------------------------------------------------------------------- -- Below are some conditions that could be applied -- Unremark the following to only execute code on Production servers -- WHERE SQLlvl = 'P' -- Unremark the following to only execute code on Test / Development servers -- WHERE SQLlvl = 'D' -- Unremark the following to only execute code on SQL 2000 servers only -- WHERE SQLVer = '2000' -- Unremark the following to only execute code on SQL 2005 servers only -- WHERE SQLVer = '2005' -- Here you can add your own conditions for example -- WHERE Owner = 'Finance' -----------------------------------------------------------------------------If you only want to execute the remote code on the production servers/instances, you would un remark the line as follows
Original line -- WHERE SQLlvl = 'P'
Un remarked line WHERESQLlvl = 'P'
With this change only the production servers/instances would have the remote code executed against.
Let's say you what a more refined condition, you could use something like this
SELECT * FROM dbo.AllSQLinstancesTable WHERE SQLlvl = 'P' AND SQLVer = '2000' AND SQLInstanceName LIKE '%fin%'
The code would only execute on servers/instances that are in the production environment, have SQL 2000 installed and the name is like 'fin'
So you see that you have many possibilities on how to refine the execution. You could even add additional columns to the "AllSQLInstanceTable" table to even more complex conditions.
The code now creates another cursor that will detect and remove any existing link connections.
-- Create cursor to cycle thru servers at are still have a link connection
-- Remove any existing Linked Server connection before continuing with code
DECLARE LinkedSrv CURSOR FOR (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0)
OPEN LinkedSrv
FETCH NEXT FROM LinkedSrv INTO @PreLinked
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @PreLinked
EXEC sp_dropserver @PreLinked, 'droplogins'
FETCH NEXT FROM LinkedSrv INTO @PreLinked
END
-- Close cursor
CLOSE LinkedSrv
DEALLOCATE LinkedSrv
Why is this done? As the code tries to create the link connection if one exists and that server/instance not longer exists the code will report back an error. This clearing of existing link connection allows the code to only use the data in the "AllSQLInstanceTable" as valid server/instance names.
If a new server/instance is added to you network, or one is decommissioned, you would only have to make the change once in the table and thus the code will continue to work without errors.
If you have linked connections that are always needed, you could modify the code so that the link connection is never touched.
Now the code starts to collect the first server/instance name and creates a linked connection to that server.
-- Collect first server name to remotely execute script on
OPEN myInstances
FETCH NEXT FROM myInstances INTO @InstanceName, @lvl, @ver
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Create/Connect/Execute/Drop Linked Server for Instance: [' + @InstanceName + ']'
-- Create Linked Server connection
BEGIN TRY
EXEC sp_addlinkedserver @InstanceName , N'SQL Server'
END TRY
BEGIN CATCH
PRINT 'ERROR! CAN NOT ADD LINK CONNECTION ON REMOTE INSTANCE: [' + @InstanceName + '], please review'
INSERT INTO [SQLSrvReports].[dbo].[GeneralError](ErrMsg, ErrInstance, ErrDate, ExecFrom)
values (error_message(), @InstanceName, getdate(),'SQL Job');
END CATCH
This is why you need to ensure that the server/instance you are executing from is "TRUSTED" by the remote server/instance as a link connection is made.
A successful link connection is made the code will show you the server/instance name as below.
Create/Connect/Execute/Drop Linked Server for Instance: FINSQL01
If link connection cannot be made, you will see an error message and an entry is made in the GeneralError table.
ERROR! CAN NOT ADD LINK CONNECTION ON REMOTE INSTANCE: FINSQL01, please review'
Once the link connection is available the code creates a login.
-- Create Linked Server login
BEGIN TRY
EXEC sp_addlinkedsrvlogin @InstanceName, 'TRUE'
END TRY
BEGIN CATCH
-- Provide warnings if script could not be executed on the remote server
PRINT 'ERROR! CANNOT ADD LINK SERVER LOGIN ON REMOTE CODE ON INSTANCE: [' + @InstanceName + '], PLEASE REVIEW'
INSERT INTO [SQLSrvReports].[dbo].[GeneralError](ErrMsg, ErrInstance, ErrDate, ExecFrom)
values (error_message(), @InstanceName, getdate(),'SQL Job');
END CATCH
You will be notified if a link connection login can or cannot be created. The error message will be copied to the " GeneralError" table. Note about the " GeneralError" table. You can execute the remote code within a job and review the "GeneralError" table for possible issues.
Finally we get to the section that contains the actual code to be executed remotely.
-- Code to be executed remotely --
-----------------------------------------------------------------------------
BEGIN TRY
-- Actual code to execute on remote server/instance
EXEC ('SELECT [name] FROM ['+ @InstanceName + '].MASTER.dbo.SYSDATABASES')
END TRY
BEGIN CATCH
-- Provide warnings if script could not be executed on the remote server
PRINT 'ERROR! CAN NOT EXECUTE REMOTE CODE ON INSTANCE: [' + @InstanceName + '], PLEASE REVIEW'
INSERT INTO [SQLSrvReports].[dbo].[GeneralError](ErrMsg, ErrInstance, ErrDate, ExecFrom)
values (error_message(), @InstanceName, getdate(),'SQL Job');
END CATCH
-----------------------------------------------------------------------------
Once again, if the successful or not a message and possible table insert is performed.In the code above you can see that the code to be executed it between the BEGIN TRY and END TRY
EXEC ('SELECT [name] FROM ['+ @InstanceName + '].MASTER.dbo.SYSDATABASES')
Here SQL is instructed to get the names of the databases (in sysdatabases) that are located on the remote server/instance which is stored in the [' + @InstanceName + ']variable. With the results shown on your screen.
Now here is some code you should execute remotely on all your production servers.
SELECT ' **** Failed MS SQL Jobs Report **** ' as ' '
exec ('SELECT name FROM [' + @InstanceName + '].msdb.dbo.sysjobs A, [' + @InstanceName + '].msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0')
The results displayed are ONLY the jobs that have failed. Knowing if a job was successful is important, knowing when a job has fail can be even more important! In my next article I will show you how I took the results, put them into a table and display them in a report using SSRS.
Example of output returned for failed jobs report
Create/Connect/Execute/Drop Linked Server for Instance: [MARKETSQL01] ------------------------------------- **** Failed MS SQL Jobs Report **** name ------------------------------------------------------------------------------------------------- Integrity Checks Job for DB Maintenance Plan 'DB Maintenance Plan1' DB Backup Job for DB Maintenance Plan 'Full One Time Backup' Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan1'
You can even create stored procedures on your remote servers/instances and execute them remotely. There are many different possibilities for you to use.
Once the remote execution is completed the linked connection is removed and the collection of the next server/instance name is inserted in to the InstanceName variable.
-- Remove created link connection
BEGIN TRY
EXEC sp_dropserver @InstanceName, 'droplogins'
END TRY
BEGIN CATCH
PRINT 'ERROR! CAN NOT DROP CONNECTION FOR INSTANCE: [' + @InstanceName + '] PLEASE REVIEW'
INSERT INTO [SQLSrvReports].[dbo].[GeneralError](ErrMsg, ErrInstance, ErrDate, ExecFrom)
values (error_message(), @InstanceName, getdate(),'SQL Job');
END CATCH
-- Collect next server name and start again
FETCH NEXT FROM myInstances INTO @InstanceName, @lvl, @ver
END
-- Close cursor
CLOSE myInstances
DEALLOCATE myInstances
PRINT ' '
PRINT ' '
PRINT '*** End of Remote Query Excution- Completed ***'
What's Next
Now that you have this working, try to see what else you could do with remote execution. I'll be creating additional articles that continues with this idea. Here are just some of reports I created with remote execution.- Global job status reports for all production systems
- Global Job status reports for Test/Development/UAT systems
- Global job schedule reports which show job name, scheduled times, frequency and time to run
- SQL 2000 and SQL 2005 error log analysis
- Link connection failures that are record from the remote execute code
- SQL Server information report showing server/instance version, patch levels, editions, etc.
- SQL Server up time for all the production servers
