A touch of R, a pinch of T-SQL and a whole heap of PowerShell (Database Weekly 9/18/2017)

The Complete Weekly Roundup of SQL Server News In this issue: Webinars - [Extending DevOps practices to SQL Server databases with ReadyRoll from Redgate](#29911) Virtualization and Containers - [Running Ubuntu Containers with Hyper-V Isolation on Windows](#29951) - [Sneak peek #3: Windows Server, version 1709 for developers](#29950) Vendors/3rd Party Products - [Unearthing Bad Deployments with SQL Monitor and Redgate’s Database DevOps Tools](#29917) - [SQL Prompt Tip: how to control when the suggestion box pops up](#29916) - [Redgate need your opinion](#29912) T-SQL - [Help! My query is too fast!](#29955) - [Remove SQL Junk (Brackets and Other Clutter)](#29938) - [What does this little check box do?](#29937) - [SSMS Shortcuts & Secrets – Live Webcast Edition (video)](#29932) - [Indexing for Windowing Functions: WHERE vs. OVER](#29930) - [Optional Parameters and Missing Index Requests](#29929) - [Using native compilation to insert parent/child tables](#29922) - [How Unicode Homoglyphs Can Thwart Your Database Security](#29920) - [Consuming hierarchical JSON documents in SQL Server using OpenJSON](#29915) Tech News : General Interest - [The Wrong Way to Clean Up the Internet](#29914) SQL Server Security and Auditing - [So, What is GDPR and Why Should Database Administrators Care?](#29949) Security news and thoughts - [On the Equifax Data Breach](#29948) - [Face ID, Touch ID, No ID, PINs and Pragmatic Security](#29941) - [Ayuda! (Help!) Equifax Has My Data!](#29928) R Language - [Automatically Write and Email Reports with R, SendGrid, & Displayr](#29924) PowerShell - [Because you need to know PowerShell](#29942) - [Do I Need to Master PowerShell?](#29933) - [T-SQL Tuesday #94 – Lets get all Posh! Creating VMs with PS](#29926) - [#TSQL2sDay – Starting Out with PowerShell](#29925) - [T-SQL Tuesday #94 – Automating Configuration Comparison](#29921) - [Importing Excel into SQL Server using PowerShell](#29919) - [Let's get all Posh! Log Shipping](#29918) PowerPivot/PowerQuery/PowerBI - [The Power Query Branding Problem](#29956) - [Power BI Theming – Chart Style Controls](#29923) Performance Tuning SQL Server - [How much should you worry about CXPACKET?](#29944) - [How Much Can One Row Change A Query Plan? Part 1](#29931) HA/DR/Always On/Clustering - [Using Synonyms with Availability Groups](#29954) - [Synchronizing SQL Server Instance Objects in an Availability Group](#29936) - [How to run the Availability Group Traffic on a different NIC](#29935) DevOps and Continuous Delivery (CI/CD) - [Benchmark your Database DevOps maturity level](#29913) Conferences and Events - [How much can you learn in 3 content packed days?](#29910) Computing in the Cloud (Azure, Google , AWS) - [Monitoring Azure Virtual Machine Performance](#29947) - [Questions About RDS SQL Server You Were Too Shy to Ask](#29945) - [Instant Log Initialization for SQL Server in Azure](#29940) Career Growth - [Tools We Use for Remote Work](#29927) Azure SQL Database - [Azure SQL Database Resumable Online Index Rebuild](#29953) Azure SQL Data Warehouse and Data Lake - [The Quick and the Dead Slow: Importing CSV Files into Azure Data Warehouse](#29946) Analysis Services / BI on the MS Stack - [The easiest way to start using variables in DAX](#29943) Administration of SQL Server - [Script all Agent Jobs Using SQL Server Management Studio](#29952) - [Database Fundamentals #10: Modifying Tables](#29934) [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-09-18 [PASS 2017]( Learning designed for the data professional PASS Summit offers five educationally-packed days lead by highly-rated, experienced speakers and sessions focused on the way you work with data. Speaker highlights include Bob Ward, and Sunil Agarwal from the Microsoft Tiger Team. Join us Oct 31 to Nov 3. [Go to www.PASSsummit.com]( [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]( [SQL Prompt]( Could your SQL coding be more efficient? Data Platform MVPs and SQL Server experts share their tips on how to standardize formatting whilst stripping out the repetition of coding with SQL Prompt. Over these 20 short videos we will help you write better, shareable SQL faster. [Check out the tips]( Editorial - Avoiding the Hell of having to Remember DDL syntax I cut my teeth on SQL in the dark days of the late eighties when one had to tap code into a text-editor screen. Old habits die hard, and I've generally been a late adopter of such niceties as SQL templates and snippets. When all you have is a blank screen and a badly-indexed printed manual you soon develop pretty good coping mechanisms, and once you find something that works well, you cling to it with white knuckles. For a long time, I kept a directory of minimal build scripts for each type of object and common routine, but with iSQL and the other unfriendly SQL editors, even that is painfully laborious. SSMS, when it replaced Query Analyser, was a revelation. Hmm, templates. Just drag 'n' drop and the template was suddenly there. One quick keystroke later and you had a form to fill in with the important details. Fill in the form and all the embedded macros have values in them. At the time, it seemed dead clever but there were some obvious things missing. For example, there's no way to take a query and turn it into a view, function, procedure, trigger or spExecuteSQL batch. You need to be able to select your query and pick the template and have it put the selected text in the body of the function. Also, there is no way to put default macro values in automatically for headers, or inline comments; values such as the date and logged-in developer, or database-wide values such as the database name. Also, with small screens, you tend to want to hide templates away to give yourself more real estate. It was this last factor that seems to have led to the SSMS snippets. Just right-click and you've got a cute menu of SQL objects that you can inject as build scripts into your query window at the insertion point. Good? No. you can't modify them or share with your team, and they have no macros. I got bored with both templates and snippets and used a programmer's clipboard called AceText instead. It saves everything you put on the clipboard and allows you to access it retrospectively. It allows you to create and share special collections that have all your favorite, specialist stuff, which you can then modify. In terms of effort, this is as close to zero as you can get, especially since you can save templates retrospectively. Although it works fine with SSMS, and any other IDE or programmer's editor, it doesn't allow the 'Surround with'/'before-and-after' and the rectangular block facilities that are so useful, unless you use AceText's accompanying text editor, Editpad Pro. Now that SQL Prompt's new formatter works so well, I'm back using that in SSMS. The snippets in Prompt have always been a bit of a grey area for me but I've been trying them out, and they certainly seem to work well. My only problem now is that I have three different ways of accessing snippets in SSMS, two native and one provided by SQL Prompt, and I'm still using AceText too. An embarrassment of riches. What's your favorite way of keeping your productivity going strong without excessing 'googling?' I'd be fascinated to know. 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 from Redgate]( - In this webinar from Microsoft MVP Steve Jones, and Redgate’s Arneh Eskandari, see how Redgate’s Database DevOps solution works to improve your database development and deployment processes. You'll see a demo of the following Redgate tools - ReadyRoll, DLM Automation and DLM Dashboard - and see how they plug into GIT. There will also be plenty of time to ask Steve and Arneh questions. ...[(more)]( Virtualization and Containers [] [Running Ubuntu Containers with Hyper-V Isolation on Windows]( - Canonical and Microsoft have teamed up to deliver the ability to run Ubuntu containers with Hyper-V Isolation on Windows 10 and Windows Servers....[(more)]( [] [Sneak peek #3: Windows Server, version 1709 for developers]( - Faster downloads, builds, and deployments with Nano Server container image, Linux containers, and more....[(more)]( Vendors/3rd Party Products [] [Unearthing Bad Deployments with SQL Monitor and Redgate’s Database DevOps Tools]( - Sudden performance issues in SQL Server can have many causes, ranging all the way from malfunctioning hardware, through to simple misconfiguration, or perhaps just end users doing things they shouldn’t. But one particularly common culprit is when deployments go wrong: I don’t know a single DBA who hasn’t been burned by a bad release....[(more)]( [] [SQL Prompt Tip: how to control when the suggestion box pops up]( - By default, SQL Prompt shows code auto-completion suggestions automatically, and continuously. Sometimes, as programmers, we need it to be a little less intrusive, especially when working through more intricate coding problems that required careful thought. Phil Factor explains the best way to set this up....[(more)]( [] [Redgate need your opinion]( - Have you tried or used SQL Prompt? Redgate is running some research on SQL Prompt pricing. Please help by filling in their short survey for the chance to win one of three licenses of SQL Prompt or a Redgate point-tool if your choice....[(more)]( T-SQL [] [Help! My query is too fast!]( - Said no one ever. Well, maybe. I have had occasions where I needed a brief pause in the middle of a batch. For example, if I’m running a big delete I might create a loop and delete in batches. If I’m having to run this for a while on an active table I might want to put a pause in each loop for a second or two to allow other processes to get in and do whatever they need to do....[(more)]( [] [Remove SQL Junk (Brackets and Other Clutter)]( - "Above all else, show the data” says Edwarde Tufte, the data visualization expert who promotes a high data-ink ratio in data visualizations. We can apply that idea to SQL. ...[(more)]( [] [What does this little check box do?]( - Ever wander around SQL Server properties and wonder what these little check boxes turn on? I do, and I get very tempted to check them. Here is one of those tempting little boxes that seems pretty handy, Use query governor to prevent long running queries....[(more)]( [] [SSMS Shortcuts & Secrets – Live Webcast Edition (video)]( - This video covers shortcuts to manage and switch between sessions, tips and shortcuts for managing Intellisense, and a few stupid pet tricks. ...[(more)]( [] [Indexing for Windowing Functions: WHERE vs. OVER]( - Erik Darling describes a case where a customer was using Windowing functions over one group of columns to partition and order by, but the where clause was touching a totally different group of columns. The query plan wasn’t happy. Users weren’t happy....[(more)]( [] [Optional Parameters and Missing Index Requests]( - At one point or another in everyone’s SQL-querying career, they end up writing a query with optional parameters. It's well known that this leads to poor cardinality estimates, full scans, and more but what often gets overlooked is that queries constructed like this don’t register missing index requests....[(more)]( [] [Using native compilation to insert parent/child tables]( - Demonstrating various approaches when using native compilation to insert rows into parent/child tables....[(more)]( [] [How Unicode Homoglyphs Can Thwart Your Database Security](CA%BC-%C5%9B%E2%84%87%E2%84%92%E2%84%87%E2%84%82%CA%88-how-unicode-homoglyphs-can-thwart-your-database-security-4b818f600799) - Bert Wagner explores how implicit unicode conversions can leave your data vulnerable....[(more)](CA%BC-%C5%9B%E2%84%87%E2%84%92%E2%84%87%E2%84%82%CA%88-how-unicode-homoglyphs-can-thwart-your-database-security-4b818f600799) [] [Consuming hierarchical JSON documents in SQL Server using OpenJSON]( - Over the years, Phil was struck by the problems of reading and writing JSON documents with SQL Server. Now that SQL Server 2016 onwards has good JSON support, he thought that the articles would be forgotten. Not so, they continue to be popular, so he felt obliged to write about how you can use SQL Server's JSON support to speed the process up....[(more)]( Tech News : General Interest [] [The Wrong Way to Clean Up the Internet]( - Having successfully fought several privacy battles, granting people the right to have themselves taken off Google search, and to have their records deleted from databases, it looks as if the European Union may soon want to regulate and license what you can watch on YouTube or other video content sites, and the audio-visual content you can publish. The impact of this would, of course, be felt way beyond Europe. Just as the GDPR affects anyone who is trading with an EU country, so the EUs plans to control video ‘channels’ are likely to affect the entire internet....[(more)]( SQL Server Security and Auditing [] [So, What is GDPR and Why Should Database Administrators Care?]( - Yes, the boss needs to know about increased risks from incorrect data handling, but what should a DBA do, other than wait for new edicts from on high?...[(more)]( Security news and thoughts [] [On the Equifax Data Breach]( - Last Thursday, Equifax reported a data breach that affects 143 million US customers, about 44% of the population. Many sites posted guides to protecting yourself now that it's happened. But if you want to prevent this kind of thing from happening again, your only solution is government regulation (as unlikely as that may be at the moment)....[(more)]( [] [Face ID, Touch ID, No ID, PINs and Pragmatic Security]( - Less than 1% of the Dropbox user base is taking advantage of the company’s two-factor authentication feature. Troy Hunt is alarmed....[(more)]( [] [Ayuda! (Help!) Equifax Has My Data!]( - Equifax last week disclosed a historic breach involving Social Security numbers and other sensitive data on as many as 143 million Americans....[(more)]( R Language [] [Automatically Write and Email Reports with R, SendGrid, & Displayr]( - This post explains how to use R to automatically write and send emails based on automatically computed analyses, so that when analysis changes or is updated, the email body text changes as well. ...[(more)]( PowerShell [] [Because you need to know PowerShell]( - The difference with PowerShell is that this is not just text output. These things are objects. And we can do stuff with that. PowerShell is a ‘typed’ environment. You already deal with objects all the time, like in Object Explorer in SSMS. PowerShell objects can include all those things, as well as .Net objects. This is where PowerShell gets its power from....[(more)]( [] [Do I Need to Master PowerShell?]( - As a SQL Server DBA, do you need to be a master of PowerShell scripts? Kendra Little's 9 minute video explains how much you need to know about PowerShell, and examples of ways she uses, and struggles with, PowerShell....[(more)]( [] [T-SQL Tuesday #94 – Lets get all Posh! Creating VMs with PS]( - If you're setting up VMs to provide redundant servers for availability and disaster recovery (HA/DR), then you'll want the machines to be configured just like the servers to which they provide failover. You can automate VM configuration with PowerShell!...[(more)]( [] [#TSQL2sDay – Starting Out with PowerShell]( - Rob Sewell offers some guidance to starting to use PowerShell with SQL and how you can help yourself....[(more)]( [] [T-SQL Tuesday #94 – Automating Configuration Comparison]( - How to use Powershell and dbatools to make sure the configuration of a test server matches production as closely as possible....[(more)]( [] [Importing Excel into SQL Server using PowerShell]( - Ever get given a Excel file to import into SQL Server for “further analysis”? Shane O'Neil give sup on T-SQL, but finds a reliable way to do it in PowerShell....[(more)]( [] [Let's get all Posh! Log Shipping]( - If you’ve ever had to setup log shipping in the SQL Server Management Studio, then you know how tedious it can be. Sander Stad builds some PowerShell log shipping functions for the dbatools module....[(more)]( PowerPivot/PowerQuery/PowerBI [] [The Power Query Branding Problem]( - A few years ago I started blogging about Power Query. Back then life was simple: I put “Power Query” in the title of a post and everyone knew what I was writing about, because Power Query was an Excel add-in you could download and install....[(more)]( [] [Power BI Theming – Chart Style Controls]( - Corporate Identity in Power BI Reports .. here we come! Hurrah for that....[(more)]( Performance Tuning SQL Server [] [How much should you worry about CXPACKET?]( - Dennes Torres offers an example to illustrate that “CXPACKET is not a problem by itself, it’s a symptom of another problem”...[(more)]( [] [How Much Can One Row Change A Query Plan? Part 1]( - Two queries – the first asks for top 100, and the second asks for top 101. The estimated plans seem identical, but one of them has a couple of very, very bad performance issues......[(more)]( HA/DR/Always On/Clustering [] [Using Synonyms with Availability Groups]( - I'm a big fan of using synonyms to point to objects in other databases, but if you've tried to use them in conjunction with Availability Groups you may have encountered a slight issue: upon failover, the synonyms are no longer valid because the instance name is part of the synonym definition....[(more)]( [] [Synchronizing SQL Server Instance Objects in an Availability Group]( - Since AGs only replicate user databases, that means that all data stored in master, model, and msdb will not be handled. Replication of your SQL Agent jobs, linked servers, logins, credentials, and all over instance objects, will not be handled for you. This means that you have to implement a process to do keep all of your cluster nodes synchronized....[(more)]( [] [How to run the Availability Group Traffic on a different NIC]( - When you deploy a new SQL Server Availability Group, the synchronization traffic between all replicas is routed by default always through your default network card. Klaus Aschenbrenner shows you how you can configure your SQL Server Availability Group, so that the network traffic goes through a dedicated different network card....[(more)]( DevOps and Continuous Delivery (CI/CD) [] [Benchmark your Database DevOps maturity level]( - Whether you’re only just starting to explore the advantages of DevOps or you’re already fully immersed in the journey, including the database brings additional advantages. Take a few minutes to complete our Database DevOps Maturity Assessment and you’ll better understand how advanced your current processes are, receive recommendations for improvements, and see how your maturity level compares with that of your peers....[(more)]( Conferences and Events [] [How much can you learn in 3 content packed days?]( - A lot, if you join us at PASS Summit. It’s action-packed learning at its best, filled with the latest Data Platform know-how. Connect with experts like Grant Fritchey, Kendra Little, and Denny Lee as they share their passion for data....[(more)]( Computing in the Cloud (Azure, Google , AWS) [] [Monitoring Azure Virtual Machine Performance]( - This post will explain how you can use Alerts in Microsoft Azure to monitor the performance of Azure virtual machines and create alerts or trigger actions when performance breaches pre-defined thresholds....[(more)]( [] [Questions About RDS SQL Server You Were Too Shy to Ask]( - There are a number of different ways that you can host SQL Server. RDS SQL Server, for example, uses SQL Server within AWS as a simple database service, much like a more versatile alternative to MySQL....[(more)]( [] [Instant Log Initialization for SQL Server in Azure]( - Dimitri Furman describes a case where the log can be initialized instantaneously and yet maintain crash recovery semantics. Specifically, this happens when database files are created directly in Azure Blob Storage....[(more)]( Career Growth [] [Tools We Use for Remote Work]( - We are in the process of trying to move to working remotely more often...could give me a list of what technologies you use for conferencing, team meetings, etc. Also maybe some pros and cons and\or issues you have run into?...[(more)]( Azure SQL Database [] [Azure SQL Database Resumable Online Index Rebuild]( - We now have the ability to resume a paused rebuilding operation rather than cancelling it....[(more)]( Azure SQL Data Warehouse and Data Lake [] [The Quick and the Dead Slow: Importing CSV Files into Azure Data Warehouse]( - Although Azure Data Warehouse is part of the bright new jewellery of the Microsoft Data Platform, the old Data Warehouse rules still apply where data imports are concerned. When it comes to data import, it pays to choose the fastest import method first and prepare your data first to ensure that it is compatible with your choice. The subtlety is in the details, as Feodor explains....[(more)]( Analysis Services / BI on the MS Stack [] [The easiest way to start using variables in DAX]( - If you still wonder why you should use variable in DAX and how to start… well, Marco Russo has a very good example....[(more)]( Administration of SQL Server [] [Script all Agent Jobs Using SQL Server Management Studio]( - While doing a recent server move, I came across the need to script all agent jobs and save them to a file so that if we needed to reference what was there after the move we could get at the agent jobs without having to restart the old SQL Server....[(more)]( [] [Database Fundamentals #10: Modifying Tables]( - Invariably, either while building a new database, or while maintaining old ones, you will find that the business needs change or that you’ve made a mistake. Either way, you will need to modify the tables that you’ve created. ...[(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