What can your data do for you in 2017? (Database Weekly 1/9/2017)

The Complete Weekly Roundup of SQL Server News In this issue: T-SQL - [Regular Expressions in SQL Server] - [SQL Server v.Next : STRING_AGG Performance, Part 2] - [Be Careful of Your Create Stored Procedure Batch] The Lighter Side - [An SQL Injection Attack Is a Legal Company Name in the UK] SQL Server News - [SQL Updates Newsletter – December 2016] - [Your 10 Favorite Blog Posts of 2016] Replication - [Transactional Replication from Availability Groups to Azure SQL Database: Part 5 – Testing] R Language - [3 Reasons to Consider Microsoft's R Tools for Visual Studio] - [7 Visualizations You Should Learn in R] PowerShell - [Getting Started with Always Encrypted using PowerShell] PowerPivot/PowerQuery/PowerBI - [Use of RANKX in Power BI measures] - [Ten Things Data Can Do For You in 2017 (aka Ten Things Power BI Can Do For You in 2017)] Polybase/HDInsight - [Partitioned Views With Polybase] - [Working With Azure SQL DW] Performance Tuning SQL Server - [Make Your Application?’s Name Heard] - [Filtered Indexes and Variables: Less Doom and Gloom] - [Query Store and In-Memory OLTP] - [SQL Server Performance Dashboard Reports unleashed for Enterprise Monitoring] - [Configure Fields and Predicates for Multiple X-Events] - [Parallelism and tempdb data file usage in SQL Server] - [Hide & Seek With Extended Events] - [SQL Server Event Handling: Extended Events] Internet of Things - [Free Web Ops & DevOps Ebooks] Hardware News - [Build Me A Build: Assembly Olympics] HA/DR/Always On/Clustering - [Configuring MSDTC Across Domains] ETL/SSIS/ELT - [What Is ETL?] Database Design, Theory and Development - [The Nested Set Model] Data Mining/Data Analysis - [Free Data Ebooks] Computing in the Cloud - [Exploring Azure Storage for SQL Server DBAs] - [Playing with Azure, Stretching Databases] Columnstore Indexes - [Columnstore Index Performance: Rowgroup Elimination] - [Columnstore Index Performance: BatchMode Execution] Career Growth - [Tell Us What You Make: The 2017 Data Professional Salary Survey] Backup and Recovery - [How Old Are Your Backups?] Azure Analysis Services - [Where Azure Analysis Services Fits Into BI & Analytics Architecture (Part 3)] AI/Machine Learning/Cognitive Services - [How six lines of code + SQL Server can bring Deep Learning to ANY App] Administration of SQL Server - [Starting with Chocolatey] - [Database Snapshots For Everyone!] [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-01-09 [SQL Monitor] SQL Monitor - Always have the answers to SQL performance issues SQL Monitor keeps an eye on your SQL Servers 24/7, so you don't have to. It helps you proactively monitor the performance of your SQL Servers, saving you time, and makes sure you always have the answers to tough performance problems. [Find out more.] [SQL Prompt] How can you help your team write better, shareable SQL faster? Find out by discovering 15 Super SQL Tips from Microsoft MVPs and other SQL Server experts. Using SQL Prompt to write, refactor, and share SQL, they show how it strips away the repetition of coding and standardizes it everywhere. [View the tips and download a free trial.] [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.] Editorial - Keeping up to, and down with, Date I spend a lot of time reading technical books and I expect that you do too. Perhaps you are sitting there nodding and saying 'Yeah, we have to keep current with the technology'. If so, lucky you. Sadly, I'm in so much in catch-up mode that I'm still struggling to absorb some of the advances first made in SQL Server 2005. I'm reasonably confident with Query Store, dynamic data masking and Hekaton, but there is just so much I don't know; and the more I read, the more awed I am by the gaps in my knowledge. It was this thought that ran through my mind the other day when someone contacted me to say that they were throwing out some old computer books and did I want them. Hell, yes. Good computer books never go out of date. I challenge you to write a book that is wiser, and more relevant for understanding the human elements of software engineering, than 'The Mythical Man-Month: Essays on Software Engineering' by Fred Brooks (1975). Even for SQL Server, Ken Henderson's three 'Guru's Guides' (2003) are classics that are wonderful to read, even though you have to treat quite a lot of what he says about the technology in an historical perspective. If you read a mixed diet of books on any technology, some old, some new, you get the best of both worlds. When you're learning PowerShell, for example, the older books are better at explaining the context and the fundamental concepts, because their readers are almost guaranteed to know nothing of the topic. The more recent books are more eager to explain the latest stuff, and are less enthusiastic to gather up the readers for whom the whole topic is rather mysterious. The same seems to be true of any longstanding technology: it pays to read a classic, followed by something that brings you up-to-date. It is foolish to insist on reading only up-to-date books on technology. Nothing beats getting the broad perspective. There is, for example, no pleasure quite like reading an old book on ALM and Business transformation techniques from the 1990s, and discovering ideas that came from Agile ten years later. This is why I have such a taste for books on technology, whatever their age. Good writing is always good writing, whenever it was done. 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. T-SQL [Regular Expressions in SQL Server] - Dev Nambi's open-source project, sql-server-regex, lets you run regular expressions in T-SQL queries using scalar and table-valued functions....[(more)] [SQL Server v.Next : STRING_AGG Performance, Part 2] - Aaron Bertand continues his performance comparisons, pitting the new STRING_AGG() function against the traditional FOR XML PATH approach....[(more)] [Be Careful of Your Create Stored Procedure Batch] - The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch. This means that anything else you have in that batch will be considered as part of the procedure, regardless of BEGIN..END....[(more)] The Lighter Side [An SQL Injection Attack Is a Legal Company Name in the UK] - Someone just registered their company name as ; DROP TABLE "COMPANIES";-- LTD....[(more)] SQL Server News [SQL Updates Newsletter – December 2016] - Round up of Microsoft's SQL-related news, announcements, whitepapers, scripts, articles, bugs and more....[(more)] [Your 10 Favorite Blog Posts of 2016] - Ten most popular posts on Brent Ozar.com in 2016....[(more)] Replication [Transactional Replication from Availability Groups to Azure SQL Database: Part 5 – Testing] - You’ve configured a remote distributor, configured all of your AG replicas as publishers, and configured your SQL Database as a subscriber! Now you want to ensure that transactions are replicating to the database, and that they continue to do so if there is a failover in the AG....[(more)] R Language [3 Reasons to Consider Microsoft's R Tools for Visual Studio] - 1. Code snippets targeted toward Microsoft R Server 2. Incorporate R projects as part of a broader Visual Studio solution 3. IntelliSense...[(more)] [7 Visualizations You Should Learn in R] - Data visualization is an art of how to turn numbers into useful knowledge. R Programming lets you learn this art by offering a set of inbuilt functions and libraries to build visualizations and present data. Before the technical implementations of the visualization, let’s see first how to select the right chart type....[(more)] PowerShell [Getting Started with Always Encrypted using PowerShell] - How to configure Always Encrypted from the command line, using PowerShell....[(more)] PowerPivot/PowerQuery/PowerBI [Use of RANKX in Power BI measures] - The RANKX function in Power BI might have an unexpected behavior when applied to a column that has a specific sort order in the data model. This article explains why, and how to address this issue....[(more)] [Ten Things Data Can Do For You in 2017 (aka Ten Things Power BI Can Do For You in 2017)] - Rather than talking about HOW to do things with data, let’s start the new year by spending some time on WHY we care about data investments in the first place....[(more)] Polybase/HDInsight [Partitioned Views With Polybase] - Kevin Feasel want to replicate “SQL 2000 partitioning” using Polybase so that if there were constraints on each individual table, you would be able to perform partition elimination, only querying the table(s) with potentially relevant data....[(more)] [Working With Azure SQL DW] - The first step to working with Polybase in Azure SQL Data Warehouse is to provision an Azure SQL Data Warehouse instance. Kevin Feasel shows how to load data into Azure SQL Data Warehouse using Polybase. ...[(more)] Performance Tuning SQL Server [Make Your Application?’s Name Heard] - That way, when you open up sp_who2 or sp_whoisactive, you won't be greeted with a dozen sessions, all sporting a program_name of .Net SqlClient Data Provider....[(more)] [Filtered Indexes and Variables: Less Doom and Gloom] - While index filter conditions don’t deal with parameters well, you can still use parameters for predicates in other columns. That’s an important distinction to make when evaluating filtered index usage....[(more)] [Query Store and In-Memory OLTP] - Once you have Query Store enabled on your databases, runtime statistics are generated for your queries; but what about the natively-compiled stored procedures and memory optimised tables that come with In-Memory OLTP?...[(more)] [SQL Server Performance Dashboard Reports unleashed for Enterprise Monitoring] - To support hosting SQL performance dashboard reports on a central SQL Server Reporting Services instance, we have customized SQL Server 2012 Performance Dashboard Reports, added new reports and uploaded in Tiger toobox github repository for customers and SQL community. ...[(more)] [Configure Fields and Predicates for Multiple X-Events] - A nice animated gif from Kendra Little showing how to set global fields and predicates for multiple events, when creating an Extended Events session....[(more)] [Parallelism and tempdb data file usage in SQL Server] - I’m sometimes asked if the number of CPU cores used by a query determines the number of tempdb files that the query can use. Good news: even a single threaded query can use multiple tempdb data files....[(more)] [Hide & Seek With Extended Events] - Dave Mason demonstrates an inconsistency between the event_stream target and other targets when capturing Extended Events data. If you use the "Watch Live Data" grid within SSMS, this will affect you....[(more)] [SQL Server Event Handling: Extended Events] - Dave Masonshows how to handle an XEvent by accessing the event_stream target, via the .NET framework. He demonstrates using C#....[(more)] Internet of Things [Free Web Ops & DevOps Ebooks] - Free Web Ops & DevOps Ebooks from O'Reilly Media ...[(more)] Hardware News [Build Me A Build: Assembly Olympics] - Erik Darling builds a new PC....[(more)] HA/DR/Always On/Clustering [Configuring MSDTC Across Domains] - Bill Graziano's checklist for configure cross-domain MSDTC with a limited trust in place between some servers....[(more)] ETL/SSIS/ELT [What Is ETL?] - ETL processes typically include programmatically retrieving data from one or more sources, performing updates on this data (if needed), and finally the pushing of the data to a destination structure....[(more)] Database Design, Theory and Development [The Nested Set Model] - The third rule of the Nested Set model is that you should always operate on the tree (CrUD ops) using stored-procedures and/or triggers that encapsulate all the nitty-gritty details of maintaining the correct position values during said insert/update/delete operations. ...[(more)] Data Mining/Data Analysis [Free Data Ebooks] - Free Data Science and Big Data Ebooks from O'Reilly Media ...[(more)] Computing in the Cloud [Exploring Azure Storage for SQL Server DBAs] - If you need to run SQL Server in an Azure Virtual Machine, your choice of Azure storage will have a great effect on its performance. Joshua explains the value of using a lab environment to allow you to make well-informed VM storage decisions when the time comes to provision your production system....[(more)] [Playing with Azure, Stretching Databases] - Stretching a database from on-premises to the cloud (Azure)....[(more)] Columnstore Indexes [Columnstore Index Performance: Rowgroup Elimination] - The columnstore index has no key columns as it would be prohibitively expensive to maintain the key order. This can impact the analytics query performance significantly if a full scan of a columnstore index containing billions of rows is needed to apply range predicates. Columnstore index solves this issue using rowgroup elimination. ...[(more)] [Columnstore Index Performance: BatchMode Execution] - The basic idea of batch mode processing is to process multiple values, hence the term ‘batch’, together instead of one value at a time. Batch mode processing is perfectly suited for analytics where a large number of rows need to be processed, for example, to compute aggregates or apply filter predicates. ...[(more)] Career Growth [Tell Us What You Make: The 2017 Data Professional Salary Survey] - Brent Ozar is running a totally anonymous salary survey (no email, IP address, city, or anything like that). It’s open to all database platforms. All responses are totally open - you can view the raw data....[(more)] Backup and Recovery [How Old Are Your Backups?] - How well is your backup strategy is being executed? Are you covering yourself for an acceptable amount of data loss for each database? Tim Ford can answer these questions with a single query....[(more)] Azure Analysis Services [Where Azure Analysis Services Fits Into BI & Analytics Architecture (Part 3)] - Melissa Coates explains how Azure Analysis Services fits into common BI architectures...[(more)] AI/Machine Learning/Cognitive Services [How six lines of code + SQL Server can bring Deep Learning to ANY App] - When you put intelligence (AI, ML, AA, etc.) close to where the data lives, then every app becomes an intelligent app. SQL Server can help developers and customers everywhere realize deep learning in their applications with just a few lines of code....[(more)] Administration of SQL Server [Starting with Chocolatey] - Steve Jones is hooked on Chocolatey and explains how he uses it to install everything he needs on any new machine....[(more)] [Database Snapshots For Everyone!] - Database snapshots have been available in SQL Server Enterprise edition in the last several versions. But if you’re lucky enough to have 2016 at work, go on and upgrade to SP1….you get extra-lucky special database snapshots in any version!...[(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 ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: [webmaster@sqlservercentral.com]