How to Propagate Schema changes to other SQL Server DBs

canuckconsulting
canuckconsulting used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.

Author

Commented:
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.
Distinguished Expert 2017

Commented:
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..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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. :)
Distinguished Expert 2017
Commented:
The issues that you have is load is automated meaning, you have to update the load dts package to reflect the new changes and it could potentially be used to dynamically load in data for the new schema, run the update, and then run the load that is also then updated.


Have you looked at scripting the triggering from the unix side, i.e. a unix cron/script that connects to each DB runs what it needs to clear the data, update the schema and then runs the load directive/data import directive from the linked server.....

another option in the sql job, is to build in a reference i.e. it copies a DB structure from the linked server, the difficulty with this, you would need to grant/assign accounts for access..

Author

Commented:
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.
Distinguished Expert 2017

Commented:
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...

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial