<div>
Visual Studio - Database Projects - Schema Compare.<br />
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. &nbsp;Then allows you to selectively deploy either all or a subset of changes from left to right.
</div>


<div>
Thank Jim.<br />
<br />
I'm looking for an automated solution. &nbsp;I don't want to manually have to sync these DBs every time I make a change.
</div>


<div>
Are you triggering this data build out from the UNIX side?<br />
Or do you have a job on each server that runs the import of data?<br />
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....<br />
presumably you have/do develop the new schema somewhere..
</div>


<div>
HI Arnold,<br />
<br />
Yes, we have a SQL Server job that is scheduled every morning. &nbsp;For each business DB, it truncates each table and then reloads it from the unix DB which is set up as a linked server.<br />
<br />
The only place we have the schema is in a Visual Studio DB project (as mentioned above by Jim). &nbsp;I had hoped I would not have to generate a new schema file from that project every time I made a change. &nbsp;Instead I was hoping there might be a way to generate the schema from the DB itself. &nbsp;I'm now suspecting that doing this would be far more work than simply generating a new schema after a change is made. :)
</div>


<div>
I think I may have mislead you Arnold. &nbsp;The index is not being added to the unix tables. &nbsp;I'm adding the index to the master SQL Server DB.<br />
<br />
So say I &nbsp;have the following six business DBs all sharing the same schema.<br />
Business1_DB<br />
Business2_DB<br />
[...]<br />
Business6_DB<br />
<br />
I then have a master db named Core_DB. &nbsp;It also has the same schema (tables, stored procs, etc) along with some additional admin functionality. &nbsp;<br />
<br />
So say all seven DBs have a table Customer and I want to add an index IX_Customer_Name. &nbsp;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.
</div>


<div>
currently you do not drop indexes to delete data, correct?<br />
your data load process maintains business process/data integrity where you are loading primary tables first and then their &quot;dependent&quot; &nbsp;i.e. table2 has a FK to table1, you load table1 data first and then table 2.<br />
<br />
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.<br />
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.<br />
<br />
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...
</div>


<div>
<div class="content wysiwyg-content">
We have six businesses each corresponding to SQL Server 2012 DBs having identical Schemas. &nbsp; These DBs are refreshed each morning from Data on our Unix systems. &nbsp;The data in each table is truncated and is re-loaded from unix.<br />
<br />
What I would like to do is have a master DB which has the same schema as the other DBs. &nbsp;When I make changes to the the schema in the master DB I want those changes pushed out to the other DBs. &nbsp;Change include the addition, modification and deletion of the following objects:<br />

<div class="bullet">
Table
</div>
<div class="bullet">
Index
</div>
<div class="bullet">
Views
</div>
<div class="bullet">
Stored procedure
</div>
<div class="bullet">
Scaler Function
</div>
<br />
This &quot;sync&quot; would be run as the first step of the morning refresh so data does not have to be retained. &nbsp;Tables, etc can be dropped and recreated if that is the easiest way. &nbsp;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.<br />
<br />
Ideally this would be restricted to objects associated with a certain SQL Server schema.
</div>
</div>


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.

How to Propagate Schema changes to other SQL Server DBs

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.