Replicate/Mirror SQL Server schema to slave databases

Question

I have a empty database called MASTERDB and around 10, or more, slave databases that are copies of the MASTERDB database.

MASTERDB will be always empty, as I only need the schema. No data is going to be replicated/mirrored.

The scenario:

• I create a new table on MASTERDB and I want that table to be created on those 10 tables automatically
• If I change a field on MASTERDB, replicate the changes for all the other databases

I have looked at Mirroring and Replication, but I could not determine if these will do what I want.

Thanks in advance

Answer 1

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.