Today we have a guest editorial as Steve is away at SQL Bits.
Have you ever had a discussion with your manager and he/she threw around phrases like “out-of-the-box” and “one-size-fits-all?” If you have, then hopefully you were able to maintain a poker face and not cringe too much.
These dangerous words were presented to me during a conversation about SQL Server and multi-site fail-over / replication. My boss wanted a single strategy which would perform optimally for all of our systems, be fully supported by Microsoft, and have zero impact to any of our development practices and database structure. A single, company-wide strategy might be possible for some companies that only have a single type of system, but my company deals with many different data processes. We have customer facing transactional systems, internal transactional systems, back-end high-volume processing systems, BI data warehouses / cubes, and every variation in-between.
With each of our types of data processing, I would normally recommend different disaster recovery or active-active strategies with varying data loss SLAs and RTOs. In addition, seamless implementation without impacting the development teams is unreasonable. Each strategy has the potential to induce constraints, or at least, change the existing procedures for dealing with failures.
With that said, there is no 100% out-of-the-box fail-over or active-active solution that comes in the form of a one-size-fits-all black box. For that matter, SQL Server doesn't have a good replication feature to support multiple masters at all. Attempting to implement a less than optimal method for the type of processing will produce nothing more than a less than optimally functioning system. Multi-site configurations need to be a part of the design of a system rather than an afterthought.