SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 Replicating Multiple DBs into 1
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SuperJB
Starting Member

12 Posts

Posted - 06/06/2012 :  11:51:35  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 06/06/2012 :  12:01:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 06/07/2012 :  04:16:07  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000