I need to update the SQL SERVER stored procedure on three different servers. I do not like to perform this manually. What are my options?
+1
A:
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.
Michael Bray
2010-02-22 15:58:09
+2
A:
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
Randy Minder
2010-02-22 15:58:13
A:
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.
Aaron
2010-02-22 15:58:26
+3
A:
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.
NYSystemsAnalyst
2010-02-22 15:59:40
Thanks! I think I might go the route of executing everything in Query Analyzer.
azamsharp
2010-02-22 16:06:08
How do I say use servername.databasename? The server is already added in the sys.servers. use [ServerName].[DatabaseName] does not work!
azamsharp
2010-02-22 16:33:59
You need to specify everything as I have above. The :connect statement is used to change the connection to a different server. The USE statement just switches to a different database. If you are going to execute this through a GUI of sorts, you need to use SQL Management Studio (not query analyzer) and you need to make sure the menu option found under Query -> SQLCMD mode is checked ON. Otherwise, the statements prefixed with a colon (:) will not be recognized. They are SQLCMD specific commands, not part of T-SQL.
NYSystemsAnalyst
2010-02-22 17:45:22
Thanks I will check it out!
azamsharp
2010-02-22 19:26:36
A:
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.
Dan Williams
2010-02-22 17:08:51
A:
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.
SQL Warrior
2010-02-22 17:12:57