Press "Enter" to skip to content

Category: Administration

Managing the SQL Server Error Log

Published 2024-06-25 by Kevin Feasel

David Seis doesn’t let the error log get out of hand:

SQL Server offers a robust logging mechanism known as the SQL Server Error Log. This log plays a crucial role in troubleshooting, auditing, and ensuring the smooth operation of your SQL Server instances. You can explore the SQL server error log through SQL Server Management Studio in the management folder. There are various tools in the GUI to make navigation easier, as well as stored procedures to enable faster parsing of the data stored.

Read on for a few tips around log management.

Comments closed

OLE Automation Security

Published 2024-06-24 by Kevin Feasel

Jeff Iannucci talks about OLE Automation:

It’s rare to see OLE Automation procedures enabled on a SQL Server instance. Most folks aren’t using these specially system procedure because they didn’t have a need to use them, have a compliance requirement that prohibits using them, or they tried using them and had adverse results. As the Microsoft documentation on OLE Automation stored procedures notes:

“Don’t directly or indirectly call Automation procedures from any SQL Server common language runtime (CLR) objects. Doing so can cause SQL Server to crash unexpectedly.”

But if this setting is enabled in any of your SQL Server instances, you need to consider it similarly to the xp_cmdshell setting. By that I mean: this is probably not a problem, but you should try to figure out why the setting is enabled, and how its associated system stored procedures are being used.

I find this perfectly reasonable. There’s a lot of fear around xp_cmdshell, when in practice, it doesn’t affect security at all unless you completely mess things up and start granting rights to non-sysadmins.

Comments closed

Don’t Enable TRUSTWORTHY on SQL Server

Published 2024-06-21 by Kevin Feasel

Jeff Iannucci shares good advice:

If you have ever used our free tool to check SQL Server security, you may have seen the check for the “TRUSTWORTHY database owned by sysadmin” show up as one of the highest of priority items, requiring action. When we started reviewing the security permissions and configurations for our clients’ instances, we didn’t expect to find it very often since TRUSTWORTHY database setting is off by default.

Unfortunately, this has been discovered with some frequency, and when combined with a few other common practices, it presents a tremendous vulnerability to escalate privileges for both authorized users and hackers.

Read on to learn more about this. And to supplement, I will once again link Solomon Rutzky’s outstanding guide on the topic.

Comments closed

Restoring a SQL Server 2000 Backup in 2024

Published 2024-06-20 by Kevin Feasel

Randolph West turns back the clock:

Problem statement: I have a SQL Server 2000 database backup that I need to restore to a supported version of SQL Server (preferably SQL Server 2022). How do I do that?

Read on for Randolph’s answer on how to do this. Pre-2008, things may get a little shaky, as that’s back when Microsoft really deprecated functionality. So even if you do restore a backup using this technique, I do wonder what happens if you were using a deprecated datatype like NTEXT or TIMESTAMP. As of SQL Server 2022, those are still in the product, so I don’t have a good example so much as a bit of niggling paranoia.

Comments closed

Postgres Vacuuming and Transaction ID Wraparound

Published 2024-06-20 by Kevin Feasel

Andreas Scherbaum explains what Transaction ID Wraparound is and how you can avoid it:

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage concurrent access to the database, and store each version of a tuple in a table. To keep the database efficient, PostgreSQL employs a cleanup process called VACUUM, which removes unnecessary tuples. This not only saves disk space and keeps the database size under control, it also prevents the dreaded “Transaction ID Wraparound” issue. Regularly running VACUUM is crucial, including on the template0 database, which is normally handled by the Autovacuum daemon. However, sometimes manual intervention is necessary.

Read on to learn more.

Comments closed

Advance Notifications for Azure SQL MI

Published 2024-06-20 by Kevin Feasel

Uros Milanovic gives us a heads up:

Advance notifications allow you to prepare for planned maintenance events on your SQL Managed Instance resources. They alert you 24 hours before a planned maintenance event. Advance notifications work hand-in-hand with SQL Maintenance Windows – with the two combined, you gain control over when your managed instances receive updates and receive a notification ahead of time.

Read on to learn more about how this works. There is a bit of setup involved to subscribe to these, though Uros provides a link to a guide on how to do it.

Comments closed

Automating SQL Server Installation via Powershell

Published 2024-06-19 by Kevin Feasel

Vlad Drumea performs an installation:

In this post I cover a script I’ve been using to automate SQL Server installation with PowerShell in my home lab.

As opposed to my previous blog post that demos the creation of a SQL Server Developer edition container, this installs a full-fledged SQL Server instance.

Read on for an overview of the script, from where you can download it, and how it works.

Comments closed

PostgreSQL ON CONFLICT Directive

Published 2024-06-19 by Kevin Feasel

Shane Borden walks through an issue you might not expect:

I’m always working with customers migrating from Oracle to PostgreSQL. One of the things in Oracle that didn’t necessarily have any additional impact other than I/O against an index was if the application executed insert statements which violated a PK constraint. Typically an exception handler was added to the code and while you could argue that the application shouldn’t do that, it typically was not something that had to be dealt with too often. However in PostgreSQL it IS something you need to be aware of.

Read on to learn why, as well as what you can do about it (other than making your INSERT operation resilient to this sort of issue).

Comments closed

Exporting Multiple Databases Concurrently via SqlPackage

Published 2024-06-18 by Kevin Feasel

Jose Manuel Jurado Diaz has a script for us:

This week, I’ve been working on a service request case where we need to export multiple databases using SqlPackage. Following, I would like to share my lesson learned to export  simultaneous several databases, saving the export files to the F:\sql folder and the logs of the operations to the F:\sql\log folder.

Few recommendations when performing these exports:

Click through for those recommendations and the script. The cynic in me would add a third tip: make sure your databases are small, or else SqlPackage won’t work so well.

Comments closed

Using the Dedicated Admin Connection in SQL Server

Published 2024-06-18 by Kevin Feasel

David Seis talks about the DAC:

In SQL Server, a feature that often stands out for its unique capabilities is the Dedicated Administrator Connection (DAC). This not just another connection to your SQL Server instance, but a special diagnostic connection designed for administrators when standard connections to the server are not possible.

Read on to see how it differs from regular connections and how you can use it.

Comments closed