SQL Server Backup & Performance Counters

Background

Experiencing  longer than expected Production to Development environment backup and restore cycles.

Wanted to capture performance metrics.

Outline

Here are metrics we will consider:

  1. Ongoing Monitoring
    • MS Windows Performance Monitor ( perfmon )
  2. Summary
    • SQL Server Statement

Performance Monitor ( perfmon )

Metrics

Items

  1. SQL Server / Backup Device
    • Device Throughput Bytes/sec
  2. SQL Server, Databases
    • Backup/Restore Throughput/sec

Tabulate

Object Counter
SQL Server, Backup Device Object
Device Throughput Bytes/sec Throughput of read and write operations (in bytes per second) for a backup device used when backing up or restoring databases. This counter exists only while the backup or restore operation is executing.
SQL Server, Databases Object
Backup/Restore Throughput/sec Read/write throughput for backup and restore operations of a database per second. For example, you can measure how the performance of the database backup operation changes when more backup devices are used in parallel or when faster devices are used. Throughput of a database backup or restore operation allows you to determine the progress and performance of your backup and restore operations.

Select Metrics

Selections

Selection – SQLServer:Databases

performanceCounter.choose.SQLServer.Databases.20180914.1202PM.PNG

Selection – SQLServer:Backup Device

performanceCounter.choose.backupDevice.20180914.1045AM.PNG

Captured Metrics

Captured Metrics

Image – 01

performanceCounter.20180914.1137AM.PNG

Image – 02

performanceCounter.20180914.1130AM.PNG

Image – 03

performanceCounter.20180914.1128AM.PNG

Backup Statement Output

Metrics

Output #1

backupDB.20180914.1215PM

 

Summary

  1. Physical Disk/Disk Writes/sec divided ( / ) by Physical Disk/Avg. Disk Bytes/sec
    • Disk Writes/sec
  2. SQLServer:databases Backup/Resource throughput/sec Versus Physical Disk/Disk Writes/sec
    • Likely SQLServer:databases Backup/resource Throughput/sec will be a multiplier of the Physical Disk/Disk Writes/sec.

 

Finding Binaries

Background

Trying to explore the various avenues for finding binaries in Linux & MS Windows.

Linux

Outline

  1. locate
  2. which

Command

locate

Outline

locate reads one or more databases prepared by updatedb and writes
file names matching to standard output, one per line.

By default, locate does not check whether files found in database still
exist. locate can never report files created after the most recent
update of the relevant database.

 

Syntax
locate [command]

Syntax

locate ilist

Output

linux_locate_20180912_0402PM

which

Outline

Which takes one or more arguments. For each of its arguments it prints
to stdout the full path of the executables that would have been exe-
cuted when this argument had been entered at the shell prompt. It does
this by searching for an executable or script in the directories listed
in the environment variable PATH using the same algorithm as bash.

Syntax

which [command]

Sample

which ilist

Output

linux_which_20180912_0254PM.PNG

 

MS Windows

Outline

  1. where

Command

where

Outline

Displays the location of files that match the search pattern.
By default, the search is done along the current directory and in the paths specified by the PATH environment variable.

Syntax

where [command]

Sample

where ilist

Output

where_20180912_0411PM

SQL Server – BulkCopy ( BCP ) – Which data file?

Background

Using BCP, we are churning though quite a bit of files.

Unfortunately, the DOS batch file that I quickly stitched together missed an importantly functionality.

And, that functionality is to log the current file being processed.

Diagnostic

Microsoft

Resource Monitor

I am a big fan of Microsoft’s Resource Monitor.

Let us use it.

Preparation

We remote connect to the source computer and launched task Manager; from Task Manager accessed Resource Monitor.

Resource Monitor – Tab – Memory

Image

ResourceMonitor_Tab_CPU_ProcessesAndAssociatedHandles_20180706_0850AM.png

Explanation
  1. When we filter on the bcp.exe process, we see our data file as one of the files mentioned in the “Associated Handles” tab

 

Resource Monitor – Tab – Memory

Image

ResourceMonitor_Tab_Memory_20180706_0852AM.png

Explanation
  1. The BCP process is using about 14 MB

 

Resource Monitor – Tab – Disk

Image

ResourceMonitor_Tab_Disk_20180706_0907AM.png

Explanation
  1. sqlserver,exe is mentioned
  2. But, not our bcp.exe file

 

Summary

To get a reliable insight into which files are being accessed, please consider Microsoft’s own Resource Monitor; specifically the CPU tab.

 

 

Error: “The requested operation could not be completed due to a file system limitation (mscorlib”

Background

Experiencing an error when trying to run an embedded process.  Part of the functionality that is bundled in the process, is a file copying module.

Error Message

Here is the error message :-

*** Error: The requested operation could not be completed due to a file system limitation (mscorlib) ***

Trouble Shooting

Application Message

Thankfully the particular file that is copied is logged, as well.


Copying log backup file to temporary work file. Source: '\\LABDB\Backup\hrdb_20180703104507.trn', Destination: '\\LABMirror\backup\HRDB_20180703104507.wrk'

Fast Copy

Attempted same file copy in FastCopy.

And, again thankfully it reported an error message; along with an error number.

Image

FastCopy_20180705_0712PM_v2.png

Text


WriteFileWait(The requested operation could not be completed due to a file system limitation665) : \\LABMirror\backup\HRDB_20180703104507.wrk
TotalRead  = 40,414 MB
TotalWrite = 40,289 MB
TotalFiles = 0 (0)
TotalTime  = 26:50
TransRate  = 25.0 MB/s
FileRate   = 0.00 files/s 

Explanation

  1. Error
    • Error Operation :- WriteFileWait
    • Error Description :- The requested operation could not be completed due to a file system limitation
    • Error Number :- 665

 

Error Causation

There are a few probably causes for error number 665.

Compression?

File

Finally, we checked the compression setting on others files in the targeted folder.

File Property

Image

file_20180706020000_trn_brushedup.png

Textual
  1. Size :- 2.07 GB
  2. Size on Disk :- 760 MB
Explanation

We can see that our file is compressed.

Folder Settings

Reviewed the Folder’s Advanced Attributes.

Image

AdvancedSettings_20180705_0717PM.png

Explanation

  1. Compress Contents to save disk space ( is enabled )

 

Remediation

Compression

Setting

We turned off compression on the targeted folder.

Fast Copy

Retried Fast Copy and it worked successfully.

NoError__20180705_1014PM_v2.png

 

Summary

Wished the original application displayed the OS’s error number and not just a simple error text that read “Error: The requested operation could not be completed due to a file system limitation (mscorlib)“.

Thankfully, there was enough diagnostic data in regards to the original file and destination folder that allowed us to replay via a more illuminating tool.

 

 

 

 

 

Windows Subsystem for Linux ( WSL ) – Error – “The app that you are trying to run is not supported on this version of Windows”

 

Background

If you try to enable “Windows Subsystem for Linux” ( WSL ) on a Windows 10 machine, you may unfortunately run into the error noted in our title message.

 

Error

Error Message

Image

NotSupportedOnThisVersionOfWindows_20180612_1012PM.png

Textual


The app that you are trying to run is not supported on this version of Windows.

 

Steps to reproduce

Currently, I am able to reproduce this error very easily.

The pathway towards reproducing are simple and direct.

And, they are:

  1. lxrun.exe /install
  2. bash

The commands above are meant to enable Linux if not currently installed.

Why?

Why am I not able to enable Linux on my Windows 10 system.

Requirements

The basis requirements are :-

  1. Bitness
    • 32-bit ( NO )
    • 43-bit ( YES )
  2. MS Windows 10 – Marketing
    • Anniversary Update  ( 2016-August-2nd )
    • Creator Update ( 2017-April-5th )

 

Versioning

Version & Build

Let us go get our Version Number

There are a couple of ways to get our Version Number.

Outline

  1. Windows System
    • About
  2. Winver

Steps

Windows System – About

  1. Access Windows System
  2. In Windows System, click on the menu Item ( Help \ About )

Image

controlPanel_System_Help_About_20180613_1050PM

Explanation

  1. Windows Edition
    • Windows 10 Enterprise 2016 LTSB

 

winver

Image

winver_20180613_1058PM

Explanation

  1. Version :- 1607
  2. OS Build :- 14393.2312

Marketing

Let us use Wikipedia to map Version Number to the Marketing Name.

Image

Windows 10 version history
Link

wikipedia_20180613_1106PM

Explanation

Our version # is 1607.

1607 maps to “Anniversary Update”.

Can we get Linux ?

Can we enable Linux Sub-system?

MSFT’s documentation:

Install the Windows Subsystem for Linux
Link

Based on the doc referenced above, here are the Marketing Versions Supported:

  1. Anniversary Update  ( 2016-August-2nd )
  2. Creator Update ( 2017-April-5th )

But, we are still stuck!

Why Stuck

It appears that though we are able to take all the necessary steps :

  1. Enable Developer Mode
  2. Enable Linux Subsystem feature

but, because we are on LTSB, we are in a tight mud.

LTSB?

DUKE

TO LTSB or Not

Link

With the release of Windows 10 in 2015, Microsoft introduced a new sub-edition of Windows 10 Enterprise called “Long Term Servicing Branch” or “LTSB”. Each release of Windows 10 Enterprise LTSB will remain relatively unchanged–receiving only security updates and bug fixes, but no feature updates–through a 10-year lifespan.

To date, Microsoft has delivered two releases of Windows 10 Enterprise LTSB (2015 and 2016) with the next expected in 2019. While, according to Microsoft, LTSB was “designed for special-purpose PCs such as those used in point-of-sale systems or controlling factory or medical equipment”, some in IT have deployed it to common end-user computers, citing the benefit of having no Windows Store apps (which includes Microsoft Edge and Cortana) and no semi-annual feature updates to deal with.

However, recent articles and an updated Microsoft FAQ point out that, as released versions of Windows 10 Enterprise LTSB will not receive newer features, they will also not be supported on newer computer processors (such as Intel’s eighth-generation “Kaby Lake Refresh” architecture, released in August, 2017) . This introduces a potential down-side to deploying LTSB, but it’s not a new concept, as both Windows 7 and Windows 8.1, both still fully supported by Microsoft on older hardware, are only partially supported on Intel’s sixth-generation “Skylake” processors and are not supported on the seventh-generation “Kaby Lake” processors.

Windows 10 – Marketing Name via PowerShell

Background

Reading through MSFT’s documentation on installing the “Linux Subsystem on MS Windows” brought to light the need to familiarize oneself with the various marketing moniker for each Released build of MS Windows 10.

Literature

Here is the specific document…

Install the Windows Subsystem for Linux
Link

And, here are the specific texts that juiced my appetite:

Image

Falls Creator Update

linuxPlatform_FallCreatorUpdate_20180613_1150AM.png

Anniversary Update and Creators Update

linuxPlatform_AnniversaryAndCreatorsUpdate_20180613_1152AM.png

Explanation

So the question is which MS Windows 10 Update am I running ….

Referenced Work

Thanks goodness there are various credible sources doing the heavy work of lining up MSFT’s version #, Marketing Version, and Build Number  low & high bars.

Here are the sources we used for this post:

  1. Microsoft
    • Windows 10
      • Windows 10 release information
        Link
  2.  Wikipedia
    • Windows 10 version history
      Link
  3. PureInfotech
    • Mauro Huculak
      • Windows Update History information helps you know exactly what’s the latest version of Windows 10 available.
        Link

Image

Wikipedia – Windows 10 – Version History

wikipedia_Windows10VersionHistory_20170613_1225PM

Code

Text file

Text file – UpdateList.txt

Outline

  1. Here is our file’s content
    • Version
    • Marketing
    • Build Number – Min
    • Build Number – Max

1809, Windows 10 version 1809 (Late 2018), 17686.00, 17686.00
1803, Windows 10 version 1803 (April 2018 Update) history, 17133.73, 17134.83
1709, Windows 10 version 1709 (Fall Creators Update) history, 16299.15, 16299.461
1703, Windows 10 version 1703 (Creators Update) history, 15063.11, 15063.1112
1607, Windows 10 version 1607 (Anniversary Update) history, 14393, 14393.2189
1511, Windows 10 version 1511 (November Update) history, 10586.104, 10586.1176
1507, Windows 10 version 1507 (Initial Release) history, 10240.16683, 10240.17831

PowerShell

PowerShell – getWindows10MarketingName.ps1

Outline

  1. Read flat file ( updateList.txt)
  2. Get MS Windows Version via calling [Environment]::OSVersion
    • Build Number
  3. Get MS Windows by issuing “Get-WmiObject Win32_OperatingSystem
    • Returns
      • Caption
      • OSArchitecture
  4. Registry Access – Get-ItemProperty
    • Argument :- HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion
    • Returns
      • ProductName
      • ReleaseID
      • CurrentBuildNumber
      • CompositionEditionID
  5. Iterate read file
    • Condition to indicate match
      • If ReleaseID from registry Path HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion matches read record
      • If Build Number falls into range of read record
    • Save Pointer
  6. If Pointer Saved
    • Display OS Version from table

Preparatory

Registry
Registry – HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion

registry_20180614_0700AM.png

 

Actual Code


Set-StrictMode -Version 1
  

[boolean]$debug=$false;
[string] $objOSVerNumberAsString = $null;
[double] $versionNumberMinAsNumber = 0;
[double] $versionNumberMaxAsNumber = 0;
[boolean] $bConverted = $false;
[string] $strLog = $null;
#[string] $CHAR_EMPTY='';
#PowerTip: Create Empty String with PowerShell
[string] $CHAR_EMPTY=[string]::Empty;

[string] $productName =$null;
[string] $releaseID =$null;
[string] $currentBuild =$null
[string] $edition =$null;

[string] $releaseIDInList = $null;

$debug=$false;
#$debug=$true;

$updateListFilename = 'updateList.txt';

#read file updateList.txt
$updateList = Get-Content $updateListFilename;

#Display Update List
#$updateList;

#Get OS version [Environment]::OSVersion
$objOSVer =[Environment]::OSVersion;

#Display OS version
Write-Host('OS Version');
Write-Host('----------');
Write-Host($objOSVer);


$objOSVerPlatform = $objOSVer.Platform;
$objOSVerServicePack = $objOSVer.ServicePack;
$objOSVerNumber = $objOSVer.Version;
$objOSVerString = $objOSVer.VersionString;


$objOSVerNumberAsString = [system.String]::Join($CHAR_EMPTY, $objOSVerNumber);


$objOSVerNumber0 = $objOSVerNumberAsString.split('.')[0];
$objOSVerNumber1 = $objOSVerNumberAsString.split('.')[1];
$objOSVerNumber2 = $objOSVerNumberAsString.split('.')[2];
$objOSVerNumber3 = $objOSVerNumberAsString.split('.')[3];


$objOSVerNumberBase = $objOSVerNumber0;
$objOSVerNumberSP = $objOSVerNumber1;
$objOSVerNumberBuild = $objOSVerNumber2;
$objOSVerNumberMinor = $objOSVerNumber3;

if ( $debug)
{

    Write-Host('');

    Write-Host('OS Version ( broken into pieces)');
    Write-Host('--------------------------------')  

    Write-Host('OSVersionPlatform      :- {0}' -f $objOSVerPlatform);
    Write-Host('OSVersionServicePack   :- {0}' -f $objOSVerServicePack);
    Write-Host('OSVersionNumberAsArray :- {0}' -f $objOSVerNumber);
    Write-Host('objOSVerNumberAsString :- {0}' -f $objOSVerNumberAsString);
    Write-Host('OSVersionString        :- {0}' -f $objOSVerString);

    Write-Host('objOSVerNumberSP       :- {0}' -f $objOSVerNumberSP);
    Write-Host('objOSVerNumberBuild    :- {0}' -f $objOSVerNumberBuild);

    Write-Host('');
}

  

$objWMIOS = (Get-WmiObject Win32_OperatingSystem);

if ($objWMIOS -ne $null)
{
	Write-Host('');

    Write-Host("WMI - Win32_OperatingSystem");
	#$objWMIOS;

	$WMIOSCaption = $objWMIOS.caption;
	$WMIOSArchitecture = $objWMIOS.OSArchitecture;

	$strLog = "`t WMI OS - Caption        :- {0}" -f $WMIOSCaption;
	Write-Host $strLog;

	$strLog = "`t WMI OS - OSArchitecture :- {0}" -f $WMIOSArchitecture;
	Write-Host $strLog;	

	Write-Host('');
}


$strRegPath = "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion";
$objRegWinOS = (Get-ItemProperty $strRegPath);

if ($objRegWinOS -ne $null)
{

	Write-Host('');
    Write-Host("Registry Path $strRegPath");
	#$objRegWinOS;

	$productName = $objRegWinOS.ProductName;
	$releaseID = $objRegWinOS.ReleaseID;
	$currentBuild = $objRegWinOS.CurrentBuildNumber;
	$edition = $objRegWinOS.CompositionEditionID;

	$strLog = "`t WinOS Registry - productName :- {0}"`
				-f $productName;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - ReleaseID :- {0}" `
				-f $releaseID;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - Current Build :- {0}" `
				-f $currentBuild;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - Edition :- {0}" `
				-f $edition;
	Write-Host $strLog;

	Write-Host('');
}


$i = 0;
$entry = $null;
$entrySaved = $null;


$iNumberofUpdates = $updateList.Length;

Write-Host '';
Write-Host 'Iterating Build List looking to match OS Build Number ... ';
Write-Host '';


foreach ( $entry in $updateList )
{

  $releaseIDInList =$entry.split(',')[0];
  $update=$entry.split(',')[1];
  $versionNumberMin=$entry.split(',')[2];
  $versionNumberMax=$entry.split(',')[3];

  
  $bConverted = [double]::TryParse(`
                      $versionNumberMin`
                    , [ref] $versionNumberMinAsNumber);

  $bConverted = [double]::TryParse(`
                    $versionNumberMax`
                      , [ref] $versionNumberMaxAsNumber);                               

  if ($debug)
  {

    Write-Host('Build {0} -f $objOSVerNumberBuild ');

    $strLog = 'versionNumberMin {0}' `
				-f $versionNumberMinAsNumber;
	Write-Host($strLog);

    $strLog = 'versionNumberMax {0}' `
				-f $versionNumberMaxAsNumber;
	Write-Host($strLog);	

  }     

  if (
			( $releaseID -eq $releaseIDInList) `
		-or `
			( `
				     ( $objOSVerNumberBuild -ge $versionNumberMinAsNumber) `
				-and ( $objOSVerNumberBuild -le $versionNumberMaxAsNumber) `
			)
	 )
    {
        # save entry
        $entrySaved = $entry;

        break;
    }

  $i = $i + 1;  

} # foreach ( $entry in $updateList )   


if ($entrySaved -ne $null)
{

    # Get fields
    $matchVersion = $entrySaved.split(',')[0];
    $matchMarketing = $entrySaved.split(',')[1];
    $matchBuildMin = $entrySaved.split(',')[2];
    $matchBuildMax = $entrySaved.split(',')[3];

    Write-Host("Version Matched");

    Write-Host("`t Version   :-  $matchVersion");

    $strLog = "`t Build     :-  {0} through {1}" `
                -f $matchBuildMin.Trim() `
				 , $matchBuildMax.Trim();

    Write-Host($strLog);

    Write-Host("`t Marketing :- $matchMarketing");

}
else
{

    $strLog = 'Version Number ({0}) not found!' -f `
                $objOSVerNumberBuild;

    Write-Host($strLog);            

}

Invoke

Syntax

powershell -file ./getWindows10MarketingName.ps1

Output

getMSWindows10UpdateName_Work_20180613_0742PM.png

Source Code Control

GitHub

DanielAdeniji/WinOSMarketingNameUsingPS
Link

Listening

Listening to the song I first heard during yesterday’s parade:

Drake – Nice for What
Link

Lyrics

You got to be nice for what to these …..

Windows 10 – Installing Linux SubSystem

Background

Availing Linux on MS Windows 10.

Here is the guide that we will be using:

Windows 10 Installation Guide
Install the Windows Subsystem for Linux
Link

Outline

  1. Enable Windows Feature
    • Microsoft Windows Subsystem Linux
  2. Review OS Version
    • Issue SystemInfo and get OS Version and Build Number
  3. Choose Install Location, based on Build Number
    • If Build Number > 16215
      • We can install from Windows Store
    • Else
      • Enable Windows Developer Mode
      • Start Bash or use “lxrun /install
  4. Register Product
  5. Create Linux User
  6. Set root user’s password

 

Install Steps

Enable Windows Optional Features

Microsoft-Windows-Subsystem-Linux

Code

powershell -C "Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux"

System Info

Code


powershell -C "systeminfo | Select-String 'OS' "

Output

version_20180610_0902PM

Explanation

As our Build Number ( 17134 ) is greater than 16215, we can install via “Microsoft Store“.

Microsoft Store

Visit Microsoft Store and search for Linux.

https://www.microsoft.com/en-us/search/result.aspx?q=linux

Here is the result of that search:

MicrosoftStore_Linux.PNG

 

SUSE Linux Enterprise Server 12

We chose to go with SE Linux.

SUSE Linux Enterprise Server 12 – Get

SUSELinuxEnterprise_20180610_0905PM.PNG

Use across your devices

UseAcrossYourDevices_20180610_0906PM.PNG

Add your Microsoft account to Microsoft Store

AddYourMicrosoftAccountToMicrosoftStore_20180610_0921PM [brushedup].PNG

Enter Password

AddYourMicrosoftAccountToMicrosoftStore_EnterPassword_20180610_0923PM.PNG

Installing

Installing_20180610_0911PM

SUSE Linux Enterprise Server 12 – Registration +User

registration_20180610_0926PM.PNG

SUSE Linux Enterprise Server 12 – Registration +User +Password

registrationUserAndPassword_20180610_0927PM.PNG

Validation

Bash

uname

Get Linux Version

Code


uname -r

Output

bash_uname_20180610_0938PM

List Users

Code


cat /etc/passwd

Output

security_users_20180610_0947PM.PNG

References

  1. Microsoft
    • Install the Windows Subsystem for Linux
      • Install the Windows Subsystem for Linux
        Link
    • Rich Turner
      • Bash on Ubuntu on Windows – Download Now!
        Link