The Complete Weekly Roundup of SQL Server News

In this issue:

Virtualization

Vendors/3rd Party Products

T-SQL

SQL Server Security

SQL Server Internals

Replication

R Language

PowerShell

PowerPivot/PowerQuery/PowerBI

Performance Tuning SQL Server

Microsoft News : General Interest

HA/DR/Always On/Clustering

Events to attend

ETL/SSIS/ELT

DevOps and Continuous Delivery (CI/CD)

Data Warehousing

Data Mining/Data Analysis

Data Access / ORMs

Computing in the Cloud

Backup and Recovery

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-03-13

SQL Clone NEW product launch: SQL Clone - live stream this March!
Data Platform MVPs Grant Fritchey and Steve Jones show how to create database copies in seconds using MBs of disk space with Redgate’s NEW database provisioning tool SQL Clone! Tune in to this live stream session on March 29th 2017. Register now.
Editorial - The Details are in the Small Print

I was clearing out some shelves the other day when I came across a vast strategy document I'd written for a multi-national about their IT development strategy. The contents outlined a variety of 'Agile' techniques and organizational changes that would underpin a business reorganisation. It was old. I flicked through it. 'Good stuff.' I thought. I leant it to a colleague who I thought would find it of interest. He admired its thickness and dignity, but I suspect he never read it.

Although I occasionally weep at the years spent writing such documentation that nobody reads, I've never lost sight of its importance. For example, Backup Strategy documentation is vital though, like the small print in an advert, it's there more to protect than to be read. It demonstrates to the organization that the team has thought analytically about the topic, in depth.

When people come into IT from another discipline, where they were used to reading documents and communicating their ideas that way, they are often astounded to learn that whole classes of IT Strategy documents are often purely tactical rather than informative. This is especially true where IT proposals will cause organizational 'restructuring' and possibly job loss. Such documents serve their tactical purpose better unread, although if you know how to delve into their 'small print', you can learn a lot about an organization, and the likely longer-term consequences of any proposed changes.

Planning and strategy documents are merely part of the broad spectrum of IT documentation. For programming, again, documentation shows that the developers have thought through the function and structure of their code, analytically. It is an essential part of any team process. Sadly, many developers often have a remarkable antipathy to the very thought of producing any such documentation, perhaps on the assumption that no-one will ever read. They will spend hours in rapt concentration on an apparently trivial and unimportant code routine, yet recoil in horror, like a vampire at garlic, when asked to document their work. Some even believe that it isn't part of their job, like a celebrity chef who refuses to help wash up. Others delude themselves with the belief that their code is 'self-documenting'.

Sure, there are many more exciting activities in life, but if you can master the art of writing the whole range of documentation that underlies the development and upkeep of modern business systems in the typical enterprise, then you are greatly empowered. If you can learn the higher and more zen-like skill of reading and understanding it, then you'll find that the 'small-print' in any organization is always illuminating.

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

Exporting Container Images - Creating a custom image on a docker hub then sharing it using the docker save and load commands....(more)

Vendors/3rd Party Products

Automatically filling your SQL Server test databases with data - This article explains how you can use SQL Data Generator (SDG) and PowerShell to automate data provisioning for test databases, during the database development cycle....(more)

Extending DevOps practices to SQL Server databases - In this free webinar, Steve Jones and Arneh Eskandari show how to use migration scripts as part of an automated database deployment. For the demo, they’ll be plugging Redgate’s Database DevOps solution into Git, TeamCity and Octopus Deploy. Register now....(more)

T-SQL

How does Row versioning impact tempDB? - Rohit Garg explains why a select statement on a table can consume huge amount of resources in tempDB, when using row versioning-based isolation levels...(more)

Using GROUP BY instead of DISTINCT - “I want to get one row for each person. Why do I have two rows for this guy?” “Well, you have two different addresses.” “But I used DISTINCT?” “Yes but you are including the address and there are two of them.” “Ok, so how do I fix it?”...(more)

SQL Server vNext: Scalar Subquery Simplification - Dewscribing the new optimizer rule in SQL Server vNext, CollapseIdenticalScalarSubquery, designed to help the optimizer cope better with inefficient queries....(more)

Identifying a row’s physical location - Wyane Sheffield shows how to identify a row’s physical location. and then various methods used to work with this value once returned....(more)

SQL Server Security

SQL Server Fixed Role Permissions - It is very important to understand who has what level of access within the server and databases on that server. Sometimes we see users being granted server or database access through the fixed roles available in SQL Server. ...(more)

Application Database Security Design - Part 1 - Authentication for SQL Server - I'm part of a new development effort which will use a SQL Server back-end. We want a secure database layer. What is the best way to architect/design this?...(more)

Old is the New New: SQL Server 2016 Learns Ancient Auditing Tricks - With the zeal applied to anything new and wonderful, SQL Server now offers system-versioned temporal tables. We throw in schema-based security and end-to-end encryption, and finally the data in our database might be almost as hard to copy, steal, or fraudulently manipulate, as the data in a ledger!...(more)

Encrypting SQL Server: Dynamic Data Masking - Dynamic Data Masking is a good way of rendering data unreadable for such purposes as user-acceptance testing, or demonstrating an application. It doesn't encrypt the data, and a knowledgeable SQL user can defeat it. However it provides a simple way to administer from the database what data the various users of a database application can and can not see, making it a useful tool for the developer....(more)

SQL Server Internals

Context in perspective 3: Seven vftables for seven interfaces - The third part of a series about various places where “context” in some form or another crops up within SQL Server, this time looking at one aspect of polymorphic behaviour, whereby the address we use to refer to an object may change depending on what interface the called member function belongs to....(more)

Replication

Monitoring SQL Server Replication With Extended Events - “How do we know replication is actually delivering transactions? How long were things taking to be delivered to the distributor? Was there a lot of latency between the distributor and the subscriber?” ...(more)

R Language

Employee Retention with R Based Data Science Accelerator - Employees’ behavior can better be understood and analyzed through such data as internal and external social media postings. Such data can be leveraged for the analysis of, for example, sentiment and thereby determination of an employees likelihood of leaving the company. ...(more)

The Rise of Civilization, Visualized with R - An animation by geographer James Cheshire that shows something at once simple and profound: the founding and growth of the cities of the world since the dawn of civilization....(more)

Generating Frequency Table - The number of times each data element or class is observed is called its frequency. A table that displays the discrete variable and number of times it occurs in the data set is called a ‘Frequency Table’....(more)

PowerShell

Quickly Creating Test Users in SQL Server with PowerShell using the sqlserver module and dbatools - How to add a user to a SQL Server Database Role with PowerShell and create test users, using the latest sqlserver module and the dbatools module....(more)

DSC Install of SQL Server - While the SQL Server installation wizard is pretty good these days, it does not allow you to automate all of the things you need to do to configure a SQL server. Powershell does. Desired State Configuration (DSC) is functionality built into Powershell that allows for the installation and configuration of a SQL Server....(more)

PowerPivot/PowerQuery/PowerBI

Theming in Power BI - Finally, we have theming in Power BI. A much requested and required feature, especially for organizations where using their corporate color themes in everything they do, is a way of life. ...(more)

Content Pack; Sharing, Self-Service and Governance Together - The 'content pack' is a very effective method of sharing Power BI content with others. You will have full governance around your Power BI content, and users will be able to use self-service features of Power BI. ...(more)

Performance Tuning SQL Server

SQL Server system_health Session Retention - The system_health session is an Extended Events session that collects system data that you can use to help troubleshoot performance issues in the Database Engine. However, your individual file size is 5 MB and number of maximum rollover file is 4. Meaning you will only get 20 MB of data. Once that limit is reached your older data is lost and there is no way to recover....(more)

Why am I getting so many checkpoint files when I have In-Memory OLTP enabled? - Data file contains rows from insert and update operations. Delta file contains deleted rows. Over time, these files can be ‘merged’, increasing efficiency. Unneeded files after the merge can be removed eventually - but this can only happen after a log backup....(more)

The Side Effects of Drop 'Unused' Index - Dropping a potentially ‘unused’ index can have a negative influence on the optimizer’s cardinality estimation. ...(more)

How to Quickly Tell if an Execution Plan has Multiple Missing Index Requests - There may be more than one index suggestion – but there is never more than one green hint....(more)

Stack Dumps in SQL Server (Dear SQL DBA Episode 33) - Learn what a Stack Dump is in SQL Server and watch a demo where Kendra Little causes a stack dump against a test SQL Server instance under load. Learn how to find information about stack dumps on your SQL Server, and how to escalate them when required....(more)

Overindexing: Missing Index DMVs and the Database Tuning Advisor - SQL Server has tools that suggest indexes– and they’ll even auto-create the indexes for you. I’m not a huge fan of these tools because they’ve got some notable flaws: they lead to creating more indexes than you need, and they aren’t super smart about the indexes they recommend....(more)

Microsoft News : General Interest

Windows Server on ARM: It's happening - Microsoft has committed publicly to use ARM chips in machines that will be running cloud services in its own datacenters before year-end....(more)

HA/DR/Always On/Clustering

Blue Screen Your SQL Server On-Demand - When testing new features or helping clients implement SQL Server High Availability solutions, it's useful to simulate blue screen scenarios...John Sterrett shows that it might be much easier than you think. ...(more)

Events to attend

24 Hours of PASS - The next edition will be held May 3-4, 2017 and will focus on data security relating to application, network, database, IoT, cloud security, or avoiding identity theft....(more)

SQLintersection Spring 2017 - SQL Skills announce their 9th SQLintersection, starting May 22 2017....(more)

ETL/SSIS/ELT

Parsing SSIS Catalog Messages for Lookup Performance, v2 - Andy Leonard' script to return all Lookup Transformation performance messages captured by the SSIS Catalog with Basic logging enabled ...(more)

DevOps and Continuous Delivery (CI/CD)

Introducing DLM Techniques for a Multi-Database Multi-Server System - Although the techniques of Database Lifecycle Management can reduce the timescales for the delivery of new functionality to business systems, what if the database 'layer' consists of several large interdependent databases and data flows with replication and audit? Does DLM scale to this level of complexity? ...(more)

Data Warehousing

Using the DbFit Framework for Data Warehouse Regression Testing - It is ironic that the users of database application need to rely on the very technologists that created the system to then devise and run their acceptance tests. Surely someone has devised a test system for databases that is simple enough for ordinary tech-savvy people to use and for them to create the tests? ...(more)

Data Mining/Data Analysis

Metadata Hygiene - Measuring and improving the quality of data is an important part of any data initiative, especially in the data warehousing space. While data quality does get its share of attention, there is a concept that is equally important but is sadly overlooked during most data projects....(more)

Implementing Analytics: Scaling Up! - There’s a couple of things you’ll want to consider prior to rolling out your analytics platform to a wider audience: structure and storage for your data sets, the right tools for scaling up your analytics; your approach to ensuring data quality; and proper documentation to guide your teams in using data correctly....(more)

Data Access / ORMs

Many SQL Performance Problems Stem from “Unnecessary, Mandatory Work” - Probably the most impactful thing you could learn about when writing efficient SQL is indexing. A very close runner-up, however, is the fact that a lot of SQL clients demand tons of “unnecessary, mandatory work” from the database....(more)

Computing in the Cloud

What Are Azure Managed Disks? - Azure Storage provides scalable, durable, and highly available storage, and to facilitate this, there are two types of storage account that can be used to provision blob, table, queue, file storage, and virtual machine (VM) hard disks. ...(more)

New White Paper: How to Build an Always On Availability Group in Google Compute Engine - You’re a database administrator, Windows admin, or developer. You want to build a Microsoft SQL Server environment that’s highly available, and you’ve chosen to use Always On Availability Groups....(more)

New White Paper: SQL Server Performance Tuning in Google Compute Engine - You’re a database administrator, Windows admin, or developer. You’re building your first SQL Servers in Google Compute Engine, and you’re stuck at the create instance screen. How many CPUs should you use? How much memory? How are you supposed to configure storage? Will it be fast enough, and what should you do if it isn’t?...(more)

Announcing Google Managed PostgreSQL (and why SQL Server DBAs should care) - Google has announced Cloud SQL, their own managed PostgreSQL database-as-a-service in beta. Do SQL Server developers need to consider PostgreSQL? Probably yes, the next time you start a project. ...(more)

Copying your SQL Database - If you ever need to move a copy of a SQL database in Azure across servers then here is a quick easy way....(more)

Backup and Recovery

DatabaseRestore: Open Source Database Restore Stored Procedure - DatabaseRestore is an MIT-licensed open-source stored procedure that restores backups that were created using Ola Hallengren‘s DatabaseBackup solution....(more)

Using dbatools for automated restore and CHECKDB - One of the things I like to do as a DBA is backup my databases, restore them to another server and run CHECKDB on them. There are some cmdlets in the dbatools project, in particular the Snowball release, that really make this easy. ...(more)

Administration of SQL Server

Split a file group into multiple data files - SQL Server does not have a built-in way to split an existing filegroup into multiple data files, but you can do it yourself. The script provided is for illustrative purposes only, not for production use!...(more)

Why PFS pages cannot be repaired - So why can’t PFS pages be repaired by DBCC CHECKDB, when all the other per-database allocation bitmaps can? The answer is that the is-this-page-allocated-or-not information is not duplicated anywhere else in the database, and it’s impossible to reconstruct it in all cases....(more)

KPIS for DBAS to show to their CIOS - Often the KPI goals and targets are set by the business. But not always. It is perfectly valid for a DBA (or their manager) to assign KPIs to the servers and databases they administer. KPIs are unique for every shop, and for every role....(more)

SQLskills SQL101: The SQL Server ERRORLOG - One of the most useful logs you can review when there’s a problem in SQL Server is the ERRORLOG. It may not always be the answer to your problem, but it’s a good place to start....(more)


Administrative