Press "Enter" to skip to content

Category: Administration

Pre-Staging Log Shipping Backups with dbatools

Published 2022-05-26 by Kevin Feasel

Jess Pomfret doesn’t waste time:

Log shipping is a SQL Server feature used for disaster-recovery where the transaction log backups are ‘shipped’ from your production instance to a secondary instance. This enables you to cutover to this secondary server in the event of a disaster where you lose your primary instance. Log shipping is not a new feature but is still quite popular.

Recently I was tasked with setting up Log Shipping for a reasonably large production database. I didn’t want to initialize the secondary database with a new full backup as I was already taking full and log backups of the database. In this case we have the option of initialising the database by restoring the full & log backups up to the latest point in time and then configuring log shipping.

Read on to see how you can use dbatools to do this easily.

Comments closed

823/824 Alerts with SQL Server and VMware

Published 2022-05-19 by Kevin Feasel

David Klee loops us in on a tricky-to-catch problem:

We’ve been tracking a weird state with SQL Server virtual machines on VMware and possible warnings on database corruption while VM backups are running, largely centered around (but not isolated to) the tempdb database.

TLDR: We’ve now got a VMware KB article on this situation that you and your VM admins should read if you hit the condition and fall into the specifics listed below. Reference VMware KB 88201 for more details.

Read on for David’s thoughts and what to do if you hit this problem.

2 Comments

Azure Resource Locks

Published 2022-05-19 by Kevin Feasel

Craig Porteous explains the benefit (and pain) behind resource locks in Azure:

In theory, these are perfect for preventing accidental (or deliberate) deletion of resources in Azure. They don’t prevent the deletion of data though, only operating at the “control plane” of a resource. That still sounds great though. Turn them on everywhere! That’s another layer of security in your cloud data platform. Right?

Yeah, here’s where the pain comes in. I tried using resource group locks but there are some resources which use delete capabilities, such as Azure Media Service. A delete lock means no ability to delete uploaded videos.

Comments closed

Negative Identity Reseeding

Published 2022-05-12 by Kevin Feasel

Andrea Allred hits identity integer absolute zero:

Recently we had a system go down because we ran out of integers. (Mental note to create an alert when we are getting near to running out of integers.) If we upped the column to a bigint we were going to have to drop all the indexes. This server doesn’t have the capacity needed to do an operation of that size and it was estimated that it would be down for 8 hours while we dropped indexes, upped the column type and added back the indexes. This was way too long as it was early in the work day (had it been evening, it would have been fine).

There was also a concern about how many stored procedures were expecting an int but would need to be modified to a bigint, along with any code. That was a big undertaking and we were in an emergency down.

This reseeding typically works well, though it’s important to know if the code will fail upon getting negative numbers.

Comments closed

Getting Cluster File Share Witness Sharepath via Powershell

Published 2022-05-11 by Kevin Feasel

Tom Collins needs some information:

I normally use the Windows reg utility to get the  Cluster File Share Witness sharepath information. This is an example command line on a server returning the File Share Witness details

reg query \\myCluster\HKEY_LOCAL_MACHINE\Cluster\Resources /s /f sharepath

I want to start integrating these sorts of tasks into automated information gathering procedures and migrate this task into the Powershell library. Could you share some Powershell code to get the Cluster File Share Witness sharepath  information

Click through for a Powershell one-liner which gets this information.

Comments closed

Azure Redis Cache Geo-Replication

Published 2022-05-09 by Kevin Feasel

Arun Sirpal shows how to set up geo-replication in Azure Redis Cache:

The concept of a geo-replicated partnership between a primary and secondary node is very similar to that of something you may have seen with Azure SQL DB, where the primary handles all R/W and then the changes are pushed to secondary ( async). This is no different with Redis.

Read on to see what limitations exist and how you can set up geo-replication.

Comments closed

Finding Azure SQL DB Backup History

Published 2022-05-05 by Kevin Feasel

Taiob Ali takes us through a new DMV:

There is a new DMV currently in preview which returns information about backups of Azure SQL databases except for the Hyperscale tier. Microsoft official documentation is here.

If you run the example query as-is from the above documentation some of the columns do not make sense.

Taiob includes a better query which provides the type of information you’re used to in on-premises SQL Server.

Comments closed

Finding the Busiest Database

Published 2022-05-04 by Kevin Feasel

David Fowler looks for the biggest, meanest database on a SQL Server instance:

I’m sure we’ve all been there at some point, sometimes it’s easy to tell. We might only have a handful of databases on the server with one known to be the heaviest utilised. But sometimes things might not be so obvious, there could be a large numbers of databases or no obvious resource hog.

In those instances we need some way to figure out what how much time each database is spending on the CPU if that’s what we’re interested in or perhaps the total number of page reads or writes if IO is our problem.

Read on for one way to do this, assuming that the instance has been up long enough to give you reliable results.

Comments closed

Automated Partitioned Table Management

Published 2022-05-04 by Kevin Feasel

Eitan Blumin automates creation and deletion of partitions in SQL Server:

Before we begin, there are a few “ground rules” we should understand first:

1 – Partition Functions define the partition ranges

This means that whenever we want to eliminate an old partition range or add a new partition range, the PARTITION FUNCTION is the object that we actually need to modify.

Click through for Eitan’s entire process and a couple of scripts. This is an area that SQL Server could have made a lot easier, especially for periodic processes, by including options like “Daily” or “Monthly” or “Weekly(start on Monday)” for intervals rather than making people specify every partition separately.

Comments closed

High Availability in SQL Managed Instance General Purpose Tier

Published 2022-05-02 by Kevin Feasel

Niko Neugebauer clears up what options you have for high availability in SQL MI’s General Purpose tier:

The two main requirements around high availability are commonly known as RTO and RPO.


RTO – stands for Recovery Time Objective and is the maximum allowable downtime when a failure occurs. In other words, how much time it takes for your databases to be up and running.


RPO – stands for Recovery Point Objective and is the maximum allowable data-loss when a failure occurs. Of course, the ideal scenario is not to lose any data, but a more realistic (and also ideal) scenario is to not lose any committed data, also known as Zero Committed Data Loss.

With those definitions out of the way, read on to learn more.

Comments closed