Updating a stored procedure on three different servers

Question

I need to update the SQL SERVER stored procedure on three different servers. I do not like to do this manually. What are my options?

Answer 1

You can use the SQLCMD utility to connect to three different servers / databases and run the script stored procedure. The script control might 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 will have to pay for it. Using a utility like SQLCMD is not so elegant, but fast and free.

Answer 2

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

www.red-gate.com

Answer 3

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

Answer 4

You can configure some replication between the servers ... there is 1 main server on which you install the update, and then send this update to another server using the publication on other servers. That would be an easy way to do this.

Answer 5

Abandon Migrator.NET , in conjunction with a builder like Hudson , which starts when you register, it should do the trick. In addition, you get versions and rollbacks with it.

Answer 6

Using the Central Management Servers feature in SQL Server 2008, you can add these three servers to one group, and then run one script change procedure for these three servers.