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

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.

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.

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.

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.

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

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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

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

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

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

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

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.

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.

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.

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?

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

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

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

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.

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.

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.

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.

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

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.

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

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.

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

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

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

Enabling change tracking for tables.

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.

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

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!

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.

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.