The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Two words that no database administrator ever wants to put together are "data" and loss". We go to great efforts to ensure that our SQL Server data is protected, backed up, available on alternate systems, even replicated to remote machines. Our goal is always to have zero data loss in all situations, even in those situations where we cannot prevent downtime from occurring.

This past week an article at InfoWorld caught me eye with the phrase "solid-state drives suffer data loss" in the subtitle. The article is written about a study from HP Labs and Ohio State University that studied the effects of power loss on various SSDs. Using a number of "enterprise quality" SSDs from various manufacturers, the authors of the study cut power to the drives while they were in use, a scenario that I'd expect would happen periodically to systems. We reboot all kinds of systems at times without shutting them down at times, for a variety of reasons. I'd expect that file systems and low level software would properly handle these situations in a manner similar to SQL Server, with some sort of recovery running when power was reapplied.

In the study, there were six potential failure types, and of the fifteen manufacturers whose drives were used, five of these failures occurred. The susceptibility of various kinds of corruption to power faults led the authors to conclude that systems with critical data should not use SSDs, test them thoroughly since they were not aware of ways to design a storage system to account for these potential issues. They did not say that SSDs were more unreliable than hard drives, but in this specific type of disaster, unexpected power loss, there could be problems.

We can design around these issues somewhat with redundant UPSes, battery backing of SSD cards and more, but disasters in the form of an accident can occur. I've seen two occasions where large data centers lost power because of maintenance work on power distribution units resulted in improper fail overs. The more complex our system design becomes, and the more we use SSDs in databases, the more likely for some failure or corruption to occur.

I wouldn't panic and remove SSDs from systems, but I would ensure that any disaster recovery processes and procedures I had were up to date and working. You never know when you will need to recover from hardware issues on a primary system and have to restore on completely separate hardware. Advance preparations and practice are the only way to ensure you can successfully recover when the need arises.

Steve Jones

Steve Jones 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 : Data Mining/Warehousing

New vision in old industry - Nathan Oostendorp thought he’d chosen a good name for his new startup: “Ingenuitas,” derived from Latin meaning “freely born” — appropriate, he thought, for a company that would be built on his own commitment to open-source software....(more)

Tech News : Disaster Recovery

Test your SSDs or risk massive data loss, researchers warn - Companies adopting flash-based SSDs as a cornerstone to the data center storage systems are risking "massive data loss" due to power outages,...(more)

Tech News : General Interest

Microsoft Azure overtakes Amazon's cloud in performance test - Microsoft Azure's cloud outperformed Amazon Web Services in a series of rigorous tests conducted by Nasuni, a storage vendor that annually benchmarks CSPs (cloud service providers)....(more)

CEO Mayer calls in all Yahoo telecommuters - Yahoo CEO Marissa Mayer is reportedly tossing conventional business thought aside and calling in telecommuters. Mayer, who became CEO last year, has been working to turn around the struggling company. Now she wants to get employees working more efficiently and creatively by forcing them to work in the office, instead of at home....(more)

Intel releases Hadoop distribution primed for its own chips - Intel has released its own Hadoop distribution in a move intended to accelerate adoption of the big data platform while ensuring more of those workloads run on Intel's own Xeon processors. The Intel Distribution for Apache Hadoop includes core pieces of the data analysis platform that Intel is releasing as open-source software, as well as deployment and tuning tools that Intel developed itself and which are not open source....(more)

The sequester will hurt tech nationally - The federal government's automatic budget cuts, due to begin Friday, may accelerate cost savings measures already in place via sharing of services across agencies, IT consolidation, and an increasing reliance on off-the-shelf products and cloud-based services, as opposed to customized development....(more)

Google open-sources Zopfli compression algorithm to speed up Web downloads - Google is open-sourcing a new general purpose data compression library called Zopfli that can be used to speed up Web downloads. The Zopfli Compression Algorithm, which got its name from a Swiss bread recipe, is an implementation of the Deflate compression algorithm that creates a smaller output size compared to previous techniques, wrote Lode Vandevenne, a software engineer with Google's Compression Team, on the Google Open Source Blog on Thursday....(more)

Tech News : Security

FBI employees, entrusted with stopping computer crimes, commit them too - Though FBI agents are held to a high standard of conduct, some fall short—far short. Take, for instance, an incident in 2007 when an FBI employee "drove past a felony traffic stop, yelled 'Rodney King' out his car window and momentarily lost control of his vehicle, swerving into the oncoming lane and almost striking a police officer," according an account of an internal FBI investigation. (When cops pulled him over, the employee claimed he had yelled, "Geeze Louise.")...(more)

Exploit lets websites bombard visitors’ PCs with gigabytes of data - A Web developer has demonstrated a simple-to-execute exploit that allows websites to surreptitiously bombard visitors' storage devices with gigabytes of junk data....(more)

9 top threats to cloud computing security - Cloud computing has grabbed the spotlight at this year's RSA Conference 2013 in San Francisco, with vendors aplenty hawking products and services that equip IT with controls to bring order to cloud chaos....(more)

Online Bad Guys Have Sneaky New Weapon - Security researchers are keeping a wary eye on malicious programs circulating on the Internet designed to carry out invasive tasks in a computer's random access memory, then disappear without a trace. "We are seeing very sophisticated code that resides in the RAM of a computer that traditional (defensive) software has no chance of detecting," says Bob Gourley, chief tech officer of tech consultancy CTOvision.com....(more)

Press Releases

Red Gate SQL Source Control and Plastic SCM - We are happy to announce that it´s possible to work with Red Gate Source Control and Plastic SCM as its source control system. Red Gate Source Control is a tool that makes source controlling/deploying your database easier. In order to make this integration real we have developed a small XML template that allows the execution of Plastic SCM. You can download the template here (Plastic SCM template). The XML file must be manually added to the following folder: C:\Users\xxx\AppData\Local\Red Gate\SQL Source Control 3\CommandLineHooks, where “xxx” is your user. We are in contact with the Red Gate guys, so hopefully Plastic will be included as a default option inside “Red Gate SourceControl” soon....(more)

Microsoft News : General Interest

Step inside the Microsoft Envisioning Center - Back in the early days of Microsoft, our mission was to put a PC on every desk and in every home — an ambitious goal for the time. Behind that was the conviction that computer technology had the innate capacity to enrich peoples’ lives. The trick was applying it in a way that made sense, solved a problem, or generally made life better....(more)

Internet Explorer 10 Now Available for Windows 7 - Internet Explorer 10 for Windows 7 is now available worldwide. Microsoft said on Tuesday that Internet Explorer 10 for Windows 7 is now available worldwide in 95 languages. The company will begin auto updating Windows 7 customers to the new browser in the weeks ahead, but those who have already installed the IE10 Release Preview will see the update kick in starting Wednesday....(more)

Microsoft Joins Open Data Center Alliance - Microsoft has joined The Open Data Center Alliance (ODCA), an industry group that publishes usage models for Open Specifications for Cloud Computing. The Open Data Center Alliance is comprised of more than 300 companies that represent over $100 billion in annual IT spending....(more)

Hardware News

OCZ to Showcase Enterprise Storage Solutions at CeBIT 2013 - Products on display include next-generation PCIe SSDs, the ZD-XL SQL Accelerator, VXL 1.3 Virtualization Software and others. At this year's CeBIT conference taking place in Hannover, Germany, OCZ plans to unveil a "complete suite of software solutions that address VMware, Linux, and SQL Server platforms". Most notable amongst its upcoming products are a range of next-generation workstation PCIe SSDs, the ZD-XL SQL Accelerator and the VXL 1.3 Virtualization Software. ...(more)

Micron Introduces SAS Solid-State Drive - Micron's P410m SSD is designed to offer the reliability and endurance demanded by 24/7 data centers The P140m is a 2.5" Solid State Drive that has a thickness of just 7 mm and is based on Micron's 25 nm MLC NAND technology. The drive features a Serial Attached SCSI (SAS), PCIe and SATA connectors and is able to deliver Read and Write Speeds of 410 MB/s and 235 MB/s respectively. Micron's target market is clearly the enterprise sector and the P140 has a number of features that the company hopes will convince businesses to make the transition from HDDs to SSDs....(more)

Blogs : Administration

Transaction Log Configuration Issues - In the my last two posts I discussed ways to reduce the amount of transaction log being generated and how to ensure the transaction log can always clear properly. In this post I want to continue with the transaction log performance theme and discuss some transaction log configuration issues that can cause problems....(more)

A Look At DBCC CHECKCONSTRAINTS and I/O - A common element used in database design is the constraint. Constraints come in a variety of flavors (e.g. default, unique) and enforce the integrity of the column(s) on which they exist. When implemented well, constraints are a powerful component in the design of a database because they prevent data that doesn’t meet set criteria from getting into a database. However, constraints can be violated using commands such as WITH NOCHECK and IGNORE_CONSTRAINTS. In addition, when using the REPAIR_ALLOW_DATA_LOSS option with any DBCC CHECK command to repair database corruption, constraints are not considered....(more)

Disabling vs. Dropping Indexes - In order to improve your applications and your databases, they will need to change over time. The structure of the database changes, the structure of the tables change, the data in the tables change, the application changes, the queries against the data change. Indexes that once aided performance now sit unused while new indexes are added....(more)

Custom Metric: Database growth steps available - This metric measures the number of available autogrowth steps for a database, so you can see when they are reduced or increased because something else (e.g. another database) starts or stops taking up space, when storage is added, etc. ...(more)

Blogs : Analysis Services / BI

Big Data and the Slough of Despond - “This miry Slough is such a place as cannot be mended; it is the descent whither the scum and filth that attends conviction for sin doth continually run, and therefore is it called the Slough of Despond: for still as the sinner is awakened about his lost condition, there ariseth in his soul many fears, and doubts, and discouraging apprehensions, which all of them get together, and settle in this place; and this is the reason of the badness of this ground”....(more)

O’Reilly Strata: Busting Big Data Adoption Myths with Halo 4–Part 3 - Structured or unstructured – what to store? How about all of it? I spoke at the O’Reilly Strata Conference last year and am back again this year to share thoughts about big data. It’s been a remarkable year for Microsoft’s data platform team. While we’ve had a long history in the data space, last year was noteworthy for several reasons. ...(more)

Traversing the Facebook Graph using Data Explorer - As I mentioned yesterday Microsoft Data Explorer Preview for Excel has hit the streets and one new feature is a built-in adapter for talking to the Facebook Graph API:...(more)

Counting Consistent Customers in MDX - The post I wrote a few years ago on counting new and returning customers in MDX has proved to be one of the most popular here; it’s certainly a topic I’ve returned to a number of times for DAX, and other people (most recently Gerhard Brueckl) seem equally interested in solving this problem. However I had a comment from Sohrab Niramwalla yesterday which raised an interesting question: how do you could the number of customers who have bought from you in every time period from the beginning of time until the current date?...(more)

Blogs : Career

Being a Remote Worker Sucks - Long Live the Remote Worker - I've been a 100% remote worker at Microsoft for just about 5 years now. My last two jobs were both 7 year long gigs, so this isn't the longest I've worked somewhere, but clocking in at a half-decade, it's the longest I've worked remotely. Given that I haven't yet been fired, it's fair to say that I'm a pretty good remote worker. ...(more)

Why Working Remote Is Teh Awesome - Working from home has been the worst, and the best, part of my life these past three years. Earlier this week Yahoo told all their remote workers to find a local office or relocate. The initial explanation I found was that Yahoo had allowed the remote workers to take advantage of their situation. This was a drain on everyone and therefore Yahoo had to take drastic actions. If the stories of abuse are true then I completely agree with their decision. However it seems that this was just an excuse for Yahoo to cover up another drastic action that some felt was needed: massive layoffs....(more)

Blogs : DBA Tools

Improvements in the Red Gate source code control suite - One aspect of SQL Server related development that I see done quite badly when visiting client sites is source code control. So many sites have almost nothing in place for this important aspect of development. By comparison, it's rare now that I visit an application development organisation that doesn't have at least reasonable source code control in place. Many DBAs seem to have grown up in environments where source code control systems weren't in use....(more)

Blogs : Deep Into Windows

Where Did My Space Go? - While working on Windows 8/Windows Server 2012, I have been tied up with other content creation projects and as such have not had time to write for AskCore. However, I’ve had a number of blog ideas rattling around in my head. Today I finally found some time to get one of them committed to paper...ah...Word document. One of the most involved classic cases that I’ve been called upon to provide support for is the ‘where is my hard drive space’ case....(more)

Blogs : DMO/SMO/Powershell

Finding SQL Server Installs Using Powershell - I’ve been writing tools for SQL Server for a lot of years. Some of these tools were never completely released. Some of them were just for me. Some of them overlapped other tools already on the market and free for all. Recently, I started updating my bag of tricks and tools. This seemed like a great time to get back into PowerShell. I decided to pull out a bit of C# code I cobbled together nine years ago as part of a tool to find SQL Server instances on a network. I never really got around to making it a “production” ready tool since there was already a most excellent one on the scene in the form of SQLPing from Chip Andrews. SQLPing is a fantastic scan tool and can scan many more things than the method covered here....(more)

Blogs : High Availability/Disaster Recovery

Log Shipping: Preparing for Disaster - Log shipping is a tried and true method in SQL Server for keeping a copy of your data on a secondary server. If you haven’t worked with this feature yet, join Jes to learn the basics, such as what log shipping is used for, what the moving pieces are, and how to set it up in this 30-minute video:...(more)

Blogs : Integration Services/ETL

Setup and Performance Issues with the Integration Services (SSIS) 2012 Catalog - For those who don’t know, I’m currently the Data Warehouse Architect for a large scale and complex multi-tenant data warehousing project. Fortunately, the project is being created in SQL Server 2012 and I thrilled to be using the new features. Let me preface this blog post by admitting that my experience with using the SSIS 2012 Catalog, SSISDB, is fairly limited, but then again most people are in the same boat since adoption of 2012 has been fairly slow. ...(more)

Public Preview of Data Explorer - The Public Preview of Data Explorer (which some of you know I’ve been following for a while, since it first appeared in SQL Azure Labs), is now available for download. You can get it here:http://www.microsoft.com/en-us/download/details.aspx?id=36803...(more)

Blogs : Performance and Tuning

Follow up on Ad Hoc TVP contention - Last month I posted PAGELATCH Contention on 2:1:103. It described a troubleshooting experience I had. I was troubleshooting a performance problem that was felt when applications issued very frequent ad hoc queries that used Table Valued Parameters (TVPs)....(more)

Temp table caching improvement for table valued parameters in SQL Server 2012 - I wanted to point out a nice performance improvement related to table valued parameters (TVP) in SQL Server 2012.   It’s not currently documented in our online documentation.   But we have had customers who inquired about this....(more)

Blogs : Software Development

Better Unit Testing with Microsoft Fakes guide … “shipped”! - We are pleased to announce v1 of the Better Unit Testing with Microsoft Fakes guide, under Visual Studio Test Tooling Guidance, which allows you to get familiar with the Microsoft Fakes framework, to be in a position to promote, position and use it effectively. ...(more)

To Automate or Not To Automate…Is that the Question? - We all know that test automation i.e. the ability to run a bunch of tests automatically, not manually, and then release based on the results, is a good thing, don’t we?...(more)

Automating SQL Server Database Deployments: A Worked Example - Alex talks through a simple practical example of a database deployment, First creating a empty database and then upgrading it through three steps by writing T-SQL scripts, adjusting configuration files and the change log, before generating a full build script containing all schema objects....(more)

Blogs : Spatial Data

Beginning SQL 2012 – Spatial Unions and Collections – Abstract from Joes 2 Pros Volume 3 - In business we often hear the phrase, “We had a good quarter.” Immediately, we know this means a three month span where sales and profits have been aggregated together for the company. Take a company like Costco that might have $65 billion in total sales during the 4th quarter (October, November and December). Of course, this total comes not from a single sale of a 65 billon dollar yacht, rather from millions of sales for common items like snacks, drinks, clothes, and light bulbs. We know how to use GROUP BY and SUM to calculate totals and combine similar data. In the case of Costco we group by calendar quarter and then sum on the sales. Although aggregates are commonly used with numbers, they can also be used with spatial land coordinates to assemble them together, much like a jigsaw puzzle....(more)

Blogs : SQL CLR

Unable to register .NET framework assembly not in the supported list - SQL Server has supported CLR usage since version 2005.  But support of .NET framework assemblies within SQL Server is limited per our support policy in KB http://support.microsoft.com/kb/922672....(more)

Blogs : SQL Server 2012

SQL Server 2012 Partially Contained Databases Part 2 - Collation - In my last post, I went over partial database containment and contained users. This post will focus on the other piece of functionality in partial containment… collation. The collation of data determines how data is sorted and compared. When all databases are using the same collation as the instance collation (selected during install), then there is really no need for containment of the collation. The problem arises when you are hosting multiple application databases on your instance that require a separate collation. The most typical example of this is when objects are created in tempdb. They will have the instance default collation. If the collations between the two are different then you may see an error similar to this one below:...(more)

Blogs : T-SQL

SQL SERVER – Beginning SQL 2012 – Aggregation Functions – Abstract from Joes 2 Pros Volume 2 - This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 2.  You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. ...(more)