The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I was at the local municipal dump the other day, throwing out all my rubbish. I like to keep my rubbish until all chance of it proving to be valuable has vanished. Sadly, that Hitachi laptop from the late nineties had to go, though that Cromemco from the late seventies remained secure from the crusher, for sentimental reasons.  As I stood back to hurl the laptop into the municipal skip, with a muttered farewell to an old friend, the supervisor took it firmly from me and placed it reverentially in a portakabin with a lot of other IT equipment.

I wondered why. ‘Does this go for recycling in China to extract the gold?’ I asked

‘not worth it, mate, but the hard drives fetch a bit.’

It wasn’t until I was half way home that it hit me. There are no useful metals in a disk drive. Why would anyone want hard disks from old laptops? The most valuable thing would be the data. Somehow, one keeps passwords, browser history, personal accounts and all sorts of clues as to one’s identity, possibly even confidential information from work.  So this is what we call ‘at-rest data leakage’.   When old archived information is stored on a PC, network, or on a backup system, and left unused in storage, then it can be retrieved easily because it is out of sight, and out of mind, of the security experts.

It set me thinking. How efficient are we generally about ensuring that any redundant equipment with data on it can never subsequently be read after it is disposed?  It always surprises me to meet people who are unaware that SQL Server files and backups can be read by anyone unless they are encrypted. We are lulled into a false sense of security by the fact that it is hard to circumvent the security system of a live database whereas it is easy to read the data files. The front door is locked and bolted, whereas the back door is flapping open.  If you let working drives leave the building in a readable state, you’re unintentionally in the publishing business.

Phil Factor 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

HP Confirms Federal Investigation Of Autonomy’s Alleged Fraud In Its Annual Report - HP has confirmed in its annual report that the U.S. Department of Justice has launched an investigation stemming from the Palo Alto company’s allegations that it uncovered widespread accounting fraud at Autonomy, the British software maker it acquired for $11 billion last year....(more)

SQL Server News : Security

Executing SMB Relay Attacks via SQL Server using Metasploit - SMB Relay attacks can be initiated through a Microsoft SQL Server. Metasploit modules can be used to gain unauthorized access to SQL Servers during a penetration test. This has been a well known and practiced attack for at least 11 years. ...(more)

Microsoft News : General Interest

Microsoft Sticks With Metro for Next-Gen Windows - New details have emerged on an upcoming version of Microsoft's operating system code-named Windows Blue. An enthusiast at the Taiwanese online forum PCBeta claims that Windows Blue will carry on with Microsoft's minimalist and blocky Metro user interface and borrow some tricks from Windows Phone 8....(more)

Microsoft News

Microsoft Abandons Expression Web and Front End Web Development - The decision to scrap Expression Web is clearly one made by management with their heads buried deep in the river bed. For Microsoft, the web is dying and the future lies in Windows 8 apps. When asked what we web developers should be doing the answer was the same: Make Windows 8 apps. ...(more)

Blogs : Administration

SQL Server 2008 Diagnostic Information Queries (Dec 2012) - Here is the December 2012 version of my SQL Server 2008 Diagnostic Information Queries, with some minor tweaks and improvements to a couple of the existing queries. There is also one new query at the very end, adapted from Erin Stellato...(more)

Index Create Memory Setting for SQL Server - The best practice is not to change the "index create memory (KB)" setting from its default value of 0, because by default SQL Server dynamically manages the amount of memory allocated for index creation operations. If additional memory is needed for creating indexes and the memory is available based on the server memory configuration settings, the server will allocate additional memory for index creation operations....(more)

Demo: SQL Server Rolling Patch Upgrade using Standby VM - For those who read my earlier post on SQL Server Rolling Patch Upgrade using Standby VM, and are interested in trying it out, below is a step-by-step demo video for your reference....(more)

Cumulative Update #5 for SQL Server 2012 - The 5th cumulative update release for SQL Server 2012 is now available for download at the Microsoft Support site. Cumulative Update 5 contains all the SQL Server 2012 hotfixes which have been available since the initial release of SQL Server 2012...(more)

Cumulative Update #4 for SQL Server 2008 R2 Service Pack 2 - SQL Server 2008 R2 SP2 Cumulative Update 4 is released. CU#4 contains a roll-up of hotfixes released since the initial release of SQL Server 2008 R2 SP2....(more)

Cumulative Update #10 for SQL Server 2008 R2 Service Pack 1 - SQL Server 2008 R2 SP1 Cumulative Update 10 is released. CU#10 contains a roll-up of hotfixes released since the initial release of SQL Server 2008 R2 SP1....(more)

Uneven query executions with parallelism - In the customer’s situation, because the CPU was pegged and very busy, SQL Server chose to execute some of the query serially. In other words, the parallel plan didn’t get executed with multiple threads. This created ‘uneven’ times because some were indeed executed with multiple threads and others were serially executed....(more)

Disk and File Layout for SQL Server - The RAID group is dead – long live the storage pool! Pools fulfill the real promise of centralized storage – the elimination of storage silos. Prior to pool technology, when you deployed centralized storage you simply moved the storage silo from the host to within the array. You gained some efficiencies, but it wasn’t complete. ...(more)

Blogs : Analysis Services / BI

Mapping Data: 2012's Year in Maps - The intersection of geography and data, though, is just beginning to fill out. Together with interactive functions like sliders, timelines, and embedded information, the best new maps resemble Rand McNally's about as much as movies look like photographs....(more)

Blogs : Computing in the Cloud

Christmas Eve AWS outage Stings Netflix but not Amazon Prime - Big problems with Amazon Web Services’ Elastic Load Balancing service in its US-East data center nailed Netflix and Heroku on Christmas Eve and carried over into Christmas. Netflix competitor Amazon Prime Instant Video appeared to be unaffected. ...(more)

Netflix: The Britney Spears of Cloud Computing - Last Spring, Netflix publicly explained why its site went down and what it learned about it. Then they went on to outline what just happened AGAIN over Christmas. Exact same problem, exact same place, exact same result: outage....(more)

Blogs : DBA Tools

SCCM & SQL Server - A DBAs Worst Nightmare - SCCM has been designed to provide a relatively straightforward deployment that does not require any strong level of expertise. This is where SCCM falls down for me, as a DBA....(more)

Blogs : DMO/SMO/Powershell

PowerShell Workflows: The Basics - Windows PowerShell workflows are designed for Long-running activities, Repeatable activities, frequently executed activities, running activities in parallel across one or more machines and interruptible activities that can be stopped and re-starte...(more)

Batch renaming files with PowerShell - I like powershell and from time to time I even use it. This time I needed to rename a lot of files in some directory I had....(more)

Windows PowerShell: Essential Admin Scripts part 2 - This article focuses on two more scripts, one to check if a host is alive and, if it is not, send a notification email, and another to check disk space, delete data from a given folder if a certain threshold is met and then notify a user by email....(more)

Use PowerShell to Find the Location of a Locked-Out User - Jason Walker talks about using Windows PowerShell to find a locked-out user’s location so as to troubleshoot locked-out accounts....(more)

Blogs : NOSQL

Love your NoSQL enemies., it pisses them off - I strongly believe that with the proper database design SQL Server 2012 can provide good performance under the heavy analytical workload. However, I was really curious to understand how the analytical databases achieve their self-tuning performance magic. There is nothing better than the competitors to show us where we have weaknesses that need some strengthening. ...(more)

Blogs : Performance and Tuning

Weird query performance after “select into” a new table - Everything points to the extremely high amount of forwarded records being the culprit.To remove the forwarded records you can use this query: ALTER TABLE dbo.Table2 REBUILD; Adding a column to a heap will most likely cause every row to move frequently causing a very high amount of forwarded records. ...(more)

3 Ways To Be Wise About Your Rowsize - SQL Server stores data in 8k pages. OK, so that’s not the problem, really. The problem is when you have row sizes that are slightly more than 4k in length, but far less than 8k. That’s the wasted space,...(more)

Partitioning in SQL Server - Part 2 - When a table grows large or huge in size then loading new data, removing old data, maintaining indexes and queries becomes a slow process. In this situation, we can partition the table, which essentially breaks the table and its indexes into logically smaller chunks called partitions. ...(more)

Blogs : Replication

SQL Replication demystified - The things being replicated are articles. A publication (the responsibility of a publisher) is a collection of these articles. At the other end of the process are people with subscriptions. It’s just like when my son got a magazine subscription last Christmas. Every month, the latest set of articles got delivered to our house....(more)

Blogs : Security and Auditing

2012's worst security exploits, fails and blunders - I'm not trying to scare you. Rather, I'm interested in opening your eyes to the types of precautions that are necessary in the digital age—as evidenced by the biggest security exploits, blunders, and fails of 2012. 'Twas a banner year for the bad guys....(more)

Securing SQL Server 2012 Integration Services Packages using Digital Certificates - There are several different ways of restricting access to SSIS packages and confidential informationthey contain. However, preventing unauthorized users from running or viewing the content of your code is not the only security measure that you should take into consideration....(more)

Popular Disk Encryption Systems cracked - When Elcomsoft say they can recover keys from hibernation files they are referring to unencrypted disks where an encrypted file container has been opened and then the computer is hibernated. This means that the decryption key that was in memory is now written to an unencrypted disk, and is therefore recoverable....(more)

Blogs : T-SQL

Obfuscated T-SQL Christmas and New Year Cards - For the past two years I have taken a stab at an obfuscated T-SQL code holiday greeting card. The code displays poorly on mobile devices and some browsers, so I’ve posted images here (the source, posted again below, is here). Here is this year’s card:...(more)

Calculate aggregates for dynamic columns using UNPIVOT - Sometimes an aggregate calculation is needed on dynamically defined columns. This can be done in several ways This tip demonstrates how the task can be accomplished by using UNPIVOT with dynamically defined set of columns. ...(more)