Updating Stored Procedure on Three Different Servers

I need to update the SQL SERVER stored procedure on t开发者_JS百科hree different servers. I do not like to perform this manually. What are my options?

You can use the SQLCMD utility to connect to the three different servers / databases and run the stored procedure script. The control script may look something like this:

:connect server1
use DatabaseName
GO
:r StoredProcedure.sql
GO

:connect server2
use DatabaseName
GO
:r StoredProcedure.sql
GO

:connect server3
use DatabaseName
GO
:r StoredProcedure.sql
GO

SQL Compare is a great tool, especially for large or complex updates. However, you do have to pay for it. Using a utility like SQLCMD is not quite so elegant, but it is quick and free.

Use a tool like Red-Gate SQL Compare to create a script and then use their Multi-Script tool to execute it on multiple servers at one time.

www.red-gate.com

You could use a SQL Server synchronization tool, such as Red Gate SQL Compare. Or you could write a small script / application to connect to each server and execute the update statement, using OSQL.

You can set up some replication between the servers...have 1 main server that you make the update on, and then send that update out to each other server by use of a publication to the other servers. That'd be an easy way to do this.

Check out Migrator.NET, this combined with a builder like Hudson that runs on a check-in should do the trick. Plus you get versioning and rollbacks along with it.

With "Central Management Servers" feature of SQL Server 2008, what you can do is to add those three servers into one group and then run a single alter procedure script against these three servers.