Pick a Database, any Database…

Having spent some years helping to define the IT strategy of a couple of well-known commercial organizations, you'd think I'd be able to tell you exactly the logical process by which an organization, or IT project, settles on their choice of database system. In fact, no. I've only rarely experienced an IT project where a rational decision was taken about the database product to use. Often it is difficult to detect anything more than a sort of 'group instinct'. Occasionally, it would be based on the existing skill set, or even the groundswell of opinion from the developers.

Sometimes, it seemed to be the result of what we called, in code, the 'lobster lunch'. The higher echelons of the organization would return from a trip to a vendor, dazzled by a smooth marketing pitch and fine seafood, and announce the choice of database technology shortly after. This was often before deciding on the aims of the project, and its database requirements.

Sadly, there is a wide difference between what the marketing people will tell you are the virtues of a relational database system and the aspects that appeal to the average commercial organization. In the broad view, fancy features hold little appeal to the IT manager, especially if they drift from the core purpose of a database. Instead, they value responsiveness of queries, ease of use and rapid delivery of applications. From this perspective, an expensive database system can be a lot cheaper on the budget of an IT project. Most of the considerable costs of maintaining a leading relational database system are incurred almost invisibly by the vendor in pursuit of performance, conformance, and reliability at scale; You are paying for the virtues you need rather than the ones that initially seem attractive.

There are also other 'hidden costs' in purchasing a database system. For example, if the database system you're considering still uses arcane and inconsistent ways of implementing procedures and functions, then the extra development costs of building a database application can soon outweigh the up-front license costs. If you must hire 'consultant' developers to fix inexplicably slow query performance, you are soon losing control of your budget.

You will quickly regret an injudicious purchase of a database system. It is a bit like buying a fancy-looking car at a knockdown price. It looks the part in the driveway, but what you're really buying is the hassle and distraction of frequent trips to the garage.

Phil Factor 

Tony Davis

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

A generic way of exporting, deleting and loading data, for database development work. It uses Flyway Teams, a PowerShell framework, JSON files for storage and a table manifest to define the correct order of dependency for each task. It should help a team maintain datasets between database versions, as well as to switch between the datasets required to support different types of testing.

Dependency information will allow you to avoid errors during a database build or tear-down, by ensuring you create or remove objects in the right order. It will also help you to avoid future 'invalid object' errors, because it will allow you to check that no database alterations have introduced broken references, during Flyway migrations.

The first release candidate for Microsoft SQL Server 2022 is out, so let’s see what’s involved with installing it.

Erik Darling does a quick roundup on some of the more interesting Q&A he's taken part in on dba.stackexchange.com recently...and realizes he should get out more!

How to use the long trusted Log Shipping technology, with tweaks to a few settings, to get a read-only copy of a database.

On needing to investigate issues with mount points not appearing in Spark clusters, Kevin Chant discovered the web terminal for Azure Databricks.

When using TDE with Customer-Managed Key, one of the important responsibilities that customers need to perform on a regular basis is key rotation, that is, rotating the TDE Protector on the server by switching to a new key (or new version of the earlier key) from Azure Key Vault.

Warwick Rudd explores the differences between an installation of Azure SQL Managed Instance and Azure Arc-enabled SQL Managed Instance.

Does Azure Synapse Link for SQL Server 2022 only create csv files, or is there a tipping point where it starts to create parquet files? Kevin Chant investigates.

Adding a read replica into a AWS RDS instance led to all sorts strange mssqlsystemresource-related errors and connection errors. The cause? Max Server Memory was set too high and stealing from the OS, causing memory to thrash and things to crash.

There are two options for using OPENJSON: with the default schema or with an explicit schema. There are performance implications for each, which I'll review with some examples.

This new, major version offers a new user interface and user experience, providing a modern look and feel without losing any of the features available in previous versions.

Chris Webb on why the new, as yet undocumented, EvaluateAndLog() function might be something to get excited about.

In today’s post, I’ll be providing a useful script to detect and troubleshoot when the query plan cache contains too many different plans for the same query hash, which could happen as a result of possible parameterization issues.

Sometimes, aquery is so simple that the optimizer doesn't bother weighing out different plans before returning results; it just uses a trivial plan. But is it the best plan?

How to do parameterization properly, even when you're executing dynamic SQL.

Look! Look! I am a Non-SARGable Predicate!

You may have noticed large variations in elapsed time for parallel queries while performing query tuning or observing a production workload. This post reviews some of the possible explanations for those variations.

How to call Azure ML Rest API inside Power Query, even if you don’t have Power BI Premium.

Power BI gives you out-of-the-box statistics and end users’ activities. But the retention period is 90 days. Tomaz Kastrun provides a script that combines all workplaces into a single SQL Server table and extracts activity logs per day. And you can keep your logs for as long as you need them.

An examination of the two parameters in the Enhanced Refresh API for datasets that use incremental refresh: applyRefreshPolicy and effectiveDate.

Let’s say you want to send a PDF version of a report to each of your store or department managers using a set of parameter values specific to each person. In the Power BI service that is not an option, but using Power Automate you can do this.

This book aims to improve the performance of your SQL Servers, how does it fare?

A way to pass and read values from a query string in R Markdown, which works on a development computer with RStudio or on a Shiny server.

Announcement of SQL Server 2022 Release Candidate 0, with a brief summary of new features and capabilities.

In this post I’ll explain what TDE is, along with its use cases, and I’ll use a thorough demo to show how to implement it in a database and how it works

As part of recent efforts to make it easier for users to adhere to the Principle of least Privilege, all new features in SQL Server 2022, can be controlled with more granular permissions, including SQL Ledger.

You need to make sure a database application is written and tested, in your dev and test environments, with the same set of permissions it will have in production

The Russia-backed Nobelium APT has pioneered a post-exploitation tool allowing attackers to authenticate as any user.

Did you know that 1/2=0? If you didn’t you could be in for some interesting surprises.

SQL Server 2002 uses Parameter Sensitive Plan optimization (PSPO) to try to fix problems caused by parameter sniffing. Does it work? As a hint, Brent pronounces it as pss-poh...

When you use sp_prepare, parameterized queries behave differently from normal and you often get inaccurate cardinality estimates. But this behaviour changes in SQL Server 2022.

Microsoft has an official list of what’s new in 2022 overall, but here I’m specifically focusing on system objects that might be interesting to script developers.

The READ UNCOMMITTED isolation level, which the NOLOCK table hint activates, is not to be trusted when it comes to writing data. You are not always protected by error 1065 in cases where writes meet NOLOCK.

If you spend much time at all in SQL Server Management Studio (SSMS), you’ve no doubt had trouble figuring out which query tab is the one you need. The "Save pinned tabs as separate row" option in SSMS can help.

Support for Ledger Objects, Query Plan Viewer and Table Designer Updates and more

Microsoft provides a rare glimpse into its more than 10-year effort to move its Office 365 and Microsoft 365 services to Azure.