The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Over the last 18 months or so, I’ve spent a lot of time reading about the General Data Protection Regulation or GDPR. If you don’t know about it, you live under a rock, are a very old school dba, here’s a reference to the law itself. If you’re working as a data professional, I’d strongly recommend you read through it to understand how it may apply to your work. There are a great deal of implications for data management, from how to deal with backups, to security, to provisioning non-production systems.

There are also a lot of other laws and rules that you might want to look over:

• The Public Company Accounting Reform and Investor Protection Act also known as Sarbanes-Oxley or just SOX for short.
• The Health Insurance Portability and Accountability Act, HIPAA.
• Payment Card Industry Security Standards Council, PCI
• California Consumer Privacy Act, CCPA
• Stop Hacks and Improve Electronic Data Security, SHIELD, from New York state (cute law names should be illegal)

I’ve been reading and studying these things in detail in order to better understand what exactly is going to be required of me as a data professional. I think you should read them too. Some of you are thinking, “A little late to the game Grant, I’ve been studying this stuff for years.” Others are thinking, “Wonder what leftovers are in the fridge, I’m hungry.” Still others are thinking “Hey, I’m not a lawyer. I don’t have to read all that crap. I just have to do what the business tells me.” It’s those last people I want to talk to.

You’re right. These laws, rules and regulations are first and foremost a business issue that will have to be addressed by management and legal. No question. However, do you seriously think that your best approach to an oncoming set of new requirements focused on how you manage your data is best dealt with by sticking your head in the sand? You’re going to let the legal department and the business just tell you how to manage the data and you’re not going to provide input? And what if they tell you to do something that just isn’t even remotely possible, physically? Then are you going to get involved?

Maybe we’re different, but I like to take a proactive approach to my work. I want to understand why I’m being asked to do something, not simply do what I’m told. Further, if I understand why something is being requested, and the thing being requested isn’t possible, I’m better positioned to offer alternatives. I’m not left scrambling to try to satisfy requirements that may not be possible. What’s more, I can anticipate what’s needed and do research ahead of time to help ensure that I’m ready when the requirements land in my lap.

I know that there are still plenty of data professionals who have the attitude, “It’s all 1s and 0s. It doesn’t matter what the business does. I just manage the data.” There are hundreds, thousands, of reasons why this approach is flawed, if not dangerous. I may not be a lawyer, but that doesn’t mean that some understanding of the law is not a part of my job. Knowing that one or more (and trust me, it’s more) of these privacy and protection laws will impact me, my job and my organization, I can better prepare to handle the situation to help ensure that my organization is in compliance.

Grant Fritchey 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.

Webinars

SQL Server monitoring for a streamlined development process - So you want to streamline your development process? A cornerstone of successful DevOps integration is efficient, reliable SQL Server monitoring. Redgate’s James King will talk you through the process of monitoring your SQL Servers to ensure you are ready and able to efficiently keep track of your estate....(more)

How HaynesPro are driving up database development standards - HaynesPro Team Leader DBA, Thailo van Ree will discuss with Microsoft MVP, Grant Fritchey, how his company has streamlined workflows, reduced setbacks and problems during deployments, and fixed the development process with the help of Redgate tools....(more)

Vendors/3rd Party Products

Join Redgate’s SQL data catalog early access program - Redgate wants your help to develop a product for SQL Server data classification that meets today’s compliance and DevOps needs. If you’re interested in taking part in research and providing feedback on new releases, please register for the early access program. ...(more)

The ‘Right to be Forgotten’ and Data Masker for SQL Server - The right to be forgotten is one of the main features of new data protection legislation across the globe. Under Article 17 of one such piece of legislation in Europe, the GDPR, individuals have the right to have personal data erased from all systems and data the company may be storing about them. Similarly, in the USA the California Consumer Privacy Act will require companies to respond to requests for data access, deletion and portability within 45 days....(more)

Scaling SQL Monitor to Large SQL Server Estates - Most organizations are finding that the size and number of databases that need to be monitored is increasing, unlike the number of people available to do the work. Now that it is possible for the organization to scale out to cloud-hosted databases, the constraints of the available infrastructure have ceased to govern the growth of the database estate....(more)

T-SQL

T-SQL Inline User-Defined Functions are Sargable - You might have heard that SQL Server user-defined functions are the devil. That’s because someone got burned. They put a scalar function in a WHERE clause on a big table and fed it a column name. And bad things happened. ...(more)

Pivot and Unpivot - One thing that I still get confused about writing is pivot queries. I find myself needing to lookup the syntax every time. Basically you use Pivot and Unpivot to change the output of a table. If you would like rows turned into columns you can use pivot and for the opposite you can use unpivot....(more)

SQL Server on Linux

SQL Server on Linux: How is Delete-On-Close Handled - Windows provides an option for CreateFile to delete a file when the file is closed (FILE_FLAG_DELETE_ON_CLOSE.)   Host Extension Handling Linux does not expose such an option as part of the open syscall.  Instead the host extension remembers that the ......(more)

Security news and thoughts

Police Are Seeking More Digital Evidence From Tech Companies - U.S. law enforcement agencies are increasingly asking technology companies for access to digital evidence on mobile phones and apps, with about 80 percent of the requests granted, a new study found. ...(more)

Move Over, ID Theft: Account Takeover Fraud Is Rising - Bad actors using account takeover fraud, or ATO fraud, are moving beyond the point of checkout. ...(more)

Geopolitics of Russia, China, U.S. Increasingly Affect IT Security - Kaspersky Lab’s move to Switzerland illustrates how the geopolitics of Russia et al. are influencing western countries wary of spying. ...(more)

R Language

Lesson 1 - What Is R? - Boy this is awkward. What is R? Hmmm...(thinking). Well, for now, let's say it's a programming language and leave it at that. Many months and posts from now, I may have a more enlightened definition. Vagueness might be a wise choice here--it masks my ignorance. However, I do know that R code can be entered from a command line interface. R code can also be written to and saved in script files that can be invoked from an R development tool. ...(more)

PowerShell

A PowerShell Pester Check for parsing SQL scripts - I like to write Pester checks to make sure that all is as expected! This is just a quick post as much to help me remember this script This is a quick Pester test I wrote to ensure that some SQL Scripts in a directory would parse so there was some guarantee ......(more)

Lesson Learned - Keep PowerShell Modules Consistent and Up To Date - This is a quick post to share something that happened on a project recently. We began to experience some intermittent issues with Azure Data Factory (V1) and it was ultimately related to an out-of-date AzureRM PowerShell module. What does ADF have to ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI Introduction: Power Query M Formula Language in Power BI Desktop — Part 6 - In this article of the Power BI series, Robert Sheldon demonstrates how to work with the Power Query M language to import and transform data....(more)

Power BI 101- Logging and Tracing, Part III - Power BI, like many Microsoft products, is multi-threaded.  This can be seen from the logs and even the Task Manager.  I know, I know…you’ve probably heard this part all before… The importance of this information, is that the logs will display Process ......(more)

Power BI for Grownups - The message in the Microsoft Business Applications Summit this week was very clear: Power BI is growing up. We’ve known for a while that Power BI is a great front-end tool for enterprise-scale tabular and multidimensional models Power BI Premium will ......(more)

Power BI Conditional Formatting By Another Column - In this video, Patrick shows you how to use Power BI conditional formatting by another column in Power BI Desktop. Using conditional formatting by another column can help you provide context which leads to faster insights. Power BI capability arrived ......(more)

Performance Tuning SQL Server

Should You Use Index Hints? - Watch this week’s post on YouTube One of the things that the SQL Server query optimizer does is determine how to retrieve the data requested by your query. Usually it does a pretty good job, which is a great because if it didn’t then we’d be spending ......(more)

Performance testing with DBCC DROPCLEANBUFFERS - DBCC DROPCLEANBUFFERS is a common practice when unit testing SQL Server performance on an isolated test instance. This allows one to evaluate different candidates for query, stored procedure, and index tuning based on execution times in a worst-case cold buffer cache scenario and provides better test repeatability by leveling the playing field before each test. However, clearing cache in this way has considerations one should be aware of....(more)

Don’t do these things in SQL Server - Recently Brent Ozar posted a link to the PostgreSQL “Don’t do this” page, which I am shamelessly reproducing below, re-tailored for a SQL Server audience....(more)

Internet of Things

An IoT Project in an Hour? Google Shows Off Cloud IoT Core - A Google senior developer advocate showed off elements of the company’s expanding IoT cloud functionalities.   ...(more)

DBA Tools

How I Configure SQL Server Management Studio - Ever go into Tools-Options? SSMS has a stunning number of options these days. Here are some of my favorites:...(more)

SQL Operations Studio: Keyboard Shortcuts, Actual Plans, & More - Last week I posted a quiz on SQL Operations Studio, a free, multi-platform tool from Microsoft. This tool is under active development and the features are improving by the day — which makes it a great time to start trying out the tool and see what you ......(more)

Database Design, Theory and Development

Could not find database ‘x’, or why good object names are important. - SQL Server provides a method for specifying object names that contain special characters – you simply wrap the name in square brackets, as in [This-Is-A-Valid_$_ObjectName]. However, simply because you can do a thing, doesn’t mean... The post Could not ......(more)

Data Warehousing

The Difference Between a Data Mart and a Data Warehouse - Click to learn more about author Gilad David Maayan. When an enterprise takes its first major steps towards implementing Business Intelligence (BI) strategies and technologies, one of the first things that needs clarifying is the difference between a ......(more)

Data Visualisation

Animating the Goals of the World Cup: Comparing the old vs. new gganimate and tweenr API! - Animating the Goals of the World Cup: Comparing the old vs. new gganimate and tweenr API! This is Part 3 of my series on “Visualizing the World Cup with R”! This is the culmination of this mini project that I've been working on throughout the World Cup. ...(more)

Four Features to Incorporate in Your Next Reporting Dashboard - As dashboards become increasingly commoditized, it’s easy to lose track of what makes them mission-critical business tools. The number of dashboard tools available is growing, and many are marketed to highlight ......(more)

Data Privacy and GDPR

Identifying HIPAA, PCI & SOX Data for Masking - Working for a company based in the UK (still currently a part of the EU) I had a lot of motivation to learn about the GDPR and what it means for data professionals. Further, the understanding that, through treaties and court precedent, the GDPR can apply ......(more)

Insurance for Data Privacy in the Digital Age - The future is data-driven. But given that colossal amounts of data are collected every day to support internet-enabled devices, new risks crop up all the time — risks that used to be almost exclusively the ......(more)

Data Mining/Data Analysis

What Are Data Trends and Patterns, and How Do They Impact Business Decisions? - Click to learn more about author Kartik Patel. In this article, we will focus on the identification and exploration of data patterns and the trends that data reveals. The business can use this information for forecasting and planning, and to test theories ......(more)

Computing in the Cloud (Azure, Google , AWS)

Azure DevOps: Show Me the JSON! - Creating resources in Azure, while not difficult, can be time consuming if you use the manual steps. One way to automate the process is by using JSON templates to save time and enforce standards. In this article, Brian Flynn describes how to create JSON templates for deploying a network with virtual machines. ...(more)

Harnessing the Power of Cloud - We are now living in a persistently connected state. Every single day, the systems we interact with produce vast amounts of data and deliver powerful services to users across the world. People, as well as business, are now a part of this 'digital' revolution. ...(more)

Introduction to Azure Cosmos DB Emulator for Creating Applications - Azure Cosmos DB is Microsoft’s NoSQL database platform running in the cloud. In this article, Suhas Pande explains many of the core concepts in Cosmos DB. Additionally, he goes over how to set up a local Cosmos DB emulator to create collections and documents. Using a local emulator is free and allows development with Cosmos DB without being connected to Azure....(more)

Backup and Recovery

Are your backup files where they say they are? - I was having a cleanup of some old scripts the other day when I stumbled upon a script I wrote some time ago.   I remember writing the script because from time to time I would get asked if I can restore a copy of a specific database to a particular point ......(more)

Azure SQL Database

Azure SQL Database Performance and Service Tiers Explained - Microsoft has two main purchase model pricing options for a single Azure SQL Database. If you are going to use Azure SQL Database, you need to decide which purchase model you prefer, and then decide which service tier meets your performance and budget needs. This choice is not permanent, since it is pretty easy to migrate to a different service tier later if your needs change....(more)

Azure SQL Vulnerability Assessment – now with PowerShell support! - You can now manage your SQL Vulnerability Assessments at scale using the new SQL VA PowerShell cmdlets. The cmdlets can be found in the Azure Resource Manager module, AzureRM 6.6.0, within the AzureRM.Sql package. Take a look at the AzureRM PowerShell ......(more)

Azure Runbook Configuration - Hello everybody! Greetings from the very warm state of Texas!  One of the most popular questions I get when talking about Azure SQL Database is how to configure Index and Statistics maintenance without a SQL Agent to execute jobs.  I normally recommend ......(more)

Azure SQL Data Warehouse and Data Lake

Introducing U-SQL database projects – U-SQL database development and deployment made easy (public preview) - Today we are pleased to introduce the U-SQL database project, a new project type in Azure Data Lake Tools for Visual Studio (ADL Tools for VS) that accelerates U-SQL database development, management and deployment. All objects (except for credentials) ......(more)

AI/Machine Learning/Cognitive Services

MTBC Brings Artificial Intelligence to Medical Practices - According to a recent press release, “MTBC, a leading provider of proprietary, cloud-based healthcare IT solutions and services, today announced launch and validation of the first phase of features supported by Allison, a voice-activated virtual assistant ......(more)

Administration of SQL Server

Using the built-in System Health session - When Microsoft introduced Extended Events (XE) in 2008, they also gave us a built-in XE session called system_health. This is a great little tool. I mainly use it for troubleshooting deadlocks as it logs all the information for any deadlocks that occur. ...(more)

Your SQL Servers are misconfigured - So are some of mine. So are 90% of the servers I run across, especially in mid-size businesses ( (more)

.NET Related Articles

Using Modal Dialog Boxes in Bootstrap 4 - Bootstrap provides an easy way to add modal dialogs to web pages. Dino Esposito explains how to create modal dialogs with Bootstrap and describes the improvements available with Bootstrap 4....(more)

Machine Learning with .NET - The .NET F# language can be used for machine learning. In this article, Diogo Souza explains what is needed in Visual Studio to take advantage of this feature and walks you through a simple regression example....(more)