The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Windows Server 2012 is due for release in a fortnight, as I write this. One of the more interesting features is the new ReFS (Resilient File System). The aim is that ReFS will eventually replace the venerable nineteen-year-old NTFS, though all previous attempts to shunt it aside have failed, the first attempt being WinFS, which was based on relational technology and which never made it into production.

ReFS should be good news because, along with 'Storage Spaces', it enables users to convert "Just a Bunch of Drives" (JBOD) storage into a resilient system that supports mirroring and striping. With this technology, we can create logical drives, or 'storage pools' to be assembled from a number of physical drives so that we can dynamically shrink and expand our storage pools whilst keeping the data intact: what a difference that would make! ReFS runs about as fast as NTFS, but we can save huge files on it and it has a Data Verification and Auto-Correction that scans and repairs bad disk clusters. ReFS, is basically, more resilient in the face of hardware failure, and more flexible than NTFS.

However, although ReFS now appears in Server 2012, it still doesn't yet have the full set of features of NTFS. In Agile parlance, it is "done", but not "done done". For example, you can't boot from it. ReFS has the features required for file storage but not yet for process execution. What does this mean for SQL Server? I'm not sure. Although there are reports of ReFS supporting SQL Server, other accounts have it that It doesn't support SQL Server, and it would seem that you have to use, instead, an old-fashioned NTFS drive for databases files. It is curiously difficult to find any definitive statement from Microsoft to say that SQL Server is fully compatible with ReFS: but there are doubts.

The great Denny Cherry, for example, pointed out in January that the absence of the named streams feature of NTFS would mean that if you run DBCC CHECKDB on a database that resides on ReFS, it would have to lock all the tables. To make matters worse, database snapshots require the use of sparse files that won't work on ReFS volumes. Are these problems now fixed?

The decision to put ReFS into Windows Server 2012 seems odd. If the lack of feature-parity with NTFS means that it can be used only for storing files rather than running applications, then this must surely make for support issues when Sys Admins assume that this is something that any file system just does. Worse, it seems that the less sophisticated NoSQL databases would seem to run fine on ReFS, thereby providing a resilience and elasticity to these whilst perhaps leaving SQL Server on aging NTFS.

Please, someone, reassure me that, at the end of a day excitedly tearing the wrapping off my shiny new Windows Server OS, I'll be able to install SQL Server satisfactorily on it under ReFS and everything will work fine!

Phil Factor.

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 : Security

Attack against Microsoft scheme puts hundreds of crypto apps at risk - Cloud-based service requires an average of 12 hours to decrypt VPN traffic....(more)

Dropbox confirms it got hacked, will offer two-factor authentication - A couple of weeks ago Dropbox hired some "outside experts" to investigate why a bunch of users were getting spam at e-mail addresses used only for Dropbox storage accounts. The results of the investigation are in, and it turns out a Dropbox employee’s account was hacked, allowing access to user e-mail addresses....(more)

Lessons in website security anti-patterns by Tesco - Troy Hunt on the many simple security errors Tesco have delivered and how we would approach this differently when applying basic security principles....(more)

Twitter suffers malware spam outbreak - Links promising photos of users lead to the Blackhole exploit kit...The malicious tweets often read "It's you on photo?" or "It's about you?" The tweets and URLs often include a user's Twitter handle. Many of the links Sophos discovered have a .RU domain name....(more)

SQL Server News

SQL Server 2008 R2 Service Pack 2 Cumulative Update #1 is available - Microsoft has released a cumulative update for SQL Server 2008 R2 SP2, which was only released last week....(more)

Microsoft News : General Interest

Windows Server 2012 set for September 4 launch - Microsoft’s Windows Server 2012 has been released to manufacturing. The final code is complete and Microsoft’s hardware and software vendor partners should get it by the end of this week. Furthermore, the date for general availability for evaluation and purchase by all customers around the world has been set for September 4....(more)

Microsoft dumps Metro name from Windows 8 - Microsoft has dropped "Metro", the name given to the user interface for Windows 8 and Windows Phone, claiming it was just a code name all along. Litigation, though, may be the real reason as it seems the word may be owned by a European company or individual that objected to its use....(more)

Microsoft ditches ‘Metro’ - now using ‘Windows 8-style UI’ - From now on, the new terminology that Microsoft is using is “Windows 8-style UI” when talking about Windows 8 applications, and “New User Interface” when talking about the company’s full product line-up....(more)

Microsoft introduces Outlook.com, Hotmail's planned successor - Microsoft is billing Outlook.com as a new free mail service that pulls features from Hotmail and Exchange. It is integrated with Windows and Office, and can pull in Twitter, Facebook, Gmail and LinkedIn contacts. The new mail client has the Metro look and feel. And it is providing users with more granular control over which ads they see and where they see them....(more)

Platform Delivered! Windows and Visual Studio RTM - Visual Studio 2012, Windows 8 and Windows Server 2012 have reached the Release to Manufacturing (RTM) phase and will soon be available for download. The new platform supports application development, deployment and management on a variety of devices and screen sizes. ...(more)

Windows 8 Released To Manufacturing - Right on schedule, Microsoft has announced that Windows 8 has been released to manufacturing on the first day of the first week of August....(more)

Microsoft's radical new business plan is hidden in plain sight - Microsoft is reimagining its entire business model, and they’ve laid out the details for anyone to inspect. You just have to read between the boilerplate sections in the company's most recent 10-K....(more)

Blogs : Administration

Function - msdb.dbo.agent_datetime - For those that ever went through the pain of interpretting the run_date and run_time columns stored in msdb's sysjobhistory table, this function is a godsend....(more)

Startup Jobs - Are there things you need to make sure are running when your SQL Agent starts? Did you know that you can run a job when the Agent starts?...(more)

How to shrink the SQL Server log - The problem is that even after you discover about DBCC SHRINKFILE and attempt to reduce the log size, the command seems not to work at all and leaves the log at the same size as before. What is happening?...(more)

SQL Server Quickies – a new way to learn about SQL Server - The goal of my new SQL Server Quickies video series is that you can learn about specific advanced SQL Server topics within 5 - 10 minutes by listening to me working hard on the flipchart explaining various SQL Server topics. And by the end we are digging into SQL Server Management Studio exploring the illustrated concepts with a practical example. Klaus Aschenbrenner starts with Data Pages in SQL Server....(more)

An in-depth look at Ghost Records in SQL Server - Ghost records are something that are a bit of an enigma for most folks working with SQL Server, and not just because of the name. Today, I’ll seek to explain the concept, as well as identify some troubleshooting techniques....(more)

Book Review: Troubleshooting SQL Server – A Guide for the Accidental DBA - My latest SQL Server read was Troubleshooting SQL Server – A Guide for the Accidental DBA, by Jonathan Kehayias and Ted Krueger. This book is designed to teach people not familiar with the intricacies of SQL Server day-to-day troubleshooting steps and techniques....(more)

Blogs : Analysis Services / BI

Color Expressions In SSAS - Color expressions in SSAS allow you to build an MDX expression to control the color of text displayed in a calculation. ...(more)

Blogs : Backup and Recovery

Rules-Driven Maintenance - Anybody who manages small to mid-size databases has rules about maintenance, and they are always very similar, but we have to go around and set up many, maybe hundreds, of jobs and scripts to implement those rules. Editing the jobs when the rules, or perhaps the deployed databases, change is really tedious. Merrill Aldrich describes a system to help manage this with less manual effort....(more)

Is the recovery interval guaranteed? - "I’ve read the SQL Server guarantees that the databases can always be brought online after a crash within one minute. Can you confirm this?" Paul and Kim kick this myth into touch....(more)

Blogs : Computing in the Cloud

Fast Storage in AWS - One of the biggest complaints about Amazon Web Services has been disk performance. In order to get fast performance it has been necessary to use RAID 0 and spread writes across multiple disks. There’s a problem – if one of those disks is slow, the whole RAID group performs poorly. If you lose a single disk in that RAID group, you’ve lost the entire logical volume. More than one team has balked at the idea of risking instability for performance....(more)

Control access at the database level using the improved Windows Azure SQL Database firewall rules - Introducing database-level firewall rules in Windows Azure SQL Database! In addition to the server-level firewall rules, you can now define firewall rules for each database in your SQL Database server to restrict access to selective clients....(more)

Blogs : Deep Into Windows

Do waiting or suspended tasks tie up a worker thread? - When we have tasks that are waiting on a resource (whether it be a timer or a resource like a lock) we are tying up a worker thread. A worker thread is assigned to a task for the duration of that task. For most queries, this means for the duration of the user’s request or query....(more)

If a worker thread doesn’t yield, is it guaranteed to run? - Put another way, can Windows preempt our worker thread and perform a context switch even though it has been “scheduled” by the SQL OS (SOS) and SQL thinks it is running? Of course it can. ...(more)

Blogs : DMO/SMO/Powershell

Using PowerShell to Aid in Security Forensics - Here’s a hypothetical thought about how Windows PowerShell can help in forensics registry analysis. The layout: You are a systems admin for a large IT corporation. You learn that a spreadsheet containing highly sensitive information was accessed without permission by a server in your group the previous day. Your task? Verify who opened it with one condition: you can’t use any non-Microsoft tools....(more)

Blogs : FILESTREAM Data

Using filestream, streaming I/O and SQL logins with impersonation - A number of months ago, I wrote an article using the SQL Server filestream feature programmatically. I've gotten a number of inquiries since from folks who *need* to login to SQL Server using SQL logins (often the same SQL login for everyone), but want to access the stream using streaming I/O (e.g. SqlFileStream data type). This doesn't ordinarily work, but how about using impersonation in SQL Server? ...(more)

Blogs : High Availability/Disaster Recovery

High-Availability White Papers and Resources for SQL Server - This is a 'so-last-version' whitepaper describing five common high-availability and disaster-recovery architectures deployed by customers, along with a case study of each. Although the white paper is specific to SQL Server 2008 R2 and isn't updated for AlwaysOn features, it's still really, really good. ...(more)

Blogs : NOSQL

Webtrends, Loggly explain Hadoop’s shortcomings - Are the downsides of batch mode the elephant in the room for Hadoop? Two data-focused companies, with different products and models, make clear why real-time analytics force Hadoop to take a back seat....(more)

Blogs : Performance and Tuning

Database optimization techniques you can actually use - A starter guide to optimizing MySQL database performance....(more)

Blogs : Security and Auditing

Data Privacy in SQL Server based on Hippocratic Database Principles - Ensuring privacy in modern information systems is of primary importance for the users of these environments. Use and trust of users certainly depends on the degree of privacy. The solution for the above mentioned problems can be found in application of the “Hippocratic Databases – HDB concept". The idea is inspired by the basic principles of Hippocratic Oath to be applied on the databases in order to provide data privacy and confidentiality...(more)

Creating a DDL Trigger - One of the most amazing features to an old SQL Server 4.2 guy was the addition of DDL triggers to the server. As with any trigger, these can be problematic in that they can overload a server, and they ALWAYS fire, so you can cause yourself problems, but in terms of auditing, I think they’re great....(more)

Blogs : SQL Server 2012

Resource Governor in SQL Server 2012 technical article published - The article focuses on the SQL Server 2012 enhancements to the Resource Governor which reflect a growing need for centrally managed database services to support isolated workloads in multitenant environments....(more)

Blogs : T-SQL

Loading data with foreign keys - Working on a customer project the other day, I needed to load some data into a database that had been scripted and re-created. The newly created database already had foreign keys in place which means for loading data, we had two options: 1) drop the foreign key constraints, load the data, re-apply the constraints 2) load the data in the correct order so that parent tables are loaded first, followed by children, followed by grandchildren, etc…I decided to give option 2 a try. ...(more)

Execution Plans … In the Cloud! - Grant Fritchey investigates some of the differences you'll see when examining execution plans for your Azure databases, through the Management Portal, versus SSMS....(more)

Identifying and Correcting SQL Server Implicit Conversion - When you build a database in SQL Server, you need to pick a data type for each field in each table. Hopefully, a great database designer took the time to carefully understand business needs and what the field would be used for, and assigned the correct data type. When that doesn’t happen, you may run into problems down the road....(more)

The Seven Sins against TSQL Performance - There are seven common antipatterns in TSQL coding that make code perform badly, and three good habits which will generally ensure that your code runs fast. If you learn nothing else from this list of great advice from Grant, just keep in mind that you should 'write for the optimizer'....(more)

How To Prevent SELECT * The Evil Way - SELECT * FROM…when administrators see that from developer code, we generally tend to cringe. Why? In a nutshell, it’s terrible on a few fronts. Jorge Segarra presents an extremely evil way (and a few less evil ways) of preventing such a query....(more)

Blogs : Virtualization

Hypervisor 101: Understanding the Virtualization Market - As acquisitions and attrition have taken their tool, the top three vendors in the hypervisor space have emerged. Prior to taking a look at those competitors, it’s important to have a clear understanding of what a hypervisor really is and what it does....(more)