Today we have a guest editorial as Steve is away on vacation. This editorial was originally published on July 16, 2014.

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.

Join the debate, and respond to today's editorial on the forums

In this case, this is an unusual item, but one that makes sense. The object that contains the string is declared inside of the master database and the collation precedence rules state:

"If the object is declared in a user-defined function, stored procedure, or trigger, the object is assigned the default collation of the database in which the function, stored procedure, or trigger is created. If the object is declared in a batch, the object is assigned the default collation of the current database for the connection."

Ref: Collation Precedence - click here

» Discuss this question and answer on the forums