Sandbox Windows Server in VMware Workstation

To create a sandbox installation in VMware Workstation I usually have to start with a Windows Server. I am using VMware Workstation Pro as it makes it possible to build more complex sandbox installations with multiple servers like SQL Server AlwaysOn Availability Groups.

This is a description of the installation and configuration of a general Windows Server to be used in different roles. When the configuration is completed I can clone the virtual machine to build the desired sandbox.

First I download the ISO installation image from Microsoft. Currently it is Windows Server 2023 Standard Edition. To use it I also have to get a license key from my Visual Studio subscription.

Then a new virtual machine is created on the ISO image with the license key. The VM is created with 16 GB memory, 4 vCores and 64 GB hard disk. After installation Windows Update is executed.

Configurations

• Network adapter: Change to paravirtual adapter „vmxnet3“. This is a off-line operation I have described earlier.
• Network adapter: Do not allow to turn off device to save energy.
• Network adapter: Show hidden devices in Device Manager and remove old Intel Gigabit Network Connection.
• Keyboard: Add Danish keyboard to English profile and remove English keyboard. This is as I live in Denmark.
• Location: Change to UK and set to UTC to avoid daylight savings etc.
• Server Manager: Do not start automatic.
• Print Spooler: Stop and disable service.

Script to do all this will be in SQLAdmin Github.

Being Effective

Being Effective is often said to be a good thing. And naturally we have an idea of what is effective. But in english there are three different words on different aspects of being effective:

Effectiveness

"Doing the right thing". That is getting the desired result. I usually think of this as not goofing around but focus on the main task.

Efficiency

"Doing things right". That is without waste of time, materials, energy and other resources. And still with success.

Efficacy

"Doing with maximum response". Usually this term is used in pharmacology when maximum response is achieved.

In Danish we only have one word on being effective (Effektiv)...

The three terms are also discussed in the article "The difference between efficacy, effectiveness and efficiency".

Migrate from HTA to SPA

Microsoft HTML Application (HTA) technology is based on Internet Explorer. And this is obsolete and will soon be removed. A HTA is running with mshta.exe, which is existing outside Internet Explorer and a default part of Windows 11. But as it is based on Internet Explorer we must face that HTA is going away too. A solid indicator is that the HTA documentation at Microsoft is no longer maintained and placed in a historical path.

HTA is a quick and neat way to build a nice and dynamic GUI for local tools. This has made HTA rather popular for many years, and there are a lot of solutions based on HTA. So migrating from HTA will be a significant task to many.

You can take the opportunity to migrate to a more complete technology stack like .Net with C#, but there will be a lot new tools to learn. Some might consider to go with PowerShell, but both WinForms and WPF are quite complex with PowerShell in comparison to dynamic HTML in HTA.

Single-Page Application

Another suggestion I have seen (link) as a replacement to HTA is Single-Page Application (SPA), where it is possible to use dynamc HTML and make the solution local on the client.

This works fine on very simple things. It is even possible to make a sensible filestructure for the SPA with external script and style files. But when you want to work with external resources like a database or the content of a file you hit rock buttom!

With HTA you can access external resources through ActiveX models or objects like ActiveX Data Objects (ADO) or FileSystemObject. And modern web browsers can't handle ActiveX. There is a Internet Explorer setting in Microsoft Edge, but that will most likely be removed.
I think that the real problem to Microsoft here is security, as ActiveX has given a lot of trouble on the internet. The COM technology that ActiveX is build on is still solid and widespread.

The conclusion must be that HTA can't be migrated to SPA as most HTAs are working on external resources like files or databases.
If I find a good replacement to HTA I will let you know.

Phytagoras hypotenuse with PowerShell

To get the hypotenuse of a right angle triangle with Pythagoras formular using the two Methods Sqrt and Pow of the .NET System.Net class with PowerShell it is possible in just one line:

[System.Math]::Sqrt( [System.Math]::Pow(11.68, 2) + [System.Math]::Pow(7.24, 2) )

Which will give the implicit formatted result

13.7419067090415

Actually you do not have to spell out the namespace System as it is implicit to PowerShell. But I like to spell it out as a kind of documentation.

This is a example on using .NET features direct with PowerShell. Also I think it shows that you have to know .NET as a PowerShell user.

The numbers are from the dimensions in mm of a RJ45 plug. So now you know the inner size of the hole to drill to make it fit.

Add SQL Agent job step with tokens in output file name

 If you create a new SQL Agent job step with tokens in output file name using the stored procedure sp_add_jobstep or want to add a filename with tokens to an existing job step using the stored procedure sp_update_jobstep you will get a error like

A fatal scripting error occurred.
Variable SQLLOGDIR is not defined.

In this case I was trying to use the token SQLLOGDIR, but it does not matter which token.

Many users of Ola Hallengren (LinkedIn) Maintenance Solution (link) had similar errors. Actually Ola is aware of the challenge - he just forgot to promote his solution ;-)

When you use the stored procedure sp_add_jobstep you with that call the internal stored procedure sp_add_jobstep_internal. You can see the definition of both procedures in the system database msdb with SQL Server Management Studio (SSMS).

The root-challenge is that somewhere along the path from calling sp_add_jobstep to setting the output file name on the job step the content of the parameter is interpreted. But at that point the execution is outside SQL Agent, and the tokens are not available.

The trick - that Ola forgot to promote - is to build a string with each token element seperated.

DECLARE @_token_log_directory NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(SQLLOGDIR))';
DECLARE @_token_job_name NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(JOBNAME))';
DECLARE @_token_date NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(DATE))';
DECLARE @_token_time NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(TIME))';
DECLARE @_output_file_name NVARCHAR(MAX) = @_token_log_directory + N'\' + @_token_job_name +  N'_' + @_token_date + N'_' + @_token_time + N'.txt';

This string can then be used as parameter value to the parameter @output_file_name in the stored procedures to a SQL Agent job step.

EXECUTE msdb.dbo.sp_update_jobstep @job_name=N'IndexOptimize - Db1.Schema1.Tbl1',
  @step_id=1,
  @output_file_name= @_output_file_name;

If you dig into Olas code to install the maintenance solution and search for the string „@TokenLogDirectory“ you will see that Ola build this string element by element. With careful seperation of $-sign and token names.