SQL DBA Blog

Category: Windows

Windows Administration Blog Archives, by Peter Whyte (Database Administrator).
Includes: Database Admin, Windows Admin, PowerShell, WSL & more…

How to Rename a Computer that Hosts SQL Server
When changing the hostname of a computer that is running SQL Server, we have to update system metadata so that remote connections and applications do not lose connectivity. The hostname system metadata is stored in sys.servers and reported by @@SERVERNAME.

This post is a guide on how to update MS SQL metadata after a hostname change. This is often needed soon after a fresh SQL Server installation – you’ve installed SQL but then realise you need to change the Windows Server Computer Name.

Considerations when changing a SQL Server hostname include:
# Failover Clusters, the renaming process differs.
# If Replication is configured on the host, I’d question why you are doing this.
# If Mirroring, turn off Mirroring pre-change and then re-establish with new computer name. This could be high risk.
# If the host is running SSRS, a configuration change may be required.
# Hardcoded hostnames for all scripts/apps will have to be updated.

This is all as described on Microsoft’s Documentation page. As always, give MS Docs a proper read and test your changes before performing in a Production environment. Hopefully, this is a new install and a single instance host you’re renaming.

The demo below includes:
# Change Windows Computer Name
# Update MSSQL with the new Hostname
# Verify Change

Change Windows Computer Name

To change the hostname of a Windows computer, we can do it via GUI or command (PowerShell).

The PowerShell cmdlet for this is Rename-Computer, ensure you are running PowerShell as Administrator:

A reboot is required post-change as per the warning on the terminal.

See MS Docs as linked above for parameters required if you are connected to a Domain, or for an example of renaming remote hosts.

Update MSSQL with the new Hostname

Now that the computer has been renamed, when we try login into SQL Server via SSMS using the old hostname, the following error will appear:

We need to change the server name to ‘localhost‘ or the new server name for us to get connected.

Next, I’m going to run a new query to get the currently registered hostname and actual hostname.
```
-- Get currently registered hostname 
SELECT @@SERVERNAME AS 'Old Server Name' 

-- Get actual hostname (undocumented proc)
EXEC xp_getnetname
```
Next, drop the currently registered name (sp_dropserver) and then register the new hostname (sp_addserver).
```
-- Remove server from list of known remote and linked servers on the local instance
EXEC sp_dropserver 'pw-pc-pah';  
GO

-- Define name of local instance after computer rename
EXEC sp_addserver 'WIN-PW-01', local;  
GO
```
For the change to be applied, we need to restart the SQL Server service.

Verify Change in SQL Server

Once the SQL Server Engine has been restarted, run the same command as before to return the currently registered hostname. It should display the new computer name:

Now you can verify any other apps or MSSQL features as per your test plan. Any existing connection strings will need to be updated with the new hostname if using it.

I hope this all worked out for you if you followed along.
August 23, 2022
How to Find Which Process is Listening on a Port in Windows

This post is a guide on how to check what service is running for a TCP or UDP port on a Windows host computer.

You might need this information to verify what port your service is running on, or you are proactively reviewing your dodgy open ports!

In this post, I’m demoing 2 ways of getting the connection & listening port information in Windows –
# Show Listening Ports in Resource Monitor (easy GUI option)
# TCPView, Sysinternals Application (quick & small download)

Show Listening Ports in Resource Monitor

We can view see detailed listings of all TCP and UDP endpoints on our machine using Resource Monitor.

Open Resource Monitor (resmon.exe), or open TaskManager → Performance tab –

In Resource Monitor, open the Network tab and expand Listening Ports –

TCPView, Sysinternals Application

TCPView is a Sysinternals tool that can be downloaded within seconds. TCPView shows all TCP and UDP endpoints on your system, and it’ll give you a better view of what you are looking for.

This might be the best option if you’re going to be spending some time reviewing this information as it has filtering and search functionality.

TCPView ->

This is an example of how it looks –

August 21, 2022
How to Check Windows Firewall Status with PowerShell
This post will help you to check your local Windows Firewall status using PowerShell (whether it’s off or on for each Profile).

PowerShell commands follow standards and use verb-noun pairs for cmdlets. The verb at the start of the command describes the action the cmdlet performs, and the noun part is the action being performed. Here’s a list of Common Verbs, another Microsoft Docs link. The place of truth as I call it.

In this demo, we’re running Get-NetFirewallProfile with Format-Table
We’re getting the Firewall Profile status, and also formatting it into a table after a pipe ‘|’ –
```
# get local firewall status powershell
Get-NetFirewallProfile | Format-Table Name, Enabled
```
The Windows Firewall on this machine is enabled, for all Profiles <- have a look at this MS Docs link to know more about Windows Firewall Profiles.

Internal follow-up links:
# How To Disable Windows Firewall With PowerShell
# How To Enable Windows Firewall With PowerShell
August 16, 2022
How to Enable Windows Firewall with PowerShell
This post is contains a demo on how to enable Windows Firewall with PowerShell in Windows Server.

This might be needed if you have discovered Windows Firewall is disabled on your or multiple computers, or you might want to re-enable it after disabling this for a test (see my other post on disabling instead of enabling).

Enabling Windows Firewall with PowerShell can be done by going through the following steps:

# Understand Windows Firewall Profiles
# Get-NetFirewallProfile PowerShell
# Set-NetFirewallProfile PowerShell

Understand Windows Firewall Profiles

Before running any command or script on your machine, it’s important for us to understand everything that it’s doing. In the command below we are disabling all 3 profiles:

# Domain – For when you are connected to a Domain Controller (computer connected to AD).
# Private – For your home or private networks.
# Public – For public WIFI locations such as coffee shops etc.

For more information on this, see this link – Microsoft Docs: Windows Firewall Profiles

Also remember, all of this can be viewed and changed via GUI to help with understandings >

Get-NetFirewallProfile PowerShell

PowerShell commands follow standards and use verb-noun pairs for cmdlets. The verb at the start of the command describes the action the cmdlet performs, and the noun part is the action being performed. Here’s a list of Common Verbs, another Microsoft Docs link. The place of truth as I call it.

In this demo, we’re running Get-NetFirewallProfile with Format-Table
We’re getting the Firewall Profile status, and also formatting it into a table after a pipe ‘|’ –
```
# get local firewall status
Get-NetFirewallProfile | Format-Table Name, Enabled
```
In the example above, the Windows Firewall is disabled, showing as ‘False‘.

Set-NetFirewallProfile PowerShell

We’re switching the cmdlet we ran above from Get to Set here (following on from verb-noun cmdlet descriptions).

To be able to run this; we have to open PowerShell or Windows Terminal as Administrator.

Set-NetFirewallProfile is being executed below, which enables all Profiles of the Local Windows Firewall –
```
# disable local firewall
Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled True

# get local firewall status
Get-NetFirewallProfile | Format-Table Name, Enabled
```
I followed the change by another run of Get-NetFirewallProfile to verify the change.
August 15, 2022
How to Disable Windows Firewall with PowerShell
This post contains a demo on how to disable Windows Firewall with PowerShell on a Windows computer.

Disabling the Local Firewall on Windows is not a recommended move, and it will ultimately make your computer less secure. Sometimes it’s necessary though, for example, during troubleshooting a difficult issue you might want to disable it for a quick test to rule the FW out as being the root cause. But in this example, you will have already tried to ensure the Firewall Rule exists before turning it off completely.

Anyway, disabling Windows Firewall with PowerShell can be done by going through the following steps:

# Understanding Firewall Profiles in Windows
# Get Status of Windows Firewall with PowerShell
# Disable Windows Firewall with PowerShell

Understanding Firewall Profiles in Windows

Before running any command or script on your machine, it’s important for us to understand everything that it’s doing. In the command below we are disabling all 3 profiles:

# Domain – For when you are connected to a Domain Controller (computer connected to AD).
# Private – For your home or private networks.
# Public – For public WIFI locations such as coffee shops etc.

For more information on this, see this link – Microsoft Docs: Windows Firewall Profiles

Also remember, all of this can be viewed and changed via GUI to help with understandings >

Get Status of Windows Firewall with PowerShell

PowerShell commands follow standards and use verb-noun pairs for cmdlets. The verb at the start of the command describes the action the cmdlet performs, and the noun part is the action being performed. Here’s a list of Common Verbs, another Microsoft Docs link. The place of truth as I call it.

In this demo, we’re running Get-NetFirewallProfile with Format-Table
We’re getting the Firewall Profile status, and also formatting it into a table after a pipe ‘|’ –
```
# get local firewall status
Get-NetFirewallProfile | Format-Table Name, Enabled
```
Disable Windows Firewall with PowerShell

We’re switching the cmdlet we ran above from Get to Set here (following on from verb-noun cmdlet descriptions).

To be able to run this; we have to open PowerShell or Windows Terminal as Administrator.

Set-NetFirewallProfile is being executed below, which disables all Profiles of the Local Windows Firewall –
```
# disable local firewall ps1
Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled False

# get local firewall status ps1
Get-NetFirewallProfile | Format-Table Name, Enabled
```
I followed the change by another run of Get-NetFirewallProfile to verify the change.

If this is a temporary change for you, to re-enable the Windows Firewall, amend the ‘-Enabled False‘ statement to ‘-Enabled True‘.
August 14, 2022
Best Terminal for Windows 2022

This is a post to share an opinion on the best Terminal for Windows 2022. The answer to all our needs in the year 2022 is Windows Terminal.

I have a blog post on How to Install Windows Terminal, and here is a link to the main Windows Terminal GitHub repo. You’ll find more up-to-date Windows Terminal information by following the GitHub link. This guide is a personal post to share a random thought.

That’s it. Done. Nothing much else to know about CLI Terminals in Windows. No need to be looking at other Windows Terminals in 2022, especially if you’re learning the ropes.

I’ll be honest, my reason for posting this one is to see how it performs against other websites for the term “Best Terminal for Windows”. It’s crazy, every post has way too much information – here are a billion different types of terminals in Windows. Download and try them all, even if you’re a novice.

“best terminal for windows” – Google Search, August 2022

Every one of them is over the top, all done for the Google SEO Ranking points that you get for writing 10,000 words in a post for a target keyword.

But… if you’re up for exploring random text editors that some folk think makes a good Windows Terminal, then go ahead.

Best Terminal for Windows

The best Terminal in Windows to use is Windows Terminal, as mentioned at the top of this post.

This is the best terminal to get you started if you’re committed to using the Windows OS. Anything else is exploratory. The best way to approach software in my opinion is, to choose the default vendor first. In this case, and in most of my work it’s Microsoft. So go try out the software that Microsoft is developing first if you are on a Windows OS, and if it’s not working to your needs, try to find an alternative.

In Windows Terminal, we can have multiple tabs for whichever terminal session need. One of the best examples is WSL –

This is a very different post from what I usually do. But it’s good to share an honest straight-to-post thought. I intentionally don’t give much personality away on this blog. It’s a place for posting my technical notes, with the hope that it provides every viewer with what they were searching for Online.

At least this top Terminal for Windows list comes from someone in the software/database engineering game. I use Windows Terminal every day and love it. Also, this post will likely & intentionally get lost in the nether. As ever, see how goes.

That’s me for now, over. This is the post you need to truly understand what the Best Terminal for Windows is, I’m sure you’ll agree.

August 13, 2022
How to Reboot WSL (Windows Subsystem for Linux)
To reboot WSL, run wsl --shutdown to stop WSL distros then enter back into WSL as you normally would to start it back up again.

This post is a guide to help explain the process of how to reboot a Windows Subsystem for Linux (WSL) distribution on your local machine.

A WSL reboot might be needed if your WSL instance or app within Linux has thrown an error message, or you are changing the .wslconfig file/memory limits as described in MS Docs – Advanced settings configuration in WSL

In my demo below, I’m showing 2 methods for restarting installed WSL Linux distributions. The first option, running WSL commands is the most up-to-date method for restarting WSL distros:
# Restart with WSL –Shutdown Command (Preferred approach)
# Restart LxssManager Service via PowerShell

Restart WSL with WSL –Shutdown

We need to run a basic WSL command to shut down the Linux distro. First, I will list installed WSL distributions followed by the wsl --shutdown command. This will stop WSL distros running which we can verify with another list command run.
```
# check wsl distros & status
wsl -l -v

# shutdown the running wsl host
wsl --shutdown

# check wsl distros & status
wsl -l -v
```
The shut-down command above immediately terminates all running distributions. When we list our WSL distributions again for the second time, we see they are all ‘Stopped’.

To start up your WSL distribution, enter WSL as you would normally. That’ll be done by re-opening the Linux tab in Windows Terminal, or by running wsl to enter a new WSL session (as per your set default WSL distribution).

Restart LxssManager Service Using PowerShell

We need to Run PowerShell as Administrator (or Windows Terminal as Admin) for this.

From what I believe, the wsl --shutdown command was released later, and this LxssManager service restart is the old method. Either way, you’re probably getting what you need in the end, a disruptive stop of your WSL distros.

Run the following command in PowerShell as Admin to restart your local LxssManager service:-
```
# restart LxssManager service
Get-Service LxssManager | Restart-Service
```
The LsxxManager service restart has stopped the Ubuntu-20.04 distro I opened above in this post.

Other notes: We are unable to reboot from within WSL due to the lack of systemd. There are some open-source projects in the works to enable systemd or even add a GUI to manage local WSL distributions. I hope to explore it at some point in the future.

That’s all for this one. Feel free to check out my WSL Tagged blog posts for more random tips on administering WSL, or have a look at the following posts to help get more familiar with WSL:
# How to Install Windows Subsystem for Linux (WSL)
# How to Upgrade WSL from Version 1 to Version 2
# How to Change Default Linux Distro in WSL
# How to Check WSL Versions
August 12, 2022
How to Restart WSL (Windows Subsystem for Linux)
This is a guide on how to restart a Windows Subsystem for Linux (WSL) distribution on your local machine.

This might be needed if your WSL instance or app within has thrown an error message, or you are changing the .wslconfig file/memory limits as described in MS Docs.

In my demo below, I’m showing 2 methods for restarting WSL distributions –

# (Preferred approach) WSL –Shutdown
# Restart LxssManager Service via PowerShell

Restart WSL with WSL –Shutdown

We need to run a basic wsl command to shut down the distro. First, I’m listing my installed WSL distributions followed by the ‘wsl –shutdown‘ command.
```
# check wsl distros & status
wsl -l -v

# shutdown the running wsl host
wsl --shutdown

# check wsl distros & status
wsl -l -v
```
The shut-down command above immediately terminates all running distributions. When we list our WSL distributions again for the second time, we see they are all ‘Stopped’.

To start up your WSL distribution, enter WSL as you would normally. This’ll be done by re-opening Linux tab in Windows Terminal, or by running ‘wsl‘ to enter a new WSL session (as per your set default distribution).

Restart LxssManager Service Using PowerShell

We need to Run PowerShell as Administrator (or Windows Terminal) for this.

From what I believe, the wsl –shutdown command was released later, and this LxssManager service restart is the old method. Either way, you’re probably getting what you need in the end, a disruptive stop of your WSL distros.

Run the following command in PowerShell as Admin to restart your local LxssManager service –
```
# restart LxssManager service
Get-Service LxssManager | Restart-Service
```
The LsxxManager service restart has stopped the Ubuntu-20.04 distro I opened above in this post.

Other notes: We are unable to restart from within WSL due to the lack of systemd. There are some open-source projects in the works to enable systemd or even add a GUI to manage local WSL distributions. I hope to explore it at some point in the future.

That’s all for now – have a look at the other WSL Tagged posts here if of interest!
August 4, 2022
How to Upgrade WSL from Version 1 to Version 2
This is a post on how to upgrade to WSL 2. The demo below will explain the process of upgrading an installed Windows Subsystem for Linux distribution from running WSL version 1 to WSL version 2.

WSL has two versions, the latest (WSL 2), and the original version (WSL 1). You can have many different Linux Distributions installed on your machine, and you can easily change the WSL version of one of them.

My other post, How to Check Installed WSL Versions, will help explain the key and why you might want to perform a WSL upgrade for particular development needs. If you are not going to be using advanced WSL features, then upgrading to WSL 2 is likely a good option for you.

This is a guide to help upgrade WSL version 2 to WSL version 1. If you are looking to go the other way and downgrade from WSL v2 to WSL v1, see my other post -> How to Downgrade from WSL Version 2 to WSL Version 1

How to Upgrade from WSL V1 to V2

To upgrade a WSL distribution from WSL version 1 to version 2, run wsl <distro-name> --set-version 2. You can get the Linux distribution by running wsl -l- v.

The WSL commands required for this are shared in the example below:
```
# check wsl distro versions
wsl -l -v

# change wsl distro using name above to desired version
wsl --set-version Debian 2
```
The screenshot above shows us listing the installed WSL distros and setting the WSL version for the Debian instance from WSL 1 to WSL2.

The WSL Linux distro will be updated immediately to WSL v2.

To downgrade back to version 1, it’s as simple as changing the number from a 2 to a 1 in the command. As mentioned above in this guide, the following posts may also help provide more info on this:
– How to Check Installed WSL Versions
– How to Downgrade from WSL Version 2 to WSL Version 1
August 2, 2022
How to Downgrade from WSL Version 2 to WSL Version 1
This is a post on how to downgrade from WSL version 2 to WSL 1. The demo below will explain the process of downgrading an installed Windows Subsystem for Linux (WSL) distribution, from WSL2 to WSL1.

WSL has two versions, the latest WSL v2, and the original version WSL v1. You can have many different Linux distributions installed on your machine, and it’s easy to change the WSL version for one of them.

My other post, How to Check Installed WSL Versions, will help explain the key and why you might want to perform a WSL upgrade for particular development needs. If you are not going to be using advanced WSL features, then having WSL running on version 2 will be the preferred option.

If you wish to upgrade WSL from version 1 to version 2, instead of downgrading as we are doing in this post, then see my other post – How to Upgrade WSL from Version 1 to Version 2

How to Downgrade from WSL 2 to WSL 1

To downgrade a Windows Subsystem for Linux distribution from WSL 2 to WSL 1, run wsl <distro-name> --set-version 2. You can get the Linux distribution by running wsl -l- v.

The first command below will check the version of WSL and the following command will set and update the new WSL version.
```
# check wsl distro versions
wsl -l -v

# change wsl distro using name above to desired version
wsl --set-version Debian 1
```
The Debian instance as marked in the screenshot above has now been downgraded from WSL2 to WSL1.

To upgrade back to version 2, it’s as simple as changing the number from a 1 to a 2 in the command.

For more WSL Tips, check out my WSL Windows page which has a list of all my WSL blog posts.
August 2, 2022