Replicating/Synchronizng multiple tables across different Databases on the same instance

Question

I have few tables that needed to be replicated/synchronized across several databases in our SQL Server 2008 cluster.

I know it's possible to replicate between multiple instances, but I'm looking for replication or synchronization in the same instance between specific tables of databases.

The replication/synchronization should happen every half-hour or so, but I don't mind it happening constantly.

I can't use DROP the target table and INSERT (copy) the source table since there are many constraints.

Reason for this is to not manage in the application layer and write to 2 different databases at the same time.

Example:

DB1 has T1, T2 and T3 - these are constantly being updated by the application, APP1 running on DB1.

DB2 needs to have an updated copy of T1 at all times, also, there is a different application, APP2 runs only on DB2.

Both DB1 and DB2 are located on the same instance, INST1.

Would it be possible to replicate T1, T2 and T3 from DB1 to DB2 ?

Solution 1

Firstly, SQL Server's replication features can be set up between different databases on the same instance.

Setting up and administering SQL Server replication might be more effort than you want to take on. There are lots of decisions to be made (what kind of replication? All of the columns or just some of them? All of the rows or just some of them? Do I want to index the target tables? Some kinds of replication require changes to the underlying data model. If you don't control the source code of the applications, is changing the data model even possible? etc, etc.), replication can break and it might not be noticed for a while, log files can grow unexpectedly.

With triggers, you have to maintain the trigger code in the case that the underlying tables change. What happens if the trigger stops working? How do you re-sync the tables? How long does that take? Etc, etc.

As mentioned in the comments, one alternative to replication is using views. Potentially, that means maintaining code in the case that the base tables (T1, T2, T3) change for whatever reason. Because of that, views would be my second suggestion.

My first suggestion would be to use the "synonyms" feature to simply refer to the original tables. If you use views or synonyms, the data will only be stored in one place (DB1), so there is no worry about synchronizing changes between copies of the data.

The possible negative here (for views or synonyms) is that DB2 will not actually contain the T1 data, so a backup and restore of DB2 (to a testing or dev server) would also need a backup and restore of DB1.

Solution 2

I have a similar problem and a specific challenge with regard to the use of synonyms. I have two databases on the same server that serve two seperate applications. DB1 includes information related to insurance agent information. DB2 includes information related to direct mail and other marketing programs.

The Agent information in the AGENT table in DB1 is needed in DB2 so I can associate the Agent to our direct mail and other marketing programs. This association needs to be defined using PK/FK relationships. Problem #1: Using a view or table valued function in DB2 that references the AGENT table in DB1 does not allow me to reference the PK in the view/function as a FK in the associated tables in DB2. Problem #2: If you define a synonym in DB2 off of the Agent table in DB1 you can't define a FK in the other tables in DB2 referencing the synonym.

If the Agent table in DB1 is the master and DB2 has an Agent table which is a subscriber to DB1, shouldn't I be able to define additional relationships, add a unique index, etc.. to the Agent table in DB2 for the application it's providing data for.

As an example, the application running off DB1 does not need to have the agent's county indexed. The application running off of DB2 does need to have the agent's county indexed. How can I use replication and allow the subscriber to have unique relationships and indexes in DB2?

Comments

[0100110010101]

Why not create a view in DB2 that references the tables in DB1?

[Idan]

Thanks for your responses. I was able to set Transactional Replication between DB1 and DB2 on the same node, but apparently - the defaults also synchronize the schema and other relations. My goal is to just synchronize the data. Also, in the future, DB2 might be offloaded to it's own instance so I guess I'll need to use SQL replication. Now for a replication question, is it possible to define that only specific schema changes will be replicated and the data itself. I would only like to that the columns will be updated on DB2 and the Data also. No relations, indexes or other changes.