Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

A Question on Change Tracking & Replication Expand / Collapse
Posted Thursday, April 5, 2012 2:01 AM



Group: General Forum Members
Last Login: Sunday, March 6, 2016 5:10 PM
Points: 131, Visits: 329
Hi everybody,

I'm in the early stages of designing a Data Warehouse for my employer. It's a green field project in which we'll be using SQL Server 2008 R2 and sourcing from approximately 10 line of business systems. Phase 1 has two deliverables, the architecture and a single view of the customer. I've build smaller and administered larger systems before.

My preferred solution for getting changed data into a conformed DW from source systems is to use transactional replication from the original source into the DW server and then to use change tracking on those replicated databases. I'll use a metadata database to keep the LSN's for each database and use SSIS to funnel the transactions from staging into a data store and subsequently build business specific data marts over the cleansed, conformed data.

We have regular changes to our LOB Applications which periodically affect the schema. I can't find any references to the effect on change tracking of re-snapshotting a replicated database. I assume - and that's never a good start - that the LSN's from the source database will continue after the snapshot so my Metadata will still be valid and I can continue to use it. Clearly schematic changes that affect the objects I'm using may require amendments to the ETL process.

Now to my specific question. Has anybody done this? Do you know whether my above assumption is correct? Could you point me in the direction of some online/paper references? Or would you advise a different process altogether?

Many thanks in advanced.


Post #1278616
Posted Friday, April 6, 2012 3:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 14, 2015 1:01 AM
Points: 243, Visits: 324
Hi Gary,
Looking at your scenario a couple of key points came out
1) Change data
2) Schema changes
3) Replication

This sounds like synch services to me as you can
1) Replicate in groups (goes towards customers only in phase 1)
2) Trap changed data only (see
3) Can deloy schema (see )
4) This can be off line so can work in a disconnected environment

While this may not be a standard use for synch services at first glance it does seem to fit the bill !

Anyway - I hope this gives you some food for thought.


PS here is some same code for replicating a scope called "Core" - simple case with no schema changes
private void core()
SqlConnection myConnectionLocal = new SqlConnection(XYZUtility.XYZConnectionString());
SqlConnection myConnectionServer = new SqlConnection(XYZUtility.XYZReplicationString());

DbSyncScopeDescription clientScope = SqlSyncDescriptionBuilder.GetDescriptionForScope("Core", null, "dbo", myConnectionServer);
SqlSyncScopeProvisioning myClientScopeProvision = new SqlSyncScopeProvisioning(myConnectionLocal, clientScope);
if (!myClientScopeProvision.ScopeExists("Core"))

SqlSyncProvider mySQLProviderLocal = new SqlSyncProvider("Core", myConnectionLocal);
SqlSyncProvider mySQLProviderServer = new SqlSyncProvider("Core", myConnectionServer);

mySQLProviderLocal.BatchingDirectory = Environment.ExpandEnvironmentVariables("%TEMP%");
mySQLProviderServer.MemoryDataCacheSize = 50000;
mySQLProviderLocal.MemoryDataCacheSize = 50000;
mySQLProviderServer.ApplicationTransactionSize = 10000;

SyncOrchestrator mySyncOrchestrator = new SyncOrchestrator();
mySyncOrchestrator.LocalProvider = mySQLProviderLocal;
mySyncOrchestrator.RemoteProvider = mySQLProviderServer;
mySyncOrchestrator.Direction = SyncDirectionOrder.Download;

SyncOperationStatistics myStats = mySyncOrchestrator.Synchronize();
state.Text = state.Text + "\rCore data replication started at: " + myStats.SyncStartTime.ToString() + " and finished at :" + myStats.SyncEndTime.ToString();


I work for 1st Consulting, a small ISV based in the UK. We build web and desktop applications using the MSFT stack. Find us at
Post #1279370
Posted Thursday, October 31, 2013 11:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 10:43 AM
Points: 3, Visits: 6
We are trying to do something similar and are having problems. We have a production DB replicated to a Warehouse server. We turned Change Tracking on for the replicated copy but no changes are showing in the change table. Doesn't replicated changes hit the change table?
Post #1510346
Posted Tuesday, February 10, 2015 2:36 AM


Group: General Forum Members
Last Login: Thursday, September 1, 2016 6:14 AM
Points: 121, Visits: 69
I Have change tracking on a replicated database.
I then synch to a number Of other databases.
It has worked fine now and has been running a couple of months
Post #1659063
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse