Press "Enter" to skip to content

Category: Administration

Registered Servers in SQL Server

Published 2022-10-31 by Kevin Feasel

Kevin Hill has a video for us:

Query multiple SQL Server instances at one time!

I have used registered servers before, though my strong preference is for a Central Management Server. There are limits to how many servers CMS can handle before the initial load gets slow—I know this because we’re over the limit and it causes SSMS to freeze for 3-5 seconds the first time I open CMS. Still, having that server list in a central location means not having to share files around with the rest of the team and trying to figure out whether you have the most recent version of the file.

Comments closed

Monitoring the Serverless SQL Pool via Log Analytics

Published 2022-10-26 by Kevin Feasel

Sidney Cirqueira shows how to monitor SQL requests in Azure Synapse Analytics:

Today I would like to share a scenario that I was working on one of my serverless SQL Pool support cases. The customer asked for an advice on how to monitor serverless SQL requests by using log analytics.

The intention of this guide is to help you with choosing the configuration required to easily setup the Synapse Analytics Workspace monitoring and all other considerations about how to monitor serverless SQL requests with Azure Monitor. Spoiler: At the end of this article, I will share the latest version of the serverless workbook posted on the Azure_Synapse_Tool_Box. This includes a really cool way to see query execution information.

Read on for that and definitely check out the Azure Synapse Toolbox if you’re a Synapse user.

Comments closed

Enabling SSH Remoting with Powershell 7

Published 2022-10-26 by Kevin Feasel

Patrick Gruenauer makes a remote connection:

With Powershell 7, we can enable and use SSH Remoting. Unlike WinRM, SSH is more popular because it is a more familiar technology and is also available on other platforms. In this post I will show you how to easily enable SSH on PowerShell 7 with a few lines of code.

Click through to see what you need to install on the machine and how to configure everything.

Comments closed

Find When a Table was Dropped

Published 2022-10-26 by Kevin Feasel

Andrea Allred does some sleuthing:

Say you have a user come to you and they dropped a table sometime yesterday, but they don’t remember when and now they need it back. You could start the restore process and roll through logs until you see the drop and then restore to the hour before or you could run this super cool query to get the time the table was dropped.

Click through to see when and even which user did it—assuming you don’t have everybody going through a connection pooled account.

Comments closed

An Analysis of Resumable Online Index Operations

Published 2022-10-20 by Kevin Feasel

Chris Taylor sums it up:

This is more of a heads up for me / reminder regarding some of the caveats to using ONLINE / RESUMABLE index operations with SQL Server.

Don’t get me wrong, there’s a ton of advantages to using ONLINE and RESUMABLE which I will highlight below but here is the link to the Microsoft Learn page if you want more in depth information

Read on for the pros and cons. I do like the idea, though I personally haven’t used the feature.

Comments closed

Database Lineage with Extended Properties

Published 2022-10-19 by Kevin Feasel

Garry Bargsley shows a use for extended properties on databases:

Databases listed with 1, 2, 3, 4? What do those databases represent? Where did those databases come from?

Would you believe me if I told you there was a way to identify where the databases were sourced from?

A setting that can be used in SQL Server would allow the person who built these databases or restored them from another system to add a note.

Extended properties are very useful but also very easy to forget and difficult to manage. I’ve seen a few vendor products make great use of them but in-house development tends to ignore them.

Comments closed

Creating a SQL Server Assessment Dashboard

Published 2022-10-18 by Kevin Feasel

Robert Blackburn builds a dashboard:

We must periodically evaluate the state of our databases. Luckily for SQL Server, Microsoft provides us with a customizable assessment through their SQL Assessment API Repo and API Documentation. You can change the rules per database and output the results to a database to track history.

However, that will take more than an hour. Let’s create a dashboard with the default rules in under an hour. We will use Azure Data Studio (ADS) and Power BI Desktop (PBI). If you are not familiar with them, both are free. Azure Data Studio is automatically installed with SSMS 18.7 and higher. You can also install them individually.

Read on to see how this works. Granted, it will not auto-update but unless the assessment output format changes between runs, at least you wouldn’t need to modify Power BI and could just refresh the data.

Comments closed

Auto Partitioning Recommendations for Oracle

Published 2022-10-18 by Kevin Feasel

Brendan Tierney checks out some recommendations:

In a previous blog post I gave an overview of the DBMS_AUTO_PARTITION package in Oracle Autonomous Database. This looked at how you can get started and to setup Auto Partitioning and to allow it to automatically implement partitioning.

This might not be something the DBAs will want to happen for lots of different reasons. An alternative is to use DBMS_AUTO_PARTITION to make recommendations for tables where partitioning will have a performance improvement. The DBA can inspect these recommendations and decide which of these to implement.

Read on to see how you can run the recommender, as well as what a recommendation looks like.

Comments closed