5

My company has many branch offices and each branch has a DB server. Some have SQL Server 2005 and some others have SQL Server 2008. The schema is the same for all these servers.

Whenever I want to do DDL like adding column, etc., I do it either by connecting to each server one by one, or by running the DDL script with OSQL through a batch file.

Is there a built in tool to do DDL at once on several servers? How about third party tools? (preferably free ones)

11

One approach:

In Management Studio 2008 R2, you can add your servers into groups called Registered Servers. I have groups for prod, dev, Prod-SQL2000 etc. You start this by going View - Registered Servers, and then adding them one by one (you can share with colleagues by exporting them)

This will then allow you to click on one of your groups (eg Prod), and selecting New Query..... Management Studio will then connect to all the SQL Servers in that group, and you can issue a query across all of them in that group. I personally do read only operations, as changing a number of servers at the same time is more risky.

4

A tool created by Atlantis Interactive called SQL Anywhere allows you to connect to multiple instances and execute code against all of them.

Find here: http://www.atlantis-interactive.co.uk/

It is FREE of charge. He has some other tools as well but I have only recently been playing around with SQL Anywhere. It is much lighter than SSMS and does some auditing that is cool as well.

4

For SQL 2008 onwards, you can use CMS (Central Management Server). You can even register SQL 2000 and SQL 2005 DBs in this. In SSMS on a SQL 2008 system, click on View>Registered Servers. Then in the Registered Servers window, expand the Database Engine to see the Central Management Server folder. Here is a starter link to read more on it.

Keep a look out for syntax differences on the different versions of SQL, when running the queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.