Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 1:25 PM
Points: 121, Visits: 297
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.

Gary.

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: Friday, April 25, 2014 8:52 AM
Points: 243, Visits: 310
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 http://msdn.microsoft.com/en-us/library/bb933994.aspx)
3) Can deloy schema (see http://msdn.microsoft.com/en-us/library/bb726035(v=sql.100).aspx )
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.

Cheers

Peter
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);
myClientScopeProvision.SetUseBulkProceduresDefault(true);
if (!myClientScopeProvision.ScopeExists("Core"))
{
myClientScopeProvision.Apply();
}

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 www.1stconsulting.biz
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse