Connecting - Level 4 of the Stairway to Azure SQL Database (SQLServerCentral 9/26/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Connecting - Level 4 of the Stairway to Azure SQL Database]( - [Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA]( - [SQL Server MERGE statement usage and examples]( - [Steps for SQL DB Geo Replication]( (From the SQLServerCentral Blogs) - [Faking Temporal Tables with Triggers]( (From the SQLServerCentral Blogs) The Voice of the DBA The Uninteresting But Necessary Work There has been a rising tide of data legislation in the last few years that asks for organizations, especially private companies, to better protect their data. The GDPR is one of the most well known, taking effect with regards to enforcement earlier this year, and I've been doing quite a bit of work in relation to this law. There are plenty of other laws, such as [California's CCPA](, [Australia's NBD](protection/New+Australian+mandatory+data+breach+laws), [Japan's APPI]( and more that we ought to be aware of as data professionals. These laws affect personal data about people in a variety of ways, and they can affect how we process and use portions of the data we store. It's not as simple as it might sound to change our data handling practices. In fact, it might not be that easy for many of us to do this now unless we've actually done something in advance: we need to have classified our data. We need to understand the impact of the various columns in our tables, the exports of flat files or reports, and even the development processes that make copies of our production databases. I'll be honest, classification work is mind-numbingly boring and uninteresting. This almost feels like busy work to me, especially once we get past the obvious tax IDs and birthdays of people. When we start examining other data, the task feels like it ought to be delegated to junior staff, but many of them lack the experience to make the decisions. What can be more frustrating is that most of them lack the status to get others in the organization to respond to questions, which means the task ultimately falls on more senior people. This also means they often do it once and then forget it, leading to out of date information. We don't like doing classification, but we need to do it. Without having some mechanism that allows us to determine if data can be moved or used in another system/database/report/etc., we end up just ping-ponging around. We assume all data is sensitive and try to lock it all down. That leads to complaints, as well as staff working to circumvent the rules until they appear meaningless. At this point we might give up on controlling data and just trust people. That leads to audit problems, potential data loss from security incidents, and plenty of embarrassment about why we didn't implement some simple controls. Then the cycle starts again. Classifying data is simple in some ways, but not easy to ensure the data is available, up to date, and easy to find for any size team. I've seen simple solutions that rely on spreadsheets. I've seen complex software packages that are expensive and cumbersome to implement with other applications. Microsoft has started to help with [a few changes in SSMS](, but this doesn't seem like a long term solution, though [SQL Server 2019 might help](. Redgate has spent [some]( [time]( on this as well, thinking about the issue and we have [an early access program now](. All of these are partial solutions that might work for some organizations, but not all. Ultimately, this is something like security, that we ought to be building into our systems from day one. Every proof-of-concept or prototype ought to be classifying data from the beginning. We won't be perfect, and won't get every label correct, but if we're always thinking about the data, we can always correct our label and more tightly or loosely decide to handle data. I'd also like to think that if we conservatively label the data early, we're unlikely to get into positions where we are mishandling data in a way that makes it more likely that we accidentally lose data. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 6.0MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [SQL Change Automation]( CI/CD for your SQL Server database Feeling the pain of managing and deploying database changes manually? Redgate SQL Change Automation completes your database delivery process by building, testing, and deploying the database changes you and your team check into version control. [Try it free]( [GDPR](www.red-gate.com/gdpr?utm_source=ssc&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20180926-slot2) How to make your SQL Server development GDPR ready Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. [Find out more about how our tools help with GDPR compliance](www.red-gate.com/gdpr?utm_source=ssc&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20180926-slot2) Featured Contents  [] [Connecting - Level 4 of the Stairway to Azure SQL Database]( Arun Sirpal from [SQLServerCentral.com]() Learn how to configure connectivity and firewall rules for your Azure SQL Database[More »](SQL+database/177945/) ---------------------------------------------------------------  [] [Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA]( Press Release from [Redgate]() Three SQL Server MVPs (Jonathan Kehayias, Ted Krueger and Gail Shaw) provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks".[More »]( ---------------------------------------------------------------  [] [SQL Server MERGE statement usage and examples]( Additional Articles from [MSSQLTips.com]() In this tip we look at how to use MERGE compared to running separate INSERT and UPDATE statements.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Steps for SQL DB Geo Replication]( SQLRUs from [SQLServerCentral Blogs]( Unless you are living under a rock, you have most likely heard of the hurricane that is bearing down on...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Faking Temporal Tables with Triggers]( Bert Wagner from [SQLServerCentral Blogs]( This post is a response to this month’s T-SQL Tuesday #106 prompt by Steve Jones. T-SQL Tuesday is a way...[More »]( Question of the Day Today's Question (by Steve Jones): I have a dataframe, pass.videos, of the PASS videos from 2016. I can't remember what the structure of the dataframe is, but I want to just get the first 4 rows to see the column names and data. How can I do this in R in order to return this data? Index SessionSID Session Speaker 1 1 65091 DevOps Tool Combinations for Winning Agility Kellyn Pot'Vin-Gorman 2 2 65092 Oracle vs. SQL Server - The War of the Indices Kellyn Pot'Vin-Gorman 3 3 65112 Make Power BI Your Own with the Power BI APIs Steve Wake 4 4 65117 A Deep Dive into Data Lakes Ust Oldfield Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: R Language. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload. Pick up your copy of this great book today at [Amazon]( today. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I have this table: CREATE TABLE UserConfig ( UserConfigKey INT IDENTITY(1,1) NOT NULL CONSTRAINT UserConfigPK PRIMARY KEY , UserID INT , IsActive BIT SPARSE , IsSubscriber BIT SPARSE , DefaultQuantity INT SPARSE , Options XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ) GO I decide to run this query. How many columns are returned? SELECT * FROM dbo.UserConfig AS uc Answer: 3 Explanation: When you use SELECT * with a table that has a column set, only the non-sparse columns are returned with the column set. Sparse columns are not returned. In this case, the result is: UserConfigKey UserID Options ------------- ----------- ---------------------------------------------------------------------------------------- 1 1 1 10 2 2 1 1 5 3 3 1 1 5 Ref: Use Column Sets - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Administration]( [ProActive DBA projects]( - Hello All, Thank you for looking into my question. I am managing SQL Server 2017 Enterprise instances on VMware ESX with... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Update Trigger to modify two tables]( - Hi folks, I'm trying to update 2 tables (one audit table and the main table with the most recent username) with... [Referential Integrity vs. Performance]( - I haven't posted in this forum for a while so please forgive me if my post question isn't in the... [Obfuscating values (via T-SQL) in several Varchar, Char, and Datetime columns ?]( - Is there any good T-SQL solution that you can recommend for obfuscating a dozen of Char and Varchar fields in... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [User Permission Reporting]( - We would like to get a list of all db users and their permissions and report on them.  Are there... [e-commerce company, planning to start a sale offer in december]( - Hi Experts, We are a small e-commerce company, planning to start an offer in december. The offer would be online on... [Job schedule with availability groups ( 4 nodes )]( - Hello, We seem to be having an issue of a SSIS job running on all nodes instead of only the first... [Adding SSISDB to AlwaysOn Availability group is not working]( - Hi there I have an AlwaysOn Availability Group and am trying to add the SSISDB to it. I am following the... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [BCP command executing in Putty]( - Hi Excerpts, I would require your help on below issue.I am new in MSSQL. My file is present in Linux server and... [How to use HASHBYTES function in sql server for multiple columns]( - I have a requirement wherein I have to create hashvalue which consist of all columns of a table. With Checksum... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Mirroring]( - Working on setting up SQL Server mirroring. Following are the steps I am thinking to do.. Take Full Backup and Transaction... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Pulling Top XX rows, where XX is a percentage of records]( - it never stops around here. . . lol My clients brings in between 20,000 and 30,000 SKUs a month. He wants me to audit... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Cannot drop the user 'dbo']( - I used to know how to do this, but nothing I try is working. My personal login is associated with a... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Regex in Replace?]( - Hello. I'm trying to turn something like '{1,2,3,4,5}' into '1,2,3,4,5'. I can't be certain that the curly braces will always... --------------------------------------------------------------- [SQL Server 2008]( : [Data Corruption (SS2K8 / SS2K8 R2)]( [Corrupted innodb table crashing mysql instance how to recover table?]( - Hello, Running a simple query against corrupted innodb table is crashing mysql instance . table test.xyz got corrupt during crash and truncate... --------------------------------------------------------------- [Programming]( : [Powershell]( [How to output everything to a log file in Invoke-SqlCmd]( - I have powershell script to run database scripts below, but I can't get it to output to my logfile the... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Starting a TeamCity build from SSIS via the TeamCity ReST API]( - Has anyone managed to initiate a TeamCity (TC) build from an SSIS script task? This is quite a simple task in... [TFS / SSIS - Get Latest Version not showing all dtsx files Solution Explorer]( - So myself and two other co-workers are using Visual Studio 2015 connected to TFS. When they add a package to... --------------------------------------------------------------- [SQL Server 2005]( : [Administering]( [Page Life Expectancy - dmv counters give occasional massive spikes]( - THIS SHOULD REALLY BY IN SQL2008 FORUM - DON'T KNOW HOW I POSTED IT HERE! Darn. I'm collecting performance stats on one... [SQL performance counter shows 0 as values]( - Environment: sql server 2005 enterprise edition on windows server 2003. Perfmon counters fro sql was working before and recently stopped working.... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com