Protecting Data from the Inside, with Database Weekly (10/19/2019)

 Database Weekly for October 19, 2019 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge Editorial  Protecting Data from the Inside It may seem unkind to suggest that your data is at risk from authorized users, either accidentally or maliciously, but insider threats are one of the major causes of data breaches. Let me give an example. In 2011, Britain's News of the World newspaper folded after 168 years of publication when various immoral or illegal practices of its journalists [were revealed](. The Wikipedia entry '[News International phone hacking scandal]( gives an excellent summary. Journalists, it was alleged, were equipped with phone numbers of people, working in large companies and government departments. For a fee, employees of these organizations would then access their database systems to divulge useful information about individuals. The journalists were, in this case, most interested in celebrities, politicians and the aristocracy but everyone was at risk. Some journalists boasted that a day's work on the phone could produce details of the personal life about almost anyone, often before the police. One journalist investigated by the police in 2002 reportedly bought information from former and serving police officers, customs officers, a VAT inspector and bank employees who had access to the data. They also employed 'blaggers' who would telephone the Inland Revenue, the Vehicle Licensing Agency, banks and phone companies, and deceive them into releasing confidential information from their records. However impervious to external attack your application and database may be, the data that your organization holds may still not be safe. It is not an option to trust solely in the probity of your internal users, or to [rely on their good sense with data](. How on earth do you guard against the theft of data within an organization? Firstly, determine what sensitive data you hold and why, and where it is stored or cached. If you hold sensitive data, then you must be able to identify individual authorized users of it. Next, you need a system that monitors usage that is entirely separate from the database system. It must log all accesses to the sensitive data. With all this in place, you can introduce a configurable alerting system that looks for any sort of unusual usage. The database must use [full access control]( so as to prevent any SQL queries from accessing tables that have sensitive data in them. I always recommend an interface of stored procedures and functions to allow applications to access this type of data, and to make monitoring easier. Through this interface, you can restrict access to sensitive data to just the minimum the user needs to complete their task. There should be a fine-grained range of database roles that match the roles of members of the organization, so that no user is able to access either more data than is necessary for the business operation, or data that is irrelevant or inappropriate. Data exports [must be restricted]( to aggregations done to the finest level of detail needed for reporting. A seasoned curator of data will never be a popular figure in the organization, because insider data breaches are seldom publicized and the extent of the threat is greatly underestimated, but it is certainly there. Phil Factor [Join the debate, and respond to the editorial on the forums](  [Redgate Database Devops]( 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. Vendors/3rd Party Products [Recreating Databases from Scratch with SQL Change Automation]( Phil Factor starts with the basics how to rebuild a set of development database from scratch, using SQL Change Automation, and then demonstrates how to check for any active sessions before rebuilding, import test data using BCP, and secure passwords if connecting to the target with SQL Server credentials. [Using the SQL Compare command line with Argfiles]( Phil Factor demonstrates how using XML argfiles to pass parameters to SQL Compare cuts out a lot of the tedious scripting involved in modifying a database schema comparison and deployment process, as required for each target database. [SQL Compare 14.0: A Command Line Interface on Linux and support for SQL Server 2019]( SQL Compare is the industry standard tool for comparing and deploying database schema changes, and version 14.0 now provides a Command Line Interface (CLI) on Linux (available as a beta, at time of writing). Teams working with SQL Server on Linux, or across multiple platforms, benefit from seamless development processes, driven by SQL Compare, without the expense or ‘friction’ of needing to maintain a Windows VM on their Linux servers. Administration [Recovery of database is 0% complete (approximately 1000000 seconds remain)]( Andy Mallon explains what happens during database crash recovery, and the need for patience. [SQL Server Needs Health Views]( Erik Darling Proposes that, in a centralized schema, maybe called health, there should be stored all the details of Backups (schedule, failures), CHECKDB (last good, failures), Agent Job Failures/Operators Notified, and so on. Azure SQL Database [Azure SQL Database Price and Performance vs Amazon RDS]( GigaOm recently published a performance benchmark of Azure SQL Database as compared to Amazon’s RDS service, but Joey D'Antoni wants to do a raw pricing benchmark - it turns out Azure SQL Database is cheaper. [JSON in your Azure SQL Database? Let’s benchmark some options!]( Silvano Coriani explore the options for loading and retrieving JSON data, in Azure SQL Database, and benchmarks the results. Computing in the Cloud (Azure, Google, AWS) [ADS – Did I Save My Changes?]( After working on a query for a long time, we want to make sure that we save the changes we have made. I have lost hours of work over the years because I didn’t save the changes. Azure Data Studio has a few features that can help prevent this from happening. Conferences, Classes, Events, and Webinars [Tactics for classification using SQL Data Catalog]( Wednesday October 23 16.00-17.00 BST/ 10.00-11.00 Central - Discover different tactics enabled by Redgate’s latest new product - SQL Data Catalog – to build a clear picture of your SQL Server estate, enabling you to ensure that the correct data is protected in the correct way. [What we learnt at PASS Summit]( Wednesday November 13 16.00-17.00 GMT/ 10.00-11.00 Central - PASS President Grant Fritchey is joined by fellow Microsoft MVPs Kendra Little, Steve Jones and Kathi Kellenberger to discuss their highlights and learnings from PASS Summit 2018, the largest Microsoft Data Platform conference. [How to make your 2020 monitoring strategy a success]( Wednesday November 20 16.00-17.00 GMT/ 10.00-11.00 Central - Are you ready for growth in 2020? Join Tony Maddonna, Microsoft Platform Lead & SQL Server Enterprise Architect at BMW Manufacturing and Redgate's Arneh Eskandari to discover how to prepare for successful estate growth in 2020 [SQL in the City Summits]( Redgate is hosting an upcoming series of exclusive Summits, in cities worldwide, to help senior data professionals deliver value quicker, while keeping their data safe. Find out more and register for your nearest event [SQLSaturday #934 - Rio de Janeiro 2019]( On Nov 23, 2019, a free day of SQL Server training in Brazil [SQLSaturday #895 - Parma 2019]( On Nov 23, 2019, a free day of SQL Server training in Parma, Italy. [SQLSaturday #925 - Porto, Portugal 2019]( On Nov 23, 2019, a free day of SQL Server training... [Redgate Database Devops]( Database Design, Theory and Development [SQL and Scope]( Understanding scoping rules is a basic skill for developers. In this article, Joe Celko gives a bit of the history of scoping in early programming languages and shows how scoping applies to SQL queries as well. DevOps and Continuous Delivery (CI/CD) [Introduction to DevOps: Database Delivery]( Before including the database in your DevOps pipeline, you’ll need to decide if you are going to use a state-based or migrations-based approach. In this article, Robert Sheldon explains the difference between them and discusses the benefits of each. [Establishing a DevOps Organisation]( DevOps might sound intimidating if your organisation has not yet begun on its DevOps journey. In this article, Rahul Varshneya explains some of the benefits and reviews the stages all DevOps organisations go through to achieve it. DocumentDB/Key-Value/Graph/other NoSQL Databases [Geneology with SQL Graph – I]( How many first/second/third cousins does this person have? If you have the genealogical data, SQL Graph can give the answers. ETL/SSIS/Azure Data Factory/Biml [Delta: A Data Synchronization and Enrichment Platform]( It is a commonly observed pattern for applications to utilize multiple datastores where each is used to serve a specific need. Now the challenge becomes how to keep these datastores in sync. [How Netflix microservices tackle dataset pub-sub]( In a microservice architecture such as Netflix’s, propagating datasets from a single source to multiple downstream destinations can be challenging. These datasets can represent anything from service configuration to the results of a batch job, are often needed in-memory to optimize access and must be updated as they change over time. [How to test ETL Processes in production]( Ed Elliott concludes his 4-part series by explaining what's involved in "testing in production”. HA/DR/Always On/Clustering [Availability Groups and Redo Latency, Data Committed on a Primary isn’t Visible on a Synchronous Secondary]( If you’re running availability groups it may seem like a great idea to offload your read only reporting workloads to a secondary. But, redo latency means that the data might not be instantaneously available on the synchronous secondary node. [Upgrading SQL Server using Availability Groups – Checklist]( A checklist, and some useful queries, for upgrading a pair of physical SQL Servers that hosted a single availability group of several terabytes of data, with minimal downtime. Performance Tuning SQL Server [Understanding Event Loss with Extended Events]( Jonathan Kehayias on how and when Extended Events will discard an event that has been generated during data collection. [Using SQL Server Performance Objects]( Thomas LaRock on the importance of the details that provide context to the PerfMon metrics, helping users understand what they are measuring, and where to find those details. [SQL Server Needs Performance Views]( Erik Darling wants every database to have in it views to fully assemble data from the mess of DMVs that accompany query store, plan cache use, index use and so on. [Getting Smart About Fixing Key Lookups]( Creating smart indexes, or occasionally rewriting the query, to fix performance e problems related to key lookups. PowerPivot/PowerQuery/PowerBI [Why Does Power BI Query My Data Source More Than Once?]( Of you find that Power Query is indeed querying your data source multiple times, the most common explanation is that it’s doing a “background refresh”. [Showing the top 5 products and Others row]( How to add an additional “other” row to the selection obtained using the Top N filter in a Power BI report. [Updated Data Profiling in Power BI Desktop]( One of the big things that was lacking with the Data Profiling feature was the text length statistics, leading to incorrectly sized string columns in data warehouses. Well, the wait is over, text lengths are now available. [Power BI Gateway — When Your PowerBI.com Login Doesn’t Match Your Active Diectory User Principal Name (UPN)]( With some data sources, such as Analysis Services, you want to pass the username of the person running the report back to the server executing the query, but if your Power BI login does not match a UPN in our local Active Directory, then the lookup will fail… [Power BI dataflows and query folding]( Matthew Roche tries to clear up a few confusions surrounding use of dataflows and query folding. PowerShell [Making SQL Agent Jobs Availability Group aware with dbatools]( How to you make sure that your SQL Server Agent jobs only do work on the instance that currently owns the Availability Group. [PowerShell Notebooks in Azure Data Studio]( Create and share live PoweShell code! [Getting SQL Server installation date with PowerShell using dbatools]( Finding out when SQL Server was installed across all your instances. Product Upgrades and Releases [SQL Server 2019 Standard Edition Feature Limitations Are Out]( Microsoft have published the features list by edition, for SQL Server 2019 (but are still editing it!) Reporting Services [Branding PowerBI Report Server & SSRS 2017]( One of the benefits of moving beyond 2014 (with SSRS) is that the appearance of the web portal (company logo & color scheme) can be customized (branded) using the same files that are used for branding PowerBI Report Server (PBIRS). SQL Server Security and Auditing [Find Embedded SQL Server Logins in Jobs, Linked Servers or SSISDB]( A database login or user (SQL Login, Oracle User, etc.) is actively being used and embedded in code, but the password is about to change. How can you find all of the places where it exists with SQL Server such as Jobs, Linked Servers and SSIS information stored on the server? Security News and Issues [Cracking the Passwords of Early Internet Pioneers]( Lots of them weren't very good. Weakest of all was the password for Unix contributor Brian W. Kernighan: "/.,/.," representing a three-character string repeated twice using adjacent keys on a QWERTY keyboard. T-SQL [Impact of UTF-8 support in SQL Server 2019]( In SQL Server 2019, there are new UTF-8 collations, that allow you to store your UTF-8 data natively. But what is the actual storage impact, and how does this affect memory grants and query performance? [What Does PERCENTILE_CONT Do?]( Kathi Kellenberger takes on a logic puzzle in trying to understand how the windowing function, PERCENTILE_CONT, works. [Using indexed views? What is an imprecise or non-deterministic convert?]( A short but interesting side-road into deterministic values, and why it is important to get your data types correct. [What the heck is the SQL Server Version Store?]( Andy Malllon takes a high-level look at what it is, what it does, what it isn't [Are Stored Procedures Faster Than Stand-Alone Queries?]( Does a query embedded in a stored procedure execute faster than that same query submitted to SQL Server as a stand alone statement? [How to Think Like the SQL Server Engine: Adding a Nonclustered Index]( Brent Ozar continues his series helping us to understand what indexes SQL Server needs to run queries efficiently, by thinking like it. [Finding Your Isolation Level]( If you're troubleshooting SQL Server or Azure SQL Database performance issues that you think are related to concurrency, it's useful to know what isolation level is being used to execute a query. Bob Pusateri explains how to find out. [Ranking Functions in SQL Server]( Suppose you are designing an SQL Server database application for a company’s CEO and you have to display the fifth most highly paid employee in the company. What would you do? Testing Software [SQLCover 0.5 - Fixes, smaller features and an exciting surprise]( Ed Elliott updates SQL Cover, his open source SQL code coverage library.  [RSS Feed]( 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. 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 follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -