The Complete Weekly Roundup of SQL Server News

In this issue:

Tech News : Data Mining/Warehousing

Tech News : General Interest

Tech News : Security

Tech News : The Lighter Side

Microsoft News : Development

Microsoft News : General Interest

Hardware News

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Computing in the Cloud

Blogs : Performance and Tuning

Blogs : Security and Auditing

Blogs : Software Development

Blogs : SQL Server 2014

Blogs : T-SQL

Articles : Administration

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-04-07

SQL Developer Bundle 12 must-have SQL Server tools
Start a new chapter of your career with everything you need. The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.
8 Steps to SQL Server Monitoring Free Download -Eight Steps to Efficient SQL Server Monitoring
Read this free guide to getting started with efficient SQL Server Monitoring. If you're a new DBA, or just need a refresher on some of the finer points, this guide has essential information for learning to monitor your servers.
14 Backup questions you were too shy to ask 14 SQL Server Backup Questions You Were Too Shy to Ask
Read Grant Fritchey’s “SQL Server Backup Questions We Were Too Shy To Ask” and get the answers to some of life’s big questions, like, “How do I retrieve a table from the log?” and, “Are SAN backups enough?” Download the free article PDF.
Editorial - Short Stroking and Performance

I saw a fantastic presentation at the PASS Summit years ago from one of the vendors that worked extensively to perform hardware monitoring. They're not in business anymore, but their employee had a world of knowledge around hardware and specific recommendations at the time regarding the size of your RAID sets and types of disks you'd fill them with. This was before the time of cheap SANs, when almost every SQL Server I knew of used local disks.

I was reminded of that presentation this week when I saw Tony Rogerson's Short Stroking piece on disk performance, helping us understand a bit about performance and how our choices on disk usage can affect the way our databases impact users. It's a short piece, with some details about why you might not want to fill up those disks and why saving some space is a good thing.

What's not said is that a large SAN can overcome lots of this (as can SSDs), but if your data files are stored on arrays that place them on mostly full disks, you could see performance impacts. That's why I'd suggest that you think about the RAID layout and number of disks, not only ensuring free space inside your mdf/ndf/ldf files, but also on the underlying disk. I'd also suggest you review the physical layout of your LUNs with the SAN administrators and not count on the large number of platters to ensure you don't encounter performance problems.

I might make one more suggestion to make friends with the SAN people and buy them lunch at times. The performance of their systems directly affects yours.

For most of us this isn't likely a big problem, but if you are pushing the limits of your disk space and performance is not what you'd like, adding disk space, often a cheap endeavor, might be a quick way to make things run faster.

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

Taking Bayesian Networks Into the Business - Bayesian techniques have typically been considered a researcher's tool, but businesses can benefit from them, too. ...(more)

Tech News : General Interest

Quantifying Physician Performance - IT is being called on to tie physician performance with revenue. ...(more)

500K Big-Data Jobs for the Taking - According to job site icrunchdata, the number of postings for big-data positions surpassed 500,000 last week. ...(more)

Tech News : Security

The Continuing Public/Private Surveillance Partnership - If you've been reading the news recently, you might think that corporate America is doing its best to thwart NSA surveillance. Google just announced that it is encrypting Gmail when you access it from your computer or phone, and between data centers. ...(more)

Tech News : The Lighter Side

The DBA Detective: Disturbing Developments - Originally one of the articles in the first DBA Team series, Grant wonders what Raymond Chandler or Dashiell Hammett would have done if asked to write technical articles for Simple-Talk. He came up with the DBA detective, hard-boiled Joe Dee Beay ...(more)

Windows Phone 8.1 Introduces Cortana (Update: Hands-on) - ...(more)

My quest to learn the Dvorak keyboard layout, part 2 - Blurry hands make me look like I can type faster than I do. Last week, I embarked on a quest to make my life more difficult—learning the Dvorak keyboard layout for the purposes of comfort and, possibly, even speed gains. The exercise could pay long-term ......(more)

5 days of TED in one page - What does 5 days of TED feel like? As Lucy Farey-Jones says: “Upon my re-entry to the real world, friends, clients and folks at my firm say: ‘How was TED?’ And there is a big pause from me as my brain tries and fails to sum it up. It’s an impossible question to answer. It’s like being asked ‘How is food?’ or ‘Puberty — how was it?’ Which is where this idea came in. I thought a way to answer this daunting question would be to make a graphic which tries to capture how TED makes me feel. I gave myself the challenge to capture 5 days in one page.”...(more)

Navy will test seafaring, firefighting robots - Fires at sea can ruin your whole day. That’s why the US Navy’s Office of Naval Research is preparing to test a pair of anthropomorphic firefighting robots this summer. Two designs of the Shipboard Autonomous Firefighting Robot (SAFFiR) will be tested ......(more)

Microsoft News : Development

Microsoft open sources more of its .Net technologies - ...(more)

Microsoft News : General Interest

Microsoft Reverses E-Mail Snooping Policy - A week after saying it was justified in snooping through a blogger's Hotmail account to track down a leaker of company software, Microsoft has changed course, saying it will refer such matters to law enforcement starting immediately. The reversal, explained ......(more)

Microsoft Announces Windows 8.1 Update - Desktop as a First Class Citizen - Microsoft’s day 1 keynote for the BUILD developer conference detailed an update to Windows coming on the next scheduled patch Tuesday called 8.1 Update. Notice it’s not called Update 1, which means there may or may not be more of these updates later ......(more)

Microsoft chops Azure prices to match Amazon's latest reductions - ...(more)

Microsoft to bring back Start menu, windowed apps to Windows - ...(more)

Hardware News

Short-stroking: Understanding the physical performance characteristics of Hard Disks (Part 1) 1 - As part of my thesis for my Business Intelligence masters (see http://reportingbrick.com) I did a lot of research around Solid State and Hard Disk drives, this series of articles reflects that research. First I’ll talk about the physical properties of hard drives and progress through to its affect on how SQL Server performs in relation to IO....(more)

Dell Announces Latitude Rugged Extreme 12 and 14 Laptops - We don’t really give much coverage to the rugged device category, but it’s at least somewhat cool see what companies can do when they want something that can withstand all sorts of extreme environments. Today Dell is announcing their latest fourth generation ......(more)

Blogs : Administration

The Primary Filegroup Cannot be Backed Up as a File Backup - File Group backups are great when working with very large databases (VLDB’s) that have been partitioned.  Typically when I come across File Group backups implemented in production the database is using the full recovery model. With the full recovery ......(more)

Availability databases in unhealthy data synchronization state (Error: 35285, Severity: 16, State: 1.) - After power surge last night, I realized few availability databases (also known as a “database replica”) have an unhealthy data synchronization state. What I mean from unhealthy is that they have a status of “Not Synchronizing” in SQL Server Management ......(more)

I Know I Shouldn't Shrink My Log file, BUT... - As a managed services DBA, I frequently have to clean up after other people's mistakes. (Come to think of it, as *any* DBA you spend time cleaning up other people's mistakes...)We all know you shouldn't shrink your log file (right?  We all know this, ......(more)

Making the Case for INSTEAD OF Triggers – Part 1 - SQL Sentry's Aaron Bertrand (@AaronBertrand) does some deeper testing on triggers, showing that INSTEAD OF triggers are worth a look. ...(more)

Tracking Automatic Updates to Statistics - Erin Stellato (@erinstellato) of SQLskills walks through three methods of tracking automatic statistics updates: SQL Trace, Extended Events, and dynamic management objects. ...(more)

Unexpected SPID change - A few time ago I had the opportunity to investigate about an unexpected SPID (Session Process ID) change. The inquiry began when a customer of ours starts to complain for locks, and lock time out error. These locks sometime were more frequent and sometimes ......(more)

How to prevent conversation endpoint leaks - One of the most common complains about using Service Broker in production is when administrators discover, usually after some months of usage, that sys.conversations_endpoints grows out of control with CLOSED conversations that are never cleaned up. I ......(more)

I think I am getting duplicate query plan entries in SQL Server’s procedure cache - Before the post dives into the subject I need to point out that Keith did most of the work.  I just kept pestering him with various scenarios until he sent me the e-mail content I needed.   Thanks Keith – Keith devised a set of steps that you can use ......(more)

Stop or Start VMs in an Windows Azure Subscription Using PowerShell Loop - To absolute beginners, PowerShell can look complex. And it is a more powerful tool than VBScript or other command line programs traditionally native to Windows. In this Ask the Admin, I’ll show you how to create a simple loop function to automatically ......(more)

SQL AlwaysOn: Failed to join the database to the Availability Group (Error 35250) - You might get this particular error "Failed to join the database to the Availability Group (Error 35250)" when you are creating a new Availability Group (AG) and initiated a Full sync which basically copies the backup and attaches the DBs on the replica ......(more)

SQL SERVER – Say No to DB Data Roles – SQL Security – Notes from the Field #022 - [Note from Pinal]: This is a 22nd episode of Notes from the Field series. Security is very important and we all realize that. However, when it is about implementing the security, we all are not sure what is the right path to take. If we do not have enough ......(more)

SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup - About four years ago, I wrote a blog post where I posted a script about finding backup time for all the databases. You can see the blog post over here SQL SERVER – Finding Last Backup Time for All Database. It has been a very popular script. However, ......(more)

What happens to in-flight data compression in an emergency? - Data compression can have many uses and advantages, but it also has its drawbacks. It’s definitely not a one-size-fits-all strategy. One of the things to be aware of is that initial compression of a table or index can take quite some time, and will be ......(more)

Blogs : Analysis Services / BI

Create Custom Time Intelligence Calculations in #dax #powerpivot #tabular - The recent Time Patterns article published in www.daxpatterns.com contains many DAX formulas that I hope will be useful to anyone is interest in implementing time-related calculations in DAX without relying on the Time Intelligence functions. There are ......(more)

Extending the PowerQuery date table generator to include ISO Weeks - Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2014/03/23/extending-the-powerquery-date-table-generator-to-include-iso-weeks.aspxChris Webb and Matt Mason have both blogged about formulas for generating  a date table using PowerQuery, ......(more)

How to build your own SSAS Resource Governor with PowerShell - Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2014/03/31/how-to-build-your-own-ssas-resource-governor-with-powershell.aspxA few weeks ago I posted a way to manually find and kill long running SSAS queries. In this post I’ll take ......(more)

Working with Web Services in Power Query - One of many cool things about Power Query is the way that it allows you to retrieve data from web services and load it into Excel. While this is a subject that lots of people (including me) have already blogged about, the Web.Contents() function – which ......(more)

CREATE SESSION CUBE and disk usage - You learn all kinds of interesting things on the forums! Recently I saw this post from Nils Rottgardt about a problem with the CREATE SESSION CUBE statement and SSAS Multidimensional: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/175fc61f-676e-4e3d-bed4-283f151641ec/create-session-cube-command-by-excel-grouping-creates-a-shadow-copy-on-the-server-disk-storage?forum=sqlanalysisservices Here’s ......(more)

Blogs : Computing in the Cloud

How Boeing merges its data centers with the Amazon and Microsoft clouds - When Boeing decided to move some of its most important applications to the “cloud,” it ran into the usual roadblock faced by multinational corporations that handle lots of sensitive data: security. Instead of keeping everything in-house, ......(more)

Blogs : Performance and Tuning

14 SQL Server Indexing Questions You Were Too Shy To Ask - One of the first things one needs to understand well about SQL Server is indexes, but somehow many of the basic questions don't often get asked or answered much on forums. Rob Sheldon answers some of these shy-making questions about SQL Server Indexes ......(more)

Blogs : Security and Auditing

Encrypt usernames and passwords stored in files - I was looking at a product recently and came across a rather unpleasant surprise: the install instructions specified that I put the database connection in plaintext in web.config. I’ll explore this particular case and why it’s particularly egregious, ......(more)

TSQL : Login Failures - Looking at a migrated server, I found the SQL Logs were full of login failures. I wanted to retrieve the list of IP addresses involved and hence import the log messages into SQL itself. The script below is how I achieved this. IF NOT EXISTS (SELECT ......(more)

Blogs : Software Development

My distributed build system - Yes, I know that you are probably getting geared up to hear about some crazy setup, and in some manner, it is crazy. My distributed build system is this: Yep, that is me manually distributing the build to do a cross check on a reasonable time frame. ...(more)

Blogs : SQL Server 2014

SQL Server 2014 Install – Step by Step - Hot cake for the day is SQLServer 2014 RTM bits. Its generally available starting today, and I downloaded a fresh copy to start testing it. I will be writing a separate post on the features I love about SQLServer 2014,but here is the step by step install ......(more)

SQL Server 2014’s new cardinality estimator (Part 1) - One of the performance improvement in SQL Server 2014 is the redesign of cardinality estimation. The component which does cardinality estimation (CE) is called cardinality estimator. It is the essential component of SQL query processor for query plan ......(more)

SQL Server 2014 Licensing Changes - With the release of SQL Server 2014, we get to learn all kinds of new licensing changes. While I don’t work for Microsoft legal, I do have a PDF reader and a web browser. You can follow along in the SQL Server 2014 Licensing Datasheet… if you dare. Server ......(more)

Blogs : T-SQL

Manipulating Dates and Times in T-SQL - In SQL 2008, Microsoft introduced some new date and time data types to augment the options available in prior versions. The full list of these data types with detailed explanations can be found in Microsoft Books on Line (BOL), but we’ll list them here ......(more)

Truncate Table – DDL or DML Statement? - Many times, categories of concepts and things overlap. It can be hard to categorize some items in a single category. The SQL TRUNCATE TABLE statement is an example of an item that is not so easy to categorize. Is it a DDL (Data Definition Language) or ......(more)

Are Table Variables as Good as Temporary Tables in SQL 2014? - There’s a couple of new features in SQL Server 2014 that provide options for how you work with temporary objects. Will inline index creation or memory optimized temporary tables forever change the way you code? Let’s take a look! Inline Index Creation SQL ......(more)

Articles : Administration

Some misconceptions about uninstalling SQL Server 2008 (and SQL Server 2008 R2) - Recently I was involved in troubleshooting a SQL Server 2008 installation that was failing. I found out it was failing because the SQL Server setup was detecting leftover components from previous SQL Server installations. OK, no problems I thought...(read ......(more)


Administrative