Link to home
Create AccountLog in
Avatar of canuckconsulting
canuckconsultingFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to Propagate Schema changes to other SQL Server DBs

We have six businesses each corresponding to SQL Server 2012 DBs having identical Schemas.   These DBs are refreshed each morning from Data on our Unix systems.  The data in each table is truncated and is re-loaded from unix.

What I would like to do is have a master DB which has the same schema as the other DBs.  When I make changes to the the schema in the master DB I want those changes pushed out to the other DBs.  Change include the addition, modification and deletion of the following objects:
Table
Index
Views
Stored procedure
Scaler Function

This "sync" would be run as the first step of the morning refresh so data does not have to be retained.  Tables, etc can be dropped and recreated if that is the easiest way.  I want this automated so that I don't' have to manually create a script file each time I add an index etc to the master DB.

Ideally this would be restricted to objects associated with a certain SQL Server schema.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Visual Studio - Database Projects - Schema Compare.
Allows you to select a 'left' and 'right' database, and with a couple of clicks identifies all of the objects that are in left db and not in right db, vice versa, and same objects but different schema.  Then allows you to selectively deploy either all or a subset of changes from left to right.
Avatar of canuckconsulting

ASKER

Thank Jim.

I'm looking for an automated solution.  I don't want to manually have to sync these DBs every time I make a change.
Are you triggering this data build out from the UNIX side?
Or do you have a job on each server that runs the import of data?
Depending on the answer, you would either adjust the mechanism if triggered initiated from the UNIX box, or have a common place where you store the schema that would be run after the drop /DB tables directive....
presumably you have/do develop the new schema somewhere..
HI Arnold,

Yes, we have a SQL Server job that is scheduled every morning.  For each business DB, it truncates each table and then reloads it from the unix DB which is set up as a linked server.

The only place we have the schema is in a Visual Studio DB project (as mentioned above by Jim).  I had hoped I would not have to generate a new schema file from that project every time I made a change.  Instead I was hoping there might be a way to generate the schema from the DB itself.  I'm now suspecting that doing this would be far more work than simply generating a new schema after a change is made. :)
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I think I may have mislead you Arnold.  The index is not being added to the unix tables.  I'm adding the index to the master SQL Server DB.

So say I  have the following six business DBs all sharing the same schema.
Business1_DB
Business2_DB
[...]
Business6_DB

I then have a master db named Core_DB.  It also has the same schema (tables, stored procs, etc) along with some additional admin functionality.  

So say all seven DBs have a table Customer and I want to add an index IX_Customer_Name.  What I would like to do is add that index to Core_DB.dbo.Customer and the next daily refresh to push that index to the six business DBs.
currently you do not drop indexes to delete data, correct?
your data load process maintains business process/data integrity where you are loading primary tables first and then their "dependent"  i.e. table2 has a FK to table1, you load table1 data first and then table 2.

You could in a linked server create a database from which your processing scheduled job will get information i.e. create index with the parameters you want. with a counter to indicate/reflect the number of DBs.
where each server that updates/performs the task, decrements the counter with the directive to delete the request if the counter is 0 or lower.

this way when you add an index on the DB, you would add the directive to the update/changes DB as well with the directive with the 6 for the number of DB server to which it applies...
Thanks!