Press "Enter" to skip to content

Month: June 2024

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

Security Tips for Backups in SQL Server

Published 2024-06-20 by Kevin Feasel

Mike Walsh shares a few tips:

There are obviously many things to check when it comes to SQL Server security. We’re talking about a lot of those elements of security this month. Today I want to talk a little more about backups as it relates to their role in securing your SQL Servers.

When it comes to your backups and security in SQL Server –>

Read on for five tips to help ensure your backup strategy is doing what you intend it to do, but is also not a real attack vector at the same time.

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

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

An Overview of gganimate

Published 2024-06-19 by Kevin Feasel

Dario Radecic shows off a neat library:

The main criticism people have when it comes to ggplot2 is the static nature of the charts it has to offer. Truth be told, it will never be an interactive visualization king like Highcharts, but it doesn’t mean animation is out of the picture.

Meet R gganimate – a natural extension of ggplot2 that allows you to visualize your data change through time or some other variable, and then render and export the chart as a set of PNGs, or a single GIF/MP4.

Click through to learn more about it. I remembered the original gganimate and was going to say, “Wow, I hadn’t heard of that library in forever.” But it turns out that Thomas Lin Pedersen built a newer version of the library and has added in quite a bit of functionality since the last time I looked. H/T R-Bloggers.

Comments closed

New Video: Online Passive-Aggressive Algorithms

Published 2024-06-19 by Kevin Feasel

I have a new video:

In this video, I cover the series of classification algorithms with the best possible name: online passive-aggressive algorithms.

I remember, when reading up on this, being incredulous that the idea even worked. But it turns out that it’s actually pretty good in practice, especially on constrained hardware. Still, this is definitely an algorithm you’d want to test in comparison to others before jumping right in, as there’s a risk you can end up with terrible results.

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

TempDB Contention and SQL Server 2022

Published 2024-06-19 by Kevin Feasel

Simon Liew shows off a change in SQL Server 2022:

Tempdb often acts as a high-traffic repository in SQL Server, experiencing significant contention. This includes not only temp table usage but also processes such as triggers, worktables for storing intermediate results for spools, cursors, sorts, work files for hash join, and temporary large object (LOB) storage, just to name a few.

A prominent issue is Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM) system page latch contention, which can be particularly problematic under specific high concurrency workloads.

Read on for a demonstration of this.

Comments closed