Press "Enter" to skip to content

Category: Administration

Finding SQL Agent Jobs with Invalid Job Owners

Published 2022-03-22 by Kevin Feasel

Chad Callihan is trying to clean up this town:

Do you know which account is the owner for your SQL Server Agent jobs? Some jobs might be owned by user accounts which can become a problem. What happens if that job owner isn’t around forever and goes away? Will that job just keep chugging along?

Without an existing owner, a SQL Server Agent job will not run. Once a user gets disabled or removed from Active Directory, a job is still going to try running under that user but will begin failing.

Click through to see what kind of errors you might find.

Comments closed

Managed Instance Link in Preview

Published 2022-03-18 by Kevin Feasel

Dani Ljepava announces support for Managed Instance link is now in public preview:

As of today, we are pleased to announce that the link feature for Managed Instance is available in the open public preview, in all Azure regions worldwide. It can be used with existing, or new managed instances, and SQL Server 2019 Enterprise, or Developer edition, including SQL Server 2022 CTP (available through EAP). We have also released the tooling support for the link in the form of automated wizards available in SQL Server Management Studio, starting from SSMS v18.11.1.

With the link, replicated databases from SQL Server on Managed Instance are usable as R/O secondary replicas. While the link is in operation, transactions commited on SQL Server (primary) are instantaneously committed to Managed Instance (secondary). This provides an exact replica of your SQL Server database on Managed Instance, synced near real-time. The link was built to be resilient, in case of the network being down, SQL Server being rebooted, or maintained, or in case of some other issue, the link will automatically resume replicating where it has left off when the issue has been resolved.

Support for 2019 is a shrewd idea, given the SQL Server version adoption curve for companies. This isn’t going to replace having a proper availability group for high availability or even (most) disaster recovery options, though, because the link is currently one-way—though Dani does mention eventual support for bi-directional operation with SQL Server 2022.

Comments closed

Creating a Trust between On-Prem AD and AWS Directory Service

Published 2022-03-16 by Kevin Feasel

Tom Collins makes a connection:

Most SQL Servers use a large portion of the authentication as Windows Authentication – utilising Kerberos and NTLM protocols via Active Directory. So when it comes to considering moving on-prem SQL Server resources to Cloud Providers – Active Directory is a foundational question.    There are other methods than Microsoft Directory – which I’ll discuss in future posts.

Utilising AWS RDS SQL Server with Windows Authentication methods is only possible using the AWS Directory Service.  i.e The AWS RDS SQL Server is created and added as a resource to the AWS Directory Service . If on-prem users require access to the AWS RDS SQL Server via Kerberos , a forest trust is required between the AWS Directory Service and the on-prem AD. 

For this post – the focus is on an existing on-premises SQL Server inventory using Microsoft Active Directory Services.

Read on to see what you’d need to do to implement this.

Comments closed

Dropping Offline Databases in SQL Server

Published 2022-03-16 by Kevin Feasel

Chad Callihan has a couple notes about dropping offline databases:

“Oops” is not a word you want to utter often as a database administrator. Sure, accidents happen (who hasn’t missed that WHERE clause when deleting data) but there are steps you can take to minimize problems that will ruin your day, week, etc. Let’s look at a few ways to delete databases that supposedly are no longer needed.

“I’m feeling lucky” isn’t something most DBAs choose.

Comments closed

Finding Zero-Record Tables Taking Space in SQL Server

Published 2022-03-14 by Kevin Feasel

Jeff Iannucci has a follow-up for us:

This is a follow up post to the previous one about tables with no records using data space, with the idea coming from twitter of all places. Sometimes on twitter the conversations kill, but after reading that post my friend Chris Wood suggested writing something to FIND those tables.

Which is a solid idea, so let’s get to it. No time to wait too long.

Click through for the process.

Comments closed

SQLBits Keynote Notes

Published 2022-03-10 by Kevin Feasel

Brent Ozar shares some thoughts from the first day’s keynote from SQLBits:

Pedro Lopes took the stage to talk about parameter-sensitive plan optimization, aka PSP Optimization. He demoed it with SQL Server 2022 CTP 1.3. I’ve written about this feature before, and there wasn’t anything new here in the demos. My opinion on this feature remains the same: I think it sounds like a phenomenal down payment. It won’t fix parameter sniffing, but I don’t think it’s going to backfire.

Read on for Brent’s thoughts around what Microsoft is doing for SQL Server 2022.

Comments closed

Writing Extended Events to InfluxDB

Published 2022-03-07 by Kevin Feasel

Gianluca Sartori’s speaking my language:

The TIG software stack (Telegraf, InfluxDB, Grafana) is a very powerful combination of software tools that can help you collect, store and analyze data that has a time attribute. In particular, InfluxDB is a time series database, built with sharding, partitioning and retention policies in mind. It is absolutely fantastic for storing telemetry data, like performance counters from SQL Server or other software products.

In order to store data in InfluxDB, you can use Telegraf, a data collection agent that takes care of extracting telemetry data from the object to observe and upload it to the InfluxDB database. Telegraf is built with the concept of plugins: each object to observe has its own plugin and it’s not surprising at all to find a specialized plugin for SQL Server.

Click through for more details and how to set it up.

Comments closed

Building a SQL Server Inventory via Powershell

Published 2022-03-07 by Kevin Feasel

Lee Markum wants to figure out where all the servers are:

You’re a data professional and you’ve been given the keys to a new SQL Server environment. You know you need to build a SQL Server inventory so you know what is in your environment, but how do you get that information?

One of the things I have talked about in other posts is how to create a SQL Server inventory. I’ve discussed using the MAP Toolkit and building your own inventory database using T-SQL. Today, we’ll see another way to generate a list of SQL Servers in your environment.

Click through for that method. In the past, I’ve used nmap (with permission, of course) to figure out all the SQL Server instances in my environment. Fun times.

Comments closed