Database Weekly reveals the Ideal Agile Database, the Mysteries of Partitioning and more (4/3/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Virtualization - [Updated SQL Server on VMware Best Practices Guide](#28559) - [Using SQL Client Aliases](#28554) Vendors/3rd Party Products - [Six shades of masking your data](#28592) - [Dealing with regulation; audit and compliance in SQL Server](#28569) - [Audit and compliance survey with prize draw](#28549) - [Webinar: How to get started with DevOps](#28548) T-SQL - [Checking for Strange Client Settings with sys.dm_exec_sessions](#28596) - [Creating a list of random numbers](#28558) - [CTEs, Views, and NOLOCK](#28552) Tech News : General Interest - [LastPass Warns Users As It Fixes 'Major' Vulnerability](#28595) SQL Server on Linux - [SQL Server on Linux. How to change port. Problems and Troubleshooting.](#28573) - [Connect to SQLAgent on Linux via new SqlServer PowerShell module](#28564) SQL Server Internals - [Context in perspective 6: Taking Sessions to Task](#28587) Software Development - [What is an exceptional situation in code?](#28575) R Language - [Learning Scrabble strategy from robots, using R](#28593) - [Running your R code on Azure with mrsdeploy](#28563) - [The Tidyverse Curse](#28562) - [Writing a conference abstract the data science way](#28561) PowerShell - [Getting SQL Server File Sizes and Space Used with dbatools](#28555) PowerPivot/PowerQuery/PowerBI - [Power Query Pivot Swap](#28581) - [Power BI Custom Visuals Class (Module 44 - Attribute Slicer)](#28570) - [Daylight Saving Time And Time Zones In M](#28551) Performance Tuning SQL Server - [Analyzing Wait Events for Faster Database Troubleshooting](#28588) - [How to Benchmark Alternative SQL Queries to Find the Fastest Query](#28586) - [Decrypting Insert Query Plans](#28582) - [Do you need more than STATISTICS IO for Query Tuning?](#28578) Hardware Testing - [Your physical memory configuration can slow down your servers](#28550) HA/DR/Always On/Clustering - [Why is my Transaction Log Growing in My Availability Group? (Dear SQL DBA Episode 36)](#28572) - [Confession: I recommended that a 5GB database get split up](#28556) ETL/SSIS/ELT - [SSIS Catalog Logging Tables](#28590) DevOps and Continuous Delivery (CI/CD) - [Avoiding the Slide From DevOps to DevOops](#28584) - [Automating database deployments to and from source control using SQL Compare and PowerShell](#28568) Data Visualisation - [Generating Plots Automatically From PowerShell and SQL Server Using Gnuplot](#28566) Data Access / ORMs - [Building Better Entity Framework Applications](#28567) - [Who Stuck These Letters In My DateTimes?](#28557) Conferences and Events - [The Microsoft Data Insights Summit is back](#28594) Computing in the Cloud (Azure, Google , AWS) - [Containers at work: .NET and SQL Server containers for Dev and QA](#28589) - [Why Some Azure VM Sizes are Unavailable When Resizing in the Portal](#28585) - [Azure Networking for SQL Server DBAs](#28565) Columnstore Indexes - [Clustered columnstore: on-disk vs. in-mem](#28560) Bugs/Patches for SQL Server - [Cumulative Update #5 for SQL Server 2016 RTM](#28580) Backup and Recovery - [This Is Your Backup Schedule On Drugs](#28583) - [Spring cleanup, LOB considerations](#28553) Azure SQL Database - [What the heck is a DTU?](#28579) Analysis Services / BI on the MS Stack - [Lookup multiple values in DAX](#28591) Administration of SQL Server - [SQLskills SQL101: Partitioning](#28577) - [SQLskills SQL101: Switching recovery models](#28576) - [The Case of the Blocking Online Index Create (A Shared Lock that Wouldn’t Quit)](#28574) - [SQLskills SQL101: Updating SQL Server Statistics Part II – Scheduled Updates](#28571) [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-04-03 [SQL Toolbelt]( Not enough hours in your day? The SQL Toolbelt lets you reduce the time spent on SQL Server development and administration. Cambridge University developer David Spaxman, for example, says: “I’m saving 10-12 hours a week using the SQL Toolbelt.” Learn how you can double your productivity, speed up deployments and protect your data. [Download a free trial.]( [SQL Clone]( NEW SQL Clone - version 1 available now! 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 Ideal Agile Database One of the pleasures in the life of a database consultant is to be able to tell a client that they don't need a relational database at all. Plenty of scientific applications don't need them nor do many social media or recreational applications. You are often much better off with a system that specializes purely in the requirements you have. There are, of course, problems with this. You have to know your requirements in detail up-front. Sometimes, the team who are designing and building an application don't know, or have never thought about, the broad scope of their requirements in detail. They become excited by the speed at which a particular database system stores and retrieves object data, and make a quick gut-decision to use it. Only later do they realize that it is almost impossible to restore a backup, or that the data can be read via an 'exploit'. There have been several disasters where trading systems built on NoSQL have failed to realize the importance of transactionality. The confusion happens because there is only one word in the industry to describe a database. Many of us use the term casually to describe any system that allows you to store and fetch data. The term 'database' comes with baggage, though. There is a general understanding that a database will allow more than one process to access the system simultaneously, can cope with recovery (Backup/Restore), security (is it possible to hack the data), access control (do users see just the data that is appropriate for them), transactionality (will it handle business transactions safely), isolation, resilience and performance. We can sometimes assume that anything that calls itself a database has all these qualities and it can come as an unexpected, unpleasant and expensive shock when it doesn't. A relational database is a safe bet, particularly where you're not yet clear about your requirements. Now that they are offering in-memory optimization for tables, it is an even easier choice. If you run out of grunt with a relational database, it is usually for the happiest of reasons: your application is so popular that it needs a distributed architecture. Even then, you have an easy migration path. You can delegate much of your ETL, archiving and specialized processing to a NoSQL database such as Couchbase, or migrate to a hybrid system such as VoltDB. What am I saying? Could it be that a relational database is so versatile and feature-rich that it is the ideal database for an Agile development? Certainly, it is safe to say that you need to have done plenty of architectural planning to be confident enough to select a NoSQL alternative that matches the requirements and priorities of your applications. 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. Virtualization [] [Updated SQL Server on VMware Best Practices Guide]( - The latest version of the Microsoft SQL Server on VMware best practices guide, updated for vSphere 6.5....[(more)]( [] [Using SQL Client Aliases]( - How to use client aliases to replicate the DNS aliases in production, avoiding the need to change the app connection string....[(more)]( Vendors/3rd Party Products [] [Six shades of masking your data]( - At Redgate, one of Foundry team's projects is to improve the management of sensitive data and synthesize more realistic data. Here, they explain six different approaches to data masking....[(more)]( [] [Dealing with regulation; audit and compliance in SQL Server]( - There’s not a great deal of built-in support for people working on the Microsoft data platform who need to carry out audit and compliance activities....[(more)]( [] [Audit and compliance survey with prize draw]( - The Foundry team at Redgate have been investigating how different regulations impact work with SQL Server. They understand the many regulations, but need your help to get a better idea of all the related activities. How has your experience with auditing and compliance been in the past? Fill out this short survey to let the team know, and in return you'll be entered into a draw to win a $100 Amazon gift card....[(more)]( [] [Webinar: How to get started with DevOps]( - More and more teams are turning to DevOps as way to improve the efficiency and quality of software delivery but it’s not always clear what's the best way to get started. We’ve invited Steve Thair, CTO of the DevOpsGuys, to share some practical tips on how you can start implementing DevOps in your organization. Register for the free webinar....[(more)]( T-SQL [] [Checking for Strange Client Settings with sys.dm_exec_sessions]( - In my performance training class, people get really excited when I cover filtered indexes, computed columns, and indexed views. Then they get a really strange expression on their face when I talk about one of the biggest drawbacks: if your connection options aren’t set correctly, your delete/update/insert (DUI) operations will fail. ...[(more)]( [] [Creating a list of random numbers]( - The random number function RAND() has a few inherent flaws, so what's a good solution?...[(more)]( [] [CTEs, Views, and NOLOCK]( - Erik Darling stumbles across a case where the syntax in a CTE had no locking hints, but the select from the CTE had a NOLOCK hint. Interesting. Does that work?!...[(more)]( Tech News : General Interest [] [LastPass Warns Users As It Fixes 'Major' Vulnerability]( - LastPass is advising users to avoid using its browser plugins while it battles to fix a "major architectural problem," which could allow an attacker to steal passwords or execute code....[(more)]( SQL Server on Linux [] [SQL Server on Linux. How to change port. Problems and Troubleshooting.]( - Most of those who use SQL Server don't even know that they are using default port #1433 to access their precious data. However, to make your data just little bit less vulnerable for external intruder you can change that port within wide range of 65K addresses. ...[(more)]( [] [Connect to SQLAgent on Linux via new SqlServer PowerShell module]( - Slava Murygin shows some basic SQL Server on Linux administration using the SqlServer PowerShell module and SQLAgent for SQL Server on Linux,...[(more)]( SQL Server Internals [] [Context in perspective 6: Taking Sessions to Task]( - Ewald Cress explores an undocumented command, DBCC TEC, which can dump a significant chunk of the object tree supporting a SQL Server session. ...[(more)]( Software Development [] [What is an exceptional situation in code?]( - What is an "exceptional" situation in code? A bug is one, but there are others, as Vladimir Khorikov explains....[(more)]( R Language [] [Learning Scrabble strategy from robots, using R]( - James Curley, a psychology professor at Columbia University, has used an interesting method to collect data about what plays are most effective in Scrabble: by having robots play against each other, thousands of times....[(more)]( [] [Running your R code on Azure with mrsdeploy]( - Let’s say you’ve built a model in R that is larger than you can conveniently run locally, and you want to take advantage of Azure’s resources simply to run it on a larger machine. This blog explains how to provision and run an Azure virtual machine (VM) for this, using the mrsdeploy library that comes installed with Microsoft’s R Server. ...[(more)]( [] [The Tidyverse Curse]( - Demonstrating a small slice of the mental overhead you’ll need to deal with as you learn base R and the tidyverse packages, such as dplyr....[(more)]( [] [Writing a conference abstract the data science way]( - Using R's Tidytext package to try to define the distinguishing characteristics of 'accepted' conference abstracts....[(more)]( PowerShell [] [Getting SQL Server File Sizes and Space Used with dbatools]( - Demonstrating the Get-DbaDatabaseFreespace command in the community-written dbatools PowerShell module....[(more)]( PowerPivot/PowerQuery/PowerBI [] [Power Query Pivot Swap]( - I recently was given a CSV file that listed users and the groups they belonged to, from an Active Directory dump and asked to transform it to show the list of groups and the users that belonged to them....[(more)]( [] [Power BI Custom Visuals Class (Module 44 - Attribute Slicer)]( - Using the Attribute Slicer you have the ability to filter your entire report while also being able to visibly see a measure value associated with each attribute....[(more)]( [] [Daylight Saving Time And Time Zones In M]( - Chris Webb learns that it's possible to convert a UTC time into the actual time in any given location in pure M, if the time zone you’re converting to is your PC’s own local time zone....[(more)]( Performance Tuning SQL Server [] [Analyzing Wait Events for Faster Database Troubleshooting]( - Thomas LaRock examines the main "groups" of wait events, internal, resource, and external, and what they mean....[(more)]( [] [How to Benchmark Alternative SQL Queries to Find the Fastest Query]( - If you have good reasons to think that a differently written, but semantically equivalent query might be faster (on your database), you should measure. Don’t even trust any execution plan, because ultimately, what really counts is the wall clock time in your production system....[(more)]( [] [Decrypting Insert Query Plans]( - After looking at query plans for modifications involving indexed views, Erik Darling takes a look at some other fairly common table design items that cause execution plan oddities, this time focusing on INSERTs....[(more)]( [] [Do you need more than STATISTICS IO for Query Tuning?]( - Is STATISTICS IO everything that you need for query tuning, or are there more metrics that you should be aware of? The Client Statistics option in SQL Server Management Studio is also useful....[(more)]( Hardware Testing [] [Your physical memory configuration can slow down your servers]( - Watch your memory configuration! You can’t just throw RAM in a physical server and expect it to work right. Depending on your DIMM configuration, you might have accidentally slowed down your memory speed, which will surely slow down your application servers. ...[(more)]( HA/DR/Always On/Clustering [] [Why is my Transaction Log Growing in My Availability Group? (Dear SQL DBA Episode 36)]( - A database transaction log is expanding, even though the DBA is running log backups and doesn’t see an open transaction? What’s going on with this Availability Group?...[(more)]( [] [Confession: I recommended that a 5GB database get split up]( - A client with a 5GB database wanted it to be highly available, and up and running in short order even if they lost the server – or an entire data center – or a region of servers. Sounds like a job for Azure SQL DB, but...a lot of the data was regenerated from scratch, every single day, during overnight ETL jobs....[(more)]( ETL/SSIS/ELT [] [SSIS Catalog Logging Tables]( - Making the most of the SSIS catalog requires an understanding of how to access the information stored in the logging tables. Although there are built-in reports to show this information, there are limitations in their use. Fortunately, the logging tables in the SSIS catalog database are (mostly) straightforward and easy to understand once you’ve worked with them a bit....[(more)]( DevOps and Continuous Delivery (CI/CD) [] [Avoiding the Slide From DevOps to DevOops]( - It is “soft skills”, not technical, tools or automation skills, that are the biggest factor in determining the ease or pain with which an organization can “switch to DevOps”....[(more)]( [] [Automating database deployments to and from source control using SQL Compare and PowerShell]( - How to get a new database into version control, and then how to create a new build script for a database version, or a database migration script to upgrade a target database to the version represented by the source object scripts....[(more)]( Data Visualisation [] [Generating Plots Automatically From PowerShell and SQL Server Using Gnuplot]( - When you are automating a number of tasks, or performing a batch of tests, you want a way of automating the production of your plots and graphs. Nothing beats a good graphical plot for giving the indications of how the process went....[(more)]( Data Access / ORMs [] [Building Better Entity Framework Applications]( - Entity Framework (EF) is Microsoft’s Object/Relational (ORM) database access library, with a new generation, EF Core, released in 2016. In this article Jon P Smith looks at six different software principles and patterns that help to keep the EF code nicely separated from the rest of the application. The six approaches make the EF database access code is easier to write, test, refactor and, most importantly, performance-tune....[(more)]( [] [Who Stuck These Letters In My DateTimes?]( - Parsing, creating, and modifying JSON in SQL Server 2016 is really easy. JSON dates and times are not....[(more)]( Conferences and Events [] [The Microsoft Data Insights Summit is back]( - June 12-13, 2017 in Seattle, WA. This is THE user conference for Power BI, SQL Server BI, Excel, PowerApps, and Flow....[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Containers at work: .NET and SQL Server containers for Dev and QA]( - Paul Stanton makes the case, in fact several cases, for using Windocks containers for .NET and SQL Server development....[(more)]( [] [Why Some Azure VM Sizes are Unavailable When Resizing in the Portal]( - Melissa Coates explains a few reasons why some sizes are not available when you are attempting to change the size/scale level of an Azure virtual machine in the portal....[(more)]( [] [Azure Networking for SQL Server DBAs]( - The network is important to any DBA because so much performance is dependent on I/O, because of the importance of security, and ensuring that everyone get the right access....[(more)]( Columnstore Indexes [] [Clustered columnstore: on-disk vs. in-mem]( - This post will highlight the fairly wide gap in functionality between clustered columnstore indexes for on-disk tables compared to memory-optimized tables, for SQL 2016....[(more)]( Bugs/Patches for SQL Server [] [Cumulative Update #5 for SQL Server 2016 RTM]( - The 5th cumulative update release for SQL Server 2016 RTM is now available for download at the Microsoft Downloads site....[(more)]( Backup and Recovery [] [This Is Your Backup Schedule On Drugs]( - Erik Darling wants to play a game: he lists out some of the worst backup schedules he's seen, and you get to tell him what you think caused such a lapse of cognizant reality....[(more)]( [] [Spring cleanup, LOB considerations]( - If you have dedicated filegroups for user data, you can recover critical data first, and then the rest. However, if a database was not created this way, you need to move the data from one filegroup to another before you can apply this kind of technique - and there are some gotchas to look out for when doing this. ...[(more)]( Azure SQL Database [] [What the heck is a DTU?]( - When you make the jump to PaaS, Azure SQL Database is sized with different service tiers, where performance is measured in DTUs. What the heck is a DTU?...[(more)]( Analysis Services / BI on the MS Stack [] [Lookup multiple values in DAX]( - This article describes different techniques to retrieve multiple values from a lookup table in DAX, improving code readability and performance....[(more)]( Administration of SQL Server [] [SQLskills SQL101: Partitioning]( - There are a few critical questions to ask and a few very important things to consider before choosing a partitioning strategy / design / architecture. Kimberly Tripp covers them....[(more)]( [] [SQLskills SQL101: Switching recovery models]( - One of the things that can catch people out is the effect of switching out of the full recovery model temporarily. In this post I’ll briefly describe the three recovery models and then the problems you can have switching from full to simple, and from full to bulk-logged....[(more)]( [] [The Case of the Blocking Online Index Create (A Shared Lock that Wouldn’t Quit)]( - A case when modifying an existing nonclustered index (added a new column to INCLUDE) was blocking any write operations on the table, due to a Shared lock on the table....[(more)]( [] [SQLskills SQL101: Updating SQL Server Statistics Part II – Scheduled Updates]( - Erin Stellato reviews ways to control when SQL Server statistics are updated, including the Update Statistics Task (Maintenance Plan), sp_updatestats and the UPDATE STATISTICS command....[(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