The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I hate deadlines. Really. I truly hate them. The problem is: they are usually unrealistic, often not much more than a wild guess. They restrict the amount of time that I can spend in preparation and research so that I can do things right; they squeeze the artistic quality out of my work as a DBA and Data Architect.

In many ways, though, this is the younger me speaking. Part of me still hates them, but as I get older (and older,) I live through more and more examples of the true value of deadlines. The trick is to get the right deadline for the right task, and then live up to it.

A recent event in my personal life provides a perfect illustration, both of why deadlines are necessary, and how they pan out in most projects. We moved to a new home. In phase 1 of the project, between putting our current home on the market and selling it, we worked steadily but without urgency. We contacted a moving company, described the project, agreed what we'd pack and what they'd pack, a timescale and a budget. After that, we did a lot of planning and talking. We seemed to have all angles covered. There was a lot of euphoria about just how well the project would go.

Suddenly, we received and accepted an offer on the house and agreed on a moving date; phase 2 began. We looked around at what we'd achieved so far and it wasn't much (though it was done immaculately). I jokingly suggested that if we continued packing at this pace, we'd have to skip this house and just move everything straight into a retirement home. Still, the deadline galvanized us. We split the remaining work between us. We worked hard, and packed faster. Sure, some of our work got a little sloppier, but we got ourselves on track to complete our share of the work, on time.

As phase two came to an end, we received a call from the moving company. Did we have enough boxes to do all the packing? Um, no, we've almost packed what we agreed with the project manager; you're supposed to do these rest! This was the 'boom' that presaged the usual final stage chaos. Too few movers arrived, needing to do too much work in too little time. Initially, they maintained a modicum of professionalism, but that started to slide quickly, as the deadline seemed impossible. As the hour of the move approached, the remainder of our breakable items were being piled up messily, like thousands of lines of hastily written, and never tested, code.

We hit our deadline, but were left so exhausted that we incapable of doing anything much, in our new home, for weeks. The movers lost in damages over 30% of what they made on the move. So, I suppose, in comparison to many IT projects, it was wildly successful!

Deadlines are necessary for motivation, but a proper deadline is an achievable goal that helps you pace yourself properly. As a DBA, you need to learn to stand up against deadlines that feel impossible to achieve, without cutting unreasonable corners. As you gain experience, you learn how long it really takes to build a server, install SQL, set up backups, write the SQL, and how much time to wire in to account for the inevitable hardware glitches, poorly written requirements, and changes in team members.

If you find you are constantly always fighting deadlines that can never be met, consider other opportunities. There are far too many other organizations out there needing their databases backed up and optimized to spend your life implementing bad practices, and fearing that the work you do isn't the best it can realistically be.

Louis Davidson (@drsql) from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

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

Free webinar: dealing with SQL Server asset management - In this interactive session, Microsoft Data Platform MVP Steve Jones joins the Redgate Foundry team to lead a group discussion on strategies for discovering the full extent of your estate, mitigating security risks, ensuring consistent server configurations, and incident response and prevention. Register now....(more)

Vendors/3rd Party Products

Dude, where’s my database? Inventory management by Foundry - Foundry, Redgate's R&D division, is currently looking into the topic of SQL Server inventory management - here's what they've found so far, and how you can get involved with their research....(more)

Poll: slow application performance? - How would you address application performance on constrained networks? Answer this quick survey to let us know whether you would be interested in products that help this....(more)

How to document multiple SQL Server databases using SQL Doc and PowerShell - You can use SQL Doc’s command-line parameters to automate database documentation, but when you try with a group of databases on a server, they sometimes don’t give you enough control over the job. Phil Factor provides a solution that allows both automation and flexibility....(more)

Using striped backups with SQL Clone - If you’re a Redgate SQL Backup customer, occasionally you’ll need to convert your SQL Backup (.sqb) files to the native SQL Server backup format (.bak), perhaps to perform native database restores on a server where SQL Backup isn’t installed. This produces a striped backup, because each thread used when making the backup will produce a separate file. Can we use a striped backup produced in this way, or indeed any striped backup, as the source for a SQL Clone image? Short answer: we can! Let’s see how that works....(more)

Moving from application automation to true DevOps by including the database - When does application automation become true DevOps? When the database is included. Tom Austin addresses the challenge of synchronizing application and database changes, and of overcoming different ...(more)

T-SQL

The Birthday Problem – with T-SQL and R - In a room of 23 people, there is over 50% chance that two or more share the same birthday....(more)

What?You?Need?To?Know?About?SQL's Group By - SQL’s group by is both common in SQL statements and just complicated enough to throw the occasional SQL writer for a loop. This means it’s critical to understand it well to write accurate SQL. This post explores its basic use, some risks to be aware of, and some of the interesting oddities associated with it....(more)

Generating HTML from SQL Server Queries - You can produce HTML from SQL because SQL Server has built-in support for outputting XML, and HTML is best understood as a slightly odd dialect of XML that imparts meaning to predefined tags. There are plenty of edge cases where an HTML structure is the most obvious way of communicating tables, lists and directories. Where data is hierarchical, it can make even more sense. William Brewer gives a simple introduction to a few HTML-output techniques....(more)

SQL Server Internals

Successful anti-patterns, using deprecated data types - From time to time it’s good to remind people about the complexity of a product such as SQL Server. The internals are so wide and complex that we easily get lost and follow what remains easier in our minds, rather than understanding the whole picture. In this post, Raul Gonzalez presents some benefits you may encounter by not following best practices....(more)

Software Development

Towards true continuous integration: distributed repositories and dependencies - For the past 8 years, Netflix has been building and evolving a robust microservice architecture in AWS. Throughout this evolution, they've learned how to build reliable, performant services in AWS. The microservice architecture decouples engineering teams from each other, allowing them to build, test and deploy their services as often as they want. This flexibility enables teams to maximize their delivery velocity. Velocity and reliability are paramount design considerations for any solution at Netflix. ...(more)

Some good "Statistics for programmers" resources - Julia Evans pulls together a list of resources that teach statistics using programming....(more)

3 Changes You Should Always Make Before Checking In Any Code - Bert Wagner shares 3 quick and easy refactorings that you should make to all projects in order to improve code readability....(more)

Security news and thoughts

Princeton’s Ad-Blocking Superweapon May Put an End to the Ad-Blocking Arms Race - A team of Princeton and Stanford University researchers has fundamentally reinvented how ad-blocking works, in an attempt to put an end to the advertising versus ad-blocking arms race. The ad blocker they've created is lightweight, evaded anti ad-blocking scripts on 50 out of the 50 websites it was tested on, and can block Facebook ads that were previously unblockable....(more)

Reporting Services

PowerShell – Working With SQL Server Reporting Services (SSRS) - Jana Sattainathan shares five PowerShell functions that he's created for working with SQL Server Reporting Services....(more)

PowerShell

Installing The SQL Server Module from the PowerShell Gallery - Drew Furgiuele explains how to get your PowerShell scripts running and executing on remote servers without having to install SSMS on them....(more)

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. If you are using PowerShell and maybe also SQL Server, it pays to use a command-line plotting tool such as Gnuplot to do all the hard work. It turns out to be handy for a range of data jobs, turning PowerShell into a handy data science tool....(more)

PowerPivot/PowerQuery/PowerBI

Format Painter in Power BI and other color stuff - Charles Sterling shares the Power BI color theme tool, Theme Master 500....(more)

Tracking LinkedIn and Twitter social media shares in Power BI - Samuel Lester shows how to retrieve Twitter and LinkedIn share totals for a list of URLs with Power BI or Power Query....(more)

Microsoft News : Downloads

SSMS 2017 Is Now Available For Download - Erik Darling talks about his experience with downloading and installing SSMS 2017....(more)

ETL/SSIS/ELT

SSIS – Adding Connections - In this quick tip, John Morehouse shows how to use the connection manager in SQL Server Integration Services....(more)

Data Visualisation

Binning Outliers in a Histogram - I guess we all use it, the good old histogram. One of the first things we are taught in Introduction to Statistics and routinely applied whenever coming across a new continuous variable. However, it easily gets messed up by outliers. Putting most of the data into a single bin or a few bins, and scattering the outliers barely visible over the x-axis....(more)

Computing in the Cloud (Azure, Google , AWS)

Azure Load Balancers and SQL Server - Load balancing in Azure has more importance for the DBA, because it is essential for Windows Server Failover Clustering in Azure, whether it is for AlwaysOn Availaiblity Groups, Failover Clustered Instances, or any other highly-available solution. Azure load balancing works out the location of the availability group, and routes traffic there. The load balancer detects a failure, and routes traffic to the new primary replica. Joshua Feierman gives an overview of what is required....(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. DBAs generally need not become experts in Azure networks, but it helps to understand the concepts and language. If you are running a SQL Server Virtual Machine in Azure, then VNets, Subnets, Network Security Groups, VNet peering and VPN gateways are all worth knowing about in order to to keep SQL Servers running smoothly....(more)

Columnstore Indexes

Partitioned Tables and ColumnStore - ColumnStore indexes are all the rage with data warehouses. They’re fast, they’re new(ish) and they solve all sorts of problems when dealing with massive amounts of data. However they can cause some issues as well if you aren’t very careful about how you setup your partitions on the ColumnStore index. ...(more)

Career Growth

How Does a DBA Build Confidence After Making Mistakes? - Ever had a database change go horribly wrong? It can feel awful in the moment, then eat away at your confidence for days afterward. In this 20 minute episode Kendra gives you practical steps that help you cope with change over the course of your DBA career....(more)

Bugs/Patches for SQL Server

Error 0x80004005 In SQL Server R Services - Kevin Feasel explains how, with a little help from Microsoft, he dealt with error 0x80004005 In SQL Server R Services...(more)

Big Data

The data ecosystem explained in 10 simple images - What do terms like Data Lake and Data Science really mean? Surprisingly enough, the answer is as simple as describing the life cycle of something that everyone know very well: orange juice. Davide Mauri explains....(more)

Analysis Services / BI on the MS Stack

SSMS Improvements for Analysis Services in the April 2017 Release - The April 2017 Release of SSMS for Analysis Services is the first release with support for the modern Get Data experience. This release also features additional capabilities for the DAX parser, which come in handy when authoring or fine-tuning queries in the DAX Query Window. Azure Analysis Services now also supports Multi-Factor Authentication (MFA) based on Active Directory Universal Authentication....(more)

AI/Machine Learning/Cognitive Services

Parvez Ahammad on applying machine learning to security - In this special episode of the Security Podcast, O’Reilly’s Ben Lorica talks with Parvez Ahammad, who leads the data science and machine learning efforts at Instart Logic. He has applied machine learning in a variety of domains, most recently to computational neuroscience and security. Lorica and Ahammad discuss the challenges of using machine learning in information security....(more)

Scientists Say Brexit May End UK’s Lead in AI - A group of prominent academics and tech executives fear that the U.K.’s exit from the European Union could jeopardize the U.K.’s lead in the development of machine learning technologies....(more)

Administration of SQL Server

Upgrading to SQL Server 2016: post-upgrade tasks - After upgrading to SQL Server 2016 is complete you will need to perform a series of tasks to verify that database is ready to be handed over to the end users for further testing. In this post, Thomas LaRock provides a checklist of items to review after the upgrade is complete....(more)

Query hash values, plans guides, and the query store - Grant Fritchey was eating dinner with Hugo Kornelis and they started talking about query hash values. You know, like everyone does at dinner. As they talked about it, Grant suddenly thought about both Plan Guides and the Query Store. He wondered what happened to the query hash values in that case? Thus are blog posts born....(more)

Creating Self Building SQL Server Data Tools Pipelines Using Jenkins and GIT - Chris Adkin explains how a self-building pipeline for the deployment of an SSDT project can be implemented using open source tools, and augmented using PowerShell....(more)

Why is a Windows authenticated login more secure than a SQL authenticated one? - Kenneth Fisher did some research to find out exactly why Windows authentication logins are more secure than SQL authentication logins....(more)

How to Use an Encrypted Password Within a SQL Agent job to Access a Fileshare in an Untrusted Domain - You have a SQL Server Agent job which needs to regularly access a remote file share in an untrusted domain. This requires you to provide the remote username and password while accessing the remote share. In addition, you do not wish to store the password of the remote share account in clear text....(more)

Remove Files From tempdb - After accidentally creating three new tempdb files sized 10GB each, Erin Stellato had to go about fixing the mistake - here's what she did....(more)

Programmatically find SQL Server TCP ports - In the past, attempting to find what ports SQL Server was a tedious task. But there is a better way. In SQL Server 2012, a new DMV was added to query the TCP ports that SQL Server is currently using. Pedro Lopes shows how to use it....(more)

How are default column values stored? - How is a default column value stored, and what if some rows exist when a column is added and then the default value changes? Paul Randal explains....(more)

A DLM Approach to Database Testing - Database Lifecycle Management aims to make the development and modification of databases more predictable. Bugs are the source of more unpredictability than anything else, purely because it is so difficult to guess how long it will take to fix them. Good testing at all stages may take some time and effort, but it greatly reduces likelihood of the wildcard factor of the bug that is first detected during the deployment process; or worse, that gets into the production release....(more)