In this issue:

Vendors/3rd Party Products

Tech News : General Interest

Tech News : Security

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Big Data

Blogs : Computing in the Cloud

Blogs : DBA Tools

Blogs : Deep Into Windows

Blogs : DMO/SMO/Powershell

Blogs : Hardware

Blogs : Performance and Tuning

Blogs : Professional Development

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : T-SQL

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 2013-05-13

SQL Backup Pro New! Safeguard your SQL backups
Protect your backups from onsite disaster with SQL Backup Pro and a Hosted Storage account from Red Gate. Learn more.
SQL source control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
SQL Monitor Custom metrics from SQL Server MVPs
SQL Monitor is the only tool with a free library of custom metrics from SQL Server MVPs. Find new metrics for your servers.
Editorial - Data Science Sanity Checks

Why would any data scientist want to stop a business Intelligence analyst, or anyone else in the business, from making use of company data? There are many reasons, in my experience, but they boil down to this: all unusual changes in data need careful checking before they can provide business insights.

Imagine that you're working in an IT or telecommunications business, such as online trading. The activities of the business show continuous 'normal' variance on top of an underlying trend. There is a heartbeat in all human activity that is surprisingly predictable. Occasionally, some activity sticks out as being odd, as something that isn't part of the fuzz of normal activity. You investigate, because levels of trading normally change slowly over time, so you need to be able to explain sudden changes.

The first question is "is there an error in our data?" This will take some checking, phone calls and plenty of SQL. No, you decide, it is a real effect.

Second question: is it fraud? Every company I've ever worked for has been the focus of determined efforts at fraud, globally. Not yours, you say. How closely have you looked? This takes much head-scratching, searches through logs, calls to experts, and tapping into the gossip. Sometimes one gasps at the extent of misplaced human ingenuity.

The third question: "is someone using your trade as a money-laundering exercise". This involves the business, as it requires careful auditing of the trades. You don't want the business to confuse normal activity with money laundering. It always ends in tears.

At that point, having eliminated the obvious three fears, we ask, "Is this a sign of a real trend from which we, as a business, could benefit"? Only now is it time to call in the BI experts.

Why all the fussiness? If you reveal to a business manager a sudden growth in a part of the business, they'll go after it just as surely as a dog goes after a rabbit. It's their nature, and good executives are intuitive and impulsive. If you show the business a trend prematurely, before you've checked the data, the consequences can be dire.

If a business expands a venture based on a trend that, in reality, arises from the activity of fraudsters, exploiting the system, then a one million dollar loss can swiftly turn into a ten million dollar loss. I've seen salespeople threatened with the sack when a change in the database introduced a bug that artificially diminished the sales figures for a region. I've had to watch helplessly when a rapid increase in certain trades resulted in the business expanding and investing to exploit it, whereupon it turned out to be transitory money-laundering swindle.

Can we ever reach the stage where we can stand aside and let the business access data without a range of checks? I don't think so. The complexity of human ingenuity in undermining complex systems is astonishing, so we can never consider data safe and sanitized without constant vigilance.

Phil Factor.

» 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.


Vendors/3rd Party Products

Job overran Custom Metric - This SQL Monitor custom metric tells you if a specific SQL Server Agent job that runs at the same time every day has overrun. The metric is useful when a job can have negative effects on other processes if it overruns....(more)

Tech News : General Interest

Backlash begins against Adobe's subscription-only plan - Petition on Change.org asking company to reconsider collects 4,400 signatures in four days...(more)

Bill Gates Is Wrong on Surface vs. iPad Tablet Claims: 10 Reasons Why - "Bill Gates said recently that iPad owners really just want the Surface tablet, and are "frustrated" by the lack of features in the iPad. His claims are hard to believe."...(more)

There is no reason at all to use MySQL: MariaDB, MySQL founder Michael Widenius - Monty Widenius on why he thinks there is no reason at all to use MySQL 5.5 instead of MariaDB 5.5....(more)

Tech News : Security

Amid a barrage of password breaches, “honeywords” to the rescue - Security experts have proposed a simple way for websites to better secure highly sensitive databases used to store user passwords: the creation of false "honeyword" passcodes that when entered would trigger alarms that account hijacking attacks are underway....(more)

Why Intel’s “How Strong is Your Password?” site can’t be trusted - A new website published by chipmaker Intel asks readers "How Strong is Your Password?" and provides a form for estimating the strength of specific passcodes. It's too bad the question isn't "How Strong is your Password-grading site," because the answer, unfortunately, is "not very."...(more)

How the Syrian Electronic Army Hacked The Onion - This is a write-up of how the Syrian Electronic Army hacked The Onion. In summary, they phished Onion employees’ Google Apps accounts via 3 seperate methods....(more)

Has Big Data Made Anonymity Impossible? - As the amount of data expands exponentially, nearly all of it carries someone’s digital fingerprints. ...(more)

Why I am the world’s greatest lover (and other worthless security claims) - Troy Hunt on the futility of security logos..."Almost every random example I picked where this logo had been used had basic security flaws. Not obscure hypothetical flaws but rather easily observable, readily exploitable flaws."...(more)

Blogs : Administration

Ditching two-phased commits - "Unless your services have downstream side effects, you can safely turn off DTC if your work is idempotent."...(more)

Locking in Microsoft SQL Server (Part 17) – Implementing Critical Section / Mutexes in T-SQL - Dmitri Korotkevitch continues his (very) deep dive into locking in SQL Server....(more)

Language Translation within SQL Server using Bing Translator public APIs and CLR Stored Procedures / Functions - Does SQL Server have a built-in way to translate my text data from English to Spanish, German to French, Italian to Russian? The answer is no, SQL Server does not have this functionality built-in, but it is indeed possible to build by leveraging CLR and the public Bing Translator APIs....(more)

Pros and Cons of Using Read-Uncommitted and NoLock - SQL Server has many options and flags that can alter its behavior, but their use should be the EXCEPTION and not the RULE. Tamarick Hill explain why using read-uncommitted and nolock is mainly to be avoided....(more)

Blogs : Analysis Services / BI

Using PowerShell for SQL Server Analysis Services tasks - Part 1 - There are several repetitive SSAS tasks that I perform on a regular basis and I want to know what options there are to automate some of these tasks such as processing partitions, creating backups and monitoring SSAS. Is there a scripting tool to automate these types of SQL Server Analysis Services (SSAS) tasks?...(more)

Blogs : Backup and Recovery

Implications of the SQL Server Bulk Logged recovery model on point in time restores - Tim Chapman on the potenital dangers of BULKED LOGGED recovery model....(more)

Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets - There are plenty of occasions when it makes a lot of sense to do backup and restore scripts in PowerShell. Microsoft have put effort into making it much easier, as Allen White demonstrates....(more)

Blogs : Big Data

Big Data: Size isn’t everything - Big Data has a name problem. There is a lot more to it than size. Shape, Speed, and…err…Veracity are also key elements...(more)

Think Again: Big Data - Why the rise of machines isn't all it's cracked up to be. ...(more)

A Very Short History Of Big Data - The story of how data became big starts many years before the current buzz around big data....(more)

Most data isn’t “big,” and businesses are wasting money pretending it is - Data scientists are by nature highly skeptical, and they’ve provided us with a litany of reasons to be weary of many of the claims made for the "Big Data" field....(more)

Blogs : Computing in the Cloud

Designing Great Cloud Applications - I get strange looks when I talk to developers about the difference between developing an application to a product versus developing an application to a service. The application you write on premise is written to a piece of software purchased, installed and configured on a piece of computer hardware that you privately own. The application you write in the cloud is written to a set of services that are available to you as well as the public to exploit. Mark Souza explores how they are different....(more)

How to Tell Your Windows Azure SQL Database Moved - The very concept of the Windows Azure SQL Database (WASD) is predicated on the up-time created by having three active copies of your database. Should there be a hardware or software failure that would cause the primary replica to go down, your database gets moved to one of the secondary replicas which has been maintaining a copy of the data from the primary replica....(more)

How To: Migrate Encrypted Procedures To Windows Azure SQL Database (WASD) - Here’s the easy answer: you don’t. WASD does not support the WITH ENCRYPTION option for objects such as stored procedures, user defined functions, triggers, or views. So the idea of migrating an object that was compiled with that option is simply not feasible....(more)

Where are the Windows Azure customer case studies – and why aren’t there more? - Buck Woody on where to find more of the "real details" on how to implement a Windows Azure solution....(more)

How To: Troubleshoot Deadlocks In Windows Azure SQL Database (WASD) - Tom LaRock on why WASD makes it EASIER to troubleshoot deadlocks than a traditional on-prem instance of SQL Server....(more)

Blogs : DBA Tools

Starting with Git for Database Development - Steve Jones tries out SQL Source Control and GitHub for storing and managing the source of his demo databases....(more)

Blogs : Deep Into Windows

How to Identify Which Request Caused a Runaway Thread, Using Windbg - When your w3wp process is stuck at 100% , you may want to identify what request the runaway thread is actually serving. Mark Rasmussen shows how, using windbg....(more)

Blogs : DMO/SMO/Powershell

The SQL Power Doc Guide For PowerShell Beginners - A walk through of how to use SQL Power Doc to discover, document, and diagnose your SQL Servers....(more)

Use PowerShell to Clean-up SQL Server Jobs - Sean McCown runs through a few operations that you may want to perform on your jobs, such as changing job ownership....(more)

Use PowerShell to Examine Dirty and Expensive SQL Server Properties - The Dirty column in the properties collection tells you whether the value has been pushed to the server. The Expensive colmns indicates that the particular property is more, well, expensive to gather....(more)

Blogs : Hardware

Enterprise Storage Systems - EMC VMAX - A DBA is expected to answer questions on what is wrong with SQL Server when there are performance problems against a magically all-powerful enterprise SAN, so this is my best understanding. The example I am using is the EMC Symmetric line, but the concepts here could be applied to other systems, if details were available. ...(more)

Blogs : Performance and Tuning

SQL Server Performance Crib Sheet - Performance is as important to a small system as it is to a large one. Grant Fritchey provides a basis to understanding SQL Server peformance, and answers questions like When do you start tuning? and How much performance is enough?...(more)

sp_Blitz™ v22: Output Results to a Table, Better Comments, and an App - Brent Ozar on a few new tricks inside the free server troubleshooting tool, sp_Blitz™....(more)

Indexing Wide Keys in SQL Server - It’s a best practice to keep your index keys as narrow as possible, and SQL Server enforces a maximum key length of 900 bytes on most “normal” clustered and nonclustered indexes. But what happens if you want to optimize the lookup of a wide column? You’re not necessarily out of luck, you may just have to get a bit creative....(more)

SQL Server Baseline Collection - Collecting information on how SQL Server is both providing data services and reacting to how those data services are being utilized, creates a foundation for efficient troubleshooting and predicting growth. ...(more)

Blogs : Professional Development

Are You Making Your Communications Stick? - Tom LaRock on four ways to make your communications stick: 1. Know Your Audience First, 2. Know The Value You Offer, 3. Tailor Your Message, 4. Practice, Practice, Practice....(more)

Blogs : Reporting Services

SSRS Production Deployment: Part 2 - In Part 1, we discussed that in development environment, we deploy reports by simply clicking “Deploy” in BIDS, but this won’t work in production. To deploy reports in production, we need to write an SSRS script, which is actually a piece of VB.NET code executing against SSRS web service....(more)

Blogs : Security and Auditing

Firewall settings for SQL Server - Firewall settings for SQL Server is a very complex topic. The specific firewall settings you may need to do, really depend on the feature set, configuration, and even your network topology. Here is some high level description though. ...(more)

Blogs : T-SQL

SQL Server Functions that help to convert date and time values... - This article will go through all the SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats....(more)


Administrative