July 15, 2013 at 2:01 am
We have a situation where we are required to track changes to data in a handful of tables but we are limited in the options available to us.
This is a SQL Server 2005 enterprise edition database which acts as the repository for a financial system.
The financial system comes with an auditing feature which can be turned on to track exactly the information required. However, when the feature was turned on there was a conflict of some sort and 3rd party support has been lacking. In the end, we have been asked to come up with alternatives.
Environment:
Server1 - SQL 2005 Enterprise
Server2 - SQL Server 2008 R2 Standard
Server 2 is our small data warehouse. It has an SSIS ETL and is the target of this operation.
Server 1 is a Production system with transaction volumes measured in the thousands per day.
My understanding is that the audit feature mentioned previously uses a trigger mechanism and performance degradation had been experienced in prior testing. I would have thought that the volumes we are talking about shouldn't be a big problem for a trigger based solution...
Anyway, the CDC feature in SQL 2008 would have been perfect except we don't have access to 2008 enterprise edition.
Normally we would capture the changes as part of our over night batch but in this case we require tracking of intra-day changes
My other thought has been to set up transactional replication on server 1 to publish the required tables onto server 2. Server 2 could then use a trigger based solution without impacting server 1.
Is this a fair use of replication? Am I over-baking this?
Basic requirements are:
Changes are captured on a few tables.
- All changes in a sequence are captured with datetime stamps.
TIA
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply