Get the right indexes without guessing, calculate running totals and more in Database Weekly (11/27/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [Extending DevOps practices to SQL Server databases with ReadyRoll and VSTS](#30495) Virtualization and Containers - [Docker and Linux Containers on Windows, with or without Hyper-V Virtual Machines](#30533) Vendors/3rd Party Products - [Free virtual event: SQL in the City Streamed](#30494) - [How to Customize Schema Comparisons using Auto Map in SQL Compare](#30493) T-SQL - [How to Calculate Running Totals in SQL Server](#30534) - [HowTo: SQL type casting](#30510) - [Does The Join Order of My Tables Matter?](#30508) - [Incorrect Syntax – What?](#30505) Tech News : General Interest - [The FCC's Proposal To Nuke Net Neutrality](#30532) SQL Server Security and Auditing - [Encrypting SQL Server connections with Let’s Encrypt certificates](#30501) SQL Server News - [Microsoft Kerberos Configuration Manager 4.1 for SQL Server.](#30531) - [Cumulative Update #6 for SQL Server 2016 SP1](#30530) - [Cumulative Update #9 for SQL Server 2016 RTM](#30529) Security news and thoughts - [Scary But True: Hundreds of Popular Sites Log Everything You Type](#30526) - [Uber Concealed Cyberattack That Exposed 57 Million People’s Data](#30521) - [Uber Hack Shows Vulnerability of Software Code-Sharing Services](#30520) R Language - [Data Wrangling at Scale](#30506) PowerShell - [PowerShell Function to Automate Availability Group Failover](#30536) - [Database Migrations – Capacity Planning with dbatools](#30503) PowerPivot/PowerQuery/PowerBI - [Dynamic Row Level Security with Organizational Hierarchy Power BI](#30519) - [Copy Conditional Columns in Power Query or Power BI](#30498) - [Why the Default Summarization Property in Power BI is So Important](#30497) Performance Tuning SQL Server - [How to Track SQL Server Changes with sp_BlitzFirst](#30535) - [Get that Profiler feel in Extended Events](#30527) - [Updated Stack Overflow Database Dump Importer v1.4](#30524) - [Perfect statistics histogram in just few steps](#30516) - [What is the Role of the UPDATE Lock in SQL Server?](#30512) - [Getting the Right Indexes without Guessing](#30511) HA/DR/Always On/Clustering - [SQL Server 2017 Read-Scale Availability Groups](#30509) DevOps and Continuous Delivery (CI/CD) - [DevOps for Data Science – DevOps Maturity](#30525) - [How do I use VSTS variables in YAML CI Build definitions?](#30499) - [Major new study reveals the true ROI of database DevOps](#30496) - [5 tips for achieving continuous delivery](#30492) Data Mining/Data Analysis - [Is there data on the quality of management decisions?](#30523) - [I May Never Be a Data Scientist](#30522) Columnstore Indexes - [Implementing Sliding Windows Data Purge Pattern with Columnstore Indexes](#30502) Career Growth - [Who are the Folks who made a difference? (T-SQL Tuesday 96)](#30539) - [Slowing Down Your Motor Mouth – Presentation Tips for Fast Talkers](#30515) - [What Specialists and Generalists Get Paid For](#30500) Backup and Recovery - [Disaster recovery 101: fixing a broken system table page](#30528) - [Restoring SQL Server Database on Linux using SQL Operations Studio](#30518) - [How we made backups faster with SQL Server 2017](#30517) Azure SQL Database - [Azure SQL Database – Service Tiers](#30537) Analysis Services / BI on the MS Stack - [Dictinct Count of a Dimension based on a Filtered Measure](#30538) Administration of SQL Server - [Importing Registered Servers from SSMS into SQL Operations Studio](#30514) - [Microsoft SQL Operations Studio Preview](#30513) - [Sessions, Temporary Objects, and the Afterlife](#30507) - [SQL Server – DBCC CHECKTABLE](#30504) [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-11-27 [Database DevOps]( Extending DevOps practices to SQL Server databases In this demo heavy session you’ll see how Redgate’s SQL Toolbelt allows you to take a local development database, source control it, set up automated builds and automate deployments to an Azure SQL database in less than an hour using VSTS. [Register now]( [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]( [Redgate Hub]( Register now for SQL in the City Streamed Redgate’s popular SQL in the City Streamed virtual event takes place again this December. Wherever you are, tune in on Wednesday December 13 to watch some of the best-known speakers from the database world present the latest technologies and tools from Redgate. [Register free now]( Editorial - A well-earned break from the database For many database professionals, it's rare to be able to take more than a week's holiday at a time. Some panic, or are you consumed with guilt, at the thought of [taking any real holiday from work at all](. They are compelled to stay in touch even when they are away. After all, what if something breaks? Sure, your expertise is important when things go wrong, but hopefully you've documented, maybe even scripted, the processes that deal with all the known things that could awry, so that even a manager could deal with them. However, as any shepherd will tell you, sheep have misadventures, and even die, in extraordinary and creative ways that you never could have predicted. Databases, and database applications are uncannily similar. I've had more than one database get tangled up in the metaphorical wire, such as the time when a password generator started to produce swearwords. I've had one or two drop off a cliff. An autopsy on a dead server will often reveal a bizarre chain of unlikely events and failures that led to database-demise syndrome. Sometimes, even while lazing on a distant sunny beach, listening to the breakers and sipping Pina Colada, it's hard to fight the occasional twinge of fear that [someone in Ops will be mistreating your server](. However, they will just have to get on with it. You've left instructions for fixing most problems. There are ways around most crises. It's up to them. It is best to be liberal with your expertise. In fact, if you have become 'indispensable', then you are failing at an important aspect of your job. The best and most competent people I've ever worked with were those who spread their expertise liberally, sharing their knowledge, and so inspiring others around them. Major organizations don't like employing heroes; they want competent and versatile people who can adapt to different roles and are good at ensuring that whoever takes over their existing role finds it easy to 'pick up the reins'. As the old management saying goes, '[if anyone in your organisation becomes indispensable, dispense with them.']( In talking to DBAs and Ops people in a high-availability setting, I always like to ask how they ensure that problems can be fixed at any hour, whoever is on duty. The answer can get complicated, involving all manner of ways of ensuring someone is available to communicate with and assist whoever is 'holding the fort', even if it's a rather grumpy CIO, at three in the morning. Scripting helps enormously, as does documentation, possibly in source control, and this can include videos, diagrams, and wallcharts. Sure, there are mobile phones, still a few pagers, but these should not be an option if you are on that distant beach. If you've covered the obvious contingencies then your conscience is clean, and there should be nothing to stop you enjoying that long holiday, every now and again. You've earned it. Phil Factor. Phil Factor 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. Webinars [] [Extending DevOps practices to SQL Server databases with ReadyRoll and VSTS]( - Microsoft MVP Steve Jones, and Redgate’s Tom Austin, will show you how Redgate’s Database DevOps solution works to improve your database development and deployment processes. In this demo heavy session you’ll see how Redgate’s SQL Toolbelt allows you to take a local development database, source control it, set up automated builds and automate deployments to an Azure SQL database in less than an hour using VSTS. There will also be plenty of time to ask Steve and Tom questions. ...[(more)]( ) Virtualization and Containers [] [Docker and Linux Containers on Windows, with or without Hyper-V Virtual Machines]( - Containers are lovely, in case you haven't heard. They are a nice and clean way to get a reliable and guaranteed deployment, no matter the host system....[(more)]( Vendors/3rd Party Products [] [Free virtual event: SQL in the City Streamed]( - Technical sessions will dive into the latest Microsoft SQL Server releases, and cover topical issues such as data compliance, protection & privacy....[(more)]( [] [How to Customize Schema Comparisons using Auto Map in SQL Compare]( - SQL Compare includes many options to make comparing objects easier on the developer. Auto mapping is one of these, and if often saves a little time and frustration. However, there are times that you many need to customize your mapping, and this gives you a technique for doing so....[(more)]( T-SQL [] [How to Calculate Running Totals in SQL Server]( - The best solution, which is also reliable and supported? Using the SUM aggregate function as a window function....[(more)]( [] [HowTo: SQL type casting]( - In this post, we are going to present the more common castings one may wish to perform, along with the SQL code to do so, when using Amazon Redshift, PostgreSQL, Google BigQuery or SQL Server....[(more)]( [] [Does The Join Order of My Tables Matter?]( - Table join order matters for reducing the number of rows that the rest of the query needs to process. By default SQL Server gives you no control over the join order?—?it uses statistics and the query optimizer to pick what it thinks is a good join order....[(more)]( [] [Incorrect Syntax – What?]( - What do we do when the error message looks to be pretty intuitive but we can’t figure out where the problem is? ...[(more)]( Tech News : General Interest [] [The FCC's Proposal To Nuke Net Neutrality]( - The Federal Communications Commission (FCC) released its proposal to end Obama-era net neutrality protections today, the day before Thanksgiving, with plans to vote on December 14....[(more)](35983.html) SQL Server Security and Auditing [] [Encrypting SQL Server connections with Let’s Encrypt certificates]( - Encrypting your SQL Server’s TDS connections should be high on your list of things to do if you’re concerned with the privacy of your data. This often boils down to one big problem: can you get a valid certificate without paying a ton of money, and will it work with SQL Server?...[(more)]( SQL Server News [] [Microsoft Kerberos Configuration Manager 4.1 for SQL Server.]( - Kerberos authentication provides a highly secure method to authenticate client and server entities (security principals) on a network. To use Kerberos authentication with SQL Server, a Service Principal Name (SPN) must be registered with Active Directory, which plays the role of the Key Distribution Center in a Windows domain....[(more)]( [] [Cumulative Update #6 for SQL Server 2016 SP1]( - The 6th cumulative update release for SQL Server 2016 SP1 is now available for download at the Microsoft Downloads site. ...[(more)]( [] [Cumulative Update #9 for SQL Server 2016 RTM]( - The 9th cumulative update release for SQL Server 2016 RTM is now available for download at the Microsoft Downloads site....[(more)]( Security news and thoughts [] [Scary But True: Hundreds of Popular Sites Log Everything You Type]( - Nearly 500 of the top 50,000 Web sites on Alexa use so-called "session replay" scripts from third-party companies to record practically everything users do while visiting their sites, according to new research from a team at Princeton University....[(more)]( [] [Uber Concealed Cyberattack That Exposed 57 Million People’s Data]( - Compromised data from the October 2016 attack included names, email addresses and phone numbers of 50 million Uber riders around the world, the company told Bloomberg on Tuesday. The personal information of about 7 million drivers were accessed as well, including some 600,000 U.S. driver’s license numbers....[(more)]( [] [Uber Hack Shows Vulnerability of Software Code-Sharing Services]( - Services like San Francisco-based Github Inc., GitLab and SourceForge are used by developers to collaborate on projects, track bugs in code and distribute early versions of applications. They’re also a target for cyberthieves....[(more)]( R Language [] [Data Wrangling at Scale]( - The sparklyr package, which allows R to work with big data. In this note we we will use the sparklyr package, but concentrate on another important package called cdata....[(more)]( PowerShell [] [PowerShell Function to Automate Availability Group Failover]( - The function takes a replica name as input and queries system tables for Availability Groups running as secondary that are online, healthy, and synchronous. For each AG found, the function generates an ALTER AVAILABILITY GROUP statement....[(more)]( [] [Database Migrations – Capacity Planning with dbatools]( - Have you ever had to deal with system migrations as a DBA and struggle with capacity planning? This post shows you how to use dbatools to migrate a large number of databases from one server to another, first ensuring storage space is present....[(more)]( PowerPivot/PowerQuery/PowerBI [] [Dynamic Row Level Security with Organizational Hierarchy Power BI]( - How to secure data according to the organizational hierarchy approach, where every person can access details for all employees under him or her. ...[(more)]( [] [Copy Conditional Columns in Power Query or Power BI]( - In this video, Adam looks at how to use conditional columns within Power Query and then copying them for use in other queries or Power BI Desktop files. This is a great technique if you don’t want to retype all of the conditional items in the dialog in Power BI....[(more)]( [] [Why the Default Summarization Property in Power BI is So Important]( - If the Default Summarization settings in your data model are not correct, it results in wrong data being displayed, or even missing data, on your report....[(more)]( Performance Tuning SQL Server [] [How to Track SQL Server Changes with sp_BlitzFirst]( - Logging calls to sp_BlitzFirst when making changes that might affect performance. ...[(more)]( [] [Get that Profiler feel in Extended Events]( - There are reasons to have issues with Extended Events. Although, in my opinion, they all boil down to one thing, I have to use XML if I want to query the output. It’s the single pain point I’ve found. If you have other reasons, such as the grid, maybe explore the tool in more detail to see if your complaints hold up. ...[(more)]( [] [Updated Stack Overflow Database Dump Importer v1.4]( - The nice folks at Stack Overflow publish their entire data set (data included) in XML format. It’s tons of fun for demos, and this Database Dump Importer will give you an easy way to get it into a relational database....[(more)]( [] [Perfect statistics histogram in just few steps]( - Having more steps in a statistic object is not always synonym of better key value coverage, and better estimations. ...[(more)]( [] [What is the Role of the UPDATE Lock in SQL Server?]( - When a user executes an UPDATE statement against a row, the user is granted EXCLUSIVE lock on that row. If you monitor locks in your system, then you also might encounter UPDATE locks. Why do we need UPDATE locks, if we already have EXCLUSIVE locks for UPDATE operations?...[(more)]( [] [Getting the Right Indexes without Guessing]( - The goal of the DBA should be to have as many high-value indexes — indexes that will be used often by the workload — as possible while limiting indexes that won’t often be used....[(more)]( HA/DR/Always On/Clustering [] [SQL Server 2017 Read-Scale Availability Groups]( - Read-Scale availability groups are ones where we don't want the availability group for high-availability or disaster recovery, instead, we want to use it to create multiple copies of our databases that span across multiple servers allowing for the spreading of a large read-only workload. ...[(more)]( DevOps and Continuous Delivery (CI/CD) [] [DevOps for Data Science – DevOps Maturity]( - Buck Woody delves into the details of implementing DevOps in your Data Science Projects....[(more)]( [] [How do I use VSTS variables in YAML CI Build definitions?](3F) - A quick tip from Ed Elliott to include build variables in a YAML build definition ( .vsts-ci.yml )....[(more)](3F) [] [Major new study reveals the true ROI of database DevOps]( - Redgate has launched a new research study that reveals the benefits different stakeholders can expect from DevOps, and provides a powerful methodology for calculating the ROI of database DevOps....[(more)]( [] [5 tips for achieving continuous delivery]( - If you’re struggling to set up a reliable, repeatable release process you’re not alone. The good news is that most of the problems you’ll encounter have been solved before....[(more)]( Data Mining/Data Analysis [] [Is there data on the quality of management decisions?]( - When you see a big company doing something that seems bizarrely inefficient, maybe it’s not inefficient and you just lack the information necessary to understand why the decision was efficient....[(more)]( [] [I May Never Be a Data Scientist]( - Chances are extremely high that I’ll never put down Data Scientist as my job. Considering what you do and what you know, I’ll bet a healthy percentage of you are in a similar situation. That’s OK. You know why? You know a lot about data, data movement and data processing that a Data Scientist doesn’t....[(more)]( Columnstore Indexes [] [Implementing Sliding Windows Data Purge Pattern with Columnstore Indexes]( - Data partitioning allows us to keep the purge process on metadata- and minimally logged-levels by switching the old-data partition to a staging table and truncating it afterwards....[(more)]( Career Growth [] [Who are the Folks who made a difference? (T-SQL Tuesday 96)]( - A round up of 62 posts from SQL bloggers explaining who made a difference to their careers and their lives....[(more)]( [] [Slowing Down Your Motor Mouth – Presentation Tips for Fast Talkers]( - Four years ago, I got frustrated feedback from listeners who had a hard time keeping up with my mouth; now I get congratulations that the pace was great....[(more)]( [] [What Specialists and Generalists Get Paid For]( - Generalists get paid to learn new things fast and adapt. Specialists get paid for things they already know very well....[(more)]( Backup and Recovery [] [Disaster recovery 101: fixing a broken system table page]( - How Paul Randal helped a reader recover data from a corrupt database, when they did not have any up-to-date backups without the corruption....[(more)]( [] [Restoring SQL Server Database on Linux using SQL Operations Studio]( - How to get SQL Server running in a Docker container, connect using SQL Operations Studio, and restore a SQL Server database....[(more)]( [] [How we made backups faster with SQL Server 2017]( - How the SQL Server team leveraged the indirect checkpoint heuristics to enable faster backups....[(more)]( Azure SQL Database [] [Azure SQL Database – Service Tiers]( - Naturally the cost of Azure SQL Database directly relates to what tier and performance level you are using. Starting from the least expensive basic database to the more premium ones I thought it would be worthwhile capturing the costs (GBP) across all tiers....[(more)]( Analysis Services / BI on the MS Stack [] [Dictinct Count of a Dimension based on a Filtered Measure]( - Enthusiastic as we were, one of the hardest nuts to crack, though it seemed so simple during requirements gathering, was to perform a distinct count of a dimension based on a filtered measure on a couple of the reports. ...[(more)]( Administration of SQL Server [] [Importing Registered Servers from SSMS into SQL Operations Studio]( - Using PowerShell to import registered servers from SSMS into SQL Operations Studio....[(more)]( [] [Microsoft SQL Operations Studio Preview]( - Microsoft has made the new cross-platform SQL Operations Studio (SOS) tool available on Github as a free open-source project. This SOS preview allows one to develop and manage SQL Server and Azure SQL Database from Windows, Linux, and macOS....[(more)]( [] [Sessions, Temporary Objects, and the Afterlife]( - Sessions, in SQL Server, are born when a Connection is made from a client library to SQL Server. Temporary objects may be created during a Session’s lifetime. The question is: for those temporary objects that are not explicitly dropped, what exactly happens to them?...[(more)]( [] [SQL Server – DBCC CHECKTABLE]( - Arun Sirpal discovers that you can run repair via DBCC CHECKTABLE, as well as DBCC CHECKDB....[(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