February 6, 2014 at 11:46 pm
Looking for a solution to real time replicate data from our OLTP system to a staging database (MSSQL server based) for Business intelligence reporting,
The requirement is to not delete data from staging database when the OLTP system data is archived. Thus staging database will maintain all old rows too.
I can achieve this using SQL replication by choosing not to replicate deletes in publication properties.
However i am worried with reinitialization of subscriber deleting the rows on the staging database as well. Reinitialization might be required if the subscription expires or under some eventuality. I can do a no sync subscription in this case.
However if the data changes on primary OLTP when the subscription expires i think i could get in hot water...
We are keen to avoid going the ETL route as extracts to staging database needs to be sent for various regions so the jobs will need to run multiple times of the data and add complexity
Any comments.....
February 7, 2014 at 8:03 am
Yes if/when you need to reinitialize the publication you will lose all the data in the STAGING server....not much you can do there. The only suggestion I could offer is to perhaps create partitions for your tables or move data to separate file groups and mark them as read only? Then back the file groups up individually to a network location somewhere. If and when you have to reinitialize, you could do so, then add the read only file groups back in after-the-fact.
I'm not even sure that would work, but since no one else currently has any idea either, I thought I'd offer my thoughts...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply