Press "Enter" to skip to content

Category: Administration

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

Low-Downtime Migration Techniques from SQL Server 2017 to 2022

Published 2024-06-17 by Kevin Feasel

Yohei Kawajiri describes three techniques for performing a SQL Server migration:

It is possible to configure a SQL Server Always On availability group with a primary replica running on SQL Server 2017 and a secondary replica running on SQL Server 2022, but there are important considerations and limitations to keep in mind: 

  1.  Backward Compatibility: SQL Server supports having replicas on different versions, but the primary replica must be on an older version than or equal to the secondary replicas. Therefore, having SQL Server 2017 as the primary and SQL Server 2022 as the secondary is valid. 
  2.  Database Upgrade Path: When you decide to upgrade the primary replica to a newer version, you need to follow a specific upgrade path to ensure minimal downtime and data integrity. 

Building an availability group? Yeah, makes a lot of sense. Performing log shipping? Sure, I could see that working. Database mirroring? I did not expect to read that one, mostly because it’s been deprecated for a decade.

Comments closed

Windows Local Admins and sysadmin in SQL Server

Published 2024-06-17 by Kevin Feasel

Jeff Iannucci continues a series on security:

If you have been reading along with our series of “30 SQL Server Security Checks in 30 days” posts, you’ve probably noticed a theme for a lot of these posts, where we recommend reviewing which principals have CONTROL SERVER permissions or are in the sysadmin role.

Full disclosure: I hope you aren’t tired of that yet, because there will be more of those posts.

However, today I wanted to turn your attention towards what might be considered potential shadow members of the sysadmin role. These are the members of the server’s local Windows Administrators group.

Read on for Jeff’s explanation.

Comments closed

Task Scheduling in PostgreSQL with pg_cron and pg_timetable

Published 2024-06-17 by Kevin Feasel

Radim Marek compares two extensions:

Working with PostgreSQL, and virtually any database system, extends far beyond merely inserting and retrieving data. Many application and business processes, maintenance tasks, reporting, and orchestration tasks require the integration of a job scheduler. While third-party tools can drive automation, you can also automate the execution of predefined tasks directly within the database environment. Although system-level cron might be a starting point, the power of the database system lies in its ability to store all the necessary information alongside your data/schema. In this article, we will explore pg_cron and pg_timetable as two distinct PostgreSQL-specific tools for scheduled task automation.

Read on to learn more about each.

Comments closed