A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

One Size Does Not Fit All

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.

Derik Hammer from SQLServerCentral.com

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

Database migrations inside Visual Studio

Feeling the pain of managing and deploying database changes manually? Redgate ReadyRoll creates SQL migration scripts you can use to version control, build and release, and automate deployments. Try it free

NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps

Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. Download your free trial

Featured Contents

How to execute a Powershell script via SSIS

Perry Whittle from SQLServerCentral.com

Learn how you can execute a PowerShell script via the SSIS execute process task. More »

Data Retention: An Inexact Art

Additional Articles from SimpleTalk

A myriad of laws regulate data retention and generally compete with each other. William Brewer provides an introduction to the principles, but gives good advice -- leave the details to the specialists. More »

From the SQLServerCentral Blogs - SQLSaturday Tools

Jason Carter from SQLServerCentral Blogs

Much has been said about SQL Saturday tools and how PASS is pulling back from development of those tools past... More »

From the SQLServerCentral Blogs - Use Boot Diagnostics to see what your Azure VM is doing at boot time

hamish.watson8 from SQLServerCentral Blogs

This blog post is about how to diagnose what your Azure VM is doing while it is booting. I have a... More »

Question of the Day

Today's Question (by Steve Jones):

In SSMS 17.5, there is a new featuer that allows you to classify data with various sensitivity levels. This is a database level task that allows you to set a sensitivity level for different columns. How is this implemented in SQL Server for the meta data storage?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Management Studio (SSMS).

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Securing SQL Server: DBAs Defending the Database

Protect your data from attack by using SQL Server technologies to implement a defense-in-depth strategy, performing threat analysis, and encrypting sensitive data as a last line of defense against compromise. The multi-layered approach in this book helps ensure that a single breach doesn't lead to loss or compromise of your data that is confidential and important to the business. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I run this query from inside a case sensitive version of AdventureWorks.

 SELECT * FROM AdventureWorks2012CS.Person.Person WHERE rowguid LIKE N'%abc%'

I get zero rows returned. However, if I change context to master and run this, I get these results.

Why?

Answer: The master database isn't case sensitive

Explanation:

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

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Administration

latence in log shipping - Hello everyone I have a SQL instance configured in logshiping mode I receive alerts on latencies of log file restoration The secondary ProductionSQLA...

SQL Server 2017 : SQL Server 2017 - Development

Generate a sequence number for corresponding numbers - I have a requirement , where i need to create a look up table to generate a Sequence number for each...

Retreving phone numbers based on phone type - i have a table where it has empid, phone type and phone number one employee has multiple phone numbers. is there...

Removing stored procedures to move to cloud - I got an interesting email today about cutting back on the reliance of the applications on databases and plans to...

Generate a unique number for a combination - How do you generate unique number based on a Loannumber , Date columns Example Declare @loannumber Int = 1234565 ,@date Date = '12-01-2017' Declare @test...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Schema related issue - I have a procedure inside a schema 'ABC'. inside this procedure in an if else block using left outer join...

Trying to Get Rid of Cursor for Creating Group Number - Here is the basic scenario, I have a table that can have site values 1-12, each site can have seq...

Db name inside the stored proc - All, I am trying to work on a stored proc that would accept DB as a parameter name and then work...

SQL Server 2014 : Administration - SQL Server 2014

Unable to connect to DAC - We are running SQL Server 2014 Web Edition. The DAC has been enabled, remote connections are enabled, and the Browser...

Moving Database Objects - Hi Experts, We are moving objects of a particular schema to a new database. What all things need to check while...

SQL Server 2014 : Development - SQL Server 2014

Clustered Index created but SQL complains - Hi Guys, I'm very new in query optimizing. I have created a non clustered Index on tbl_A as below also note that...

SQL Server 2012 : SQL 2012 - General

help me with building the SQL statement - The following SQL works fine ( run it please and you will see the exact output I am looking for ) However...

DBCC Emptyfile - I have a question, I hope someone here can answer. I have searched multiple web sites, and can't get a...

SQL Server 2012 : SQL Server 2012 - T-SQL

Query - Performance between dates - Im running a query and it has numerous linked tables. I have entered a date parameter of one week (28/01/2018...

SQL Server 2008 : SQL Server 2008 - General

Updating null fields wehn changing field to not null. - Hi, we have a flat table in SQL server that has no PK field and no constraints. The table is...

Purge database - Hello I have just started a new job and my first task is to purge the database.This is a transaction...

database diagram - "add related table" greyed out - Hi there With SQL 2008 database diagram - when I right click on my table to add related tables, this option is...

SQL Server 2008 : T-SQL (SS2K8)

Coalesce and simple pivot (or group by?) - hi, my SQL knowledge got a little bit rusty. Trying with Group By failed, so this is my question: CREATETABLE #tblTemp1(ID...

OPENQUERY error: An unexpected NULL value was returned - Hi, all! Over the past year, we've been migrating our reporting databases from 2000 to 2008R2. We've got many stored procedures...

Cloud Computing : SQL Azure - Administration

Issue with using Ola Hallengren Backup script - Hi, I am using OH's excellent scripts, however I have found and issue when it comes to backing up to...

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.