In this issue:

Tech News : Disaster Recovery

Tech News : General Interest

Tech News : Security

SQL Server News : Development

Product Reviews

Microsoft News : Patches, Bugs

Hardware News

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Career

Blogs : Computing in the Cloud

Blogs : DMO/SMO/Powershell

Blogs : Events

Blogs : Excel

Blogs : High Availability/Disaster Recovery

Blogs : Integration Services/ETL

Blogs : Performance and Tuning

Blogs : Policy Based Management

Blogs : Security and Auditing

Blogs : Software Development

Blogs : StreamInsight

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 2012-11-19

SQL Backup "SQL Backup Pro 7 improves on an already wonderful product" - Don Kolenda
Have you tried version 7 yet? Get faster, smaller, fully verified backups. Download a free trial of SQL Backup Pro 7.
SQL Source Control Is your SQL Database under Version Control?
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.
SQL DBA Bundle Top 5 hard-earned lessons of a DBA
In part one, read about ‘The Case of the Missing Index’ and learn from the experience of The DBA Team. Read now.
Editorial - SSD Lifetimes

One of the things that has concerned many DBAs looking at SSD drives is the possible limited life of these drives. In the past, many consumer drives have had issues, and can fail without warning. However there are plenty of people that still love them for the fact that they are crazy fast and can dramatically speed up your system. Just make sure you keep good backups of your data since it may not be recoverable.

For servers, a number of manufacturers have been working hard to build more reliability and longer lifetimes into their drives. FusionIO has listed 2 million hours and 6 years write endurance for their cards, which is a quite a long time. Other manufacturers are working hard as well and I suspect we'll see more and more SSDs architected into SQL Server instances over the next few years. Microsoft has a white paper on using FusionIO cards in tempdb, and I know a few SQL Server installations have been quite pleased with their results of using these cards in production systems.

This week I saw an analysis of the Samsung SSD 840 over at AnandTech. They examined the wear of this drive and came to the conclusions that writing over 10GiB a day, you're still get many years of service from this drive. That gives me a lot of confidence in the drive, and with Amazon listing the 250GB version for $180, it's an upgrade you might consider for your laptop.

» 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 : Disaster Recovery

Hurricane Sandy Post-Mortem: Will Your Critical IT Systems Survive Another Major Power Outage? - The Hurricane Sandy event that hit the Northeast confirms that the use of diesel fuel to run backup generators, the location of the critical systems and primary data center as well as the geographic location of backup sites are significant to sustaining the ongoing critical operations...(more)

Tech News : General Interest

The Daunting Task of Defining Big-Data - The best definition of big-data I ever heard was simple and to the point: "It’s the stuff we used to call 'a whole lotta data,' " a presenter at a recent conference said....(more)

Cloud security: Outages are bigger risk than breaches - Security remains a chief inhibitor to enterprise adoption of cloud computing resources and one Gartner analyst says the biggest concern should not be that data could be compromised in the cloud, but rather that there may be a cloud outage that could lead to data loss....(more)

Tech News : Security

NASA scrambles to encrypt laptops after major breach - NASA is scrambling to implement full disk encryption on agency laptops after one containing unencrypted personal information on a "large" number of people was recently stolen. Agency employees were told of the October 31 theft of the laptop and NASA documents from a locked car in an email message Tuesday from Richard Keegan Jr., associate deputy administrator at NASA....(more)

The HIPAA Police Are On Their Way - One of the lesser known requirements of the Health Information Technology for Economic and Clinical Health (HITECH) Act requires the U.S. Department of Health and Human Services (HHS) to conduct periodic audits to ensure that healthcare organizations and their business associates are complying ...(more)

Microsoft Patch Tuesday Brings Critical Fixes, Also for Surface - Most consumers haven't even received their Microsoft Surface tablets yet. But on Patch Tuesday, Redmond will be rolling out a patch for the Windows 8 RT version of its sleek new portable device. ...(more)

SQL Server News : Development

Seamless insights on structured and unstructured data with SQL Server 2012 Parallel Data Warehouse - In the fast evolving new world of Big Data, you are being asked to answer a new set of questions that require immediate responses on data that has changed in volume, variety, complexity and velocity. A modern data platform must be able to answer these new questions without costing IT millions of dollars to deploy complex and time consuming systems....(more)

Product Reviews

Book Review: DAX FORMULAS for PowerPivot: The Excel Pro’s Guide to Mastering DAX - This week I got the opportunity to read a new book out on the market by Rob Collie (PowerPivotPro) – “DAX FORMULAS for PowerPivot: The Excel Pro’s Guide to Mastering DAX”. I have to admit that once I received the book I read through it in a couple of days, couldn’t put it down and wanted to finish it. I really enjoyed the style of the book, just like Rob’s blog posts, and there was just a really good natural transition of the content that it kept me hooked. While reading the book I really felt that I was in a classroom and Rob was presenting the material, something about the writing style, really enjoyed it (hat tip to Rob)....(more)

Book Review: SQL Server 2012 Analysis Services The BISM Tabular Model - I will start off by saying, whenever I see the names Marco Russo, Alberto Ferrari and Chris Webb on a SQL Server Analysis Services book….I know it will good. This book is no exception! I can say with 100% honesty that this is one of the best technical books I’ve read....(more)

Microsoft News : Patches, Bugs

Microsoft Internet Explorer Multiple Use-After-Free Vulnerabilities - Multiple vulnerabilities have been reported in Microsoft Internet Explorer, which can be exploited by malicious people to compromise a user's system. 1) A use-after-free error within the "CFormElement" class can be exploited to dereference already freed memory. 2) A use-after-free error within the "CTreePos" class can be exploited to dereference already freed memory....(more)

Microsoft .NET Framework Multiple Vulnerabilities - Multiple vulnerabilities have been reported in Microsoft .NET Framework, which can be exploited by malicious people to disclose potentially sensitive information, bypass certain security restrictions, and compromise a user's system. 1) An error within permissions checking of objects that perform reflection can be exploited via a specially crafted XAML Browser Application (XBAP) or an untrusted .NET application. 2) A sanitisation error when processing partially trusted code can be exploited to disclose certain data via a specially crafted XAML Browser Application (XBAP) or an untrusted .NET application. ...(more)

Microsoft Office Excel Multiple Vulnerabilities - Multiple vulnerabilities have been reported in Microsoft Office Excel, which can be exploited by malicious people to compromise a user's system. 1) An error when processing the "SerAuxErrBar" record can be exploited to cause a heap-based buffer overflow via a specially crafted file. 2) An input validation error can be exploited to corrupt memory via a specially crafted file. ...(more)

Microsoft Windows Kernel-Mode Drivers Three Vulnerabilities - Multiple vulnerabilities have been reported in Microsoft Windows, which can be exploited by malicious, local users to gain escalated privileges and by malicious people to compromise a user's system. 1) A use-after-free error exists within win32k.sys when handling certain objects in memory. 2) Another use-after-free error exists within win32k.sys when handling certain objects in memory....(more)

Microsoft Internet Information Services Two Information Disclosure Vulnerabilities - A security issue and a vulnerability have been reported in Microsoft Internet Information Services, which can be exploited by malicious, local users and malicious people to disclose certain sensitive information. 1) The security issue is caused due to the server not properly restricting access to certain log files and can be exploited to gain access to usernames and passwords of configured accounts....(more)

Microsoft Windows Briefcase Integer Underflow and Overflow Vulnerabilities - Two vulnerabilities have been reported in Microsoft Windows, which can be exploited by malicious people to compromise a user's system. 1) An integer underflow error within the Briefcase handling feature can be exploited via a specially crafted file. 2) An integer overflow error within the Briefcase handling feature can be exploited via a specially crafted file....(more)

Hardware News

Samsung SSD 840: Testing the Endurance of TLC NAND - NAND endurance is something that always raises questions among those considering a move to solid state storage. Even though we have showed more than once that the endurance of today's MLC NAND based SSDs is more than enough for even enterprise workloads, the misconception of SSDs having a short lifespan still lives. Back in the day when we had 3Xnm MLC NAND with 5,000 P/E cycles, people were worried about wearing our their SSDs, although there was absolutely nothing to worry about. The move to ~20nm MLC NAND has reduced the available P/E cycles to 3,000, but that's still plenty....(more)

Intel ships 60-core Xeon Phi processor - Intel hopes to deliver performance and power-efficiency breakthroughs to servers with the new Xeon Phi family of processors, the first model of which is now shipping to customers, the company said on Monday. Chips in the Xeon Phi range, also called "Knights Corner," work with server CPUs to speed up scientific, math, and graphics tasks. Targeted at servers and supercomputers, the first Phi chips have 60 or more cores, with the fastest chips delivering more than a teraflop of performance per second....(more)

Blogs : Administration

Remote Desktop and Surface - The Windows RT operating system is not the same as Windows. After all the talk about the upcoming OS and hardware, this should not be a shock to anyone. This means that there are going to be some apps that just won’t run on your RT device. OK, not a problem. Because you can always attack to a remote desktop session, right? Maybe....(more)

How Can Reference Counting Be A Leading Memory Scribbler Cause? - The concept of the memory scribbler comes up quite a bit in support. The term can often be over used but I ran into a specific example that commonly fools people, including support engineers. The random nature and even the resulting behaviors are so broad that these issues often take quite a bit of troubleshooting to determine cause....(more)

Does larger TEMPDB log file affect the performance of startup of SQL Server? - I invested some time today investigating what SQL Server does with the initialization of TEMPDB’s log file, because a question was raised last week during Robert Davis session on TEMPDB at SQL PASS Summit in Seattle, and I wasn’t sure about what the answer would be....(more)

SQL SERVER – Changing Default Installation Path for SQL Server - Earlier I wrote a blog post about SQL SERVER – Move Database Files MDF and LDF to Another Location and in the blog post we discussed how we can change the location of the MDF and LDF files after database is already created. I had mentioned that we will discuss how to change the default location of the database. This way we do not have to change the location of the database after it is created at different locations....(more)

Blogs : Analysis Services / BI

PASS Summit 2012 Twitter Dashboard - As a business intelligence developer, my skills include taking the raw data, shaking it to remove all the junk, and presenting it to business users in the most sexiest way possible – smart people often use the words ETL, and BI to define this process, but I’m not smart – I neither have glasses nor a French beard. ^_^...(more)

Blogs : Backup and Recovery

The Computer Backup Rule of Three - I'm ALWAYS pounding people to backup. I will continue. BACKUP YOUR STUFF. If you care about it, back it up....(more)

Quick Tip - Speed a Slow Restore from the Transaction Log - Here's a quick tip for you: During some restore operations on Microsoft SQL Server, the transaction log redo step might be taking an unusually long time. Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations. To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement....(more)

Blogs : Career

Agile way of acquiring SQL Server skills - You probably heard about 7 Days To OLAP by Simon Doubt. It’s Simon’s experiment on learning OLAP during his stay on the PASS Summit. This is a very interesting concept and you should try it. The experiment has four key elements:...(more)

Rules of Holes -#1: Stop Digging - You may have heard of the 'First Rule of Holes'. It goes something like this: " When you suspect you might be in a hole, stop digging. " ...(more)

Blogs : Computing in the Cloud

Windows Azure SQL Database : How to create Windows Azure SQL Database - Windows Azure SQL Database is a full featured relational database-as-a-service offering formerly known as SQL Azure. You can attach more than one subscription with your Microsoft Account and every subscriptions can have more then one Windows Azure SQL Servers and every Windows Azure SQL Server can have more than one Windows Azure SQL Databases. ...(more)

Windows Azure Virtual Machine Readiness and Capacity Assessment for SQL Server - Windows Azure Virtual Machine Readiness and Capacity Assessment for Windows Server Machine Running SQL Server...(more)

Ack! Where the heck did Azure Reporting and Data Sync go??!?! - As you might have noticed, we have been moving more and more of our portal functionality to the new HTML5 portal, plus all of our new features are showing up there and not in the original Silverlight portal. Unfortunately, we are stuck for some period of time where some of the features and functionality are not replicated to the HTML5 portal....(more)

Developing a Support Plan for Cloud Applications - Last week I blogged about developing a High-Availability plan. The specifics of a given plan aren't as simple as "Step 1, then Step 2" because in a hybrid environment (which most of us have) the situation changes the requirements. There are those that look for simple "template" solutions, but unless you settle on a single vendor and a single way of doing things, that's not really viable....(more)

Using a Windows Azure worker role to generate reports using Azure Reporting - While trying to reproduce an issue in Azure Reporting, I found myself building a simple worker role that generated a report using the ReportViewer control in server mode. I found a couple gaps in the overall content available, so I thought I would try to post a more complete example here. ...(more)

Blogs : DMO/SMO/Powershell

Sqlcmd is dead. Long live Sqlcmd - SQL Server 2008 and higher ship with the invoke-sqlcmd cmdlet while SQL Server 2005 and higher includes the sqlcmd.exe utility. Not wanting to take a dependency on invoke-sqlcmd, a few years ago I’d written a simpler variation I called invoke-sqlcmd2 which I’ve updated along with others....(more)

Blogs : Events

Data Governance Winter Conference - The Premier Event in Data and Information Governance, The Westin Beach Hotel and Spa, Ft. Lauderdale, Florida. Dec 3-5, 2012 ...(more)

Interesting Products I Saw At PASS - For my last post from the PASS Summit, I thought I’d mention briefly some of the products that caught my eye as I wandered round the exhibition hall this afternoon:...(more)

Blogs : Excel

Chart Styles in Office 2013 - With Excel 2013, you now have a new default style and set of chart styles to apply to your chart. The new styles are designer quality and will make your chart beautiful in one click!...(more)

Blogs : High Availability/Disaster Recovery

SQL SERVER – Retrieving Random Rows from Table Using NEWID() - However, I have not blogged about following trick before. Let me share the trick here as well. You can generate random scripts using following methods as well....(more)

Migrating SQL Server Databases that use Database Master Keys - There’s a lot of things to consider when migrating databases between SQL Server instances. I want to provide some tips for dealing with Database Master Keys, and in particular the case when those keys are encrypted by the Service Master Key....(more)

Database Mirroring Performance Counters - This week’s SQL Skills insider email contains a simple but often too true scenario regarding database mirroring. If you haven’t signed up to the insider email I suggest you do as there is wealth of great information provided by the SQL Skills team on a Bi-Weekly basis....(more)

Blogs : Integration Services/ETL

Quick Tip: SSIS and SAP BW - If you have ever had to work with Integration Services and data sources other than the Microsoft variety you know how frustrating it can be. I recently started a project using SQL Server 2012 and SAP BW 7. The customer had already decided that the other commercial options weren’t viable and settled on the Microsoft Connector 1.1 for SAP BW. A test package was built and worked....(more)

Looping through multiple servers in SSIS Part 3 - Over the last couple of weeks I’ve set up a SSIS package that will loop through a group of servers and export the list of databases to a central location. I used a Foreach Item Enumerator to list the servers but honestly those require a bit too much maintenance for me. In my office we have 60-70 instances right now and are merging with a group with 60+ more. In my mind that’s a headache waiting to happen. So today I’m going to modify the package to pull and loop through a list of servers from a table. Still somewhat manual (entering records into the table) but at least I don’t have to open the package each time....(more)

Existence Check Deconstructed -- SSIS Lookup Transformation - There are countless ways to mix and match components in SSIS to simulate what SQL Server does in a query. Here I am going to show you how to replicate an existence check using SSIS components. The only time I use this type of configuration is when I have cross-server joins. I find it more efficient for SSIS to hop to different servers in discrete components than to rely on SQL Server to do linked server calls. Here is an example of a simple select statement that does a cross-server join to validate the data....(more)

Blogs : Performance and Tuning

Fragmented Log files could be slowing down your database - Something that is sometimes forgotten by a lot of DBAs is the fact that database log files get fragmented in the same way that you get fragmentation in a data file. The cause is very different but the effect is the same – too much effort reading and writing data....(more)

Fixing Gatekeeper Row Cardinality Estimate Issues - The Query Optimiser needs a good estimate of the number of rows likely to be returned by each physical operator in order to select the best query plan from the most likely alternatives. Sometimes these estimates can go so wildly wrong as to result in a very slow query. Joe Sack shows how it can happen with SQL Queries on a data warehouse with a star schema. ...(more)

Queries barely over the Cost Threshold for Parallelism - Previously I had discussed SQL Server parallelism, with my thoughts on the best settings for: Cost Threshold for Parallelism (CTP) and Max Degrees of Parallelism (MAXDOP) in Parallelism Strategy and Comments . At the time, I had intended to follow up with detailed measurements. So now a mere 2 years later, here it is. The general thought was that CTP should be raised from the default value of 5, and MAXDOP should be changed from unrestricted, on modern systems with very many cores...(more)

Time Trial sys.dm_exec_query_plan vs sys.dm_exec_sql_text - I’ve been working on a way to test performance usage on a server by database. I started out using sys.dm_exec_query_stats since it has every statistic I wanted. Unfortunately it doesn’t contain the database id which makes it rather difficult to split the data out by database. This lead me to cross applying sys.dm_exec_query_plan, which does contain dbid....(more)

Blogs : Policy Based Management

Creating Objects on a Specific Filegroup with Policy Based Management - At PASS Summit last week, I presented “The What, Why, And How of Filegroups” to a packed room. It was great to see so many people eager to learn about something that is fundamental to every SQL Server database, but often not understood well enough....(more)

Blogs : Security and Auditing

Stealing VM Keys from the Hardware Cache - Research into one VM stealing crypto keys from another VM running on the same hardware. ABSTRACT: This paper details the construction of an access-driven side-channel attack by which a malicious virtual machine (VM) extracts fine-grained information from a victim VM running on the same physical computer....(more)

Encryption in Cloud Computing - This article makes the important argument that encryption -- where the user and not the cloud provider holds the keys -- is critical to protect cloud data. The problem is, it upsets cloud providers' business models: In part it is because encryption with customer controlled keys is inconsistent with portions of their business model....(more)

Blogs : Software Development

Database Deployment: The Bits - Copying Data Out - Occasionally, when deploying a database, you need to copy data out to file from all the tables in a database. Phil shows how to do it, and illustrates its use by copying an entire database from one server to another. ...(more)

A dacpac limitation – Deploy dacpac wizard does not understand SqlCmd variables - Since the release of SQL Server 2012 I have become a big fan of using dacpacs for deploying SQL Server databases (for reasons that I will explain some other day) and I chose to use a dacpac to distribute my recently announced utility sp_ssiscatalog (read: Introducing sp_ssiscatalog (v1.0.0.0)). Unfortunately if you read that blog post you may have taken note of the following:...(more)

Blogs : StreamInsight

What Is StreamInsight? A Primer for Non-Programmers - Are you trying to figure out whether StreamInsight might be something you could use, but you’re having trouble sifting through all the programming jargon that’s used to describe it? StreamInsight is, ultimately, a set of programming tools, and at some point it takes a programmer to implement a StreamInsight solution. But it really should be possible to get a handle on what StreamInsight is all about even if you’re not a programmer yourself....(more)


Administrative