I am looking for suggestions/ideas to the following problem. We have a number of data warehouses for each of our clients (on SQL Server 2014). These data warehouses are maintained fairly fresh with continuous data loads every few minutes. When DW needs to change, this will, more often than not, require a complete data re-load. The challenge is that we can't afford to take DW offline for extended period of time and, consequently, make any applications, which use this data unavailable. It is acceptable if the data is stale, but it has to be available.

Therefore, we are looking for a solution, which will enable us to maintain a secondary copy of the data warehouse. Let's call these copies A and B. The secondary copy (B) needs to be kept in-synch with the primary copy (A). When the time comes to make significant DW changes, we would like for the following to happen: 1) stop synchronization between A and B 2) point our applications to the secondary copy (B) 3) perform our upgrade/maintenance/re-load of the primary copy (A) 4) point our applications back to the primary copy (A) 5) re-establish synchronization between A and B (we understand it will take some time)

So basically B acts as a temporary "fill-in" while A is being upgraded and reloaded.

The solution has to be automatable, meaning, we need to be able to do everything from a command-line/powershell.

We looked at some of the existing HA/DR technologies, for example SQL Server Always On, but it seems that breaking and re-establishing synchronization (as described above) may not be a good fit for it. It seems that what we need is a 3rd party software, possibly capturing disk activity at the system level.

I appreciate any ideas, recommendations or experiences.

Thank you!

  • Have you considered sql server database replication? You can do transactional replication which is near real time or snapshot replication. Which can be run at intervals. – Sir Swears-a-lot Aug 25 '15 at 22:30
  • The difficult part is getting apps to change connections. We found it easier load to a principal db, replicate and run all of our reports off the replica. – Sir Swears-a-lot Aug 25 '15 at 22:36
  • Thank you, Peter, for your suggestion. We are using transactional replication in our environment, so we are familiar with what's involved. Given the number of replication setups we would need to maintain, we have decided against this solution. Too much maintenance overhead. – SQL_Guy Aug 26 '15 at 0:10
  • Are you accepting write transactions during all of this or strictly reads? How much downtime would be acceptable? The thing that makes AvailabilityGroups so cool is that you don't actually ping the server directly, but rather the listener. It might be feasible to kick the secondary replica out of the AG and do the upgrade. Shut down the primary and restart the AG on the secondary. Upgrade the primary and re-introduce to the replica. Your down time would be minimal, assuming you have the steps scripted out - not hard to do. Connections strings would always be pointed to the AG. – Steve Mangiameli Sep 3 '15 at 20:47
  • Steve, thank you for your comment! Having a read-only copy is what I am after. The amount of downtime will vary, so I can't be more specific. Have you done this sort of script-driven manipulations of AG? (with adding/removing primaries and secondaries) Do you know if it's feasible? – SQL_Guy Sep 6 '15 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.