SQL Features that Time Forgot, SQL Server and Data Governance, and Why is My Database Application so Slow? - (Database Weekly 8/26/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Virtualization and Containers - [Docker images for custom SQL Server Installations](#29762) Vendors/3rd Party Products - [SQL Server and Data Governance](#29751) - [SQL Census update: new server view](#29749) - [Register for the Redgate Hub Launch Livestream on September 6](#29748) T-SQL - [Learn SQL Server Starter Pack](#29775) - [Generate a Temporary Table Definition to Match the Resultset of a Query](#29774) - [Automated Identification and Graphing of SQL Dependencies](#29759) - [Finding all Palindromes Contained in Strings with SQL](#29758) - [Query Store functionality in earlier versions of SQL Server](#29757) - [Multi-Statement TVFs in Dynamics CRM](#29756) Testing Software - [Why don’t you unit test SQL Server code?](#29779) SQL Server Security - [Token-based authentication including Multi-factor auth for Azure SQL DB using Azure Active Directory](#29780) SQL Server Internals - [The SQL Server Features that Time Forgot](#29752) Software Development - [Why is My Database Application so Slow?](#29781) PowerShell - [Enable SSL Encryption and Import Certificate for Multiple Instances of SQL Server using PowerShell](#29773) PowerPivot/PowerQuery/PowerBI - [Power BI Custom Visual (Part 6)](#29778) Performance Tuning SQL Server - [Runtime Metrics in Execution Plans](#29767) - [SQL Server 2017: Potentially Interesting New Extended Events](#29763) - [sp_query_store_flush_db, Query Store, and Backups](#29761) - [The DMV Diaries: sys.dm_os_workers](#29760) HA/DR/Always On/Clustering - [Sync SQL Logins and Jobs](#29769) - [Automatic Seeding in Always On Availability Groups in SQL Server 2016](#29768) DevOps and Continuous Delivery (CI/CD) - [Protecting production data in non-production environments](#29750) Database Design, Theory and Development - [The Trillion Row Table](#29785) Data Privacy - [Should You Use Always Encrypted?](#29765) Data Mining/Data Analysis - [Data science without borders](#29784) Computing in the Cloud (Azure, Google , AWS) - [Azure Resource Manager (ARM) Templates](#29755) - [Using the Copy Wizard for the Azure Data Factory](#29754) Columnstore Indexes - [ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures](#29766) Backup and Recovery - [Avoiding the Five Biggest Disaster Recovery Mistakes](#29783) Azure SQL Database - [U-SQL Tip: Generating ranges of numbers and dates](#29777) - [Azure SQL Database – Scaling up](#29771) Analysis Services / BI on the MS Stack - [Generating a series of numbers in DAX](#29770) Administration of SQL Server - [Upgrading SQL Server– Windows Server 2016 Licensing Issues](#29782) - [DBCC SHRINKDATABASE Log Space Usage](#29776) - [VLFs the Forgotten Foe](#29772) - [Upgrading SQL Server– Database Compatibility Levels](#29764) .NET Related Articles - [The Care and Feeding of Tuples in C#](#29753) [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 2017-08-28 [Redgate Hub]( The best career move you can make On the new Redgate Hub, we’ve gathered together a wealth of information to help you solve problems, share ideas and discover the latest insights. Whatever you need to know about to advance your career, the answer is probably right here. [Move to the Redgate Hub]( [SQL Clone]( SQL Clone: Now supporting databases up to 64TB Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. [Try it free]( [SQL Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( Editorial - The Adequate DBA "Aim High!" "You could be president some day!" "You're just as smart as that Einstein kid down the street" … …And other crazy things parents tell their kids. Of course, very few kids grow up to be President, or a groundbreaking scientist. Most of us are merely adequate. Adequate parents, and adequate at our jobs, and that's just fine. I'm proud to call myself an adequate DBA. Perhaps being "adequate" doesn't sound like a source of pride, but over time I've come to realize that in the same way that adequate parents are what families need, so adequate DBAs are what most organizations require. So, what does it mean to be an Adequate DBA? It certainly doesn't mean just being "average" at everything. But conversely, being a master in the art of backup and restore does not, by itself, make you a great DBA. Most experienced DBAs will be expert in most aspects of the core database engine. When confronted ceaselessly with poorly written queries, funky database designs, and users who think that SET ISOLATION LEVEL SERIALABLE will give them the best results, they develop a keen eye for code that will cause performance problems; if not now, then soon. They have a good understanding of database security and permissions. Their backup and recovery routines are polished and well-practiced. They will have created and accrued monitoring scripts and tools that alert them quickly that the workload is causing stress conditions on the server. Surely a DBA who knows all is more than adequate? In my first years working with SQL Server, the product was just a relational engine so my answer would have been "yes". Now, it's a "maybe". SQL Server has expanded to include, for example, data movement tools, reporting tools, and analytical tools (such as R/Python scripts in 2017+). These are valuable to most organizations and so they expect their DBAs to be able to exploit them. They can't be expert in all these areas, but they need to be adequate. Then there is this "new thing" called The Cloud. Even in the once conservatively-paced world of the DBA, specialized knowledge can quickly become obsolete knowledge. Backup and restore capabilities, along with many other routine management tasks, are becoming automated by the cloud systems. The engine is [becoming better at tuning itself](; it's surely only a matter of time before the engine "learns" how to use all that data stuffed into DMVs to, for example, create an intelligent set of indexes, all by itself. As some of the old skills fade in importance, the DBA needs to replace them with adequate knowledge of a whole range of new ones, from Power BI, and machine learning to master data services and containerization. So, we arrive at the paradox that being a great DBA is the art of being adequate in an increasingly wide area of expertise. Achieve this, and you may be one of the greatest DBAs. Louis Davidson (Guest Editor). 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. Virtualization and Containers [] [Docker images for custom SQL Server Installations]( - I no longer install SQL Server on my workstation, I use Docker to spin up instances as and when I need them. This means I don’t have to go through the installation, my machine is cleaner and performance is not reduced by having multiple versions of SQL Server installed....[(more)]( Vendors/3rd Party Products [] [SQL Server and Data Governance]( - Does your organization have a data governance program, or are you thinking of implementing one? Redgate want to hear from you, as they're collecting data that will later be published in a report on the topic. Complete their survey to receive a copy of the report, and to be entered into a prize draw for a $100 Amazon gift card....[(more)]( [] [SQL Census update: new server view]( - SQL Census is a prototype from Redgate Foundry that helps you trace SQL Server user access permissions. Find out what's new and what's next for the tool in this blog post from Santiago Arias....[(more)]( [] [Register for the Redgate Hub Launch Livestream on September 6]( - To celebrate the launch of the new Redgate Hub, Data Platform MVP Steve Jones is hosting an educational livestream event alongside the team at Redgate HQ. They’ll introduce you to everything the Redgate Hub has to offer by highlighting some recent technical articles and how-to guides with in-depth live presentations. Register now....[(more)]( T-SQL [] [Learn SQL Server Starter Pack]( - All you need is SQL Server Developer Edition, Windows Server 2016 Evaluation Edition and Virtual Box....[(more)]( [] [Generate a Temporary Table Definition to Match the Resultset of a Query]( - Have you ever needed to store the results of a complex query in a temp table? How did you go about working out what the definition for that temp table should be, the columns and their data types?...[(more)]( [] [Automated Identification and Graphing of SQL Dependencies]( - If you’re anything like me—and if you’re reading this, you probably are—you’ve found yourself in a position where you’ve created 60+ interdependent (600+ line) data transformations defined in SQL as a core step of your ETL. You may even be sitting there thinking how did I get here, and what do I do now?...[(more)]( [] [Finding all Palindromes Contained in Strings with SQL]( - Given a string, find all substrings from that string, which are palindromes. Challenge accepted!...[(more)]( [] [Query Store functionality in earlier versions of SQL Server]( - OpenQueryStore is an open source project designed to, you guessed it, bring Query Store functionality to earlier versions of SQL Server (2008 to 2014)....[(more)]( [] [Multi-Statement TVFs in Dynamics CRM]( - Andy Mallon investigates a database performance issue with Microsoft Dynamics CRM, and finds a fix that speeds up nearly every database call in the application....[(more)]( Testing Software [] [Why don’t you unit test SQL Server code?]( - This post will show the benefits of test-driven development and including automated SQL Server unit testing within your release pipeline. Even if you have a large code base and no existing unit tests, you can start introducing tests now to make your database code more robust to change....[(more)]( SQL Server Security [] [Token-based authentication including Multi-factor auth for Azure SQL DB using Azure Active Directory]( - SQL server security team presents an application solution for token-based authentication with multi-factor (MFA) support for SQL DB using Azure AD auth....[(more)]( SQL Server Internals [] [The SQL Server Features that Time Forgot]( - Every new release of SQL Server comes with new features that cause a ripple of excitement within the industry: well, amongst the marketing people anyway. What happens to all the exciting TLAs that are bandied about when a new version launches? It's mixed, it seems. Adam Machanic's classic post, the The SQL Hall of Shame, has inspired Rob Sheldon to look back at some of the features that, though worthy, have may have failed to hit the mainstream....[(more)]( Software Development [] [Why is My Database Application so Slow?]( - When your application is running slowly, the reflex action is to blame the database queries. It is certainly true that some of the more extravagant delays can be fairly blamed on a missing index or unnecessary locking, but there are other potential villains in the drama, including the network and the application itself. Dan Turner points out that you could save a lot of time and money by working out for sure where the problems lie before diving into the detail....[(more)]( PowerShell [] [Enable SSL Encryption and Import Certificate for Multiple Instances of SQL Server using PowerShell]( - A recent project involved setting up encrypted connections on all our SQL Servers. The hard part was copying the certificate to the all the servers and making it easy of course was PowerShell. There is a trick to getting it to recognize the thumbprint that I will tell you about once we are at that set....[(more)]( PowerPivot/PowerQuery/PowerBI [] [Power BI Custom Visual (Part 6)]( - Explaining the main parameters we need for doing forecasts using “Exponential Smoothing”....[(more)]( Performance Tuning SQL Server [] [Runtime Metrics in Execution Plans]( - Capturing query execution metrics is much easier now that you can see the runtime metrics in execution plans when you’re using SQL Server 2016 SP1 or better in combination with SQL Server Management Studio 2017....[(more)]( [] [SQL Server 2017: Potentially Interesting New Extended Events]( - Out of the 194 new events SQL Server 2017 RC2, Erik Darling provides a rundown of the new Events with names or descriptions that seemed interesting....[(more)]( [] [sp_query_store_flush_db, Query Store, and Backups]( - Kendra Little covers a few things worth knowing about how Query Store works with backups and restores....[(more)]( [] [The DMV Diaries: sys.dm_os_workers]( - Following hot on the heels of sys.dm_os_threads, today we look into the worker objects built on top of them. This is intended to be supplementary to the official documentation on sys.dm_os_workers, so I’ll comment on that where appropriate, rather than repeating it....[(more)]( HA/DR/Always On/Clustering [] [Sync SQL Logins and Jobs]( - f you are using Availability Groups or Mirroring you know you need to sync SQL logins and jobs among replicas. What are your options?...[(more)]( [] [Automatic Seeding in Always On Availability Groups in SQL Server 2016]( - Klaus Aschenbrenner explains the what, why and how of the Automatic Seeding option that is available beginning with SQL Server 2016....[(more)]( DevOps and Continuous Delivery (CI/CD) [] [Protecting production data in non-production environments]( - In this article, Grant Fritchey looks at the need to ‘shift left’ the database and associated database testing, while keeping sensitive data secure when it is outside the production environment....[(more)]( Database Design, Theory and Development [] [The Trillion Row Table]( - What happens if you create a trillion-row table in SQL Server? Nothing good....[(more)]( Data Privacy [] [Should You Use Always Encrypted?]( - Always Encrypted can keep your most sensitive data – think credit cards and social security numbers – safe by encrypting them in the database driver, running on the app server. That way, when it gets to the SQL Server, it’s already encrypted. But that comes with a few big drawbacks....[(more)]( Data Mining/Data Analysis [] [Data science without borders]( - Wes McKinney makes the case for a shared infrastructure for data science.Continue reading Data science without borders. ...[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Azure Resource Manager (ARM) Templates]( - If you need a way of deploying infrastructure-as-code to Azure, then Azure Resource Manager (ARM) Templates are the obvious way of doing it simply and repeatedly. They define the objects you want, their types, names and properties in a JSON file which can be understood by the ARM API. Ed Elliott takes the mystery out of a simple means of specifying your Azure environment, whether it is a VM with blockchain software, SQL Server or a Web App on Linux with PostgreSQL....[(more)]( [] [Using the Copy Wizard for the Azure Data Factory]( - It used to be a considerable task to creating a feed for a data warehouse. Now, it just takes a few minutes to work through a series of screen s to create a pipeline that, in this example, creates a pipeline that brings data from a remote FTP server, decompresses the data and imports the data in a structured format, ready for data analysis. The Copy Wizard for the Azure Data Factory is a great time-saver, as Feodor Georgiev explains....[(more)]( Columnstore Indexes [] [ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures]( - Rowgroup Elimination is possible in stored procedures, but it doesn’t provide any feedback to plan choice. No matter how many were eliminated or not, the plan remained the same. As eliminations decreased, performance got worse using the ‘small’ plan. This is textbook parameter sniffing, but with a twist....[(more)]( Backup and Recovery [] [Avoiding the Five Biggest Disaster Recovery Mistakes]( - Having a disaster recovery (DR) plan is critical for businesses of all types and sizes. This concept is a given but creating and executing good and comprehensive DR plans for most organizations is complex and difficult. Businesses today have sophisticated ......[(more)]( Azure SQL Database [] [U-SQL Tip: Generating ranges of numbers and dates]( - Many common scenarios for U-SQL developers require constructing a RowSet made up of a simple range of numbers or dates, for example the integers from 1 to 10. In this blog post we'll take a look at options for doing this in U-SQL....[(more)]( [] [Azure SQL Database – Scaling up]( - Scaling up and down your SQL Database is something that is quite common to do. I want to discuss the impact of moving up and down tiers, in terms of your transactions and connections....[(more)]( Analysis Services / BI on the MS Stack [] [Generating a series of numbers in DAX]( - This article describes how to create a table with a series of numbers in DAX by using the new GENERATESERIES function or through a workaround using CALENDAR....[(more)]( Administration of SQL Server [] [Upgrading SQL Server– Windows Server 2016 Licensing Issues]( - If you are planning a complete data platform upgrade, you should be planning on using Windows Server 2016 as your operating system. One potential issue with Windows Server 2016 is the fact that Microsoft is now using a core-based licensing system for that product, which could potentially be a point of confusion and extra SQL Server licensing costs for some organizations....[(more)]( [] [DBCC SHRINKDATABASE Log Space Usage]( - As well as introducing index fragmentation, data file shrink also generates a lot of I/O, uses a lot of CPU and generates loads of transaction log. But how much is "loads"?...[(more)]( [] [VLFs the Forgotten Foe]( - How many of you check the amount of Virtual Log Files (VLFs) your transaction logs have?...[(more)]( [] [Upgrading SQL Server– Database Compatibility Levels]( - One important factor to contemplate during an upgrade and migration effort are your database compatibility levels, which control certain database behaviors to be compatible with a particular version of SQL Server. ...[(more)]( .NET Related Articles [] [The Care and Feeding of Tuples in C#]( - Tuples have many uses in languages like Lisp, F# and Python; they underlie relational theory. The .NET support for tuples seems rather limp by comparison. Why is this, and what are the obvious uses for tuples in C#? Tom Fischer takes on the cause of C# tuples and tackles the 'what', 'why' and 'when'. He makes the case for using them, while explaining the tribulations of the .NET implementation in the past....[(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 ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com