The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Guest Editor: Louis Davidson

As we rush headlong into 2013, many of us prepare the annual document of self-deception otherwise known as our "New Year's Resolutions". Most people don't intend to lie to themselves; it just often ends up that way. If you are lazy then the act of writing down "don't be lazy" won't transform you into a marathon runner. Likewise, writing down "lose weight" won't precipitate an immediate downward spiral in pant size. Why not? Too often, the problem is that our past failures are staring us in the face, informing us up front of the likely futility of our resolutions, and preventing us from moving on.

We have to forget the pain of past failures, just like the one-year-old babies that constantly put stuff in their mouths, even if most times it tastes awful. If memories of past failures prevented them from developing, then they would starve after their first beetle snack.

As we age, our sense of trepidation grows as we learn the consequences of our actions. After suffering a bashed head, or worse, the first time a teenage buddy suggests jumping off a bridge, we learn that next time that just maybe we should wait and see what happens to the other person first. However, we can overdo caution. What if we carry that initial memory around indefinitely and it develops into a phobia (a fear of heights, for example) that thwarts many other ambitions?

Can DBAs overdo caution as well? On the one hand, it is important that we remember and learn from both our good and bad experiences. On the other, if we never break free from any of the pain of past failures, they can forever dog our progress. I spend a considerable portion of my professional life maintaining backward compatibility with my own previous mistakes and failures. Our primary system is so heavily laden with poor choices made 20 years ago (and replicated in multiple systems since, built to replace the previous "broken" systems) that every decision I want to make as an architect has to be considered against a thousand other busted processes. Why don't I just fix the original problems?

What, if, for example, I could find the time and tools to apply a set of selective DELETE statements to my "bad data" (call it SQL amnesia!), apply a thin layer of CHECK constraints, and reinsert rule-conforming data? I would no longer be tied to past mistakes; I could save hours every single day by not having to write code that remembers that someone may have entered a value like "I ain't got any idea", instead of a standard value like "Unknown"!

So this year, let's make it our New Year's Resolutions as DBAs and programmers to admit that we failed in the past. If you take decisive action you may get it wrong occasionally, but it is much better to dust yourself down and try again to take control of your life and work. A healthy dose of selective, enforced amnesia about the pain of failure is the answer. Fix bad data, rewrite terrible queries, eliminate unneeded cursor usage, normalize our sloppy table designs, and move on!

Eventually, it will become so difficult to remember how our previous malformed designs worked us stupid that we never look back.

Louis Davidson

Louis Davidson (@drsql) from SQLServerCentral.com

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

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Tech News : General Interest

Cloud security to be most disruptive technology of 2013 - In its report, "Information Security Shake-Up," the Security for Business Innovation Council said it was evident many organizations are preparing to move more business processes to the cloud. This year, it will even be "mission-critical apps and regulated data" consigned to the cloud....(more)

Tech News : Security

Hospitals far from immune to patient-data theft - Health care industry falls short in securing data for patients as BYOD and cloud adoption rise, security funding tumbles...(more)

Microsoft News : General Interest

In blocking Windows Phone access to YouTube, Google delivers rough justice - Google can't be trusted because it blocks access to APIs, says a top Microsoft lawyer, ignoring 30 years of Microsoft's own mischief in that department...(more)

Blogs : .NET

Microsoft Developer Platform at a Glance - A yearly roundup of the Microsoft developer platform., including SQL Server, Visual Studio 2012, .NET Framework 4.5, Windows Azure, Windows Phone, Office 2013, and more. ...(more)

Blogs : Administration

Full-Text Search – Stoplists in SQL Server - Full-text search is an interesting subsystem in SQL Server. It allows you to implement searches through a variety of text formats stored in SQL Server. In the first of a series of posts that looks at different facets of full-text search, Steve Jones discusses the stoplist, a list of stopwords that SQL Server should not include in a full-text index....(more)

Checking the Instance Fill Factor - It's a bad idea to change the instance’s default fill factor but if you aren’t sure if it’s been changed on any of your instances, here’s how to check it....(more)

Rebuilding Indexes Will Reset Index Usage Statistics in SQL Server - Ask any SQL Server DBA the following question: “When is the data reset for a DMV?” The most likely answer will be “after the instance has been restarted”. A handful of folks will even go so far as to say “when you manually reset them” using something like DBCC SQLPERF. I don’t know too many people that would ever answer “when you rebuild indexes”....(more)

The Myth Around 32-bit SQL Server Instances on 64-bit Operating Systems, and AWE - Recently we had a discussion in an distribution list where somebody asked whether a SQL Server 32-bit instance could address more than 4Gb of RAM when running on top of a 64-bit OS. One of the really smart guys in Microsoft SQL Server Support replied “sure, just add more RAM and enable AWE – SQL will use that memory”. I was much convinced that this was incorrect, so I jumped in and said that AWE does nothing under those circumstances. But the good news is that I was wrong....(more)

Evaluate This–Server Core and MinShell - Andrew Fryer shows us how to rip parts of the interface out of Windows Server to create a minimal UI (known unofficially as MinShell)....(more)

Blogs : Backup and Recovery

Day 3 of 31 Days of Disaster Recovery: Determining Files to Restore Database - Everyone on your team who might be called upon to perform a restore should be performing regular test runs of restoring the backups. It’s this lack of testing that leads to fumbling around trying to figure out what files to restore....(more)

Importance of where you store your backups - Paul Randal presents the results of his backup survey, showing that most people store backups locally and off-site, and offers food-for-thought to those who do not have offsite backups....(more)

Day 1 of 31 Days of Disaster Recovery: Does DBCC Automatically Use Existing Snapshot? - SQLSolidier attempts to disprove, definitively, that the DBCC CHECK commands will use an existing snapshot for a database....(more)

Blogs : Computing in the Cloud

How full is my Windows Azure SQL Database? - While the level of compatibility of Windows Azure SQL Databases is high, there are a number of things that need to be dealt with differently, compared with how they are done with on-premises SQL Server....(more)

Blogs : DMO/SMO/Powershell

Pseudo-Polymorphism in PowerShell - When learning a strongly-typed, object oriented language one of the first concepts you learn is polymorphism. Polymorphic members accept object types that define a particular contract. Class instances that meet this contract, even if they extend from it, can be provided to these members. In PowerShell, this can be true when dealing with .NET classes....(more)

PowerShell Workflows: Restrictions - PowerShell MVP Richard Siddaway discusses some of the restrictions around Windows PowerShell workflows and how they can be overcome....(more)

Blogs : Hardware

SQL Server 2012 Standard Edition Licensing Limits - Glenn Berry makes a case for adjusting some hardware-related licensing limits for SQL Server 2012 Standard Edition, in light of the capabilities of modern, commodity server hardware....(more)

Blogs : Integration Services/ETL

Microsoft advocates checkpoints - proceed with caution [SSIS] - In December 2012 Microsoft published a whitepaper entitled SSIS Operational and Tuning Guide, which advocates the use of checkpoints so that in the event of failure, you do not lose all of the progress the package has made up to this point. Jamie Thomson explains why he feels checkpoints are worth avoiding, for the time being....(more)

SSIS Tips Tricks and Best Practices: SSIS for Azure and Hybrid Data Movement - This paper outlines best practices for using SSIS for cloud sources and destinations and for project planning for SSIS projects to be used with Azure or hybrid data moves, and gives an example of maximizing performance on a hybrid move by scaling out the data movement....(more)

SSIS Tips Tricks and Best Practices: SSIS Operational and Tuning Guide - This paper outlines SSIS best practices for cloud sources and destinations, discusses project planning for SSIS projects whether the project is all in the cloud or involves hybrid data moves, and walks through an example of maximizing performance on a hybrid move by scaling out the data movement....(more)

Blogs : NOSQL

Cassandra 1.2 database better geared for 'fat servers' - Adjusting to changes in corporate hardware buying habits, the Apache Software Foundation's Cassandra NoSQL distributed database has been updated to better use larger servers through the introduction of virtual nodes and configurable policies for disk failure....(more)

Getting started with Hadoop - I wanted to get started playing about with Hadoop but had trouble installing Cloudera’s CDH. As I only wanted to have a working version of Hadoop for development purposes I decided to skip using Cloudera’s distribution and go direct to the Apache Hadoop release. Here’s the process I went through to set it up on OpenSuSE 12.1....(more)

SQL is Agile - Armin Ronacher sees a lot of people claiming that non-relational datastores are so much easier for prototyping than SQL based ones. Here, he explains why he doesn't entirely agree....(more)

Blogs : Performance and Tuning

The SQL Query Optimizer – when Logical Order can get it wrong - It’s very easy to get in the habit of imagining the way that a query should work based on the Logical Order of query processing – the idea that the FROM clause gets evaluated first, followed by the WHERE clause, GROUP BY, and so on – finally ending with whatever is in the SELECT clause. We even get in the habit of creating indexes that focus on the WHERE clause, and this is mostly right. But it’s only mostly right, and it will often depend on statistics....(more)

Blogs : Professional Development

Database Management Survey 2013 - As part of my PhD with the Open University I am currently undertaking an independent database survey . My research investigates the current practices and procedures that are used and examines the complexities of managing database systems. There are no prizes for helping, just the satisfaction that you have helped the database community as a whole and of course my gratitude...(more)

2013 and the Impossible Challenge - Having missed my mid-life crisis entirely, I sometimes contemplate those things that I have I always longed to do but failed, and the excuses I have stood up against that wall. “I don’t have enough time” is my usual go-to when my internal critic demands to know why I have not yet learned a foreign language, or finished my Great American Novel....(more)

Blogs : Reporting Services

Configuring Reporting Services to send email subscriptions outside the organization - It is possible to send a email subscriptions in Reporting Services to recipients that are outside the domain. Maria Esteban explains how....(more)

Blogs : Security and Auditing

SQL Server: Three Common DDL Change Log Methods - Who is changing your objects (tables, views, stored procedures, functions etc) or creating new one, or who actually deleted one or more objects? ...(more)

EE-K! DM’ing your password is NEVER a good idea - EE is over in the UK and they’re “the new network for your digital life” who brings you “4G and Fibre Broadband”. A quick look at All My Tweets shows that requesting passwords through Twitter is a standard operating procedure. So what’s wrong with all this? Troy Hunt counts the ways....(more)

Blogs : T-SQL

Help to Improve SQL Batch Operations and ETL - How many times have you copied data into a table and got one of many less than helpful errors? Simon Sabin explains an ideas whereby the errors get dumped into a table just like the output clause does. You can then examine the contents of the errors table and do something with it....(more)

SQL Server 2012 Sequences - SQL Server 2012 sequences provide Oracle style flexibility in doing parent child inserts in situations where the IDENTITY property would otherwise be used....(more)

Avoid broken Views in SQL Server with Schemabinding - The broken views problem is something I discovered when working on a production system. When I first observed it I was shocked. When I dug deeper into it I was even more shocked at the pure evilness this behaviour could cause...(more)

Handling Errors in SQL Server 2012 - The error handling of SQL Server has always been somewhat mysterious. Now at last, the THROW statement has been included in SQL Server 2012 that, when combined with the TRY ... CATCH block, makes error handling far easier. Robert Sheldon explains all....(more)