Deploying Scripts To Multiple Instances

,

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.

Checking for success.

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

Rate

4.25 (4)

Share

Share

Rate

4.25 (4)