Press "Enter" to skip to content

Category: Administration

Azure Resource Locks

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

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

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

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

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

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

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

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

Persisting Data in Azure Redis Cache

Arun Sirpal feeds the mogwai after midnight:

I mentioned before that you could use the idea of data persistency to rebuild your data from total failure. There are two types. RDB and AOF.

RDB – persists a snapshot of your cache in a binary format. The snapshot is saved in an Azure Storage account. AOF – saves every write operation to a log. The log is saved at least once per second into an Azure Storage account. 

I’m a big proponent of using Redis as a caching service. I’m not a big proponent of using Redis as a persisted database, mostly because I’ve had a lot of bad experiences with persistent Redis…

Comments closed