Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Deploying Scripts To Multiple Instances

By Joe Doherty,

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

Total article views: 8932 | Views in the last 30 days: 9
 
Related Articles
FORUM

Error Connecting to SQL Server 2005 Instance

Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

FORUM

SQLCMD -S servername\instancename Error

SQL Network Interfaces: The target principal name is incorrect. Sqlcmd: Error: Microsoft SQL Native ...

FORUM

Error: Installing SQL Server Cluster Instance

Error: Installing SQL Server Cluster Instance

FORUM

Error Locating Server/Instance Specified

(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

FORUM

Error Locating Server/Instance Specified

(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones