Press "Enter" to skip to content

Category: Administration

Public Preview of SQL Server on Azure Arc

Published 2020-09-28 by Kevin Feasel

Sasha Nosov gives us an update on Azure Arc:

The preview includes the following features:

– Use Azure Portal to register and track the global inventory of your SQL instances across different hosting infrastructures. You can register an individual SQL instance or register a set of servers at scale using the same auto-generated script.

– Use Azure Security Center to produce a comprehensive report of vulnerabilities in SQL servers and get advanced, real time security alerts for threats to SQL servers and the OS.

– Investigate threats in SQL Servers using Azure Sentinel 

– Periodically check the health of the SQL Server configurations and provide comprehensive reports and remediation recommendations using the power of Azure Log analytics.

Click through for more information and documentation.

Comments closed

Stellar Repair: A Review

Published 2020-09-25 by Kevin Feasel

Grant Fritchey reviews a product which attempts to repair corrupted SQL Server databases:

Let’s start with the most important piece of information you need: it works.

The software itself is really simple to use and just does what you need, repairs your corrupted SQL Server instance. On that alone, I can recommend the tool.

However, there are a few gotchas I ran into along the way. Mostly, little stuff. It’s things a little polish in the UI and some clean up around language could help out. Don’t get me wrong, I’m happy with this software. It worked. It’s just how it works that we should talk about.

Click through for Grant’s full review.

Comments closed

Automatic Soft NUMA in SQL Server

Published 2020-09-25 by Kevin Feasel

Ameena Lalani walks us through NUMA and automatic soft NUMA in SQL Server:

Modern processors have multiple cores per socket. Each socket is represented, usually, as a single NUMA node. The SQL Server database engine partitions various internal structures and partitions service threads per NUMA node. With processors containing 10 or more cores per socket, using software NUMA to split hardware NUMA nodes generally increases scalability and performance. Prior to SQL Server 2014 (12.x) SP2, software-based NUMA (soft-NUMA) required you to edit the registry to add a node configuration affinity mask, and was configured at the host level, rather than per instance. Starting with SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x), soft-NUMA is configured automatically at the database-instance level when the SQL Server Database Engine service starts. Please read this  documentation and this documentation for more understanding.

Read on for more info.

Comments closed

Using Synonyms in SQL Server

Published 2020-09-23 by Kevin Feasel

Greg Larsen takes us through the ins and outs of synonyms in SQL Server:

Once a database object has been created, and lots of application code has been written that references the object, it becomes a nightmare to rename the object. The nightmare comes from the amount of effort and coordination work required to make the name change without the application failing. If just one place is missed when coordinating the rename, the outcome could be disastrous. This is where a synonym can help minimize the risk associated with renaming a base object.

I’ll admit that I don’t really think about synonyms much and have used them at most a couple of times in my career. I can see where they’d be useful, but that comes at the risk of something going wrong and people not even realizing they exist.

Comments closed

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