The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Once, so long ago that I can safely recount it without breaking confidences, I had the task of sorting out a problem with a 'Business Intelligence' report for a motor manufacturer. A senior engineer for one of the many associated companies, bemused by some car acceleration and braking figures, had questioned whether someone had "messed up the SQL".

With a task like this, you check the obvious first. No, the same figures produced the same results by four different methods, including hand-calculation; it looked as if the problem was the data. The system had been in place for years. Where had the data come from? I uncovered a labyrinthine maze of abstracts and aggregations, but the raw data was elusive. When I eventually found it, I found that the aggregations were correct, and the data was apparently correct.

Then it dawned. Acceleration and braking figures are quoted in quite a complicated way. Acceleration, for example, is the time it takes to reach a particular speed, which in turn is the distance that would be covered in that time interval if the car travelled at the same rate for that time interval.

This would be fine if humanity agreed on that distance, or how one measured it. Yes, they had extracted the raw data from different systems, some of which used imperial measurements, and others metric. There were also variations in the time intervals for measuring speed. Clarity was lost as soon as they became aggregated 'acceleration figures'.

It was nobody's fault, though the IT systems got the blame. If we don't collect the raw data in the same consistent way, in the same units of measurement, then aggregation and consolidation is more difficult. If it is not subject to rigid controls then it becomes impossible.

This is why the Business Intelligence expert must be a cynical generalist with a keen eye for the detail of the bedrock of data types and check constraints. Graphs and charts are dangerous in the wrong hands, and if built on data that is carelessly gathered will mislead as often as they lead. The canny BI specialist will spend a lot of time verifying and validating the raw data before emerging with the seductive visualizations.

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 : General Interest

Skype will hit Windows 8 Friday, turn your PC or tablet into a phone - Microsoft's Skype team announced yesterday that Skype will debut on the Windows Store this Friday, the same day Windows 8 and Windows RT hit retail availability....(more)

Gartner predicts 90 percent of enterprises will bypass Windows 8 through 2014 - Widespread Windows 8 adoption by businesses is years away, primarily because it is so different from Windows 7 that the learning curve for end-users will be a nightmare, experts say....(more)

Microsoft has no plans for a second Windows 7 Service Pack - Sources close to Microsoft's sustained engineering team, which builds and releases service packs, have told The Register there are no plans for a second Windows 7 SP – breaking precedent on the normal cycle of updating Windows....(more)

Meet Microsoft's First Surface Tablet -- From 21 Years Ago - In a video lost among the millions on YouTube, Gates marked the debut of Windows for Pen Computing, the first tablet interface for Windows....(more)

Why Windows 8? - "Why did Microsoft throw a grenade into the middle of Windows and institute the biggest-ever change in this platform? The answer is simple, as it turns out. And the clues to Microsoft’s thinking have been sitting right in front of us for the past three or four years. In our hands, actually. I’m referring of course to smartphones and tablets."...(more)

Amazon cloud outage impacts Reddit, Airbnb, Flipboard - Amazon Web Services experienced problems in the East Coast region, which in turn hurt many Web sites. ...(more)

Turning to the past to power Windows' future: An in-depth look at WinRT - Windows 8 is a major release, and it is very different from the Windows before it. And yet it's strangely familiar: when you peek under the covers of the new user interface and look at how it all works, it's not quite the revolution that Microsoft is claiming it to be....(more)

Tech News : Security

Tools To Help Cover Your Tracks Online - Not all online privacy tools are created equal, but good ones do exist to safeguard your identity and personal data. The Little Snitch firewall helps savvy Mac users detect new malware on their machines, while HTTPS Everywhere keeps others from snooping on your data. Wickr is a free iOS app that encrypts your iPhone communication....(more)

Android apps used by millions vulnerable to password, e-mail theft - Researchers uncover faulty encryption in apps available in Google's Play Market....(more)

Phony certificates fool faulty crypto in apps from AIM, Chase, and more - Researchers have uncovered defects in a wide range of applications running on computers, smartphones, and Web servers that could make them susceptible to attacks exposing passwords, credit card numbers, and other sensitive data....(more)

The 25 Most Popular Passwords of 2012 - "Though I had hoped that we, as people, would have improved our passwords by now, it turns out that we, as people, are still unimaginative and so very lazy. Just take a look at the most popular (read: the worst) passwords of 2012. They're terribly predictable."...(more)

Microsoft News : General Interest

Want to know more about Microsoft Research? - Science@Microsoft, an e-book that commemorates our many years of Microsoft Research activities with the academic research community. ...(more)

Microsoft News

The Windows 8 upgrade install experience - If you take good care of your PC, you might not need to do a clean install....(more)

Microsoft Releases Windows 8 & Windows RT - Just over a year after first introducing Windows 8 and Windows RT to the press at their BUILD conference, the run-up to the launch of Windows 8 and Windows RT has reached its end. I...(more)

Hardware News

AMD Piledriver CPUs hit the market - AMD has announced that processors based on its much-anticipated Piledriver architecture are now available to the public, in a move that marks the company's competitive return to the world of high-end desktop PCs....(more)

Non-volatile memory's future is in software - New memory technology will serve dual roles of mass storage and system memory, challenging NAND flash memory use in SSDs and embedded in mobile products...(more)

Apple Fusion Drive—wait, what? How does this work? - New feature sounds less like hybrid drive caching and more like auto-tiering....(more)

Microsoft Surface Review - AnandTech's full review of Microsoft's first branded tablet: Surface....(more)

Blogs : Administration

Partitioned Tables, Indexes and Execution Plans: a Cautionary Tale - Table partitioning is a blessing in that it makes large tables that have varying access patterns more scalable and manageable, but it is a mixed blessing. It is important to understand the down-side before using table partitioning....(more)

Book Review: Troubleshooting SQL Server: A Guide for the Accidental DBA - What makes this book so good is that it distills the knowledge and experience of Jonathan Kehayias and Ted Krueger in a format that any DBA, no matter their skill level, can easily understand and directly apply to their day-to-day work. Don’t let the title fool you, this book is not only for accidental DBAs. I think every DBA who reads this book will learn something new. I know I did. ...(more)

How SQL Server Stuff Works: Filegroups (Video) - If you work with SQL Server databases, you work with filegroups. Learn what filegroups are, cover best practices for using them, and see them in action! Bring your hard hat – we’re diving into the database structure!...(more)

Viewing SQL Server 2008 R2 Audit Logs Using SSMS 2012 - Short version of this post: you can’t. Tom La Rock loses hours trying so you don't have to. You can thank him later....(more)

Blogs : Backup and Recovery

SQL Server : How to Keep Database Restore History - A script to create a job, which will fetch restore related log entries from SQL Server Log and archive it to a user created history table....(more)

Why does restoring a database needs TempDB? - Nacho Alonso Portillo on the "Restore Database" command appears to need or use TempDB right at the beginning of the restore sequence. ...(more)

Blogs : Computing in the Cloud

Simplifying Big Data for the Enterprise - New previews of Microsoft's Hadoop-based solutions for Windows Server and Windows Azure, now called Microsoft HDInsight Server for Windows and Windows Azure HDInsight Service....(more)

A Simple Truth about Cloud Security - "I keep hearing that security is a fear factor for some organizations that are considering moving to the cloud. They worry that they’ll have to change their whole security approach in a highly virtualized environment." Joel Sloss thinks that those IT admins should see it as an opportunity, not a threat to their survival....(more)

Understanding Windows Azure SQL Database Size - The database on Windows Azure Sql Database is stored in two parts: Database data and metadata. Wayne Walter Berry explains how the two types of data are different and how they are reflected in the bill you are charged monthly for usage....(more)

Blogs : DMO/SMO/Powershell

Using Invoke-WebRequest - Powershell and the invoke-webrequest cmdlet provides an excellent way to explore the details of a webpage so you can quickly craft a custom web-scraping script. ...(more)

Blogs : High Availability/Disaster Recovery

Database Mirroring Error - "Msg 1416, Level 16, State 31, Line “LineNumberHere” Database "DatabaseNameHere" is not configured for database mirroring" Chris Mcgowan explains the cause....(more)

Why the registry size can cause problems with your AlwaysOn/Failover Cluster setup - HarshDeep Singh explores a very interesting issue, where one of the cluster nodes in an AlwaysOn environment became unstable, and the administrators ended up evicting the node from the Windows cluster as an emergency measure....(more)

Blogs : Integration Services/ETL

SSIS Basics: Using the Execute SQL Task to Generate Result Sets - The Execute SQL Task of SSIS is extraordinarily useful, but it can cause a lot of difficulty for developers learning SSIS, or only using it occasionally. What it needed, we felt, was a clear step-by-step guide that showed the basics of how to use it effectively. Annette has once again cleared the fog of confusion. ...(more)

Using the Bing API to geocode data in SSIS - Simon Lidberg shows how to create a custom component in SSIS to geocode geocode data SQL Server databases....(more)

Blogs : Performance and Tuning

Clustered Indexes Have Statistics Too - It may seem obvious, but I’ve heard more than one person suggest to me that statistics on a clustered index just don’t matter. That if the clustered index can satisfy a given query, it’s going to get selected. ...(more)

Trace Flag 2371 and Statistics - One of the issues that I see published often on forums like SQLServerCentral is the advice to update statistics on your tables if you have strange performance issues, or sudden changes in performance. Statistics are important for the query optimizer, and you should understand the basics of how they work....(more)

CloudSeeder: CLR Stored Procedures For Creating CPU Pressure - CPU pressure in SQL Server creates all sorts of interesting side-effects, such as exacerbating waits and making various other conditions much easier to reproduce. Adam Machanic offers a CLR library that will make simulating CPU pressure that much easier. Use with caution!...(more)

Blogs : Professional Development

TL;DR Use A Picture - TL;DR is shorthand for “too long; didn’t read” and it’s used across the web to summarize a tedious article. It also applies well to many emails. Jeremiah Peshcka advocates straetgic use of pictures to tell your story....(more)

Blogs : Software Development

Are Unit Tests Overused? - Unit Testing has come to dominate the many types of test that are used in developing applications. This has inevitably been at the expense of other types, such as integration test. Does a successful unit test regime ensure quality, or should we see unit testing as just one of a range of tests that can together give us confidence in an application?....(more)

Deliberate Practice - It’s easy to assume, as software engineers, that there is little need to “practice” writing code. After all, we write code all day long! Just by writing a little each day, we’re constantly learning and getting better, right? Unfortunately, that’s just not true....(more)

Mind Mapping for Successful Software Development - "If your software team hasn’t tried mind mapping to help plan themes, design technical implementations, build a test strategy, or write user documentation, it’s worth your time to learn how simply you can get started."...(more)

Blogs : SQL Server 2012

Want to Learn SQL Server 2012? - Want to get your hands on a full-featured version of SQL Server 2012 to do some prototyping? It may be cheaper than you think.......(more)

Blogs : T-SQL

TSQL - Solve it YOUR Way - Parsing Tips and Tricks - Returning Digits after the Decimal Point - The blog series "TSQL - Solve it YOUR Way" tackles a numeric parsing question: Given a numeric value, return the digits after the decimal (ex: 3.142 returns 142)....(more)

Stored procedures, recompilation and .NetRocks - "Every developer that works with SQL Server has to access their data in SOME way... how? Adhoc SQL, prepared statements (like sp_executesql) or stored procedures. To be honest, none are perfect. You shouldn't always use adhoc. You shouldn't always use prepared statements... And, dare I say - you shouldn't always use stored procedures?"...(more)