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 ?
Best Answer
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.