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:
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.
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.
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.
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..
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..
ASKER
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. :)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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...
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...
ASKER
Thanks!
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.