patternsqlMinor
SQL Server database synchronization
Viewed 0 times
sqldatabasesynchronizationserver
Problem
Problem definition
Our users need the ability to query a database that is mostly up to date. The data can be stale up to 24 hours and that is acceptable. What would be the lowest cost approach to getting and keeping a second database up to date with a production copy? Is there an approach I'm not thinking of?
Workload
We have a third party application that we use to monitor stock trading activity. During the day, lots of little changes occur as part of various work flows (yes, this trade was valid. No, this is suspicious, etc). At night, we perform large set based operations (load the previous day's trades).
The current solution and problem
We make use of database snapshots. At 10 p.m. we drop and recreate the snapshot. The ETL processing then begins. This is obviously taxing on our disk but allows our users the ability to query the database without locking the database (they use an Access front end). They use it late into the night and early in the morning so they will notice downtime.
The problem with this approach is two-fold. The first is that in the event the nightly processing fails, and that's not terribly uncommon, we get to restore the database which results in the snapshot being dropped. The other problem is our processing times are slipping past our SLA. We are attempting to address this by working with the vendor after having identified poorly written queries and lack of indexing. The database snapshot is also a culprit in this slowdown as evidenced by the speed difference when it is present versus not---shocking, I know.
Approaches considered
Clustering
We had database clustering turned on but that didn't address the needs of making the data available and just generally complicated the admin's lives. It has since been turned off.
SQL Server Replication
We started looking at replication last week. Our theory is that we can get a second catalog stood up and synchronized with the production database. Prior to ETL beginning, we'll sever the conne
Our users need the ability to query a database that is mostly up to date. The data can be stale up to 24 hours and that is acceptable. What would be the lowest cost approach to getting and keeping a second database up to date with a production copy? Is there an approach I'm not thinking of?
Workload
We have a third party application that we use to monitor stock trading activity. During the day, lots of little changes occur as part of various work flows (yes, this trade was valid. No, this is suspicious, etc). At night, we perform large set based operations (load the previous day's trades).
The current solution and problem
We make use of database snapshots. At 10 p.m. we drop and recreate the snapshot. The ETL processing then begins. This is obviously taxing on our disk but allows our users the ability to query the database without locking the database (they use an Access front end). They use it late into the night and early in the morning so they will notice downtime.
The problem with this approach is two-fold. The first is that in the event the nightly processing fails, and that's not terribly uncommon, we get to restore the database which results in the snapshot being dropped. The other problem is our processing times are slipping past our SLA. We are attempting to address this by working with the vendor after having identified poorly written queries and lack of indexing. The database snapshot is also a culprit in this slowdown as evidenced by the speed difference when it is present versus not---shocking, I know.
Approaches considered
Clustering
We had database clustering turned on but that didn't address the needs of making the data available and just generally complicated the admin's lives. It has since been turned off.
SQL Server Replication
We started looking at replication last week. Our theory is that we can get a second catalog stood up and synchronized with the production database. Prior to ETL beginning, we'll sever the conne
Solution
Modifying their structure is generally frowned upon
Replication is more than likely out and I'd throw Sync out before that. (from real life high transacitonal tests on Sync Framework)
If 3-4 hours is your data latency exceptance, log shipping will probably be your bests bet here on a read-only copy. But how much change is happening in the log? find that out be monitoring it to see how quickly and how much you need to push across.
If you can't go to Mirroring or upgrade to 2012 enterprise, that is out although that would be a sound strategy if you can go Enterprise if not on it.
SSIS isn't meant to just dump data over but it can do it. You're looking at far too much in the terms of lookup transformations though and the task would be expensive in time and resources. Although, like I said, it can do it.
Really, there will be a distinct narrowing of choices based on answering a few questions
Replication is more than likely out and I'd throw Sync out before that. (from real life high transacitonal tests on Sync Framework)
If 3-4 hours is your data latency exceptance, log shipping will probably be your bests bet here on a read-only copy. But how much change is happening in the log? find that out be monitoring it to see how quickly and how much you need to push across.
If you can't go to Mirroring or upgrade to 2012 enterprise, that is out although that would be a sound strategy if you can go Enterprise if not on it.
SSIS isn't meant to just dump data over but it can do it. You're looking at far too much in the terms of lookup transformations though and the task would be expensive in time and resources. Although, like I said, it can do it.
Really, there will be a distinct narrowing of choices based on answering a few questions
- Data latency acceptance
- Amount of data change in a given minute,hour and day Connectivity to the secondary
- Read requirements on the secondary instance
- Up-time of the secondary instance
- Alterations to existing schema and all objects
- Security
Context
StackExchange Database Administrators Q#25627, answer score: 6
Revisions (0)
No revisions yet.