Press "Enter" to skip to content

Category: Administration

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

Querying Deadlocks in Azure SQL DB

Published 2024-06-17 by Kevin Feasel

Josephine Bush wants to find the deadlocks:

A couple of weeks ago, a developer came to me and wanted to know how to figure out what was causing a deadlock. I honestly didn’t know where to look or if this was even being captured in Azure SQL DB already. It turns out that Microsoft has you covered with deadlock tracking. At least for a period of time. It looks like you can go back about a month, maybe.

Read on to see how you can find this information in Azure SQL DB. If you’re working in on-prem SQL Server and you don’t have any tooling set up, you can find some deadlocks in the system health extended event.

Comments closed

Stop Long-Running SQL Agent Jobs

Published 2024-06-14 by Kevin Feasel

Lori Brown puts a halt to things:

I have always done this by having a monitoring job that executes on a schedule that runs at a time when you need other jobs to stop.  Of course, you need to be aware that stopping jobs can come with unwanted side effects of some data change that may be unfinished (there may be a rollback) and the stopped job will have to gracefully be re-run at another time.  You will also see the stopped job as cancelled in the job activity monitor.  And, hopefully you are aware that you can tell a job to stop but if it is doing work using a linked server, it may not stop as expected or it can take a while if it is rolling back a transaction.

Read on for an example of how to do this.

Comments closed