Investigating SQL Server issues locally on Windows Server 2019 core

Reading Time: 4 minutes

In this post I want to discuss investigating SQL Server issues locally on Windows Server 2019 core. Since I want to discuss something different from previous posts like my T-SQL Tuesday contribution.

Main reason I am doing this post is to encourage you to use your imagination and to experiment. Because I honestly believe doing this will allow yourself and the industry to develop further.

SQL Server Scenario

Imagine, you’ve been told there’s a SQL Server instance that has been deployed and is now having problems. After being told this you try to connect to it remotely, only to be unable to connect.

So, you connect using a remote desktop onto the server only to realise it’s a Windows Server core install. What do you do under those circumstances?

Do you stay connected to the server and try and fix it, or do you look to redeploy another new one and hope the issue doesn’t reoccur? In this scenario we will examine what you could do if you stayed connected to look further.

Using Notepad locally whilst investigating SQL Server

Now for those of you who have yet to discover this, Notepad works in Windows Server core. Basically, Explorer doesn’t work but Notepad does. So, you can use it as a lazy replacement.

For example, say it’s late at night and you’ve forgotten the Powershell syntax to search for a folder location, you could open Notepad. Once it is running, click the file ‘File’ menu and then ‘Open’. Afterwards you can then navigate around to find folder locations.

And it doesn’t stop there either. Copy and paste still works if you Remote Desktop onto a server with Windows Server core installed.

Another key point is that you can paste directly into whatever folder is open within the Open window in Notepad. You just paste the content directly into the ‘Open’ window.

This is very useful if you want to copy over updates to a stand-alone SQL Server that you are testing.

Instead of taking the time to copy the files onto another location, and then copy them over and running them from there you have an alternative.

Now another quick thing you can do in Notepad is create new files of various types. This includes UDL files which I will discuss next.

UDL Files to test SQL Server locally

It would appear you can either copy or create UDL files onto your core server and open them. Doing this on a server with SQL Server installed allows you to test if you can connect to it locally. If you can connect then the odds are the problem is a network issue. For instance, a firewall setting.

For example, I created a file in Notepad and saved it as ‘Test.udl’. I then opened this file straight from the command line to open the ‘Data Link Properties’ window.

Previously, I’ve used UDL files a lot to troubleshoot issues. You can find out more about them in detail in this old post here.

Check SQL Server using Registry Editor (Regedit)

For those of you who are unaware regedit also works in Windows Server core. One way you can use this to investigate issues is to open regedit and check if TCP/IP is enabled for the SQL Server instance.

SQL Server management applications

One thing I discovered about SSMS is that you can install it on core edition, but you can’t seem to open it.

However, I downloaded the Visual Studio code zip file and extracted it for use on the server. It opens up for use in Windows Server core, but it closes if you try and do anything that involves file manipulation.

Azure Data Studio had a very similar result. It’s great if you just want to use it to connect to a SQL instance and run queries in Windows Server core, but if you try and do any file related tasks it will crash.

Using Powershell and DOS commands to investigate

It goes without saying that Powershell and DOS commands work on Windows Server core. I actively encourage you to use either of these to investigate issues where possible.

It will come in handy if you have to do things like this for for many servers. In fact, I actively encourage you to learn Powershell in depth to do a lot of advanced tasks.

Aim of this SQL Server scenario

My example for investigating SQL Server issues locally on Windows Server 2019 core was based for one specific scenario.

It feels both surreal and amusing to do a post that actively promotes the use of Notepad to investigate SQL Server issues, but here it is.

Final word about investigating SQL Server issues locally

I hope you realise the true aim of this post is to stir up your imagination and to encourage you to try new things.

Imagine the possibilities if you were to do similar experiments with a cloud solution or SQL Server on Linux. Feel free to let me know if it’s helped spark your imagination or if you have tried similar experiments.