February 25, 2014 at 12:51 pm
Scenario: Business reporting data needs to be available 24/7 and improve data refresh to every 6hours.
Current: We use nightly snapshot replication to keep our reporting database up to date. We have the previous days data to use for the next days reports.
Questions: Challenging question and scenario. We'd like to minimize our downtime while the snapshot is being populated. Transactional replication is not an option based on the current table structures lacking primary keys on some tables. Is there a way to 'flip' databases? Meaning we use SnapshotDB1 for data reporting until SnapshotDB2 is fully populated then use SnapshotDB2.
February 26, 2014 at 10:56 am
In theory you could do something like this:
ALTER DATABASE SnapshotDB1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE SnapshotDB1 MODIFY NAME = SnapshotDB3
GO
ALTER DATABASE SnapshotDB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE SnapshotDB2 MODIFY NAME = SnapshotDB1
GO
ALTER DATABASE SnapshotDB1 SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
Any active connections would have to reconnect.
I've never done this, but in theory it should work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2014 at 12:11 pm
I was thinking something similar and it might just be a better solution then using SQL aliasing. Because you can execute a script at the end of the subscription sync.
I will definitely keep this theory in mind. Thank you very much. Any other ideas are welcomed!
March 10, 2014 at 10:44 am
I understand you are doing snapshots , if in your case its the change is only data not schemas . what about opting some ETL process or import/export job .
Just a different thought as downtime during snapshot is matter of concern in your scenario .
March 12, 2014 at 6:37 am
trinityr (2/25/2014) Is there a way to 'flip' databases? Meaning we use SnapshotDB1 for data reporting until SnapshotDB2 is fully populated then use SnapshotDB2.
You can do this by "queuing" database snapshots.
You would need a logical database with a collection of views and a stored proc which dynamically regenerates the views when a new snapshot becomes available. There would always be a small lag in data but you could easily do this every 15 minutes or less.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply