These days most of us work in an environment where more than one SQL Server
instance exists. And sometimes we're required to perform the same task on each
of these servers - whether it be an update to a stored procedure or running your
maintenance jobs - it can be quite tedious manually carrying out the same thing
over and over again. This kind of work also introduces the potential of human
error due to the repetition, boredom, time restrictions, stress, etc.
Some of you may be fortunate enough to have acquired a piece of software that
manages this for you. But if like me you haven't then you might find this
article useful.
I am going look at how you can run a script against multiple 'target' servers
from one 'source' Server. In this example we will connect to a number of
production servers and run a command on each.
What you decide to run on your target servers is entirely up to, however the
purpose of this article is to demonstrate one way to manage the entire process.
I have split the article in to the following sections to help make it easier to
deal with.
Obtaining the list of target servers.
Connecting to the target servers.
Applying your script to the target servers.
Error handling / reporting on the job.
Obtaining the list of target servers.
In our example we will obtain the list of target servers (or more accurately SQL
instances) from a table within our database. You could specify these manually
however I feel it is more prudent to manage your deployments this way -
providing your table of instances is kept up to date. Perhaps you could link
this in with your asset database.
Your instance table could include other fields that define the type of server it
is - such as Database, Web, etc., whether it is a production server and an
indication of whether it should be included in updates. That way when you want
to deploy to or update a number of servers you can be more selective in your
query that chooses the instances.
Before we continue you should run the following script to create a table in your
database and populate it with the SQL instance names we want to work with.
Remember to run this script against the correct database (i.e. the database that
we'll be using throughout this exercise).
-- Script to create a table to contain names of our target SQL instances
CREATE TABLE [dbo].[mySQLinstancesTable] ([SQLInstanceName] [VARCHAR] (125))
GO
-- Populate with some instance names - change these to names of your own servers if you like
INSERT INTO [mySQLinstancesTable] VALUES ('myProdSQL1')
INSERT INTO [mySQLinstancesTable] VALUES ('myProdSQL2')
INSERT INTO [mySQLinstancesTable] VALUES ('myProdSQL3')
INSERT INTO [mySQLinstancesTable] VALUES ('myProdSQL4')
GO
OK so lets start looking at how our basic script should look like at this point.
-- Script to deploy to multiple servers DECLARE @InstanceName VARCHAR (125) -- Set up cursor for your sql instances DECLARE myInstances CURSOR FOR SELECT SQLInstanceName FROM mySQLinstancesTable OPEN myInstances FETCH NEXT FROM myInstances INTO @InstanceName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'I am now working on instance: ' + @InstanceName FETCH NEXT FROM myInstances INTO @InstanceName END CLOSE myInstances DEALLOCATE myInstances
After you run the script above you'll see that it displays four lines of text
stating that it is working on each of the target servers that we obtained from
the [mySQLinstancesTable] table. Therefore adding more servers to this table
will produce more iterations of the script and therefore more lines of output.
You may already see this if you entered the names of your own instances.
Using the CURSOR feature we are able to loop through the script dealing with
each instance in turn. It is beyond the scope of this article to talk any
further about cursors so hopefully you are familiar with them enough to
understand what is going on so far. At this point you should be able to work out
which lines of code are included in each iteration of the cursor.
Connecting to the target servers.
In this step we'll look at how to make a connection to each of our SQL
instances. SQL 2000 provides a feature called 'Linked Servers' to enable you to
to connect remotely to your other instances and run scripts. There is another
feature 'Remote Servers' which isn't as feature rich and has only been included
for backward compatibility. You should avoid using this feature if at all
possible.
Linked Servers allow you to easily connect to external data sources (SQL,
Oracle, Access, ODBC, etc.). You can easily link data from these different
sources to produce reports or data extraction. If you are new to Linked Servers
it is worth reading up on the subject to understand what you can achieve with
it.
Linked Servers relies on the Distributed Transaction Coordinator service running
in Windows services - make sure this is running before continuing. You will find
your Windows services in Control Panel / Administration Tools / Services.
By default SQL enables remote servers to connect and run scripts - this is known
as the Remote Access option.
You can view this value on your server by running EXEC sp_configure 'remote
access' - configured_value should be '1'.
Configuring the connection is quite straight forward however some people do find
the security side a little tricky depending on how their systems are configured.
Rather than go into the different scenarios here I'll leave it to you to read
the section in BOL - look under Linked Servers, Security. Instead I'll assume
that you have the required access to all your target servers.
If you want to quickly find out if you have access to one of your remote
instances run the following...
sp_addlinkedserver @server = 'ServerName\YourSQLinstanceName'
You may only need the instance name depending on how your network has been
configured. When you run this command you simply configure the connection. Only when you
attempt to do something on the remote sever does the security bit kick in (i.e.
checks for access rights).
So lets try the following...
SELECT * FROM [ServerName\YourSQLinstanceName].[DatabaseName].[dbo].[TableName]
If you didn't specify the ServerName in the connection command then don't enter
it here.
Remember to double check that the fields above contain the correct details of
the server, instance, database and table that exist on the remote instance (or
on your local instance if that's how you are testing).
I'm sure by the results that are returned you'll be able to work out if it was
successful or not. If not the error returned is usually good enough for you to
be able to research. Providing you have the remote object details correct
chances are it'll be a security issue. Don't be put off at this stage - spend a
little time understanding which security model you use and you'll crack it in no
time. Why not just try them all - you'll quickly find which one works for you,
and it's a good learning exercise!
Applying your script to the target servers.
OK lets now look at the revised script with the code that connects to each
instance. After the script connects to each instance it will execute a command
to prove we have successfully established a connection and have been able to get
information from the remote instance. This command basically counts the number
of records in the SYSINDEXES table of the MASTER database.
-- Script to deploy to multiple servers DECLARE @InstanceName VARCHAR (125) DECLARE @mySQL VARCHAR(250) -- Set up cursor for your sql instances DECLARE myInstances CURSOR FOR SELECT SQLInstanceName FROM mySQLinstancesTable OPEN myInstances FETCH NEXT FROM myInstances INTO @InstanceName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'I am now working on instance: ' + @InstanceName -- Connect to the sql instance SET @mySQL = 'sp_addlinkedserver @server = [' + @InstanceName + ']' EXEC (@mySQL) -- Run a command remotely SET @mySQL = 'SELECT COUNT(*) FROM [' + @InstanceName + '].[MASTER].[dbo].[SYSINDEXES]' EXEC (@mySQL) -- Drop the remote instance connection SET @mySQL = 'sp_dropserver [' + @InstanceName + ']' EXEC (@mySQL) FETCH NEXT FROM myInstances INTO @InstanceName END CLOSE myInstances DEALLOCATE myInstances
You might find you get better results if you configure Query Analyzer to output
to text (menu: Query / Results in Text).
Checking for success.
For each server the script connects to you should see something like the
following:
I am now working on instance: myInstance1
-----------
102
If you see any number on your output then you have confirmation that the script
ran successfully on the remote server as it has returned the total number of
records in SYSINDEXES. Try changing the command that runs on each instance so
that you get results that are more relevant to you and your data.
Remember you must have instance names that exist on you network configured in
your [mySQLinstancesTable] table.
If you've got to this point and had positive results then I'm sure you're
already thinking about how you can use SQL's Linked Server feature to help you
in maintaining multiple servers. As I mentioned at the beginning of the article
the ability to apply an update to multiple servers can save on time and human
error. But make sure you thoroughly test your script before deployment - the
ease of being able to deploy to many servers in a very short time is quite
appealing however you should remember the potential scale of the problem if
something was to go wrong when it ran on each instance.
Because of this we should look at some error handling and reporting so that we
can be confident everything ran as planned.
Error handling / reporting on the job.
I'll just cover the basics here and leave the decision with you on how much
error handling and reporting you are a happy with.
As you can see from the revised script below I have included a number of @@Error
commands. This system variable always contains the error number of the last
command that was executed. If the last command ran successfully the @@Error will
have a value of 0 (i.e. zero). It is important to check for this value
immediately after the last statement was executed otherwise you cannot trap
errors effectively.
If the last command did not run successfully then the value of @@Error is
recorded in our user defined variable @Error and the focus of the script jumps
to the spERROR: section at the end of the script.
-- Stop unnecessary SQL ouput SET NOCOUNT ON -- Script to deploy to multiple servers DECLARE @InstanceName VARCHAR (125) DECLARE @mySQL VARCHAR (250) DECLARE @Error VARCHAR (150) -- Set up cursor for your sql instances DECLARE myInstances CURSOR FOR SELECT SQLInstanceName FROM mySQLinstancesTable OPEN myInstances FETCH NEXT FROM myInstances INTO @InstanceName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'I am starting work on instance: [' + @InstanceName + ']' -- Connect to the sql instance SET @mySQL = 'sp_addlinkedserver @server = [' + @InstanceName + ']' EXEC (@mySQL) IF @@Error <> 0 BEGIN SET @Error = @@Error GOTO spERROR END ELSE PRINT 'Connected to instance: [' + @InstanceName + ']' -- Run a command remotely SET @mySQL = 'SELECT COUNT(*) FROM [' + @InstanceName + '].[MASTER].[dbo].[SYSINDEXES]' EXEC (@mySQL) IF @@Error <> 0 BEGIN SET @Error = @@Error GOTO spERROR END ELSE PRINT 'Executed command on instance: [' + @InstanceName + '] - see results above' -- Drop the remote instance connection SET @mySQL = 'sp_dropserver [' + @InstanceName + ']' EXEC (@mySQL) IF @@Error <> 0 BEGIN SET @Error = @@Error GOTO spERROR END ELSE PRINT 'Dropped connection on instance: [' + @InstanceName + ']' PRINT '- - - - - - - next instance - - - - - - -' FETCH NEXT FROM myInstances INTO @InstanceName END CLOSE myInstances DEALLOCATE myInstances spERROR: IF @Error <> 0 BEGIN PRINT 'An error has occurred while working on: [' + @InstanceName + ']' PRINT 'The error number was: ' + @Error END
The error handling employed in this example is far from perfect but it serves as
an introduction. There are areas that can fail and would not be captured by the
error handling. You can improve on this as you begin to develop it more to your
own situation.
From a reporting point of view you will see the information messages printed to
screen after each step has completed. This is useful if you need to work out
where the script fails, and also useful if you plan to automate the task via a
job where you can record the output in a text file.
Conclusion
We have touched on a number of SQL Server's features in this article which I hope have
been thought provoking and useful. You now have a script you can build on and
tailor to your own needs.
We're all learning so please leave comments if you feel you can improve on what
is written above. Or if you have any questions then please ask. Lots of skilled
people read this site and will be able to offer you good advice.
Joe
