Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Question on Change Tracking & Replication


A Question on Change Tracking & Replication

Author
Message
MonkeyMan
MonkeyMan
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
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.

Gary.
Peter Bannister
Peter Bannister
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 325
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
paulette.eimer
paulette.eimer
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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?
sihundleby
sihundleby
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
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
smorneau
smorneau
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
I'm also currently trying to find a way to identify recently changed data on a subscriber. I'm considering CT and CDC and would like to know what you implemented and your thoughts on the results.

Right now, I don't see how we can reinitialise using CT and not lose where we were up to before the reinit.
Using "DELETE" over "DROP " on the precmd of the article solves the thing with CDC, but I'd have to scan everything using this technique and CT.

Regards
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search