Category Archives: PowerShell

Find forks for a GitHub repository

In a recent episode of Brent Ozar’s Office Hours, someone asked if there are any interesting forks of the First Responder Kit, on GitHub.

So I thought it would be useful to have a way to quickly check for forks and see when they were updated.
I came up with the following PowerShell function which does exactly that.

I hope someone will find it useful.
Enjoy!

Easily attach StackOverflow database

Brent Ozar maintains a Microsoft SQL Server version of the public Stack Overflow data export.

He provides detailed explanation on how to download, extract and use it.

You need to download the desired versions and put them on your network.

For easy deployment of the StackOverflow database, I’ve put together a PowerShell function that takes care of downloading the files from your domain (if needed), extracting the archives and mounting the files on an existing instance.

The function uses the dbatools and 7Zip4PowerShell PowerShell modules. If they are not present on the host, they will be installed from the official PSGallery.

This will extract the StackOverflow2010.7z archive to D:\Temp, then it will get the default path for Data and Log from the ‘server\instance’, it will move the data and the log files to the appropriate location and then mount them.

The function also offers the possibility to specify that the data and log files should be placed in custom locations.

The full version of the script is available on my GitHub account.

https://github.com/cviorel/PowerShell/blob/main/Get-StackOverflow.ps1

Hope this comes in handy!

Enjoy!

SQL Server Management Studio – automatically get the latest version

When working with SQL Server, I prefer to have a jump host with my tools already installed.

You may also have them on your main laptop/workstation, but I would also recommend to have a VM in the cloud or in your datacenter.

Everyone has a different list of tools they use, but most of the time you’ll find these apps on those lists:

  • command line tools to interact with various cloud providers
  • putty
  • winscp
  • chocolatey
  • SQL Server Management Studio
  • Azure Data Studio
  • git
  • vscode
  • vim
  • various PowerShell modules

In this post I will focus on SQL Server Management Studio.

Since version 18.7, the Azure Data Studio is also included in the installer.

By default Azure Data Studio is installed along with SSMS, but this can be excluded by using:

Eventually, this will not be a valid option, as SSMS will require dependencies provided by Azure Data Studio.

This is a good time to update the script I’m using to automatically install the latest version of SSMS.

I could not find an official manifest with the SSMS versions that I could query, so the script is using the GitHub page where Microsoft publishes the SSMS release notes.

If SSMS is already installed, the script will compare the existing version with the latest one available and it will notify the user.

You can also reference this script directly from my GitHub account.

Feel free to grab it and share it you you find it useful.

How to keep your VM templates up-to-date using packer

Today I’m gonna describe the process I’m using to keep my VM templates up to date.

All the scripts that you need to get started can be found on my GitHub repo. Feel free to get them and use them in any way you might find useful.
Make sure you update the variables inside the json\vars.json file to match your environment.

Either if you have a homelab where you test things or have a full enterprise environment, you will need to make sure you are always deploying the same thing across all your environments, every time, without exception.

For a long time sysadmins used hand-crafted golden images for their OSes that they need to further customize every time they were deploying a new VM.

In today’s modern infrastructures, we need to constantly change things and we need a better way to have these golden images tested and kept up to date.

The article here will show how this can be accomplished for a Windows 2019 OS image. I am usually using this to quickly spin up a bunch of VMs on my vCenter.

They will be configured later to host Domain Controllers, SQL Servers, various web applications, etc.

This allows me to focus on my testing and not spend a lot of time on the infrastructure itself.

The tool we’re going to use is Packer.

It will work out of the box with any major cloud providers and because it’s lightweight, portable and command-line driven, it’s suited for Continuous Delivery pipelines, keep dev/prod parity or to package up software with complex requirements.

The installation of Packer is really simple and all the needed steps are described in the docs.

On my GitHub Repo you’ll find a small script that you can use to download the latest version of Packer.

The first step is to have a template for our build. This is just a JSON file that contains information about how packer needs to access my infrastructure, access keys, various settings for my image.

In a second step I need to make sure I have a valid configuration. Packer has a validate command for this:

In step 3, after the validation is successful, I can proceed to building the image.

Notice that I’m passing the var-file option in order to tell packer where my variables are defined.

WARNING: This file will hold your secrets so be very careful about who’s able to access it.

The force option will instruct packer to force a builder to run when artifacts from a previous build prevent a build from running.

This parameter can behave differently for each builder, but in general it will remove the artifacts from the previous build.
Check out the docs about what options you can use for the build command.

At this point we have an image that’s ready to be used.

In the example scripts I’m using, you will notice that in order to allow packer to connect to the image, I enabled WinRM connectivity over http.

This was executed as the last action in the answer_files\win2019core\autounattend.xml file:

This approach is used to perform an Unattended Installation for Windows and this is the reason we have the floppy_files section in our JSON template:

As a good practice we should make sure that after all the customization is done, we should leave the template in a clean and secure state.

This is not a trivial task and should not be taken lightly as doing it wrong can expose you to a bunch of security issues.

It’s not the scope of this article, but you can read a more in-depth analysis on this blog post:

https://missionimpossiblecode.io/post/winrm-for-provisioning-close-the-door-on-the-way-out-eh/

To sum up, in my environment it takes about 8 minutes to finish a build.

I hope this will give you a better insight on how to prepare and maintain your images, either on-premises or in the cloud.

Thanks for reading!

Wrong network location profile causes issues with Windows Failover Cluster

Hi folks,

The other day I was pulling hair from my head trying to configure a Windows Failover Cluster intended for an SQL Server Availability Group setup.

During the cluster validation stage I always got this message:

The Windows Firewall on node node01.domain.local is not properly configured for failover clustering.
In particular, the ‘Public’ firewall profile is enabled on adapter ‘node01.domain.local – SLOT 1 PORT 2’.
The ‘Failover Clusters’ rule group is not enabled in firewall profile ‘Public’.
This may prevent some network communication between cluster nodes.

The OS install and networking part was already configured by a someone else and it was a pretty straightforward installation.

The issue turned out to be caused by the 2 NICs we have for iSCSI traffic which did not have a gateway configured.

Windows uses gateways to identify networks. If it doesn’t have a gateway configured, or if it can’t successfully ping it, it will not be able to identify the network it’s connected to and will assume it’s a public one.

Network cards in Windows can be connected to one of these type of networks:
– Public
– Private
– DomainAuthenticated

By default, the public network location type is assigned to any new networks when they are first connected.

A public network is considered to be shared with the world, with no protection between the local computer and any other computer. Therefore, the Windows Firewall rules associated with the public profile are the most restrictive.

As part of the Windows Failover Cluster validation/creation there are checks to verify connectivity (between cluster nodes, active directory, etc.).

These were the settings I had:
Before

All I needed to do was to move all non-domain network interfaces into the private profile:

After

After the change the cluster creation went without issue.

This small detail be easily missed and can cause a lot of headaches and lost time investigating failover clusters.

Cheers!

Register-PSRepository fails on Powershell Core when running behind a corporate proxy

Trying to register a psrepository behind a corporate proxy fails on Powershell Core.

The error you receive is:

Running the same on Windows Powershell 5.1 works.
Now, let see why it’ not working on Powershell Core.

Trying to find more details about the error message:

Let’s look at the function inside the module where the error is triggered (bonus tip on how to jump directly to the line number)

The error message is misleading, as it turns out the part that is failing is this one:

We know the endpoint location is valid, so that means we cannot pass through the proxy.
Digging around the internet led me to this GitHub issue.

Apparently, .NET Core is not respecting proxy in some cases.

The recommended workaround from Steve Lee is to set

or to create

Turns out our little problem is now fixed and the Register-PSRepository command runs successfully.

I hope this post will save you some time in case you run into the same issue.

Voilà!

Block Chrome Software Reporter Tool

Use the following PowerShell Snippet to disable Chrome Software Reporter Tool from eating your CPU cycles:

ChromeCleanupEnabled’s value determines whether the Software Reporter Tool may run on the system.

ChromeCleanupReportingEnabled’s value determines whether the results are reported to Google.

How to Change your Network Profile in Server 2012R2

Network Profiles were first introduced to allow administrators to configure different firewall profiles based upon what network a user connects to.
Administrators could change the profile by navigating to Network and Sharing Center in control panel and selecting the profile which suits their needs.
In Server 2012, Administrators can no longer change the network profile in Network and Sharing Center, they can view just not change.
You can accomplish this in Power-Shell:
To see the current profile:

To change the profile:

You can do it with a one line command:

Get SMO version on your server

A quick way to find out what SMO versions are installed: