The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

If you are holding, in your organisation, personal data about real people or commerce, it is wrong, and in many cases illegal to do database development work or testing using your production data. This, of course, probably applies to a minority of database systems, but data breaches caused by attacking backups or copies of production data within development are increasing.

Within the industry, two solutions have been suggested to the obvious need to do aspects of development and testing on data that is as near to production data as possible, in terms of quantity, distribution, nature and appearance. These solutions are masking and simulation. Both techniques are useful for other purposes in development but neither really fit the bill for performance testing as part of deployment.

The problem with masking, also referred to as obfuscation, is that you have two choices. Mask lightly so that the data keeps close to the current distribution, or mask effectively and lose your distribution. A ‘light’ masking is crackable, in much the same way that encryption is cracked, and there have been successful breaches in consequence. Effective masking takes the data from its distribution so it becomes pointless. After all, you need production data to test queries and processes on data of production distribution. Dynamic Data masking, by the way, is for a very different purpose and remains an effective way of doing aspects of application UAT.

The problem of simulating database data is that it is currently done for a different purpose. It can fill a bunch of related tables with great aplomb, but can’t simulate the way the world works, and therefore get close to the distribution and nature of production data. Take for example, a company hierarchy: If this includes dates and times, it would need to simulate typical careers, patterns of promotion and recruitment: it would need to accurately copy the churn of employees, possibly different at different levels of the hierarchy. Take another example, the pattern of website purchases is highly variable through weekly, monthly and yearly cycles. You’d need to simulate national holidays and sports events. From my own experience I can tell you that the convincing simulation of website traffic or phone-call information is as difficult as predicting the future. Even advertising breaks within popular television programs can cause a big blip.

We should, I believe, grasp the nettle. The facts seems to be that neither masking nor simulation will help much. Why do we need production data? If it is to check query performance as part of unit test, then use the statistics from the production system, disable refresh, and perform your query on simulated data. It works well-enough for me. If we need it to learn how to handle lots of data, use an open data real database. If you really, really need to do a batch of performance tests as part of deployment, then run these tests in staging under conditions agreed with your auditor as being compliant and secure. We need to think laterally.

William Brewer 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.

Vendors/3rd Party Products

5 shortcuts in SQL Prompt you didn’t know about - When writing SQL, you can use SQL Prompt to quickly jump to some really handy formatting and refactoring actions. In this blog post, Redgate's Carly Meichen shares five shortcuts you might find useful....(more)

Generating test data with localized addresses using SQL Data Generator - How do you generate data that gives you realistic-looking addresses for your part of the world? With a little tweaking, and a basic grasp of ‘reverse regex’ expressions, a tool like SQL Data Generator (SDG) can do the job very well - Phil Factor explains....(more)

SQL Clone beta launch – are ewe ready? - PASS Summit this year will see a long-awaited demonstration of the new SQL Clone beta. SQL Clone enables databases and database backups to be cloned in seconds and saves up to 99% of disk space. Data Platform MVP, Grant Fritchey, will show how SQL Clone enables faster provisioning of databases allowing teams to develop test and fix code in isolated environments. Find out what's in the beta - and what's coming soon....(more)

Tech News : General Interest

How the Web Became Unreadable - There’s a widespread movement in design circles to reduce the contrast between text and background, making type harder to read. Apple is guilty. Google is, too. So is Twitter. Typography may not seem like a crucial design element, but it is. ...(more)

Tech News : Security

LinkedIn says hacking suspect is tied to breach that stole 117M passwords - An alleged Russian hacker arrested in the Czech Republic following an FBI-coordinated tip-off is suspected of taking part in a 2012 breach of LinkedIn that resulted in the theft of more than 117 million user passwords....(more)

Microsoft News : General Interest

Microsoft is phasing out its LightSwitch business-app development tool - Microsoft's LightSwitch business-app development tool is on its way out, with PowerApps as its recommended replacement....(more)

Blogs : Administration

Decoding Key and Page WaitResource for Deadlocks and Blocking - Kendra Little walks you through decoding SQL Server's blocked and deadlocked resources graphs....(more)

Centralized Management Server 101 - Central Management Server registered servers can be a huge time-saver, especially if you are in an environment that has a lot of servers to manage and ensure consistent configuration among all of them...(more)

How to Set Up SQL Server Database Mail in One Easy Script - Database Mail in Microsoft SQL Server allows for messaging to occur between SQL Server and anyone with a valid email address. It can be daunting to set up through SQL Server Management Studio but by using the SQL Server Template in this article you'll find you can do so in just a few minutes....(more)

Blogs : Analysis Services / BI

SQL Server as a Machine Learning Model Management System - If you are a data scientist, business analyst or a machine learning engineer, you need model management – a system that manages and orchestrates the entire lifecycle of your learning model. Analytical models must be trained, compared and monitored before deploying into production, requiring many steps to take place in order to operationalize a model’s lifecycle....(more)

Blogs : Backup and Recovery

Performing Transaction Log Backups using AlwaysOn Availability Group Read-Only Secondary Replicas - This series of posts will address some of the aspects of transaction log backups that may not be obvious, especially as it pertains to how the log truncation LSN is maintained across all of the replicas (primary and secondary), as well as what can actually be backed up....(more)

Blogs : Big Data

Can Data Science and Big Data Improve Design? - Can Big Data improve design and create a greater emotional response among consumers?...(more)

Blogs

Skilling and De-skilling - By using any tool, it can make you more productive for the task it is designed for, but leave you curiously exposed when the tool isn’t available. William Brewer discusses the topic in this guest editorial for Simple Talk....(more)

Blogs : Computing in the Cloud

Migrating from SQL Server to Azure SQL Database using Bacpac Files - Database migration from SQL Server to Azure SQL Database is a process that many organizations must implement as they move to Azure public cloud. This article is a guide that describes one specific implementation path for the migration process....(more)

Blogs : Data Mining/Data Analysis

Free eBook: Using SQL Server 2016 for Data Science & Advanced Analytics - With this eBook, you will learn how to install, configure and use Microsoft’s SQL Server R Services in data science and advanced analytics projects....(more)

Blogs : Database Design, Theory and Development

Reading, Writing, and Creating SQL Server Extended Properties - What I’d like to do in this article is to explain something about SQL Server extended properties, why they are so important, and illustrate some ways of working with them, scripting them, and maintaining them....(more)

Using the Dynamic Online Catalog to Explore Keys and Relationships - I'll be describing ho to explore keys, constraints and relationships using SQL Server's system catalog views, illustrating as much as possible with practical and immediately useful code. ...(more)

How many NUMA Nodes do I have? - A few techniques for finding out how many NUMA nodes your SQL Server has....(more)

Blogs : DMO/SMO/Powershell

Supercharge your PowerShell functions with a PoshRSJob template - Cody Konior shares how to integrate PoshRSJob cleanly into your functions through a default -Parallel parameter and using a template....(more)

Blogs : Events

PASS Summit: Advice From A Newcomer - PASS Summit 2016 is coming very soon. If 2016 is going to be your first time at the conference, you may be looking for (or have already received) some advice....(more)

Blogs : NOSQL

Consistency is Consistently Undervalued - Micro-services and NoSQL have been trendy over the last few years due mainly to their success at large companies like Google. I’m not going to tell you not to use them, but I am going to try to explain that one of the most important things you lose in both cases are transactions...(more)

In what areas are NoSQL Databases beneficial over Relational Databases? - I have been working with MySQL and SQL Server for quite a long time and thought of trying MongoDB. I realized that I won't be having the ability of joining a lot of tables and all. Preferably what areas would you suggest that NoSQL databases would be beneficial over the traditional relational databases?...(more)

MariaDB CTO: NoSQL Database Bubble To Burst Soon - There is so much money poured into some companies and they do not have a way to monetise until [a solution] is proven worthwhile. That bubble environment is mostly open source. And I believe that we will see a similar crash as we had in 1999. You will see a similar bubble, of course smaller in scale in the database space.”...(more)

Blogs : Performance and Tuning

SELECT * Does Not Hurt Performance - Grant Fritchey wants to test the theory that SELECT * hurts performance - because he thinks this it's bunkum....(more)

Retrieving SQL Server Query Execution Plans - One of the most useful tools in SQL Server for analyzing and troubleshooting a query’s performance is the execution plan. An execution plan can tell you how the database engine intends to execute a query, as determined by the query optimizer, or how the database engine actually executed the query....(more)

Searching Strings in SQL Server is Expensive - Brent Ozar demonstrates the high computational costs of searching strings across large datasets....(more)

Blogs : Security and Auditing

Botched Server Install Results in $2.14 Million HIPAA Breach Fine - A Catholic health care system has agreed to pay $2.14 million to settle claims it failed to change the default settings after installing new server, allowing public access to the private health records of 31,800 patients. It agreed it was in potential violation of security rules of the Health Insurance Portability and Accountability Act (HIPAA)....(more)

Blogs : SQL Server 2014

New Cumulative Updates Released for SQL Server 2014 - Microsoft just announced a couple of CU’s for SQL Server 2014 that you may be interested in....(more)

Blogs : T-SQL

Implementing a custom sort - Rob Farley looks in detail at how to order data....(more)

Articles : Administration

How to Fix Ten SSDT Deployment Snags, With or Without ReadyRoll - Even a database development framework like Sql Server Data Tools (SSDT) doesn't get it right all the time, and there are ten deployment 'gotchas' in particular that can cause some head-scratching amongst developers to get right. From his unique perspective of creating a tool to make such deployments in SSDT less stressful, Dan Nolan discusses each pitfall and how to avoid it, whether you have ReadyRoll or not....(more)

Successful Anti-Patterns, Storage Requirements - One of the things you realize with experience is that there is not one absolute truth when speaking about SQL Server… that comes along with the magical answer for most of the questions, “it depends”. But what makes the difference it’s to know what it depends on....(more)

How To View Always Encrypted Data in SQL Server Management Studio - One of the shiny new features in SQL Server 2016 is Always Encrypted. Here's how you can view the plain text data inside of SQL Server Management Studio....(more)

Articles

Managing Packages Using Windows PowerShell - Windows hasn't had a package manager in the style of the Advanced Packaging Tool (APT) of Linux distributions. Apt-get is a great way of installing packages and other software. We have Chocolatey, of course which has a growing library of software and even allows you to automatically update software. Now PowerShell has an 'official' way of using any system, such as NuGet, Chocolatey, GitHub or PSget. It is definitely useful and likely to mature into an indispensable tool for Windows users. Nicolas explains why...(more)