Introduction

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.

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

As you can see this would make the life of a DBA a little easier. With all the results displayed in one location you could easily review and/or save to a file.

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 need 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

1. 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.
2. Click on "Actions" and select "Find"
3. 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"
4. Your server should now be displayed in the "Search results" window. Right click on your server's name and select "Properties"
5. 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). See diagram below.

1. 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! But 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 you feel that using trusted connections is not viable or 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.

My company has allowed me to have my own DBA only instance on a server that houses several instances for the IT department. Here I have full control. Now you don't necessarily need your own instance but it is recommended.

Second , is collection information for your main table. That is all your SQL server names, versions, department or system owners and type of environment (production, development, test, UAT).

If you have a list, great! If not, here is what you can do to start. Open a command window (CMD) and execute : osql -L or isql -L which will display a list of SQL servers on your network. Now if 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 third 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 called

Database 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

From the list your created earlier (remember the "osql -L >srvlist.txt" command?) you can now use the Import Data Wizard to import the data into this new table. To do this just right click on the "SQLSrvOps" database, highlight "tasks" and "Import Data". I am not going to get in to details of this tool as I'm sure you can take it from here. Just read and follow the prompts.

Once the data is loaded, open the table and add the additional information.

Continue to populate the table until you have a few servers to test on. Here is a sample

My sample has a list of 6 servers with one instance called Corp\SharePoint1 which is a production environment (P), owned by IT and is running on SQL 2005.

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. In future articles I will show you how you can use this to create a report for failed jobs. This is the main report I use every day to check the status of the nightly executed jobs. In the mean time 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

Here is the definition of the columns used

• 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

Here is my sample.

You can see that the remote execution did not get information on the FINSQL01 and the INVENT01 server. This does not mean that the jobs failed but the remote execution on the status has failed.

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 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'

The output is showing which jobs failed on MARKETSQL01 server and would continue to display any other failed jobs on the other servers/instances.

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 ***'

This loop continues until all the servers/instances are processed and the "End of Remote ..." message is displayed.

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

As you can see there is a lot of automation you can add to your environment making it easier to be a DBA with many servers/instance to manage. I hope you find this article interesting and useful in your day to day DBA role and would be interested to hear how you use remote execution.