The Complete Weekly Roundup of SQL Server News

In this issue:

Tech News : General Interest

Tech News : Security

SQL Server News : Bugs/KB

Product Reviews

Microsoft News : Patches, Bugs

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Computing in the Cloud

Blogs : DMO/SMO/Powershell

Blogs : Excel

Blogs : Performance and Tuning

Blogs : PowerPivot

Blogs : Replication

Blogs : Reporting Services

Blogs : SQL Server 2014

Blogs : T-SQL

Blogs : Virtualization

Blogs : XML, XPATH and XQUERY

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-05-05

sqldbabundle 5 Worst Days in a DBA’s Life – Part 2
The DBA Team return in The Girl with the Backup Tattoo. When a crazy DBA tampers with his own backups, even the DBA Team need to call on some extra help to fix the damage. Will they succeed? Read the new article now.
SQL Compare Need to compare and sync database schemas?
Let SQL Compare do the hard work. ”With the productivity I'll get out of this tool, it's like buying time.” Robert Sondles. Download a free trial.
Ricky Leeks on .NET Memory Management Ricky Leeks on Learning .NET Memory Management
Pick up all six free articles in one free download. Find out what Ricky has to teach you about garbage collection, memory management gotchas, and more. Download the article pack free.
Editorial - Lots of Security Concerns

This past week I found more security headlines appearing that bothered me than I have most other weeks. I think I still have a little hangover from the Heartbleed bug that was reported last month. The only good thing is that researchers are using the bug to look for the bad guys. A reminder that trying to exploit this for fun is probably not a good idea.

Security certainly has changed, and rapidly, and it seems as though we are going backwards. In addition to criminals and hackers using bugs to attack us, we potentially have law enforcement, or even border guards that may look through our devices. I certainly hope this is struck down, though even if it isn't legal, that might not help you when you cross a border or are stopped in front of an office. It makes me think we more and more need a way to wipe our devices quickly and easily, but then reload them from backups. I'd love to see someone provide a way to do this that might not allow immediate restores in sticky situations. Maybe the iTunes, "connect to a computer" isn't such a bad idea.

Cloud computing security also had a bit of a setback when a judge ordered Microsoft to comply with a subpoena. A US judge is asking a US company to comply with US law about data stored overseas. This is disconcerting, though to be clear, this is a potential discovery action from US plaintiffs and defendents. It is disconcerting, however, for companies that are looking to some sort of cloud service. I'm glad to see Microsoft isn't just letting this go and is looking to appeal this and seek higher courts that can make rulings about digital information and having any handling of this data be consistent with analog data.

This is even more disconcerting as a survey about encryption use in the cloud found many people expecting the cloud provider to manage encryption. No, no, no, no, this isn't what we want. Companies need to manage and secure their own data, including their own encryption keys.

Perhaps it doesn't matter if you're in the cloud or not. After all, most employees think their employers do a poor job of securing their own systems. Could the cloud be that much worse?

» 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.

Tech News : General Interest

Cortana and Schema.org - Some definitions: Cortana – Microsoft’s digital personal assistant, competing with Apple’s Siri and Google Now. “She gets to know you by learning your interests over time. She looks out for you, providing proactive, useful recommendations. And Cortana ......(more)

The Internet of Things: enabling the era of precision manufacturing - Remember pictures from old factories where a low-level employee roamed the factory floor on a regular schedule, writing down on a clipboard readings from gauges and other instruments? Who knows when his supervisor ever looked at the results, and what ......(more)

The Case for Moving From TPC to Database Throughput Units in Database Performance Comparisons - Scientific testing is based on controls, transparency, and repeatability. Whenever we as technical professionals want to test the performance of a database system, we search for a series of tests that show the system’s metrics against a standard. But ......(more)

Obama panel supports warrant requirement for e-mail, cloud content - Jeff DeMaria/Flickr On Thursday, a panel commissioned by President Barack Obama to examine the implications of "big data" concluded that cloud and e-mail content should be constitutionally protected—a recommendation that Congress is seemingly unwilling ......(more)

Tech News : Security

It’s more Pluralsight, it’s more website attacks and it’s more security - How much really changes in only three short years in the world of application security? Ok, a few sites get owned and some nasty hackers come up with some new ways of making some poor developers lives a misery but that’s about the extent of it, right? ......(more)

Data Theft a Major Concern for Organizations - Many businesses believe cyber security threats fall through the cracks of their companies’ existing security systems, a survey finds. ...(more)

Judge: Microsoft Must Obey US Warrant Seeking Data Stored in Ireland - US federal judge strikes down Microsoft’s challenge to warrant for customer’s data stored overseas Read More ...(more)

Stanford’s password policy shuns one-size-fits-all security - Stanford University network engineers have unveiled a refreshingly enlightened password policy. By allowing extremely long passcodes and relaxing character complexity requirements as length increases, the new standards may make it easier to choose passwords ......(more)

Supreme Court to rule on warrantless searches of electronic devices - Elvert Barnes/Flickr The Supreme Court on Tuesday will take on the digital-age controversy over search and seizure of smartphones and other devices. In two cases coming before the court, warrantless searches of an electronic device not only provided ......(more)

Google Chrome protection for Heartbleed-hacked sites called “completely broken” - Aurich Lawson Update: A few hours after this article went live, Google engineer Adam Langley published a blog post taking issue with the GRC characterization that Chrome's CRLSet is "completely broken." In the post, Langley said he has always been ......(more)

SQL Server News : Bugs/KB

SQL 2012 and SQL 2014 NUMA Fix - SQL 2012 and SQL 2014 NUMA Fix Typically a SQL Server Cumulative Update release is not a noteworthy event, however SQL Server 2012 SP1 CU9 and SQL Server 2014 CU1 include a fix that is noteworthy from a from a CAT Lab perspective.  In short, if you have ......(more)

Product Reviews

Introducing Microsoft SQL Server 2014 Book Review - Microsoft’s newly released SQL Server 2014 database is closely followed by its first book, how does it fare?...(more)

Microsoft News : Patches, Bugs

Emergency patch for critical IE 0-day throws lifeline to XP laggards, too - Brandon Weeks Microsoft has released an emergency update for all recent Windows operating systems—including the recently decommissioned XP—fixing a critical security bug that is currently being exploited in real-world attacks. The decision to patch ......(more)

Blogs : Administration

What is a Non-Clustered Columnstore Index? - First introduced in SQL Server 2012, the Columnstore index is a new in-memory feature that allows for the creation of indexes that are stored in a column-wise fashion. It is targeted for data warehouses and can improve query performance by 10 to 100x. ...(more)

SQL SERVER – Good Value for Page Life Expectancy – Notes from the Field #026 - [Notes from Pinal]: In the past, I have been wrong many times, but I was man enough to accept my mistakes and correct myself. Page Life Expectancy is a very similar subject for me. In the past when I had written a blog post based on Microsoft’s white ......(more)

Shrinking tempdb no longer prohibited - For the longest time the guidance around tempdb is that if you shrink it on a live system then it could cause tempdb corruption. A few months ago I was discussing this with my good friend Bob Ward from Product Support and neither of us could remember ......(more)

Blogs : Analysis Services / BI

DAX #2 – Installing AdventureWorks DW Tabular Model SQL Server 2012 - OK, first thing first. We all know the famous Adventure Works multidimensional sample project. To learn the tubular mode/DAX, we also have a sample tabular project. Download sample database and projects from CodePlex You can follow this link to CodePlex ......(more)

Blogs : Computing in the Cloud

Encryption in the cloud is scarcer than you think - The Ponemon Institute, working in conjunction with encryption solutions provider Thales e-Security, has produced its third annual rep ...(more)

Azure SQL Database: New Service Tiers Q&A - Earlier this month, we celebrated the launch of Microsoft SQL Server 2014, announced that the Analytics Platform System is generally available, and shared a preview of the Intelligent Systems Service. Quentin Clark summarized his keynote speech at the ......(more)

Blogs : DMO/SMO/Powershell

Powershell Goodies - If you haven’t seen some of the other blog post, or the Microsoft Powershell Blog, Microsoft recently release Script Browser for Powershell ISE.   This little addin allows you to quickly search and import Powershell scripts directly from TechNet Script ......(more)

Blogs : Excel

Power Query to HDFS – Remote Name Could Not Be Resolved - I wanted to share a quick resolution I had to a problem with connecting Power Query to data stored in HDFS. Seems easy enough. Launch Excel and through Power Query select From Other Sources –> From Hadoop Files (HDFS) Next you’re prompted to provide ......(more)

Blogs : Performance and Tuning

How Automatic Updates to Statistics Can Affect Query Performance - In my previous post, I explored different methods to track automatic updates to statistics to determine if they were affecting query performance. In the latter half of the post I included options, one of which was to enable the Auto Update Statistics ......(more)

The Performance of Traversing a SQL Hierarchy - Dwain Camps show that, depending on the size and characteristics of some hierarchical data, six different methods of traversal can each be the fastest at some point. He illustrates convincingly that It is dangerous to generalize from just one set of ......(more)

How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT) - I ran into this behavior working on a 1 trillion row, spatial index build but the behavior can apply to any Gather Streams operator, retaining the sort order as rows pass though it.   I was just surprised a bit by the behavior until I dug deeper to understand. ...(more)

Introduction to Extended Events (video) - Extended Events were introduced with SQL Server 2008. Extended Events is a replacement for Server Side Traces and so much more – it’s a lightweight way to look at SQL Server performance, events, deadlocks, locking, blocking, and more. In this webcast, ......(more)

Blogs : PowerPivot

How to handle fact tables with different granularities in #dax #powerpivot #tabular - A common question I receive from Excel users learning Power Pivot is how to handle table that have different granularities. In reality, this terminology is not the one they use: the concept of “table granularity” is used mostly by Kimball practitioners, ......(more)

Blogs : Replication

Why Can't I Reinitialize My Subscriber? - I recently had a colleague request help with a replication problem he was having with a client.  The client was running transactional replication from a SQL Server 2005 Publisher to multiple SQL Server 2005 Subscribers and was having issues after attempting ......(more)

Blogs : Reporting Services

SharePoint Adventures : Using Claims with Reporting Services - Back in February of 2011, I created a blog that walked through using Kerberos with Reporting Services. Since then, we have moved Reporting Services to a shared service within SharePoint.  This changes the game and we are now in the Claims world.  I’ve ......(more)

Blogs : SQL Server 2014

SQL Server 2014 Managed Lock Priority for Partition Switch and Online Reindex - Online reindex and partition switch have both been around for a while now, however one of the biggest issues which still exists is they both require a schema modification lock which is very unfriendly with other locks.     From the lock compatibility ......(more)

SQL Server 2014 Updatable Columnstore Index - I have had many calls with customers that essentially ended with your I/O subsystem cannot keep up with your workload.  The obvious next question was how do we solve that?  My answer was usually get a faster IO subsystem or do less IO!  Now, SQL 2014 ......(more)

The Cardinality Estimator in SQL Server 2014 – Going Forward and Backward - I saw a talk from Joe Sack (b | t) on the cardinality estimator (CE) in SQL Server 2014 and found it very interesting. To be fair, some of the "how it works" isn’t something I care about much, but I did like Joe sharing some places in which you might ......(more)

Delayed Durability in SQL Server 2014 - Delayed Durability is a late-breaking but interesting feature in SQL Server 2014; the high-level elevator pitch of the feature is, quite simply: "Trade durability for performance." Some background first. By default, SQL Server uses a write-ahead log ......(more)

SQLAuthority News – Two Microsoft Whitepaper on In-Memory OLTP - Earlier this week, I was presenting at Great Indian Developer Summit in Bangalore. Lots of people were interested in the subject of In-Memory OLTP database. Everybody wanted to know how to do, use the new features of In-Memory OLTP feature. Microsoft ......(more)

Memory and IO in Tempdb: SQL 2014 Improvement - I was excited to read Bob Dorr’s recent post about a hidden gem in tempdb performance in SQL Server 2014. I had a copy of SQL Server 2014 Developer Edition handy, as well as a workload that beat up tempdb, so I decided to do a quick performance test. I ......(more)

Blogs : T-SQL

Video: Designing Stored Procedure Solutions - My new session for 2014, “Designing Stored Procedure Solutions”, is now up on the Sessions page: abstract, video, and code! This month, I gave this session at the Oklahoma City SQL Server User Group‘s 5 year anniversary, and at the Alaska SQL User Group, ......(more)

Find SQL Database Owner with T-SQL - One thing that on occasion bugs me about PDW or APS or whatever it happens to be called this week is the lack of a UI to do things. I’m not one of those people who has to have a UI to perform operations. I have no problem creating databases, doing backups ......(more)

DBA Myths: TRUNCATE TABLE deletes all the rows of a table instantly. - Most DBAs and developers I’ve talked to over the years have felt that TRUNCATE TABLE is an instant DELETE. However would you believe that it doesn’t actually delete the data from the pages at all? Here is an example: -- Setup code CREATE DATABASE TruncateTest GO USE ......(more)

Stupid T-SQL Tricks – Part 1: Logarithms - Not just for math geeks, logarithms and their inverse functions (the exponentials) can have useful and often unexpected capabilities in T-SQL.     “Heeeeeere’s Johnny!” – Ed McMahon introducing Johnny Carson for the Tonight Show If you’re old enough ......(more)

Blogs : Virtualization

Getting the most out of Hyper-V Dynamic Memory - Microsoft introduced Dynamic Memory in Windows Server 2008 R2 (W2008 R2) Service Pack 1 (SP1) to change how memory is assigned to virtual machines. Instead of assigning all of a virtual machine’s potential RAM to the virtual machine, you could opt to ......(more)

Blogs : XML, XPATH and XQUERY

How to grab multiple parent/child elements from XML Data Source - We had a question come up about why an XML query wasn’t pulling a 2nd Parent/Child element from the resulting data.  This was grabbing data off of a Web Service.  The example I was given was a public Web Service call for getting Weather information. ......(more)


Administrative