Databases made for Ops, not 'Oops!'

Databases must be designed and developed to be easy to maintain. From the start, a database should be simple to check, monitor and study. Monitoring systems can only assist; it is up to the database developer to make things easy for those who are subsequently given the task of keeping it running.

I wish I could smile in some saintly way, and tell you that I've always written my databases, or other systems, in such a way that anyone could check it and understand what was going on. Twice, in my professional career, I've been caught out.

"Where do you work? Ah, The Imperial Bank! I once worked there. How did you like it?"

"Well, it is fine most of the time…but evidently we once had a wild man developer who wrote an essential database. It uses all sorts of unusual performance tricks and unconventional techniques. Nobody who worked on it subsequently ever really knew how it worked and so it was a nightmare to make changes in line with changes in the business. What is worse, if it ever hits a problem, which is mercifully rare, nobody can easily profile it or even detect what is slowing it down. Some people left the company rather than be assigned to work on it."

"How terrible. It must have been some time after I left because I don't remember it." (Gulp!)

I had a similar experience, mercifully not with a database, after I wrote a system that automatically updated thousands of PCs within a corporate network. It lasted at least two decades purely because everyone was too frightened to replace it. I've been a poor team-player at times.

I hesitate to try to list all the requirements for monitoring and maintaining databases, for such an august and learned readership as Database Weekly, but I'll try to explain what I mean.

In the same way that the unit test must precede the creation of a database 'programmable object' such as a stored procedure, so must devising the means to monitor that object, and ensure it provides the necessary evidence to reproduce and fix any problems. You must, in a sense, plan its visibility. The same goes for processes, and the entire database. The alerting system, extended events and scheduled tasks provide the means for an excellent start. They are there for a good reason. You can, of course, also use a third-party monitoring system, and these will help by providing supervision, and general metrics, create graphs and establish baselines so that you can see easily the symptoms of a problem. However, only the database designer and developer can provide the means to baseline, monitor and log the specific processes that have been created for that particular database system. The database itself, dedicated to storing data, provides a uniquely easy way of recording the monitoring and logging data for these processes.

The closer working relationship between Dev and Ops has made it much easier to understand what makes Ops people smile and hum while they maintain production database systems. It pays to discuss, and then create, a written and agreed 'monitoring standard' that its appropriate for your organisation. It is surprising how often this turns up ideas that help development as well and leave everyone wondering why the blazes they've never been done before.

Phil Factor

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

KPIs are a handy option in the data warehouse because they can be used across various dimensions and hierarchies.

Kasper de Jonge shows how you can see who connected to a AS Azure database and what queries were sent.

In this case, the cause of the long failover time was enabling Query Store after upgrading, for a database with a large workload of ad hoc queries, meaning there was 100GB of Query Store data to load.

Data classifications in Azure SQL DW entered public preview in March 2019. They allow you to label columns in your data warehouse with their information type and sensitivity level.

The recommended way to backup and restore databases in Managed Instance is using built-in automatic backups and cross-instance point-in-time restore. However, if you need to use the manual backups, Jovan Popvic explains the procedure that you would need to follow.

How to connect Python to Microsoft T-SQL, Postgres, MySQL and SQLLite

As a part of SQL Server production support and day to day tasks we usually come across situations where we get requests to run a T-SQL script against multiple servers. Sometimes the request is to not only to run the script, but save the output too

Whether you’re looking to go cutting-edge and hop on SQL Server 2019 as soon as it’s available, or whether you’re just trying to evacuate from SQL Server 2008 to anything that’s going to be supported after this summer, you’re probably doing upgrade projects. This is a good time to ask yourself 5 questions...

Brent Ozar on questions that SQL Server setup wizard should ask you, by default, to help you protect the data

Often, we can't build a fully functional SQL Server database just from the DDL code. Most databases also require what is often referred to as 'static', or 'reference' data, which will include such things as error messages, names of geographical locations, currency names or tax information. What's the best way to handle such data?

Querying perfmon data to track memory usage patterns on NUMA nodes

The max_dispatch_latency property is the maximum duration that an event, once captured, would reside in the buffer before written to the target. The default for this property is 30 seconds, which is fine in practice but for live demos you can consider reducing it (minimum is 1 second).

Ginger Grant shares a few tricks to make it easier to upgrade from Power BI Data Model to Tabular.

A critique of a first attempt to write an M query to convert a folder full of images to text that caused PowerQuery to perform multiple file reads

This article describes how to use and interpret the information provided by the Power BI Performance Analyzer, finding the bottlenecks in slow reports.

Dave Mason takes us through his journey in learning how to visualize data with R, in Power BI desktop.

A preview of areas of development for the PowerShell 7 release.

As a companion script with his recent article abou...

Sometimes, simply switching the order of columns in an index means SQL Server takes more fine-grained locks, for a shorter period of time, causing less blocking.

No matter what you use to collect or display them, deadlock graphs can return some confusing information.

Even if your scalar function doesn’t touch tables, it still cripples performance by forcing serial processing, blowing up your CPUs, and obfuscating your query plans. Scalar user-defined functions: not even once.

Erik Darling explains a trick that has a specific use case, but can be quite effective when you spot it.

This post provides new information about the preconditions for minimally logged bulk load when using INSERT...SELECT into indexed tables.

If someone sends you a cached plan that’s slow, how can you tell if it’s because of parameter sniffing?

Erin Stellato explains the limits on both plan cache size, and number of entries. If you’re seeing plan cache bloat due to an ad hoc workload, take note of the space consumed by your plan cache and consider enabling the optimize for ad hoc setting.

Bert Wagner summarizes 12 techniques he's learned over time, from trial and error, for rewriting queries to improve performance.

Does index fragmentation hurt query performance? Possibly. It depends on your workload. Tibor Karaszi explains.

In this article, Kathi Kellenberger demonstrates how to create a project and report using the SSRS Report Wizard. The wizard is fine for getting started, but you’ll soon learn about its many limitations. She also explains how reports fit in projects and solutions.

The SQL Server 2019 CTP 2.5 release introduces a new Java implementation; your Java code now needs to inherit an abstract base class from the Java Language Extension SDK.

How to build out Azure resources during deployments, using a few handy Azure commands for BASH.

SQL Server has many tools used to make it secure. In this article, Robert Sheldon demonstrates several of these features including SQL Server Configuration Manager and the Vulnerability Assessment tool.

Kendra Little explains why DBAs don't allow sysadmin permissions in development, and why, it at all possible, they should.

In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors?

When it detects a deadlock, SQL Server will, by default, pick the process the has the lowest cost to rollback. This can present a potential issue if the process is a business critical process. The DEADLOCK_PRIORITY allows us to have a bit of control over whether or not a process is chosen as the victim.

On my machine, code that calls SYSDATETIME() 10 million times in a loop takes about 11.6 seconds to execute, while with SYSUTCDATETIME() it takes 4.3 seconds. Why is SYSUTCDATETIME() so much faster than SYSDATETIME()?

As database changes are added to the DevOps pipeline, unit testing of those changes becomes crucial. In this article, Adam Hafner describes unit testing and how it applies to databases.

Some recommendations for determining when it makes sense to run Docker Containers on Windows--and when it doesn't.

Grant Fritchey plays around with container volumes, showing how to move a backup file into a container by just copying it to the volume, and then running a restore from that backup.

Grant Fritchey creates a container with a database & some data and a couple of general customization, then creates from it a new custom image.

Wednesday June 12, 16.00-17.00 / 10.00-11.00 Central - Find out how Compliant Database DevOps enables agility while safeguarding your organisation in the event of a data breach or regulatory audit.

Monday June 17 16.00-17.00 / 10.00-11.00 Central - Discover how adopting 4 steps will lay your foundations for automation and Compliant Database DevOps.

Monday June 17 16.00-17.00 / 10.00-11.00 Central - Discover how adopting 4 steps will lay your foundations for automation and Compliant Database DevOps.

Wednesday June 12, 16.00-17.00 / 10.00-11.00 Central - Find out how Compliant Database DevOps enables agility while safeguarding your organisation in the event of a data breach or regulatory audit.

In this independent review of SQL Provision, the Bloor analyst finds that SQL Provision’s combination of database cloning and data masking stands out with “clear advantages over competing approaches, such as data subsetting or synthetic data generation".

Discover the latest market trends and assess your approach to test data management. For example, the Bloor analyst recognizes “…an increased emphasis on test data provisioning, as opposed to merely test data management.” The report also covers key capabilities offered by vendors in the market, including Redgate.

Here's how to check the current state of connections to the production SQL Server, what to look for and why.