Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 Replicating Multiple DBs into 1

Author  Topic 

SuperJB
Starting Member

12 Posts

Posted - 2012-06-06 : 11:51:35
Hello everyone,

I'm just trying to get some feedback on "the BEST way" to go about doing this:

My company uses an external software which runs with SQL2005 DBs. There are a few different DBs with exactly the same structure (one for each location we deal with - don't ask why - this is what we have to deal with at the moment). We can't change anything in the current structure and it's recommended that we add the minimum amount of objects possible as to not jeopardize the current software.

We now have the need to monitor some DML actions (inserts and updates) for some tables. It doesn't matter which DB the change is made on, we need to capture it.

So imagine we have DB1, DB2, DB3, exactly the same in structure but with different info. What's the best way to track these changes into DBDestination? The Destination DB doesn't need to have the same structure btw. We just need a few columns from 2/3 tables. It most likely will be a SQL2008 (but no guarantees).


What's the best / most optimized way of doing this?! I know how to with triggers, but I'm wondering if there's a way that requires less maintenance, better performance, etc...


Thanks in advance,
SuperJB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-06 : 12:01:17
You'll need to add DML triggers to each of the tables that you want to track. Those triggers would write to the destination database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SuperJB
Starting Member

12 Posts

Posted - 2012-06-07 : 04:16:07
Hi tkizer,

Thanks for the feedback. That was my initial plan, just wanted to figure out a more optimized way for doing this! Since no one else replied, I guess I'm going to proceed with this approach.


SuperJB
Go to Top of Page
   

- Advertisement -