Sql-server – a good way to replicate code across multiple databases

sql server

SQL Server 2005

I have two databases (Source1 and Source2) with the same data model and I plan to merge the data into a third database (Target) with an "abstracted" data model. In the future, it's possible to have additional Source databases that will need to be merged into Target.

Originally, I was hoping to build views and stored procedures that would reside in the Target database that I could dynamically run again Source1 to load Target then switch to Source2. That doesn't seem to be possible without dynamic SQL.

So plan B is to build the views and procedures in the Target database then deploy them to Source1 and Source2. What is the best way to replicate code to multiple databases?

I'm open to any new ideas that address this situation.

Best Answer

SQL Server has replication built in. Have you looked into that?

If it's just a few views or procedures, I'll use a Central Management Server to deploy them to multiple servers. You just define the group of servers and then execute the script. Pretty simple.

For 3rd party options, Idera has Multi Query and Red Gate has Multi Script as well.

Related Solutions

Sql-server – Replicate/Mirror SQL Server schema to slave databases

Neither mirroring nor replicaiton are appropiate here. Mirroring creates identical physical copies of the database. Replication is good at replicating data, but falls short exactly at replicating schema changes, so you would use the very wrong tool for the job.

What you describe is a a problem of change control, not replicaiton. You should approach it as such. My favourite approach is described in Version Control and your Database. Brent Ozar PLF's last video also addresses the same problem: Coping with Change Control Video.

Sql-server – standard way to use common data and procedures across multiple databases

In addition to Thomas' answer, for the things like common lookup data and generic functions, you can use synonyms.

USE CentralDB;
GO

CREATE TABLE dbo.Dates(...);
GO

USE OtherDB;
GO

CREATE SYNONYM dbo.Dates
  FOR CentralDB.dbo.Dates;

Now your other databases can treat these like first-class, local objects, but you only need to maintain one copy. Note that some functionality is missing, e.g. you can't set up foreign keys.

In a previous life I managed a system where we would have ~500 databases with nearly identical schema on each instance. We managed common data using synonyms, and deployment for code that had to be local using Red-Gate SQL Compare an SQL Farms Combine (very much like Multi-Script).

Best Answer

Related Solutions

Sql-server – Replicate/Mirror SQL Server schema to slave databases

Sql-server – standard way to use common data and procedures across multiple databases

Related Question