The Complete Weekly Roundup of SQL Server News

In this issue:

Virtualization and Containers

Vendors/3rd Party Products

T-SQL

SQL Server Security and Auditing

SQL Server Security

SQL Server on Linux

SQL Server News

Security news and thoughts

Reporting Services

R Language

PowerShell

PowerPivot/PowerQuery/PowerBI

Performance Tuning SQL Server

NoSQL

Microsoft News

HA/DR/Always On/Clustering

DevOps and Continuous Delivery (CI/CD)

Database Design, Theory and Development

Data Access / ORMs

Conferences and Events

Career Growth

Backup and Recovery

Azure SQL Database

Azure SQL Data Warehouse and Data Lake

Azure CosmosDB

Administration of SQL Server

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-10-02

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
Database DevOps How mature are your database DevOps processes?
Complete Redgate’s Database DevOps Maturity Assessment to understand how mature your processes are, how you compare against your peers, and receive recommendations for improvements. Take the assessment
Editorial - The Worms-eye View

I meet a lot of people in IT these days who seem to have a very narrow field of expertise. We kid ourselves that IT has now become so complex that we need to specialize in roles so narrow that one can only hope that the world of work will always value us, just for our ability to program in JavaScript with jQuery. It is a symptom of the very transient, role-based nature of any employment nowadays.

Times have changed. In the first half of the twentieth century, the common attitude in the West was that you worked for one company for all or most of your working life. This had ramifications both for employer and employee. The employer had a responsibility to nurture the potential and skills of the employee, and the employee was expected to take a broad view in understanding how the business worked. For any aspiring manager, it was essential to get a broad 'worms-eye-view' of what it was like to work within a range of teams, and a diversity of jobs.

This meant that employees were required to change jobs quite often, within the company, to give them a better understanding of the business processes and to give them the opportunity of experiencing different work environments and different types of team. For a while, you might work in Sales, then a spell in Manufacturing, then on to Finance. It was a huge game of Musical Chairs.

Even within the IT activities, people changed jobs often. I experienced Unix workstations, CAE, CAD, Helpdesk, Development, Database administration, production support, network architecture and a range of other tasks. I loved it. The most immediate impact of this practice was to see how all the IT processes were supposed to work together, but sometimes didn't. One developed a terrific sense of how effective communication of ideas and requirements could rapidly get things done. It also demolished the strange tribal barriers that so often get thrown up within IT. We were all one tribe, because we'd all been initiated in all the common IT roles.

Even the most elaborate team-based development methodology won't compensate for the blinkered attitudes and tribal cultures that come from over-specialization. If, for example, you've experienced being an Ops guy and understand the pain points, you are going to more likely to understand DevOps processes when you are developing applications. When you've worked on the requirements for compliance in business IT, you'll never sneer at the work of the people who ensure that IT practices conform with industry standards. If you've supported end-users, you'll quickly recognize bad application design. There is a lot to be said for diverse experience in IT.

Phil Factor.

» 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

The docker kill command - The stop command sends a SIGTERM signal to the main process within the container whereas the kill command sends a SIGKILL signal....(more)

Vendors/3rd Party Products

SQL Monitor Quick Tip: Using the SQL static code analysis performance rules - Static code analysis parses the source code, checking the syntax for compliance with a built-in set of rules. These rules are designed to encourage good coding practices and, applied during development and testing work, help you minimize the number of ‘code smells’ that creep into your application and database builds....(more)

Templates and Snippets in SSMS and SQL Prompt - In SSMS, we have access to templates that provide building blocks for creating various types of query, common routines to create various schema objects, or to do common DBA tasks such as running backups. We can also use SSMS T-SQL code snippets to define the basic structure for a block of code or a T-SQL statement that creates the common types of database objects....(more)

How Redgate use SQL Clone internally to provision databases - A common thread behind the software Redgate develop is a problem or issue they’ve come across in the software development process themselves. In this interview, Redgate IT Manager Joseph Woodhouse explains how SQL Clone is being used to implement a new database provisioning process at Redgate right now...(more)

T-SQL

How to Write Efficient TOP N Queries in SQL - A very common type of SQL query is the TOP-N query, where we need the “TOP N” records ordered by some value, possibly per category. In this blog post, we’re going to look into a variety of different aspects to this problem, as well as how to solve them with standard and non-standard SQL....(more)

SQL Puzzle - Prime Numbers - Kenneth Fisher's T-SQL Challenge: calculate the first 10 prime numbers....(more)

Saving and Restoring Session Option Settings - Kenneth Fisher shows how to find the values of the various SET options, using the @@OPTIONS function....(more)

Just How Minimal Can That Problem Repro Get? - Often among the hardest of my decisions is whether I should spend more time trying to simplify a given problem by eliminating additional factors, shrinking the data needed for a repro, etc... or just put all that effort into investigation purely aimed at understanding the behavior....(more)

SQL Server Security and Auditing

Checking Out SQL Vulnerability Assessment - A new tool from Microsoft where you can discover, track, and remediate potential database vulnerabilities. This tool is available for both on-premises SQL Server and Azure SQL Database. ...(more)

Auditing Data Access in SQL Server for GDPR Compliance - In relation to the GDPR, I’ve recently been looking at the tools available within SQL Server to support auditing our databases. Many of you out there will already has full-scale auditing solutions in place. For others though, the prompting of GDPR could be the first time you’re really thinking about it....(more)

SQL Server Security

Effective permissions on SQL Server - SQL Server Management Studio allows you to view effective permissions on an object, but it’s limited in a few important respects. To work around some of those limitations, I’ve built a stored procedure to display all the defined and effective permissions across an entire SQL Server database....(more)

SQL Server on Linux

Would you consider running SQL Server on Linux? - I'm wondering how many Linux people out there would consider running SQL Server on Linux in a production environment. If not, why not?...(more)

SQL Server News

Enhancing query performance with Adaptive Query Processing in SQL Server 2017 - SQL Server 2017 and Azure SQL Database introduce a new generation of query processing improvements that will adapt optimization strategies to your application workload’s runtime conditions....(more)

SQL Server 2017: Fast, faster, and the fastest database everywhere you need it - Bob Ward on the speed of SQL Server 2017: "From a SQLOS scheduling engine that minimizes OS context switches to read-ahead scanning to automatic scaling as you add NUMA and CPUs. And we parallelize everything! From queries to indexes to statistics to backups to recovery to background threads like LogWriter. We partition and parallelize our engine to scale from your laptop to the biggest servers in the world."...(more)

In-database Machine Learning in SQL Server 2017 - You can start using Python-based in-database Machine Learning Services for production usage now. With support for both R and Python, we have rebranded ‘R Services’ to ‘Machine Learning Services’....(more)

Security news and thoughts

Deloitte hit by cyber-attack revealing clients’ secret emails - Hackers may have accessed usernames, passwords and personal details of top accountancy firm’s blue-chip clients...(more)

Reporting Services

MSBuild support for Reporting Services projects now available - By invoking msbuild.exe on your project or solution file, you can orchestrate and build products in environments where Visual Studio isn’t installed....(more)

R Language

Box-and-whisker plot and data patterns with R and T-SQL - Understanding exploratory graphics is vitally important to the R programmer/data science newbie. This week I wanted to share what I learned about the box-and-whisker plot, a commonly used graph in R – and one that greatly helps to understand and interpret spread of data. ...(more)

R Markdown for documents with logos, watermarks, and corporate styles - I outline how I structure analytical project folder systems and some hints for matching R Markdown documents to a corporate style guide including adding logos, watermarks, and of course colours and fonts....(more)

PowerShell

Scheduling powershell tasks with sql agent - By default, only members of the sysadmin role are allowed to create jobs with the CmdExec Job Step, but adding non-sysadmins as principals to the CmdExec proxy works as well....(more)

Creating a PowerShell Module from a DBA’s Perspective - How to create a PowerShell module so you can store for future use, the functions that are useful to as a DBA....(more)

PowerPivot/PowerQuery/PowerBI

Ribbon Chart is the Next Generation of Stacked Column Chart - The new Ribbon Chart may look very similar to Stacked Column Chart. but it is more powerful and gives you much more interesting insight....(more)

Power BI Blog Slicer - Julie Smith offers a Power BI report built off of the content from Microsoft’s Power BI blog, set up to run daily and refresh....(more)

Setting Data Types On Columns Containing Table Values In M - Working on a Power BI custom data connector, Chris Webb works out how to avoid having to set the data types on the new columns that appear in a table – even if the function you have invoked returns a table with data types set on columns....(more)

Performance Tuning SQL Server

Live Query Plans and Blocking - Live Query Plans are cool, but they don't show you everything. You still need other ways of looking at DMVs for issues....(more)

10 Cool SQL Optimisations That do not Depend on the Cost Model - Today, we don’t want to talk about cost based optimisation, which depend on a database’s cost model. We’ll look into much simpler optimisations that can be implemented purely based on meta data (e.g. constraints) and the query itself. ...(more)

A Serial Parallel Query - This blog post demonstrates a technique to create demos that show how parallel thread imbalance can lead to poor performance....(more)

Changes to query text and the effects in Query Store - Be careful when changing objects (stored procedures, functions) where you have queries with forced plans. Inadvertent changes in spacing or casing create entirely separate queries in Query Store, which means that the original query with a forced plan is no longer in use....(more)

NoSQL

Why SQL is beating NoSQL, and what this means for the future of data - After years of being left for dead, SQL today is making a comeback. How come? And what effect will this have on the data community?...(more)

Microsoft News

Meet the new Microsoft R Server: Microsoft ML Server 9.2 - Microsoft ML Server 9.2 is now available. ML Server provides a scalable production platform for R — and now Python — programs. The basic idea is that a local client can push R or Python code and have it operationalized on the remote server....(more)

PowerShell in Azure Cloud Shell (Preview) is now publically available in Azure Portal - Microsoft announce the public availability of PowerShell in Azure Cloud Shell....(more)

HA/DR/Always On/Clustering

Adding a Replica back into a SQL Server Availability Group - Klaus Aschenbrenner explains why you might remove a Replica from an Availability Group, and how to add it back....(more)

DevOps and Continuous Delivery (CI/CD)

What are the leading DevOps drivers in financial services? - DevOps is gaining ground everywhere. By offering a route to releasing software faster, with fewer errors, it can give companies which adopt it an immediate advantage. Unfortunately, it’s not something you can just buy or decide to do tomorrow. Instead, it’s a shift that needs the right guidance to become reality. In the financial services sector specifically, there are three main drivers for adopting DevOps....(more)

Benchmark your Database DevOps maturity level - Whether you’re exploring 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 the 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)

Database Design, Theory and Development

How the rowversion datatype works when adding and deleting columns - The rowversion data type is useful for determining rows that have changed, because it it automatic and the user cannot override the value in the column. However, there is a major concern when you use rowversions, and that is what happens when you change the structure of the table, and expect the consumer to see that change? ...(more)

Data Access / ORMs

Identifying SqlConnection objects in a dump - I recently had to troubleshoot an ADO.Net connection pool exhaust issue. This problem may indicate a connection leak, but it can also be caused by an undersized connection pool....(more)

Microsoft Excel, TLS & SQL Server – Important Considerations - TLS 1.0 is being deprecated due to various known exploits and will no longer be PCI compliant as of June 30th, 2018. This could impact a popular feature in Excel to import data from SQL Server....(more)

Conferences and Events

All Day DevOps–A Free, Virtual Conference - The schedule is packed, with speakers from all over the world covering a variety of software development topics from a DevOps perspective. ...(more)

Announcing Seattle Freecon 2017 - A great line-up of SQL Server people put on a free conference on the Tuesday before PASS Summit in Seattle....(more)

Career Growth

How to Hire a Junior DBA - Brent Ozar on the questions you should ask yourself when setting out to hire a junior DBA. ...(more)

Backup and Recovery

Checklist: DR Plan Sanity Check - It can be a big benefit to get another viewpoint on your disaster plan. No matter how well you think you’ve covered yourself, someone who thinks about things differently than you do can often see something you don’t....(more)

Azure SQL Database

Machine Learning Services with R support in Azure SQL Database - Announcing the preview of Machine Learning Services with R support in Azure SQL Database....(more)

Native Scoring using PREDICT function in Azure SQL Database - The PREDICT function allows you to perform scoring in real-time using certain RevoScaleR or revoscalepy models in a SQL query without invoking the R or Python runtime....(more)

SSIS Package Execution in Azure - SQL Server Integration Services is in Public Preview on Azure!...(more)

Azure SQL Data Warehouse and Data Lake

How to generate 1 billion rows using U-SQL - I was interested in generating some dummy data to do some load testing in MS Azure and came up with a pretty nifty way to generate lots and lots of data using U-SQL. The tip is to simply create a small U-SQL custom generator and use it to extract from a dummy file....(more)

Azure CosmosDB

Query Azure CosmosDB from a SQL Server Linked Server - Describing how to connect direct to CosmosDB from SQL Server using a SQL Linked Server....(more)

Administration of SQL Server

Modern SQL Server Servicing Model - Glenn Berry explains a pretty big change for how Microsoft are going to service SQL Server, starting with SQL Server 2017....(more)

Which sp_configure Options Clear the Plan Cache? - There’s no logic built into RECONFIGURE – it just flat out blows the plan cache if you issue any sp_configure command to alter MAXDOP, regardless of whether the value was changed or not....(more)

Preparing your data platform for peak period sales - In the last few years, Black Friday and Cyber Monday have become synonymous with both getting a good bargain and causing retail pandemonium. However, times are changing. News items about people queueing outside shops 4am have been replaced with those about just how much consumer spending has moved online. How do you prepare?...(more)

Just Check ALL the Boxes - Today I ran into something on a client server I unfortunately see too often. The DBA goes through the trouble of configuring and setting up alerts\operators but doesn’t really understand what the options in the configurations mean...so they check all of them....(more)

SQL Server Memory Myths and Misconceptions - Many of these have come up from even very seasoned DBAs so don’t feel bad if you’ve been laboring under a misconception yourself....(more)


Administrative