Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
RedGate has some tools to help automate updating the schema across multiple computers, but it's not automated entirely. –  jcolebrand Jul 18 '11 at 5:51
    
Related: dba.stackexchange.com/q/7707/2660 –  Nick Chammas Nov 17 '11 at 16:27

3 Answers 3

up vote 10 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
+1. Nice link to some free SQL stuff. –  StanleyJohns Jul 18 '11 at 17:47

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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