The Matchbox Database

Azure SQL Database Edge is a fascinating development for anyone working with SQL Server, because it promises to extend the range of applications for the product far more than their previous embedded systems or IoT products.

Well, it certainly fascinates me. I've recently been running versions of SQL Server on progressively smaller devices, as a sort of hobby. I've done a few industrial database-driven applications, the first in about 1980, well before the first commercial SQL Database. I'd have loved, then, something more resilient than the KSAM database system that we used.

Although there is nothing much wrong with small-scale SQL databases such as SQL Lite, they aren't so useful for embedding in appliances because they are only single user. For many years I used SolidDB for embedding into real-time applications because it was so robust, but the idea of using SQL Server has a certain attractiveness, because it requires no extra learning. I notice that plenty of folks are using MongoDB in network appliances nowadays, but I reckon that a well-designed relational database will always give the best response.

It was fun, and miraculous, to see Azure SQL Database Edge working in a container on a Raspberry Pi 4, which can fit in your hand. SQL Database Edge is the SQL Server database engine, even with columnstore and in-memory OLTP, but running on 64-bit ARM devices. It runs on ARM and Intel architecture, without analytical engines or in-memory capabilities, but it is optimized for edge devices. It's just SQL Server, on Linux, on tiny computers that can run anywhere.

It is great for learning the technology, but don't get too overexcited about running a website on it, because licensing for these embedded system products currently only permit it to be installed on the device with an integrated (embedded) software application (or suite of applications) dedicated to a specific use. The idea of running an enterprise application on it is laughable anyway. I can get a second-hand, rack-mounted HP server to host SQL Server for not a great deal more than a Raspberry Pi.

I'm rather wary of running SQL Server in a container, though. So far, I must confess, my experience of running SQL Server containers in Windows or Linux has given me the feeling of being a pioneer. Advice and installation instructions seem to change. Any installation that succeeds gives me a feeling of triumphing over adversity. It all can be made to work but why make it all so difficult? There is a curious retro feel to the experience, as if one were back in the late seventies. I once proudly got a whole range of SQL Server containers working on Windows, only for Docker to withdraw support for the version of the operating system and I lost the lot without warning. It doesn't inspire confidence.

Phil Factor

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

Josh Crang explains why SQL Monitor will be switched to run on .NET Core, and how the team tested the potential performance improvements in the monitoring service.

Phil Factor demonstrates a cunning way to test stored procedures or functions, such as after refactoring, by storing the 'before' and 'after' results in views and then using SQL Data Compare to spot any discrepancies.

Grant Fritchey explains what's involved in masking a SQL Server database. It can seem a daunting task, but it all becomes a lot more logical if you start from a plan, based on agreed data classifications, and then use a tool like Data Masker to implement the masking, and track progress.

SQL Change Automation enables users to make database changes to production safely and efficiently using PowerShell cmdlets, which can be integrated easily into any release management tool. This article will show you how to automate database deployments safely, by using SQL Change Automation from within PowerShell scripts, and how a deployment script for a release can be checked and amended as part of the process.

The "metro map" of SQL Monitor, with sightseeing tips and essential stops along every line....

We’re pleased to announce that the SQL Prompt extension for Azure Data Studio is now available to download for free. With SQL Prompt in Azure Data Studio, you can use an extensive collection of code snippets to write your SQL code quickly and efficiently. You can also keep your code consistent using the powerful formatting capability, and customize the applied style to suit your preferences.

Nearly 1000 database professionals took part in this year's State of Database Monitoring survey. Providing insights into how they monitor their estates, the technologies they work with, what their biggest challenges are, and emerging trends for 2020. For the detailed analysis of the responses, download your free copy of the report.

Help Redgate to build the next generation of Database DevOps solutions so that we can help you to deliver the next generation of your software by completing this short 2-page survey on current development practices.

Disable TCP/IP as a network protocol to SQL Server. BOOM. Instant firewall.

FullText Indexes in SQL Server are really not something that cause many people problems, until they do. When a problem does happen with a FullText population or crawl, the Log file for the catalog is the starting point for troubleshooting what is causing the problems.

In this article of the series, Robert Sheldon discuses emerging trends in storage like virtual SANs, intelligent storage, computational storage and storage-class memory.

With the merge approach you always consume as little memory as possible. With Process ADD, small partitions, and memory use, will grow over time.

Query Store is now enabled by default, as is Transparent Data Encryption.

Marco Russo recently implemented Power BI models by extracting data from Azure Data Lake Storage (ADSL) Gen 2, and shares a lesson learned when using this connector.

Niko Neugebauer explains why he wants you to vote to allow Resource Governor to control the CAP of the TOTAL IO in MB/s.

Niko Neugebauer presents a non-exhaustive list of security measures that will help make your SQL Server VMs on Azure a little bit safer.

Recently announced in preview, blob index is a managed secondary index that allows you to store multi-dimensional object attributes to describe your data objects for Azure Blob storage. This allows you to categorize and find data based on attribute tags set on the data.

Distributed teams of all sizes need the ability to quickly develop and deploy quality code. Join Kendra Little to discover ways to increase your team’s rate of deployment to production databases and continuously improve code quality. Date and time: Wednesday June 10, 12-1pm CDT / 6-7pm BST Can't join us live? register to receive the recording.

The time has come to bring your database into version control and DevOps automation, whether on-prem or in the cloud. Join our experts from Redgate and Octopus Deploy to see how Runbooks, cloning, and automation enable this. Date and time: Wednesday June 3, 10-11am CDT / 4-5pm BST Can't join us live? register to receive the recording

Innovative companies are finding effective ways to bring the database and DevOps together. Hear real-world success stories from DBTA 2020 Database DevOps Innovation Award winners, PASS & Zions Bancorporation in this special round table webinar. Date and time: Thursday June 4, 1-2pm CDT / 7-8pm BST

We have this job, and one of the steps in the job creates files…and we need you to check if it creates the files, otherwise we don’t need to do any of the rest of the steps.

To create a database that enforces the relationships between the tables, you need to work with declarative referential integrity (DRI), frequently shortened to referential integrity(RI). It is the foundation on which the relational part of the relational storage engine is built.

Interpreted languages would seem to be better for DevOps, but compiled languages have speed on their side.

In some shops, testing is entirely in the realm of quality analysts. In this article, Julio Sampaio argues that testing should be automated and start with the developers.

Having forgotten to shutdown the Azure-SSIS Files Integration Runtime after use, one too many times, Andy Leonard configures a trigger to schedule a pipeline's execution.

Having had a 2-node availability group (AG) + fileshare witness system experience an unexpected failover recently, Josh provides some general advice and debugging tips.

How to create create a single Master Control Job that automatically enables any jobs where at least one step run on a database which currently has the Primary/Principle role in an HADR solution.

In this article in the storage series, Robert Sheldon explains infrastructure options that simplify administration and improve resource utilization. He discusses the differences and benefits of converged, hyperconverged, and composable infrastructures.

A dedicated, special purpose CPU could create more balance among system resources.

Hugo Kornelis explains what can cause a Key Lookup or RID Lookup where the Estimated Number of Rows (Per Execution) is more than 1.

Hugo Kornelis, master of the execution plan, explains the lack of data reported by a Compute Scalar operator, and how this affects the graphical execution plan

Using some often overlooked features of sp_whoisactive to investigate parameter sniffing.

Extended Events shows the same query having a different plan handle. Why? Erik Darling investigates...

In SQL Server, troubleshooting blocking problems is a pain. It’s one of those things you really have to be monitoring for pretty actively in order to catch the full extent of it. Dedicated monitoring tools will often give you a tree view of the blocking.

The client statistics checks out the datasize as it’s prepared on the server. It tell’s you it’s sending the data over, even when it’s not, and it can often help when you know beforehand that there’s an awfull lot of data waiting for you.

Hugo Kornelis explains another case where where execution plans can mislead you, this time when missing nodes cause misleading estimates.

A common request that is raised by clients is how to sort a table in Power BI by multiple columns, in the same way you can in Excel. For a while, there was no way (at least no easy way) to do this until the Power BI March 2020 update.

This article explains how to show different customers with the same name in a Power BI report by using zero-width spaces, thus simplifying the presentation without adding visible characters to make the names unique.

Wouldn’t it be great if there was a way to work with a small subset of your data in Power BI Desktop and then, after you publish, load all the data when you refresh? The good news is that this is now possible with the new deployment pipelines feature in Power BI!

Using the XMLA endpoint, the Power BI Premium service now includes the capabilities of SQL Server Analysis & Azure Analysis Services combined with newer data modeling capabilities of Power BI.

Small multiples are a great way to communicate information across different subsets of data.

This updated technical whitepaper outlines considerations and best practices for a well-performing and secure organizational Power BI deployment.

If you find yourself looking at an SSRS Report in Visual Studio, but you don’t see the Shared Data Sources, Shared Data Sets, or Reports folders try to selecting Solutions and Folders.

When the optimizer doesn’t estimate the correct amount of memory for a query, either memory is wasted that could be used for other processes or some operations will spill to disk. Microsoft has added Memory Grant Feedback to help overcome this issue. In this article, Greg Larsen explains what you need to know about this new feature.

Explaining a new feature called ‘sensitivity rank’, added in Data Classification, starting SSMS 18.5.

This blog post outlines the work that Microsoft is doing to eliminate uninitialized stack memory vulnerabilities from Windows and why we’re on this path.

The whys and hows of reading research papers for solution finding, discovery or mere curiosity.

Table variables can cause performance issues with joins when they contain a large number of rows. In SQL Server 2019, Microsoft has improved how the optimizer works with table variables which can improve performance without making changes to your code. In this article, Greg Larsen explains how this feature works and if it really does make a difference.

Brent Ozar created a new check for sp_BlitzFirst to find statistics that were updated in the last 15 minutes, possibly causing plan caching issues and parameter sniffing. He streamed it live, and you can watch.

Brent Ozar writes a few new "background noise" queries, which would produce real-world style data in a few seconds, in the style a user would actually write them.

How SQL Server stores currency values using the MONEY and SMALLMONEY data types.

Unless you run workloads on Cloud VMs, you might be totally unaware of the problems that arise regularly on the VMs that are not sized “appropriately”.

One of the things that bothered me about running my demos on containers was that I couldn’t use windows authentication. Instead I had to pass in a SQL login to connect for every command...enter PSDefaultParameterValues.