Backup Performance Testing; HIPAA and SOX(Database Weekly 7/30/2018)

The Complete Weekly Roundup of SQL Server News In this issue: Vendors/3rd Party Products - [Join Redgate’s SQL data catalog early access program](#32665) T-SQL - [Merge Statements](#32703) - [Database Fundamentals #18: The SELECT Statement](#32686) - [Querying Data in Temporal Tables](#32682) - [CROSS APPLY in T-SQL: Why to APPLY](#32671) Tech News - [UN Environment and Google Announce Ground-Breaking Partnership to Protect our Planet](#32668) SQL Server Security - [Managing SQL Logins in different environments](#32681) SQL Server on Linux - [Taking the Linux plunge](#32661) Software Development - [How to Successfully Deliver IT Projects in a Not-so-Agile Organization](#32664) Security news and thoughts - [Why No HTTPS? Here's the World's Largest Websites Not Redirecting Insecure Requests to HTTPS](#32697) - [A Brief History of Data Security](#32666) R Language - [Real-time data visualization using R and data extracting from SQL Server](#32688) Product Reviews and Articles - [SQL Prompt code analysis: avoid non-standard column aliases](#32679) PowerPivot/PowerQuery/PowerBI - [Power BI 101- Logging and Tracing, Part II](#32676) Performance Tuning SQL Server - [Extended events (XE): Create a simple XE](#32685) - [Performance Myths: The query result cache](#32669) - [Dangerous moves: Setting max size for In-Memory OLTP containers](#32659) - [What To Do When Wait Stats Don’t Help](#32658) Microsoft News - [The Ability Hacks: Microsoft employees hack to make tech more accessible](#32693) - [Microsoft delivers public previews of its Office 2019 server products](#32692) HA/DR/Always On/Clustering - [SQL Server Availability Groups – Enhanced Database Level Failover](#32699) - [AlwaysOn_health Extended Event Session](#32683) - [Why Availability Databases Are Not Synchronizing?](#32663) ETL/SSIS/ELT - [SSIS Script Task:Error: The Script Task is corrupted.](#32673) DevOps and Continuous Delivery (CI/CD) - [Get started with DevOps](#32694) Database Design, Theory and Development - [Jump to Start Test-Driven Database Development (TDDD) – Part 2](#32698) Data Science - [Common Mistakes to Avoid When Learning to Code in Python](#32690) Data Privacy and GDPR - [Forget GDPR. Think HIPAA, SOX, PCI, SHIELD and the CCPA.](#32701) - [Information Security in Practice](#32700) Conferences, Classes, and Events - [VMworld 2018 SQL Server Sessions](#32684) Computing in the Cloud (Azure, Google , AWS) - [Google is Building a Version of Kubernetes Engine for On-Prem Data Centers](#32677) - [Creating SQL images in Azure with ACR Build – Part Two](#32675) - [How Azure Data Sync Helps Businesses Develop Cloud Migration Strategy](#32662) Career Growth - [Industry Practice Is No Substitute for Foundation Knowledge](#32674) Backup and Recovery - [Backup Performance Testing](#32702) Azure SQL Database - [Next on the Menu – event file target for Azure SQL Database](#32695) - [PsPing your Azure SQL Server](#32687) Azure SQL Data Warehouse and Data Lake - [Build hybrid cloud analytics solutions with ADLA Task in SSIS](#32672) - [New eBook - Data Lakes in a Modern Data Architecture](#32656) AI/Machine Learning/Cognitive Services - [A quick tour of AI services in Azure](#32691) - [Machine Learning Results in R: one plot to rule them all! (Part 2 – Regression Models)](#32689) - [Big Tech is Throwing Money and Talent at Robots for the Home](#32678) Administration of SQL Server - [SQL SERVER – Quick Introduction to Startup Procedures](#32696) - [Mimic Production in Development](#32680) - [SQL Server Database Corruption! Understanding and Diagnosing](#32660) [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 2018-07-30 [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=dbw&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20180728-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=dbw&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20180728-slot2) [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more]( Editorial - Why Volunteering May be a Bad Idea for You Recently, as I was doing some work for the upcoming PASS Summit, a question popped in my head: “Why am I doing this?” It reminded me of the time when I was young (and idealistic). I went to the leader of a non-profit group asked about making it my permanent vocation. The leader looked at me, and asked the following question: “Is there anything else you feel an equal or greater desire to do? If so, try that first.” Taken aback, I answered that I was getting interested in database work from my current job. The rest is a twisted historical tale, but I never did find out if this person sensed I was unsuited for this vocation that shares a lot of similarities with volunteering, or if it was just the question that was asked of everyone. However, after 20 or so years in the SQL Community, I know the reason the question needed to be asked. One of the things that almost anyone coming to a local meetup, national conference, SQL Twitter account, or blogs will be inundated with is “Do you want to get involved? Why don’t you speak at our event? Why don’t you start an event? Are you on Twitter, if so, check out #sqlhelp, if not, ‘why not’?” If you are considering giving in to the call for volunteering in the SQL community, you need to ask yourself that question I was asked. “Is there anything else you would rather be doing?” If so, do it, and don’t feel bad about it. This goes for event organizing, speaking, taking lunch tickets at a SQL Saturday, picking sessions, writing blogs, answering questions, whatever you are considering doing. If your undeniable desire is to be elsewhere, you are going to be a terrible volunteer. If you have ever worked with volunteers, you will recognize that a terrible volunteer is worse than no volunteer at all. Thinking you have a staff of people to help you achieve a task is great until helpers vanish into the ether, and you end up doing the work of 15 alone. Now that I have your attention, realize my point isn’t to dissuade everyone else from volunteering. That would leave me with more work than I could imagine! No, the point is that that you need to understand the costs to you before committing, so that your commitment will be true. Any gift of time you provide to the community will certainly have lasting value to others, who, like yourself, have vocational needs that are most likely going to be met by the kindness of strangers. Just be wary that, as you volunteer to do more and more, you are possibly going to miss things like: - Children’s extra-curricular activities – As a prime example, last year’s PASS Summit was on Halloween. - Events, like sporting events, concerts, etc. – Sometimes you may want to go see that new Marvel film that just came out, so you avoid spoilers. - Exercise – For many years, this plagued me. I worked during the day, and then volunteered at night, and my waistline expanded. - Sleep – Sometimes, if you can’t give up something, you just lose sleep. Sleep is really important and missing it will catch up to you in the long run. A side benefit of volunteering in the community is that you often end up with another group of people you call family and friends who are also giving up part of their life to assist others, including you, with database needs. The SQL community is filled with amazing people and these friends will often assist you in ways that are amazing such as: - Having people that you can ask questions about SQL that will help you out anytime, and usually getting a better answer than you can pay for - Assist with finding that next work opportunity As a final consideration, understand that volunteering in the SQL Community is a largely thankless act. You may get a pat on the back, a free meal, and maybe even an award. No matter how hard you may try, there are only going to be a few people who attain a level of fame like Brent Ozar and end up making an amazing living because of their community involvement. The pay for most of us is beyond terrible. I have always expected to get somewhat less than $0 (including expenses), and I have rarely been surprised or disappointed. Most people in the SQL Community could make better money by working a spatula at Wendy’s, and the hours would probably be more predictable. The question I asked myself to open with last Saturday night: “Why am I doing this?” was not the first or last time I will think this, but so far, through all the years my reply to myself is: “because it is worth it”. Louis Davidson (@drsql) from [SQLServerCentral.com]( » [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 [] [Join Redgate’s SQL data catalog early access program]( - Redgate wants your help to develop a SQL Server data catalog 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)]( T-SQL [] [Merge Statements]( - Over the years I have come across a lot of merge statements and I find most of the time there is one fundamental flaw in how it is written by most people. In the update section in most cases I find that there is no section there to check if anything ......[(more)]( [] [Database Fundamentals #18: The SELECT Statement]( - In my previous Database Fundamentals post, I showed you how to use the Query Designer to build a query. That was a SELECT statement. The basic construct of all your SELECT statements will be the same. You’re going to define a list of columns, the table ......[(more)]( [] [Querying Data in Temporal Tables]( - Following on from my previous post about modifying data in a temporal table, I will be looking into querying the data in this post. When you want to get latest (actual) state of data in a temporal table, you can query the same way as you query a normal ......[(more)]( [] [CROSS APPLY in T-SQL: Why to APPLY]( - Why use APPLY? Michelle asked a great question on the quiz page: In your own words, why would one want to use a cross apply operator rather than a join operator? I’m old school, and I’m just not getting why a cross apply would be so much better to use than a join....[(more)]( Tech News [] [UN Environment and Google Announce Ground-Breaking Partnership to Protect our Planet]( - A recent press release reports, “UN Environment and Google announced today a global partnership that promises to change the way we see our planet. Combining environmental science, big data and unprecedented accessibility, this joint effort aims to expand ......[(more)]( SQL Server Security [] [Managing SQL Logins in different environments]( - In this post I want to show you how we can manage SQL Server logins to avoid a very common problem Speaking of security, in SQL Server we have the possibility of authenticating in two different ways, one it is based on Windows Authentication and allow ......[(more)]( SQL Server on Linux [] [Taking the Linux plunge]( - I’m a Windows guy. Kinda have had to be, given my profession as a SQL Server DBA. But recently I’ve become a bit discontent with Windows. Nothing out of the usual has contributed to this, just things like UAC, updates etc. Now in the past, I’ve had to ......[(more)]( Software Development [] [How to Successfully Deliver IT Projects in a Not-so-Agile Organization]( - Agile methodology for software delivery is accepted at many organizations but is not used everywhere. In this article, Mohammad Rizvi gives advice on how to successfully implement a software project in a non-Agile environment....[(more)]( Security news and thoughts [] [Why No HTTPS? Here's the World's Largest Websites Not Redirecting Insecure Requests to HTTPS]( - Presently sponsored by: Matchlight by Terbium Labs: Know when your exact data appears on the dark web. Schedule a meeting during Black Hat to learn more!As of today, Google begins shipping Chrome 68 which flags all sites served over the HTTP scheme as ......[(more)]( [] [A Brief History of Data Security]( - Data Security describes the protection of digital data from a cyberattack or a data breach. A data breach is the unauthorized opening of data, typically to read or copy the information. Stolen data may contain confidential information, such as customer ......[(more)]( R Language [] [Real-time data visualization using R and data extracting from SQL Server]( - In the previous post, I have showed how to visualize near real-time data using Python and Dash module. And it is time to see one of the many ways, how to do it in R. This time, I will not use any additional frames for visualization, like shiny, plotly ......[(more)]( Product Reviews and Articles [] [SQL Prompt code analysis: avoid non-standard column aliases]( - There was a time when column aliases in SQL Server and Sybase were only declared by an assignment expression, and some people are still fond of the old way of doing it, in much the same way as some of us still like line-dancing, or singing madrigals ......[(more)]( PowerPivot/PowerQuery/PowerBI [] [Power BI 101- Logging and Tracing, Part II]( - So we went over locations and the basics of logging and tracing in Power BI. I now want to know how to make more sense from the data. In Oracle, we use a utility called TKProf, (along with others and a number of third party tools) to make sense of ......[(more)]( Performance Tuning SQL Server [] [Extended events (XE): Create a simple XE]( - When someone says still uses SQL Profiler.Image is taken from hereAn ancient, dark place, a source of many legends and histories, although some people have been able to get out alive, some others are stuck there, the locals refer to it as "The nosy one" ......[(more)]( [] [Performance Myths: The query result cache]( - I was recently doing a training session when a developer commented that it was OK to run an expensive query twice because on the second execution, SQL Server would use the “results cache” and be “practically free”. It’s not the first time I’ve heard ......[(more)]( [] [Dangerous moves: Setting max size for In-Memory OLTP containers]( - I recently saw a thread on twitter, where the OP talked about setting the max size for an In-Memory OLTP container. I responded as I always do: it’s not possible to set a limit on anything having to do with storage for In-Memory OLTP. Unfortunately, that’s not correct: through SSMS or TSQL, you can in fact set a max size for a container. ...[(more)]( [] [What To Do When Wait Stats Don’t Help]( - Some SQL Server workloads are slow even though there aren’t any hints in the wait stats that suggest ways to make them go faster. This blog post works through a columnstore example of such a workload....[(more)]( Microsoft News [] [The Ability Hacks: Microsoft employees hack to make tech more accessible]( - The Ability Hacks shares the "behind-the-scenes" stories of the Ability EyeGaze Hack team and the Learning Tools Hack team  This week at the Microsoft One Week Hackathon, we will witness employees from around the company work together to “hack” solutions ......[(more)]( [] [Microsoft delivers public previews of its Office 2019 server products]( - The 2019 releases of on-premises Exchange Server, SharePoint Server and Skype for Business Server, which are due out before the end of 2018, are now available in public preview form. ...[(more)]( HA/DR/Always On/Clustering [] [SQL Server Availability Groups – Enhanced Database Level Failover]( - Database level health detection failover (DB_Failover) option for Availability Groups was introduced in SQL Server 2016 with the objective to provide a mechanism for availability groups to failover, if one or more databases in the availability had any issues. This feature helps guarantee the high availability for your databases and is a recommended best practice for all availability Groups with mission critical databases. This Microsoft document describes the database level health detection failover option in detail. In its initial implementation the database level health detection option was designed to check the following conditions on the primary replica of the availability group....[(more)]( [] [AlwaysOn_health Extended Event Session]( - The AlwaysOn_health XEvent session is installed by SQL Server by default, and started by the Create Availability Group wizard when a new availability group is created. AlwaysOn_health Extended Event Session The xEvent session collects events triggered ......[(more)]( [] [Why Availability Databases Are Not Synchronizing?]( - If you are working on SQL Server Availability Group Databases, you may have seen that Availability Group is online but at-least one availability database at the primary or the secondary is not synchronizing. In the blog, we are going to explore how to ......[(more)]( ETL/SSIS/ELT [] [SSIS Script Task:Error: The Script Task is corrupted.]( - There are probably a lot of SSIS corruption errors, but that is one that is very easy to solve. The whole error message is texted like this: Script Task:Error: The Script Task is corrupted. Script Task:Error: There were errors during task validation. Script Task:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task "ST_74aca886806a416fa34ae89cac6237c2" uses version 15.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services. at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)...[(more)]( DevOps and Continuous Delivery (CI/CD) [] [Get started with DevOps]( - I'm starting to put my own head into the vast world of DevOps myself and over the next few months I'll be sharing resources I find interesting and useful along the way. As a starting point, you might want to look this short video: Get started with GIT and ......[(more)]( Database Design, Theory and Development [] [Jump to Start Test-Driven Database Development (TDDD) – Part 2]( - We discussed the basics of test-driven database development (TDDD) with examples and compared it with traditional database development in the first part of this article. In the second part, we are going to move beyond basics to focus on a more realistic scenario of meeting report requirements by using TDDD....[(more)]( Data Science [] [Common Mistakes to Avoid When Learning to Code in Python]( - Python is one of the simple program language one can learn and it’s a very flexible, object oriented language when it comes to syntax. Python created a new revolution in the coding segment. Coding is joy. Coding is fun.Coding is everything to programmers, ......[(more)]( Data Privacy and GDPR [] [Forget GDPR. Think HIPAA, SOX, PCI, SHIELD and the CCPA.]( - The introduction of the GDPR in Europe caused a lot of companies to start thinking more seriously about data privacy. It also prompted some American companies to reconsider doing business in Europe, and US-based websites like the Los Angeles Times and the New York Daily News have actually blocked access to EU visitors....[(more)]( [] [Information Security in Practice]( - Along with the GDPR, regulations require that confidential data is protected and used properly. In this article, William Brewer discusses the ways that data manages to migrate around the organisation and the challenges found in protecting that data....[(more)]( Conferences, Classes, and Events [] [VMworld 2018 SQL Server Sessions]( - I hope to see you all at this year’s VMworld 2018 USA conference where I’m lucky enough to have been selected to present four SQL Server-themed sessions! The first is an all-day boot camp (VAP3768WU) with Oleg Ulyanov from VMware Corp. where we take ......[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Google is Building a Version of Kubernetes Engine for On-Prem Data Centers]( - Google Cloud is building a version of its Kubernetes Engine service users can run in their own data centers • Currently in alpha, GKE On-Prem is meant to give enterprises a consistent way to manage their application infrastructure in-house and in the ......[(more)]( [] [Creating SQL images in Azure with ACR Build – Part Two]( - In Part One I detailed how we can use ACR Build to push SQL Server docker images to the Azure Container Registry. But that all seems a bit manual, doesn’t it? One of the cool things about ACR build is that we can hook it into GitHub so when we commit ......[(more)]( [] [How Azure Data Sync Helps Businesses Develop Cloud Migration Strategy]( - Microsoft's new Azure cloud platform tool brings on premises even closer to the cloud, helping companies develop their cloud migration strategy. ...[(more)]( Career Growth [] [Industry Practice Is No Substitute for Foundation Knowledge]( - “A short time ago a colleague asked me where he could find a "Databases 101" guide for the non-technical professional. As it turns out, the internet is littered with information, and mis-information, regarding data and databases. This makes it difficult ......[(more)]( Backup and Recovery [] [Backup Performance Testing]( - Reliable database backups are perhaps the single most important aspect of a Database Administrator’s job. The business risk from data-loss that can occur without a reliable database backup are substantial enough to bring many smaller... The post Backup ......[(more)]( Azure SQL Database [] [Next on the Menu – event file target for Azure SQL Database]( - In this SQL Snacks™ we will examine the techniques required to implement the file target for an Azure SQL Database. We will also examine the differences in the Extended Event system between on premises and Azure SQL Database. The link with the instructions ......[(more)]( [] [PsPing your Azure SQL Server]( - PsPing tool is part of the sysinternals PsTools download found –  ( This is the tool of choice when wanting to find out latency to your Azure SQL Server. In addition to standard ICMP ping ......[(more)]( Azure SQL Data Warehouse and Data Lake [] [Build hybrid cloud analytics solutions with ADLA Task in SSIS]( - Today, we are pleased to announce new support for the Azure Data Lake Analytics Task (ADLA Task) in the Azure Feature Pack for Integration Services (SSIS). The ADLA Task enables you to easily extend your existing SSIS workflows with big data compute capability in the cloud powered by ADLA. More and more customers are storing large amounts of raw data in the cloud. At the same time, some customers continue to process the data on-premises due to legacy or security constraints. Since the movement of big data is very costly, it is a good choice to transform the born-in-the-cloud big data into reasonable size in the cloud, and then only move aggregated data off the cloud and integrate it with existing on-premises data sources....[(more)]( [] [New eBook - Data Lakes in a Modern Data Architecture]( - This is a quick announcement of a new resource available published to the BlueGranite site.If you're interested in data lakes, you might want to check out an updated ebook just published to the BlueGranite site. It's called "Data Lakes in a Modern Data ......[(more)]( AI/Machine Learning/Cognitive Services [] [A quick tour of AI services in Azure]( - If you're after a quick overview of some of the services available in Azure to build AI-enabled applications, you might want to check out the 6-minute video below. It provides a tour of three services: Azure Cognitive Services, pre-trained machine learning ......[(more)]( [] [Machine Learning Results in R: one plot to rule them all! (Part 2 – Regression Models)]( - Given the number of people interested in my first post for visualizing Classification Models Results, I’ve decided to create and share some new function to visualize and compare whole Linear Regression Models with one line of code. These plots will help ......[(more)]( [] [Big Tech is Throwing Money and Talent at Robots for the Home]( - Behind the scenes, big tech companies are funding secret projects to develop robots. ...[(more)]( Administration of SQL Server [] [SQL SERVER – Quick Introduction to Startup Procedures]( - In today’s blog we are going to talk about the SQL Server feature which actually exists for a quite a while but interestingly enough it is not known much. Let us have a quick introduction to startup procedures. In SQL Server we can mark any procedure ......[(more)]( [] [Mimic Production in Development]( - Attempting to debug production performance problems in your development environment can be problematic in many ways, leading to a frustrating troubleshooting experience. One very common situation is the resources on the development environment are substantially... The ......[(more)]( [] [SQL Server Database Corruption! Understanding and Diagnosing]( - Of all the things that can ruin your day as a DBA and possibly get you fired, database corruption ranks right up there with backups and security enforcement of the database. Database corruption can be a resume generating event! A corrupt database can affect system stability, lead to unnecessary downtime, and possibly the loss of some data. To manage the risk you must know what database corruption is, how to diagnose, how to fix, and how to prevent....[(more)]( --------------------------------------------------------------- Administrative To be removed from this list, please click [here]( Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can [change your profile]( or follow the instructions on the daily newsletter. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com