A Fine Slice Of SQL Server
Andrew Pruski tries out a tool:
We’ve all been there as DBAs…people requesting access to the servers that we look after to be able to view certain things.
I’ve always got, well, twitchy with giving access to servers tbh…but what if we could completely restrict what users could do via powershell?
Enter Just Enough Administration. With JEA we can grant remote access via powershell sessions to servers and limit what users can do.
Click through to see how it works.
Comments closedLukas Vileikis continues a series on backing up MySQL. Part 2 involves Percona XtraBackup:
As already stated above, Percona XtraBackup is one of the primary offerings for MySQL & Percona database administrators developed by Percona. The tool is an open-source backup utility that does not lock our databases during the backup processes it performs. Percona says that their tool can provide automatic verification of backups that have been taken, offer fast dumping and restore times, and above all, it’s supported by their award-winning consulting services helping us make sure that our data and its backups are in safe hands by day and by night.
mysqlpumpis a backup utility that is used via the command-line interface. The tool is very similar tomysqldumpin that it provides us with the ability to take logical backups, but also different at the same time – the goal ofmysqlpumpis to be an extendable, parallel-supporting replacement ofmysqldump. In their blog from 2015, MySQL team said that one of the primary aims of introducingmysqlpumpwas not be forced to implement legacy functionality that is provided bymysqldump.
Read on to see how both of these work.
Comments closedAaron Bertrand follows up on a prior post:
As soon as you realize your growth rates are higher than expected, you need to plan to buy or allocate more disk space. There is no way around this—more data means more disk. You can delay the inevitable for a little bit with better compression, but this is not a long-term fix, and it can impact query performance in different ways (trading CPU for I/O).
Once more disk is in place, you can plan your growth better.
Click through for some guidance on how to plan that growth.
Comments closedKoen Verbeeck sets up a great weekend project:
SQL Server 2022 has been released! In this blog post, I’ll walk you through how you can install the latest version of SQL Server on your machine. I’ll be using the developer edition of SQL Server 2022, which can be downloaded here.
Click through for guidance on setting up Developer edition.
Comments closedDavid Fowler troubleshoots an issue:
Checking the logs on the secondary, it was littered with ‘Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (80090302) 0x80090302(The function requested is not supported).’ messages. The primary server wasn’t able to authenticate with the secondary, but why? Everything looked ok as far as I could see.
Click through for the fruits of David’s labor.
Comments closedWilliam Assaf mentions a welcome change to SQL Server 2022:
Starting with SQL Server 2022, transaction log file growth events up to 64 MB in size can benefit from instant file initialization (IFI). As usual, the transaction log is otherwise unable to benefit from instant file initialization.
This should be a big performance improvement if your transaction log files unexpectedly grow. Of course, you should try to avoid autogrowth events altogether.
The prior default of 10% autogrowth has led to so many problems over the years. I’d like new database files (MDF and NDF) to have a similar default as well.
Comments closedDenny Cherry shares a few thoughts on Page Life Expectancy:
One of the very misunderstood values that you can monitor in Microsoft SQL Server is Page Life Expectancy (PLE). I’ve read online that many people think that the value of Page Life Expectancy is worthless to monitor. And that really isn’t true. While Page Life Expectancy won’t tell you a specific problem by itself, it can be used to point you in the correct direction so that you know where to look.
There was a backlash against PLE, specifically the “PLE should be at least 300” concept, but Denny throws that away and digs into the actual benefit from observing this metric.
Comments closedAndrew Pruski picks up Chekov’s Gun:
Recently I had to create another pacemaker cluster, this time on-premises using VMWare virtual machines. The steps to create the pacemaker cluster and deploy an availability group where pretty much the same as in my original post (minus any Azure marlarkey) but one step was different, creating the STONITH resource.
A STONITH resource is needed in a pacemaker cluster as this is what prevents the dreaded split brain scenario…two nodes thinking that they’re the primary node. If the resource detects a failed node in the cluster it’ll restart that node, hopefully allowing it to come up in the correct state.
Read on to see how Andrew did it.
Comments closedIn this case, the SQL Server has multiple NUMA nodes, with greater than 16 logical processors per node – that’s the last line of the screenshot. In that line, Microsoft says MAXDOP should be half of the number of logical processors with a max of 16 – so 16.
But it’s recommending 8. Hmm.
Read on for the answer.
Comments closedKevin Hill has a video for us:
If you need to find the SQL Server ErrorLog in a hurry and don’t want to spend 30 minutes drilling into every drive on the server:
Fortunately, Kevin has anticipated me and has put together the “I don’t watch videos” version.
1 Comment