April 5, 2012 at 2:01 am
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.
April 6, 2012 at 3:16 am
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
October 31, 2013 at 11:09 am
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?
February 10, 2015 at 2:36 am
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
November 10, 2016 at 11:26 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply