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.