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.
canuckconsultingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
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.
canuckconsultingAuthor 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.
arnoldCommented:
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..
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

canuckconsultingAuthor 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. :)
arnoldCommented:
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..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
canuckconsultingAuthor 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.
arnoldCommented:
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...
canuckconsultingAuthor Commented:
Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.