Problems displaying this newsletter? View online.
Database Weekly
The Complete Weekly Roundup of SQL Server News
Hand-picked content to sharpen your professional edge

SQL Tamagotchi! Mini-Servers as Database Pets

I love small SQL Server installations as much as the big beasts. There is much pleasure to be had from buying second-hand, 19-inch racks and installing SQL Server instances on them, getting strange little black boxes intended for routers and turning them into network SQL Server instances, or even spinning up Ubuntu virtual machines in Azure as unlikely hosts for database instances.

There was a time that I used to wonder why I did it. I supposed that it is the same pleasure that one gets from keeping goldfish or a pet rat. It's difficult to justify in economic rather than emotional terms. I won't pretend I do it out of dedication to work. It's fun, though not without frustrations.

It is useful, though, to have them when I'm trying out different technologies, different database systems and generally keeping up to date. It is good to keep all this play separate from the day-to-day work, and it allows me to try things out well away from any corporate systems. It also helps with some SQL Server Development work.

As well as providing a great skunkworks, I believe that it is useful to have a pet SQL Servers for work. Even if most of your work is on shared servers, a lot of testing, especially when you test out resilience and scalability, is better done when you are a god in an empty universe that you can destroy. Even development work is usually helped by doing it on a slow server because it is completely obvious when you get it wrong. There was a happy time, long ago, when I used to be able to see the arm of the hard drive flicking backwards and forwards frantically whenever I got an index wrong. Ah, nostalgia.

Perhaps it is slightly strange that I like to create a local network of data stores and databases just when everyone who knows stuff is increasingly using the cloud and hosted services. However, I think there is room for both technologies, when you're doing development work. After all, all you need is an Ubuntu 16.04 machine with at least 2 GB, preferably lot more, and a good SD card, hang it on a decent local network, and you have a very responsive system. Linux SQL Server has the ultimate virtue of working just like Windows SQL Server, and it just takes a few minutes to install. You can soon simulate some quite complicated setups.

The lazy way, I reckon, is to host instances in docker containers on a Linux server. Once you have a good docker image, it is quick work to set things up. A Windows host seems to work just as well, though the hard work is done in a VM. This gives you a very flexible server that can be doing SQL Server work one week and then MongoDB, PostgreSQL or Apache CouchDB the next.

However, personally, I'm even more fascinated by the idea of using a Raspberry Pi 4 with SQL Server or stuffing SQL Server onto an Intel 'Coffee Lake' mini-ITX rack.

Phil Factor

Join the debate, and respond to the editorial on the forums

Redgate Data Masker
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.
Vendors/3rd Party Products

Database Build Blockers: Cross-Server Database Dependencies

Phil Factor demonstrates how to tackle builds when databases make cross-server references. The technique uses synonyms to represent the remote objects, and local 'stub' objects to overcome the problems caused by 'missing references' when building the individual objects.

Resolving Cross Database Dependencies in SQL Change Automation using Local Databases or Clones

Kathi Kellenberger explains how to avoid build problems, when working with set of inter-dependent databases, by restoring or provisioning copies of all required databases to the development, build, or test instance.

Managing Data Masking Rules in Larger Teams using Source Control

What if you have several people in the team who are responsible for data security across your databases, and they need to work together to develop and maintain the data masking configurations, which must then be applied consistently as part of an automated provisioning process? How should they do it? The solution turns out to be simple: source control.


Disable all Triggers on a Database

Sometimes it’s best not to ask why. However, if for some reason you have a number of triggers on tables within a database that you would like to temporarily disable, read on.

Analysis Services / BI on the MS Stack

High Memory Usage and Calculated Columns

An explanation of how calculations involving large time ranges and ineffective DAX expressions can cause big spikes in memory used, in Azure Analysis Services.

Azure SQL Database

Research Paper Week: Constant Time Recovery in Azure SQL DB

If you’re considering enabling Constant Time Recovery in SQL Server 2019 (when it ships), you should read the paper to understand the risks and drawbacks of a feature like this.

Research Paper Week: Automatic Indexing in Azure SQL DB

This easy-to-read 12-page paper is wonderfully candid about how Azure’s auto-indexing does what it does, the challenges it faces, how it’s succeeded, and times when it’s failed.

Azure SQL DB Serverless

The new serverless model, currently in public preview, provides a compute tier that is optimal for a single database that has unpredictable usage patterns.

Prevent mistakes with Azure Resource Locks

Azure Resource Locks are the Azure version of child proof locks on your kitchen drawers. They are handy when you have to give people a little more access to an Azure environment than you might like, and want to remove the chance of someone accidentally deleting a resource.

Azure SQL Managed Instance

Track unauthorized backups using Azure SQL Managed Instance Audits

If you don’t use TDE on a database, or there is a risk that someone can remove it and then take a backup, then Azure Managed Instance provides auditing mechanism that enables you to track who performed a backup and when. This way you can always track if some unauthorized action is happening.

Backup and Recovery

Accelerated Database Recovery in SQL Server 2019: Choose your own filegroup for the version store

ADR makes use of a per-database version store, instead of putting everything in the transaction log and TempDB. And now, SQL Server 2019 CTP 3.2 introduced the ability to choose which filegroup you want to use for the version store, which will help with performance.

Computing in the Cloud (Azure, Google, AWS)

Looks Like A VM Problem

The Cloud is basically an AirBnB for your server. That’s cool. But, they’re still VMs, and you still need to pay close attention to how they are set up for SQL server.

TOP 5 Benefits of Azure Dedicated Hosts (Preview) for SQL Server Workloads on Azure VMs

Discussing discuss top benefits of Azure Dedicated Hosts for SQL Server workloads, namely Physical Isolation, Compliance, Control Failovers, Cost Savings and Licensing flexibility.

Conferences, Classes, Events, and Webinars

What You Need to Know About the 2019 DORA Accelerate State of DevOps Report

Wednesday September 18 16.00-17.00 BST/ 10.00-11.00 Central - Join Jez Humble and Steve Jones to learn the latest insights from the 2019 DORA Accelerate State of DevOps Report.

Using Redgate, AKS and Azure to bring DevOps to your Database

Tuesday 17 September 11.30-12.30 BST - Join Microsoft MVP's Hamish Watson and Rob Sewell to learn practical solutions on how to bring DevOps to your database.

SQL in the City Streamed: 20 Years of Redgate

Wednesday September 4 14.00-19.00 BST/ 08.00-13.00 Central - Register for our free virtual learning event, to enjoy educational and entertaining sessions from Microsoft MVPs and celebrate 20 years of Redgate.


PowerShell 7 Preview 3

A preview of some of what's coming in the next release (around end of year)...

Data Mining / Data Analysis

A Brief History of Master Data

Before master data and transactional data, there was the datum. And to understand that history, we must go backwards in time.

Database Design, Theory and Development

Research Paper Week: In-Memory Multi-Version Concurrency Control

This white paper compares and contrasts the methods used by SQL Server (Hekaton In-Memory OLTP), Oracle, Postgres, MySQL, SAP HANA, and others.

DevOps and Continuous Delivery (CI/CD)

Key Findings for Database Professionals from the Accelerate State of DevOps Report 2019

Speed and stability are not tradeoffs, but heavy change processes negatively affect both.

How do you make DevOps Succeed?

Grant has been involved both in successful failed DevOps implementations, but what were the key differentiators between between the two?

Introduction to DevOps: DevOps and the Database

When the DevOps pipeline does not include the database, the database can become a bottleneck and slow down the delivery of new features. In this article, Robert Sheldon discusses the challenges involved with including the database and how to overcome them.


Rounding to 15 Minute Intervals

Needing to round Date/Time data to the nearest 15 minute increment but discouraged at how unhelpful the DAX Date/Time function were, Dave Mason tries to do better using T-SQL.

Blank row in DAX

There are two functions in DAX that return the list of values of a column: VALUES and DISTINCT. This article describes the difference between the two, explaining the details of the blank row added to tables for invalid relationships.

Performance Tuning SQL Server

Profiler and Trace versus Extended Events

Why on earth would you recommend to new data professionals working on modern systems, let’s say at least 2016 or better, that they should be using Trace?

A quick tuning win with Memory-Optimized Tables

If you have Table Variables that use User-defined Table Types, a quick win is to create the type as a Memory-Optimized table.

Batch Mode Bitmaps in SQL Server

Paul White delves into batch mode bitmaps, which have a very different implementation from their row-mode counterparts.

For Better Query Store Adoption, Make Querying It Easier

Erik Darling on the irony of needing to tune Query Store queries in order to find queries to tune...

It’s Not Always Parameter Sniffing

When you’re investigating performance issues, it’s important to know what things look like when the server is running well, and what things look like when the’re not.

Can DOP Change Query Plan Choices?

After a while tuning a query, sometimes it’s fun to mess with the DOP it’s run at to see how things change. I wouldn’t consider this a query tuning technique, more like a point of interest.

Research Paper Week: Plan Stitch: Harnessing the Best of Many Plans

This paper proposes something really cool: watching an execution plan change over time, splitting it into parts, and then combining parts to form a SuperPlan™!


Have you ever been absolutely, positively sure that those gosh dang darn stats updates were putting your query on the trolley to Slowsville, but had not way to prove it? Your wishes will be 100% granted in SQL Server 2019.

Monitoring TempDB Contention using Extended Events and SQL Monitor

When the tempdb database is heavily used, processes in any database on the instance will be forced to wait, due to contention when the SQL Server engine tries to allocate pages in tempdb. Phil Factor shows how to monitor for signs of trouble.


Storytelling with Power BI 5/7: Ensure consistent context

Highlighting two features that help you to set the context, the data property of bookmarks and Sync slicers.

Dynamically Show Completed Months in Power BI

One common requirement I have come across a few times is to limit a Power BI visual to only show completed months and exclude the current active month. This is common if you load data on a daily basis, but some of your reporting is done on a monthly basis.

Using Data Category to Have a Better Visualization in Power BI

This article explains how to use Data Category, a lesser-known field-level configuration that can be helpful in multiple visualizations.

The Art of the Disconnected Table

How to build a Power BI report to see the MTD and YTD of sales for a selected date, and a graph showing the sales of each day in the month of the selected day up to that day.

Reporting Services

Report Builder Basics for Power Users and IT Professionals

Explaining the Microsoft Report Builder tool which is used to build and manage paginated reports, and how to use it to create SSRS reports.


Yes and No Questions Are Not So Simple

Joe Celko explains why questions requiring yes or no answers are more complicated than you might realize in both spoken language and computing.

SQL Server News

SQL Server 2019 release candidate is now available

Amit Banerjee announces the first public release candidate for SQL Server 2019.

SQL Server Security and Auditing

Tracking Data Changes - Change Tracking, Part 2

Enabling change tracking for tables.

Data Security: How to Secure Sensitive Data

Your audit should include investigations into your system vulnerabilities, current threats, and best practices for protection. The OWASP Top 10 vulnerabilities list is a tool that can help you determine where risks might be present and how they can be avoided.



T-SQL often provides multiple ways to “skin a cat”1 as they say. In this post, we’ll take a look at two “interesting” ways to convert dates and times from character-based columns into a column using the preferred datetime data-type.

Transaction Isolation Levels and sp_executesql

Isolation Levels and sp_executesql don’t mix quite as nicely as you might hope.

Constructing a datetime from an integer date and an integer time

Storing date and time separately as integers back before we had date and time data types - but there’s still lots of legacy code out there that use them!

Testing Software

You are naming your tests wrong!

Giving your tests expressive names is important. Proper naming helps understand what the test verifies and how the underlying system behaves. In this post, we’ll take a look at a popular, yet inferior naming convention and see how it can be improved.

The Lighter Side

License Plate "NULL"

There was a DefCon talk by someone with the vanity plate "NULL." The California system assigned him every ticket with no license plate: $12,000.

RSS FeedTwitter
This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the This newsletter was sent to you because you signed up at Note: This is not the 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 follow the instructions on the daily newsletter.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -