The Complete Weekly Roundup of SQL Server News

In this issue:

SQL Server News : Bugs/KB

Microsoft News : General Interest

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Big Data

Blogs : Computing in the Cloud

Blogs : Data Mining

Blogs : Database Design, Theory and Development

Blogs : Deep Into Windows

Blogs : DMO/SMO/Powershell

Blogs : Events

Blogs : Integration Services/ETL

Blogs : NOSQL

Blogs : Performance and Tuning

Blogs : Professional Development

Blogs : Replication

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : Software Development

Blogs : SQL Server 2014

Blogs : T-SQL

Blogs : Virtualization

Database Weekly - www.databaseweekly.com

The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server News for 2014-07-07

SQL DBA Bundle The seven tools in the SQL DBA Bundle support your core SQL Server database administration tasks.
Make backups a breeze! Enjoy trouble-free troubleshooting! Make the most of monitoring! Download a free trial now.
SQL Server Backup and Restore eBook Free eBook: SQL Server Backup and Restore
With the tools, scripts, and techniques in this free eBook, you will be prepared to respond quickly and efficiently to disaster, whether it's disk failure, database corruption, or accidental data deletion. Download the free eBook.
SQL Backup Want faster, smaller backups you can rely on?
Use SQL Backup Pro for up to 95% compression, faster file transfer and integrated DBCC CHECKDB. Download a free trial now.
Editorial - DBAs and the Fear Factor

"Do one thing every day that scares you" – Eleanor Roosevelt

Laudable as these sentiments are, inspirational even, most DBAs are likely to treat them with a customary degree of caution and circumspection. The DBA profession isn't necessarily one we associate with a love of danger and keen spirit of adventure.

Many DBAs protect vehemently what to others seem archaic practices, such as attention to detail, conservative change patterns, treating data quality, integrity, and security like it really matters, and so on. This is possibly because a DBA's existence is centered largely on protecting data from people whose mantra seems to be "Write one line of code every minute that scares the DBA".

Nevertheless, it's a mistake to ignore Mrs. Roosevelt's sentiments entirely. Fear, in and of itself is a wonderful guide to what is right and wrong, and yet if we never confront anything that we fear, or don't understand, then we risk stagnation. We then become the type of DBA who bemoans the fact that Microsoft didn't spend less time inventing newfangled bells and whistles that just complicate things, and more time perfecting the features that were already in SQL Server 2000.

When Mrs. Roosevelt said those words, she was encouraging us all to tackle one thing every day that we find foreign, difficult or scary. For some bold and fearless types, this might mean crazy pursuits such as blindfolded motorcycle riding or cliff diving. For the rest of us, a group which no doubt the first lady was more likely addressing, it just means saying hello to a stranger, eat something new, or attempting to learn something that we find difficult.

For the DBA, embracing the sentiment doesn't mean jumping off the metaphorical cliff, declaring open season on sys admin privileges, undertaking mass denormalization, or learning to love heaps. Yes, these do give us fear, but not the right kind of fear! It just means occasionally looking over the edge of that cliff, feeling the increase in your heart rate and tightness in your chest as you tackle the unknown or, in some sense, frightening.

In the years since SQL Server 2000, Microsoft has introduced any number of features that, while more complicated than you might be comfortable with initially, will make life easier for you and better for your users. These encompass data and backup compression, new server roles and permissions, partitioning, new generation High Availability Technology, and even the entirely in-memory data tables and indexes, and columnar data structures.

If you have so far chosen the ostrich school of learning, get your head out of your sand, look around, and try something new. Some of these features are 'scary' and will challenge what you think you already know about SQL Server and its data structures. However, I almost guarantee you that, in many cases, once you get a glimpse of their value you will end up pushing as hard as you can to get them implemented in your company.

If the fear of these new technologies is your company's, or vendor's, rather than yours, then don't let their fear turn into your apathy. Pick up a copy of SQL Server Express Edition (free) or Developer Edition (cheap), and try out new features on your laptop on a rainy evening. One day you may need another employer who has learned to store data in a modern database server and expects all of the newfangled goodies to be used rather than shunned.

Louis Davidson (Guest Editor)

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


The Weekly News

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.

SQL Server News : Bugs/KB

June 2014 Cumulative Update for SQL Server 2008 R2 SP2 - Microsoft has released the final Cumulative Update (#13) for SQL Server 2008 R2 Service Pack 2....(more)

Fix for SQL Server 2012 SP2 Data Corruption Issue Now Available - Microsoft recently announced service pack (SP) 2 for SQL Server 2012. However, we were aware of a potential data corruption issue related to online index rebuilds, so we had been recommending that customers avoid installing it until the fix was available....(more)

Microsoft News : General Interest

Microsoft admits to technical error in IP takeover, but No-IP still down - Microsoft admitted Tuesday it made a technical error after it commandeered part of an Internet service's network in order to shut down a botnet, but the Nevada-based company says its services are still down. No-IP claims Microsoft doesn't appear to be very good at DNS....(more)

Microsoft Brings Machine Learning as a Service to Azure Cloud - Microsoft said it is launching a cloud-based machine learning service, called Azure ML, in July. The service can be used to help create applications that can predict the future on the basis of previous data....(more)

Blogs : Administration

MSSQL Server Error Logs - Tim Dunn reluctantly hits the registry and constructs a PowerShell script to determine the installed folder, and therefore error log location, for each SQL Server instance on a server....(more)

New sp_Blitz® v35 Checks for Unsupported and Dangerous SQL Server Builds - News broke recently of a dangerous data loss bug in SQL Server 2012 and 2014, and Aaron Bertrand explained which patch levels are affected. It’s kinda tricky, and I’m afraid most people aren’t even going to know about the bug – let alone whether or not they’re on a bad version....(more)

Rename or Change SQL Server Standalone Default Instance - Rohit Garg walks through the simple 8-step process....(more)

Avoiding Database Deployment Disasters – 7 Tips - If continuous delivery for the database is the dream, the reality of releasing database changes for many teams is rather more akin to "push the Go button while chanting the age-old mantra please don’t break, please don’t break"...(more)

Blogs : Analysis Services / BI

The case of the vanishing KPIs - Investigating a tabular model that had some KPIs which were not showing up in Power View....(more)

Rolling 12 Months Average in DAX - Computing the rolling 12-month average in DAX looks like a simple task, but it hides some complexity. This article explains how to write the best formula avoiding common pitfalls using time intelligence functions....(more)

Blogs : Big Data

Big Data, Normalization & Analytics: Meaning & Constraints - Combining data extracts from databases for analytical purposes without knowing what the source database tables mean -- what exactly in the real world they represent -- can produce wrong results....(more)

Blogs : Computing in the Cloud

Version Control as a Cloud Service - Will the trend toward cloud-based services for version control continue, or is the question made irrelevant with the growing acceptance of distributed source control? Is Cloud-based version control still too risky for a service that must be secure, and almost risk-free?...(more)

Microsoft Azure Cloud Services Part 2: Anatomy of a Cloud Service - On the different ways you can build with a Cloud Service, and what exactly happens in terms of virtual machines when you deploy your application....(more)

Survey Shows SQL Dominates NoSQL in the Cloud - Recent research sponsored by Database as a Service (DBaaS) company Tesora shows SQL databases are holding their own in cloud usage....(more)

Blogs : Data Mining

Real world use cases of the Microsoft Analytics Platform System - Parallel Data Warehouse (PDW) got a new name: the Analytics Platform System [Appliance] or APS. Customers can buy an APS appliance with PDW or with PDW and HDI in configurable combinations....(more)

Blogs : Database Design, Theory and Development

How In-Memory Database Objects Affect Database Design: Or does it? - As an architect, it is important to realize that the database design affects the code. So will we need different data structures or will the normal normal model suffice? My initial feeling is that the logical model doesn't change. The actual implementation details (such as the lack of foreign, unique, and check constraints) will be a difference, and the hardware/implementation layer changes immensely, and this affects your code in ways you had best understand....(more)

Blogs : Deep Into Windows

Window’s UTF-8 Support Is A Travesty - I have been creating standardised feeds of ISO data lately. While I was working with this data, I began to notice a pattern: Windows has very poor support for UTF-8. In this post, I am going to be complaining loudly about this!...(more)

Blogs : DMO/SMO/Powershell

Bad practices – making scripts needlessly interactive - Richard Siddaway starts an occasional blog series on things he has seen people doing that either make their script far more complicated than it needs to be or just completely negates the power that PowerShell brings to daily admin tasks....(more)

Blogs : Events

PASS Summit Abstract Feedback - As of this week, PASS announced that anyone who submitted for Summit could e-mail and request to see the feedback on their submitted abstracts. Adam Machanic shares the feedback on his abstracts....(more)

PASS and Summit 2014 Session Selections - Andy Leonard offers analysis and opinions on the speaker and session selections for the Professional Association for SQL Server (PASS) Summit 2014. ...(more)

Blogs : Integration Services/ETL

Using Synonyms to Extend SSIS - There are a million and one uses for synonyms. There are at least that many uses for SSIS. The reality is, not all of those uses are good for you nor for your data nor for your database....(more)

Developing a Custom SSIS Source Component - SSIS was designed to be extensible and there will always be occasions when you need to write your own custom SSIS component. Yes, it is time to hone up your C# skills and cut some code, as Saurabh Dhoble explains....(more)

Blogs : NOSQL

NoSQL Has a Weakness. Just Tell No One. - Analytics tooling for NoSQL databases is almost non-existent...what usually happens is that companies extract, transform, normalize, and flatten their NoSQL data into an RDBMS, where they can slice and dice data and build reports....(more)

Relational Database Engine: Doomed in a NoSQL World? - Is the relational database management system going to go away? Probably not. And if it is, it isn't because of NoSQL database....(more)

Blogs : Performance and Tuning

TPC-H: Schema And Indexes - People use the TPC-H benchmark to evaluate data warehouse products to make purchasing decisions. Thomas Kejser explains what it is, what he thinks is wrong with it, and the challenges you face when tuning it....(more)

Introduction to Spinlocks in SQL Server - Spinlocks in SQL Server, and how you can troubleshoot them....(more)

Trivia about Trivial Plans - This post is trivia in that it won’t help you do your job as a developer or DBA. But it’s interesting anyway. It offers a look into an interesting part of SQL Server’s query optimizer....(more)

Blogs : Professional Development

The Curse of Working With A DBA - The reason why developers want to bypass the relational database is not the horror of the relational database itself, although, that’s there. No, a very large reason why is the DBA....(more)

I am not an MVP - Bill Fellows' ramblings on the MVP nomination process, and a nice infographic....(more)

You go to the effort of writing a blog and then … - Fatherjack wonders why many people, having gone to the effort of creating the content, fall at the last hurdle when presenting it online....(more)

On Managing and Doing Stuff - The titans of industry that founded the modern age didn’t just sit around sending each other emails (we called them Memos then), but they did stuff....(more)

Blogs : Replication

Monitoring SQL Server Transactional Replication - It was the best of times, it was the worst of times. I was a SQL Server DBA, and if something went wrong in Transactional Replication, Kendra Little explains what she learned from that experience about monitoring replication....(more)

Blogs : Reporting Services

Data Driven Subscriptions On A Budget - Data Driven subscriptions in SQL Server Reporting Services (SSRS) is only available if you have the Enterprise or BI Editions for 2012 & 2014, Enterprise or Data Center Editions for 2008R2 or Enterprise for 2008. But what happens when the money is not in the budget for those versions? Can you still get Data Driven subscriptions? The answer is You Bet!...(more)

Blogs : Security and Auditing

SERVER ROLES WITH TSQL - A few handy snippets of TSQL you can use when working with Server-Level Roles in SQL Server 2012....(more)

Blogs : Software Development

Scaling a standard Azure website to 380k queries per minute of 163M records with loader.io - People don’t appreciate just how far modern web servers can scale. Of course this is also predicated on there being well-designed apps, but particularly a modern incarnation of IIS running on Azure can scale a hell of a long way and that’s what I’m going to show you here today....(more)

Blogs : SQL Server 2014

Statistics and Natively Compiled Procedures - Statistics are one of the single most important driving factors for the behavior of the query optimizer. The cardinality estimates stored within the statistics drive costing and costing drives the decision making of the optimizer. So, how does this work with the new SQL Server 2014 natively compiled procedures? Differently....(more)

SQL 2014 CARDINALITY ESTIMATOR: WHY YOU SHOULD CARE - SQL Server 2014 comes with a lot of shiny things. Hekaton (or as Microsoft Marketing likes to call it, In-Memory OLTP), updateable Columnstore indexes, and buffer pool extensions are some of the more common enhancements. All of those new features are there to improve performance. Yet there is one thing even more important than all other new things combined: the new Cardinality Estimator (CE)....(more)

Blogs : T-SQL

Stored Procedure Parameter Validation - Using RAISERROR to return error code 18 and terminate the stored procedure, if the input parameters fail validation....(more)

The SNAPSHOT Isolation Level - Concurrency problems are hard in the same way that multi-threaded programming is hard. Unless serializable isolation is used, it can be tough to code T-SQL transactions that will always function correctly when other users are making changes to the database at the same time....(more)

Blogs : Virtualization

Deploying a Non-Clustered Hyper-V Host: Enabling the Hyper-V role - How to enable the Hyper-V role, and how to build a production-ready standalone Windows Server 2012 R2 Hyper-V host....(more)


Administrative