Press "Enter" to skip to content

Category: Administration

The Problem with VM Backups of SQL Server

Published 2020-09-22 by Kevin Feasel

Sean Gallardy turns a problem on its head:

Now let’s get to the main point, which is how long the VM stays paused or stunned – remember, this is a “small” or “short” amount of time, one might even say “trivial”. When it is kept this short to where it’s “trivial” as in less than a second then all is good and you most likely won’t notice it except in very high workloads… but we should be running with VSS integration and not VM level so it’s still incorrect, but hey. When this time is not short of trivial then GOOD things start to happen, most notably that high availability kicks in.

I appreciate the framing of this post, as the failover wasn’t a problem; it merely exposes the actual problem.

Comments closed

A Postgres Version of WhoIsActive

Published 2020-09-22 by Kevin Feasel

Josh Simar is on a mission:

 while ago, I asked in the twitterverse if there was a Postgres equivalent for the great sp_WhoIsActive script. While I didn’t get a flat-out no (which I wasn’t expecting) I didn’t get anyone pointing me in the direction of something pre-done and did get some advice that I should take it on.

Well it took quite a while and it’s still nowhere near as robust as the MSSQL version but as a first stab I have created the pg_WhoIsActive function.

While doing it I basically said that I want a 1 to 1 equivalent as much as possible but to get it out quick I had one major rule for the POC.

Check it out.

Comments closed

Baselining SQL Server with Grafana

Published 2020-09-21 by Kevin Feasel

Ajay Dwivedi has an interesting project:

Are you tired of not knowing what is usual workload of your server? Do you want to know what is speed of your server?

If you are a developer or DBA who manages Microsoft SQL Servers, it becomes important to understand the current load vs usual load when SQL Server is slow.

Recently, for this reason, I have been working on my own automation to capture performance counters that would help me get an idea of CPU/Memory/IO/Network issues. Baselining also lets me know if the Server load has increased gradually over time.

I have created GitHub repository ‘SqlServer-Baselining-Grafana’ that contains all the scripts that will help anyone to set up the baseline on individual SQL Server instances, and then visualize the collected data using Grafana through one Inventory server with Linked Server for individual SQL Server instances.

Baselining is one of those concepts we often talk about but just as often have trouble implementing.

Comments closed

Using Lightweight Query Profiling in SQL Server

Published 2020-09-16 by Kevin Feasel

Taiob Ali walks us through lightweight query profiling:

With the release of lightweight profiling, you can monitor real-time query progress while the query is in execution. There are a few ways to do this.

– Using System dynamic management view sys.dm_exec_query_profiles which monitors real-time query progress while the query is in execution.
– Using System dynamic management view sys.dm_exec_query_statistics_xml  which returns returns query execution plan for in-flight requests.

Click through for more details, including how to enable it.

Comments closed

Using an Azure VM’s D Drive for tempdb

Published 2020-09-15 by Kevin Feasel

William Assaf shows how you can use the temporary D drive on an Azure VM to host tempdb in SQL Server:

Moving your SQL Server instance’s TempDB files to the D: volume is recommended for performance, as long as the TempDB files fit it the D: that has been allocated, based on your VM size. 
When the D: is lost due to deallocation, as expected, the subfolder you created for the TempDB files (if applicable) and the NTFS permissions granting SQL Server permission to the folder are no longer present. SQL Server will be unable to create the TempDB files in the subfolder and will not start. Even if you put the TempDB data and log files in the root of D:, after deallocation, that’s still not a solution, as the NTFS permissions to the root of D: won’t exist. In either case, SQL Server will be unable to create the TempDB files and will not start.

Read on for a few options and William’s thoughts on the relative merits of each.

Comments closed

Managing the SQL Server Error Log

Published 2020-09-14 by Kevin Feasel

Guy Glantser has some tips for managing the SQL Server error log:

SQL Server maintains its own log, also called “SQL Server Error Log”. This log contains messages describing informational and error events, similar to messages that you can find in Windows logs. In fact, many of the messages found in the SQL Server Error Log can also be found in the Windows Application Log. The SQL Server Error Log is a great place to find information about what’s happening on your database server.

SQL Server uses 7 log files to store these messages. One file serves as the current log file, and every new message is written to that file. The other 6 files are archived files, and they contain previous messages. Each time SQL Server is restarted, it recycles the files. What does it mean? First, it means that a new log file is created and becomes the new current log file. Second, the oldest log file (“Archive #6”) is deleted. And third, all the other log files are pushed back one step. The previous current log file becomes “Archive #1”, the previous “Archive #1” log file becomes “Archive #2”, and so on.

Read on to see how you can change this, manage the size of log files, and retain data for a longer time.

Comments closed

A Review of KaDeck: Kafka Management Tool

Published 2020-09-11 by Kevin Feasel

Guy Shilo reviews KaDeck:

If you want the real web version then you need to register in their website and create a team first. Yes, KaDeck is aimed at teams of people working together. You create a team online and shortly you receive a mail with team id and a secret key that you will use when running KaDeck web..

It seems that there is no host installation of KaDeck right now (rpm package, binaries or so) but only a Docker image. This forces the user to run it in Docker or a Kubernetes cluster (or one of it’s commercial distributions such as OpenShift). Another thing is that checks your license online each time you start the container. If you want to use t offline you have to do a process of offline activation. You can reach it from the administration menu.

Click through for Guy’s thoughts on the product.

Comments closed

A Review of Distributed Availability Groups

Published 2020-09-11 by Kevin Feasel

Joey D’Antoni shares some thoughts on distributed Availability Groups in SQL Server 2016 and later:

I’m writing this post because I’ve been mired in configuring a bunch of distributed availability groups for a client, and while the feature is technically solid, the lack of tooling can make it a challenge to implement. Specifically, I’m implementing these distributed AGs (please don’t use the term DAG as you’ll piss off Allan Hirt, but more importantly its used in Microsoft Exchange High Availability, so it’s taken) in Azure which adds a couple of additional changes because of the need for load balancers. You should note this feature is Enterprise Edition only, and is only available starting with SQL Server 2016.

Read on for some of the positives around distributed AGs, as well as some negatives (mostly around the lack of tooling).

Comments closed

Starting and Stopping Services Things with dbatools

Published 2020-09-11 by Kevin Feasel

Mikey Bronowski continues a series on dbatools:

OK, let’s start. While doing some maintenance tasks there is a need to start/stop/restart SQL Services. Using SQL Server Configuration Manager is one way or even straight from SSMS, however, working with multiple instances is easier with dbatools.

Mikey covers services, SQL Agent jobs, endpoints, traces, and Extended Events sessions, so check it out.

Comments closed