CODEWITHSUNDEEP
sqlsql-serverstored-procedures
azamsharp
azamsharp

Reputation: 20086

Updating Stored Procedure on Three Different Servers

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?

Upvotes: 4

Views: 777

Answers (6)

SQL Warrior
SQL Warrior

Reputation: 11

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.

Upvotes: 0

Dan Williams
Dan Williams

Reputation: 4950

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.

Upvotes: 0

DCNYAM
DCNYAM

Reputation: 12126

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.

Upvotes: 3

Aaron
Aaron

Reputation: 7541

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.

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48512

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

Upvotes: 2

Michael Bray
Michael Bray

Reputation: 15275

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.

Upvotes: 1

Related Questions