CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008database-designrdbms
Behrang Saeedzadeh
Behrang Saeedzadeh

Reputation: 47971

How to share (use) data between two databases?

We have a "master" application in our organization with a "master" database that contains the majority of the information that our organization depends on.

Then we have another "side" application that has its own database schema, but requires the data that is stored in the master database for operation.

At the moment, every night we run a job that brings in the data from the master database to the "side" database.

Needless to say, this has resulted in lots of headache and has degraded our productivity substantially.

I have made some research and it looks like that using heterogenous data replication it is possible to share the data between the two database in a real-time fashion, but I am not an expert in this area.

So my question is what are some of the best practices for sharing data between two heterogenous databases (in our case, the DBs are SQL Server 2008).

Upvotes: 0

Views: 1924

Answers (1)

DCNYAM
DCNYAM

Reputation: 12126

Check out this question: Syncrhonizing 2 database with different schemas.

According to this article from Microsoft (http://msdn.microsoft.com/en-us/library/ms152489.aspx), "...if you need to transform the schema as the data is moved such that the schema on the Subscriber is not a subset of the schema on the Publisher, SQL Server 2008 Integration Services (SSIS) (SSIS) is the recommended solution." Therefore, since the schemas are different for your two databases, I would recommend using an SSIS package to do an extract-transform-load operation.

Upvotes: 3

Related Questions