Using SQL Agent Jobs to Troubleshoot SQL Managed Instances

- 7 minutes read - 1322 words

SQL Server Managed Instance Diagnostics

The great thing about SQL Server Managed Instance (SQL MI) is that you don’t need to perform any maintenance or troubleshooting on the OS or SQL installation. In theory. If you’ve used SQL MI, you might find that this isn’t always the case. If you have issues, you’ll find that you have limited troubleshooting options on the server and your options for running scripts are constrained. Microsoft support doesn’t have unfettered access either. They have hard boundaries on what can be accessed on your servers and they need to escalate quite extensively to other internal teams when examining certain problems.

Some items, like SQL Agent Job issues, can be investigated via routine queries of msdb tables. The queries might need to be modified slightly, but nothing special is needed. Other problems, such as connectivity issues, need a different approach. You can’t login to the server via RDP or manage the operating system directly, which limits troubleshooting. I’ll go over a few methods that work. You have to be a little creative to perform some troubleshooting, but you can do more than you might realize at first.

The next section lists standard troubleshooting options and tactics in SQL MI. Feel free to skip ahead to the SQL MI extended troubleshooting options and use cases - this stuff is a summary of a few available options.

SQL MI Standard Troubleshooting Options

I use the following techniques to troubleshoot all flavors of SQL on a regular basis. Some are used daily and some are for specific scenarios, but they are all useful and nothing special is needed to use these (other than proper authorization).

  1. Error logs
    You likely won’t see much in the standard error logs, but it is worth checking.
  2. SQL Agent Logs
    SQL Agent logs are invaluable when troubleshooting. Of course, their value is limited to items run via SQL Agent.
  3. Azure Portal
    The Azure Portal has the standard metrics available for troubleshooting SQL. It can show you utilization numbers, you can create alerts, add diagnostics and check query history. This is all useful and you’ll want to keep an eye on items in the “Monitoring” tab.
  4. Extended Events
    Extended events (XEvents) provide a nice window into SQL MI. They provide a window into all versions of SQL, so it’s a toolset you should become comfortable using if you troubleshoot SQL. I was a reluctant convert to XEvents. It is different from Trace / Profiler, but now it is an indispensable part of my tools for examining SQL. You can still use Trace with on-prem servers (for now), but it is not allowed on Azure. XEvents are crucial for troubleshooting and monitoring SQL MI at a deeper level.
  5. System views and functions
    SQL Server dynamic managed views and functions replace direct access to system tables. They not only replace the system tables, they usually enhance the older functionality and provide additional functionality. They cover a wide range of topics, including operating system information, I/O information, and performance information.
  6. SQL Server Audits
    SQL Server and Database Audits allow tracking and recording events on SQL. Although it is primarily for tracking events, it can be used for some troubleshooting, especially if you are looking at modified objects.

SQL MI Extended Troubleshooting Options

As mentioned in the introduction, you can’t RDP into an MI and you can’t directly manage the OS, so that leaves few options to gather networking and OS level information. That leaves SQL Agent jobs as the next logical method to get detailed diagnostics.

Executing Command Scripts (DOS commands)

Simple troubleshooting on a machine often starts with the command prompt. Regular command line scripts can also be executed in SQL MI via SQL Agent Jobs. I’m sure there are some limitations, but anything that I needed via a CmdExec task was supported. This allows some basic networking troubleshooting (e.g., nslookup for DNS resolution).

Executing PowerShell Scripts

When more complex tasks are needed, Powershell scripts can be executed via SQL Agent jobs. Depending on your network and firewall settings, you may be limited to the default modules. But the ability to run PowerShell is very powerful and allows for advanced troubleshooting. You can test additional network settings, send email, confirm certificates and registry settings. I’m sure there are some things that can’t be done in PowerShell in SQL MI, but you should be able to gather enough information to get your support ticket moving.

Multiple tests can be combined into a single job to simplify and expedite troubleshooting. The next example shows network connectivity tests for multiple servers, combined into a single script.

Test-NetConnection -ComputerName www.microsoft.com -Port 80  
Test-NetConnection -ComputerName www.microsoft.com -Port 443

Results are shown in the history for the job. The following is a cleaned-up version of the output for the above script.

Date		8/14/2026 9:13:58 PM
Log		Job History (Test Net Connection)

Step ID		1
Server		xxxxxxxx
Job Name		Test Net Connection
Step Name		Test Net Connection
Duration		00:00:51
Sql Severity	0
Sql Message ID	0
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
Executed as user: xxxxxxxxxxxxxxxxxx 
ComputerName     : www.microsoft.com  
RemoteAddress    : 23.14.142.89  
RemotePort       : 80  
InterfaceAlias   : xxxxxxxxxxxxxxxxxx  
SourceAddress    : xx.xx.xx.xx  
TcpTestSucceeded : True    
ComputerName     : www.microsoft.com  
RemoteAddress    : 23.14.142.89  
RemotePort       : 443  
InterfaceAlias   : xxxxxxxxxxxxxxxxxx  
SourceAddress    : xx.xx.xx.xx 
TcpTestSucceeded : True.  
Process Exit Code 0.  The step succeeded.

Use Cases

This isn’t something that you’ll be doing on a regular basis. Hopefully, you’ll never have to do this. It’s a troubleshooting technique for a small range of problems. I’ve used it in conjunction with Microsoft support to troubleshoot a TLS certificate issue. The only way to determine the root cause was to extract information from the SQL MI. We actually had the issue on all of our non-production instances, which made tracking down the issue interesting.

Remember - almost all connections with a SQL Server (any flavor) will be initiated from another machine, so you likely only need to perform this type of troubleshooting when SQL MI is the client instead of the server. SQL Mail is an obvious, common, and best practice example of SQL as a client. SQL Agent jobs are very flexible. API calls and outgoing connections are possible via jobs and stored procedures. I’ll be nice and just say they aren’t a pattern I recommend.

Keeping the outgoing context in mind, the following is a non-exhaustive list of items you might need to check from your SQL MI.

  • Network connectivity, including firewall and NSG rules
    • If you can hit the API or server, the firewall is probably not the issue
  • DNS resolution / NSLookup
  • SSL / TLS certificate validation
  • Sending email
  • Registry key verification

Additional Tactics

Before you open a support ticket, you will want to be sure the problem you’re having is actually on the SQL MI. There are a few additional things you can check before you open a ticket.

  • Verify functionality works from other servers in Azure if possible
    • If this is a new environment or server you are hitting, this can help ensure everything is setup correctly
  • External vendor support
    • External vendors should have access to additional logs on their servers
    • Vendors can also assist validating your configuration
  • Run commands from a VM on the same VNET with the same network access / NSG configuration as your SQL MI
    • Allows creation of network traces
    • Allows full PowerShell scripts, etc.

Summary

SQL MI is normally pain-free for network, OS, and SQL maintenance. If you run into a situation that looks like an error on the SQL MI, you will need to open a support ticket. PowerShell and command scripts can be run via SQL Agent jobs to speed up troubleshooting and provide necessary information to the support team. Don’t try to change settings on the server with these methods - only use this to gather information.

References

SQL Server Agent
SQL Server vs SQL Managed Instance Differences
PowerShell Test-NetConnection
CmdExec nslookup

comments powered by Disqus