Press "Enter" to skip to content

Category: Administration

E-Mail Alerts when a Database is Offline

Published 2023-11-24 by Kevin Feasel

Volker Bachmann gets an e-mail:

As the second article in the dbatools Quickies series, I would like to introduce another mail script that sends an email when databases on the servers examined are offline.

Unfortunately, it happens every now and then that databases are briefly taken offline but then forgotten. Here we receive weekly emails with all databases that are offline and where we can then follow up. If no database is offline, we will still receive an email with a short note.

Click through for the article in English and German, as well as a dbatools script and some additional remarks from Volker.

Comments closed

I/O Freeze in SQL Server 2022 T-SQL Snapshot Backups

Published 2023-11-24 by Kevin Feasel

Anthony Nocentino has a public service announcement:

SQL Server 2022 introduces a new feature to enable application-consistent snapshot backups. TSQL Snapshot Backups enable the SQL Server to control the database quiesce without external tools. Using TSQL Snapshot backups enables instantaneous restores, independent of the size of data, for a database, group, or server backups, including point-in-time recovery.

When you use this feature, it freezes I/O. You’ll see a record like this in your error log when you execute the command ALTER DATABASE TestDB1 SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON. This blog post will show you that the I/O freeze is just for write operations. You can continue to read from the database during this frozen state.

Read on to understand what’s going on, what “frozen” really means, and why this is a huge improvement over the classic behavior of the volume snapshot service.

Comments closed

Setting a Fixed Size for Transaction Log VLFs

Published 2023-11-21 by Kevin Feasel

Paul White was assured that there would be no math:

This is all very familiar, of course, but it is also dumb. Why on earth should we have to worry about internal formulas? It seems ridiculous to have to provision or grow a transaction log in pieces just to get a reasonable VLF outcome.

Wouldn’t it be better to be able to specify a fixed size for VLFs instead?

Starting with SQL Server 2022, there is now a way though it is undocumented and unsupported for the time being at least.

Read on to see what that option is, how it works, and what the limitations are. Looking at the side effects, I’d say this is probably not something you want to jump on right now.

Comments closed

Primer on Indexing and Partitioning in Postgres

Published 2023-11-20 by Kevin Feasel

Salman Ahmed gives us a 10,000 foot view of two topics:

When it comes to managing large and complex databases in PostgreSQL, an important decision you’ll face is how to optimize your data storage and retrieval strategies. Two common techniques for improving database performance and manageability are indexing and partitioning in PostgreSQL.

Read on for a quick overview of each topic, including the variety of index types and partitioning strategies available.

Comments closed

Finding Disks Low on Space via dbatools

Published 2023-11-14 by Kevin Feasel

Volker Bachmann has a script for us:

I will publish short PowerShell scripts with dbatools in loose order that will help with simple tasks or checks on multiple systems.

I would like to start with a script that lists hard drives that have less than a certain percentage of free space left, here 10%.
These are summarized in an email and sent as a list.

Click through for the script, as well as quick German and English explanations.

Comments closed

Searching for Tenant Settings in Microsoft Fabric

Published 2023-11-14 by Kevin Feasel

Wolfgang Strasser does a search:

Another nice feature update for administrators hit Microsoft Fabric – tenant settings got a search box!

Before the this new feature was added to the tenant settings page in October, you had one chance to search for the right tenant setting in the long list – the browser search feature. Just hit CTRL + F and search for i.e. Excel.

This is a quick quality of life improvement.

Comments closed

Creating a User in Postgres

Published 2023-11-13 by Kevin Feasel

Daniel Calbimonte adds a new user two separate ways:

Open PGAdmin and connect to the server where you want to create the new user.

In the Object Browser, expand the Server and go to Login/Group Roles tree, and right-click on the folder. Select Create>Login/Group Role option from the context menu.

In addition to adding a user via PGAdmin, there’s also a script to add users via pgSQL, and that will look a lot more familiar to SQL Server administrators.

Comments closed

Things to Ignore: SQL Server I/O Affinity

Published 2023-11-13 by Kevin Feasel

Sean Gallardy recommends you not touch this:

I honestly have no idea how or why people tend to use this configuration option, if you know please drop me a line and let me know or put a comment below, I’m genuinely curious. When I ask people why this is set when I see/find it, I normally get a “well that’s how the last server was” or “I don’t know”. Awesome. I always like to just change random settings for no particular reason. Some days you wake up decide you’re going to go change a bunch of settings on your computer because why not, it’ll be fun.

There probably is a reason, though Sean’s speculation of “so the benchmark scores for SQL Server testing would be higher” is just as likely the cause as anything else. My fallback alternative is “one very large customer threw a lot of money Microsoft’s way to add a setting that works for them but nobody else.” There are a couple of those in the product, too.

Comments closed

Bring-Your-Own-Key in Azure SQL Database

Published 2023-11-07 by Kevin Feasel

Rod Edwards shares some hard-earned guidance:

Some organisations are more strict on security than others. Thats just the way of the world, whether it be local policy, industry policy, paranoia or worryingly…just not considering it a priority.

This is why Microsoft have to offer BYOK, no, not the famous Icelandic singer from the 90’s and beyond either. I’m (very) tenuously referring to “Bring Your Own Key” which allows customers to let the encryption key to be handled by Microsoft for their encryption purposes, but create and use one of their own.

Read on to learn more about how it works, as well as a couple of important warnings you should keep in mind.

Comments closed

Creating and Connecting to an Azure Postgres Cluster

Published 2023-11-06 by Kevin Feasel

Louis Davidson shares some notes:

As I have dealt with other platforms, PostgreSQL has stood out to me as the platform I am most interested in because it feels like the one that is most competitive with SQL Server’s platform (Oracle is out there too, as is MySQL, and many others, but PostgreSQL feels like the balance of affordability and features that it has a similar feel enough to get started.)

There are a few high-level differences that can be confusing. A cluster is really just a server (or in SQL Server, an instance). Second, the way you execute a batch of code is very different, and sometimes this is based on the tool you are using. As you dig into how PostgreSQL works, some things will feel really normal, and some stuff will be very different from the other servers you have used.

Read on for the first post in the series, covering setup and connection.

Comments closed