2017-03-15

PowerShell script template

I have made this simple template to start a new PowerShell script file. It helps me to get the basics right from the beginning. Also it supports my effort to write all scripts with advanced functions.

<#
.DESCRIPTION
.PARAMETER <Parameter Name>
.EXAMPLE
.INPUTS
.OUTPUTS
.RETURNVALUE
.EXAMPLE

.NOTES
Filename  : <Filename>
.NOTES
<Date and time, ISO8601> <Author> <History>

.LINK
Get-Help about_Comment_Based_Help
#>

#Requires -Version 4
Set-StrictMode -Version Latest

#Import-Module G:\Teknik\Script\Sandbox\Module.sandbox\Module.sandbox.psm1


#region <name>

function Verb-Noun {
<#
.DESCRIPTION
  <Description of the function>
.PARAMETER <Name>
  <parameter description>
.OUTPUTS
  (none)
.RETURNVALUE
  (none)
.LINK
  <link to external reference or documentation>
.NOTES
  <timestamp> <version>  <initials> <version changes and description>
#>
[CmdletBinding()]
[OutputType([void])]
Param(
  [Parameter(Mandatory=$true, ValueFromPipeLine=$true)]
  [String]$param1
)

Begin {
  $mywatch = [System.Diagnostics.Stopwatch]::StartNew()
  "{0:s}Z  ::  Verb-Noun( '$param1' )" -f [System.DateTime]::UtcNow | Write-Verbose
}

Process {
}

End {
  $mywatch.Stop()
  [string]$Message = "<function> finished with success. Duration = $($mywatch.Elapsed.ToString()). [hh:mm:ss.ddd]"
  "{0:s}Z  $Message" -f [System.DateTime]::UtcNow | Write-Output
}
}  # Verb-Noun()

#endregion <name>


###  INVOKE  ###

Clear-Host
#<function call> -Verbose -Debug


2017-02-04

DBA Backlog

As a Database Administrator both in projects and daily routines like administration or operations all kinds of things show up. Normally they are associations that doesn't really fit in the plans or the context, but still relevant or even important in the long run.
Usually these things and ideas end up on post-it notes, whiteboards or another volatile medium. If you are lucky to be in a large project with experienced developers and project managers some items might be saved.

Another way is to create your own backlog of ideas, points of interest, nice to haves and other things that might make your works better. In ITIL these things will be considered part of Continual Service Improvement (CSI) as input to the Plan part of the Deming Circle.

I have collected a set of attributes I have used to establish a backlog in a project or a team. Not all attributes are relevant in each situation. Sometimes it is also necessary to twist an attribute to make the backlog usable.

And to my proposal for a general backlog:
Attribute NameAttribute Description
TitleShort and descriptive title of the backlog item.
ThemePredefined themes to sort the backlog on a "dimension" like technology. This is very useful when the backlog grows.
{ SQL Server | SSDB | SSIS | ... }
DescriptionProse description of the backlog item with as many details and thoughts as possible.
The description can easy change and grow over time, even before the backlog item is activated and processed.
PriorityThe priority of the backlog item. Predefined values that makes sense, even to senior management.
{ Critical | High | Medium | Low }
StatusStatus of the backlog item. The values are inspired by Kanban to support an effective execution of the backlog.
"Keep the Ready queue short".
{ Backlog | Ready | Doing | Done }
EstimateA quick estimate of the time needed to process and finish the backlog item.
The item is finish when the issue is solve and documented – completety. As i agile develoopment.
{ Day | Week | Month | Quater | Year }
DeadlineDate of deadline, if one is required. If a deadline exists it is usally given by business or management.
ChangedTimestamp of last change to the backlog item.
CreatedTimestamp of when the backlog item was created. Usually when the item was entered into the backlog.
Changed byThe person who last changed the backlog item.
Created byThe person who created the backlog item. Usually the person who entered the item into the backlog.
TangibleThis is more a business attribute that tells about where business can relate to the backlog item.
OwnerBusiness owner of the backlog item.
ResponsibleThe person who is responsible on the backlog item. Usually the person who is to get the job done.

I personally have good experiences on building a backlog as a SharePoint list rather quick and effective. This also gives all the SharePoint features on filtering, printing and other trivialities.
Another possibility I have tried a few times is using the backlog features i Microsoft Team Foundation Server (TFS). It works really nice also for Database Administrators.

2016-11-16

SQL Server 2016 SP1

Microsoft has just released Service Pack 1 for SQL Server 2016.
The release is announced in several blog posts, e.g.
There are some rather interesting details on Microsoft database technology in general from the Connect() conference in the blog post "Announcing the Next Generation of Databases and Data Lakes from Microsoft" on SQL Server Blog (Data Platform Insider).
The official descriptions on SQL Server 2016 SP1 editions from Microsoft is more a presentation than technical details.

The installation set on Service Pack 1 can be downloaded from Microsoft Download. The release information (KB3182545) gives the details on what is fixed, but as the information is a collection of links it will take some time to get through all the details.

The new version number after the upgrade is 13.0.4001.0, and the installation will restart the services.

The installation set is named "SQLServer2016SP1-KB3182545-x64-ENU.exe" and takes 551 MiB. If the installation set is unpacked it takes 733 MiB.
The installation can be with the GUI by executing "setup.exe" or by command line - as usual.
Log files are generated as by previous SQL Server installations and upgrades. Usually in a folder named with a timestamp in the path "%ProgramFiles%\Microsoft SQL Server\130\Setup Bootstrap\Log\", e.g. the folder name "20161116_205924".
According to the logfile "Summary_<servername>_<foldername>.txt" (5 KiB) the installation took about four minutes on my workstation. The logfile "Details.txt" (9.14 MiB) contain about 59000 lines in my case. Usually I only look into Details.txt when I have an installation error...

This was the initial - and common - findings. When I get more they will surface here.
Enjoy!

2016-11-09

MS16-136

For the first time in a long time we have a security update for SQL Server with MS16-136.
Some technical details on the update itself are in KB3199641.

The update fixes elevation of privilege on three different components in SQL Server:

  • Database Engine (RDBMS); there are three CVE's spread over the different major versions of Database Engine. The documentation speaks of "improperly handles pointer casting" without further details.
  • Master Data Services - MDS; There is a cross-site-scripting (XSS) vulnability in the MDS API. This could be in the web application part of the API.
  • Analysis Services - SSAS; in this case the vulnability is due to "improperly checks FILESTREAM path.".
  • SQL Server Agent; the vulnability lies in "incorrectly check ACLs on atxcore.dll". This file is a part of the SQL Server Agent ActiveX subsystem, which is - finally - removed from SQL Server with the 2016 version.

The update is for SQL Server 2012 and newer. SQL Server 2008 (R2) are not hit by this security issue.
And not a word about SQL Server 2005, 2000 - or older ;-)

History

2016-11-09 Post created with initial references.
2016-11-27 Details on SQL Server components added.

2016-09-04

IOmeter execution

Installation

Download IOmeter from iometer.org. I also download the documentation to be sure that I have the updated documentation.

Manual Setup

The drive must be defined on each worker before any other configuration in the IOmeter GUI.
You can only define one drive ("target") per worker.
Set up for 20 workers if the server has that many cores. If you have more cores than that you still don't need to define more that 20 workers in IOmeter.

IOmeter need a storage size to test on. It should three times larger than the complete amount of cache storage in the stack to the drive. On a SAN-drive this gives that the test file might be rather large. If you don't know the cache size and are working on local disks you could start with 40GB.
The maximum disk size is a little tricky as it is measured in number of sectors in IOmeter.
To get from storage amount like in GB to number of sectors you need to know the sector size. On a lot of disks it is 512B but some large disks with rotating platters mights have a sector size on 4KB. The number you can get in PowerShell with the CIM class Win32_DiskDrive where the attribute BytesPerSector gives the value.
When you have the sector size and know the size you want for maximum size the the calculation can be like this on a 40GB maximum:
(40GB * 1024MB * 1024KB * 1024B) / 512B = 83.886.080 sectors

In the tab "Access Specifications" I chose Default and added it as assigned.
The definition then can be altered by editing the default specification.
The "Transfer Request Size" is changed to the sized wanted for the test, e.g. 64 Kilobytes.
As SQL Server Database Engine mainly works sequential setting "Percent Random/Sequential Distribution" should be set to 100% Sequential. This setting is for some tests set to 100% Random.
A lot of SQL Server storage activity on data i reading, and then the setting "Percent Read/Write Distribution" should be set to 100% Read. This setting is for some test set to 100% Write.

On the tab "Results Display" the setting "Update Frequency (seconds)" should be set for 2 seconds to display the testing in a usefull way.

Start with 12 outstanding I/Os. Justify up and down to get highest bandwidth ("Total MBs per Second (Decimal)") before latency ("Average I/O Response Time (ms)") grows.

Test

A test series could be like this

  • Read 8 KB random I/O
  • Read 32 KB random I/O
  • Read 64 KB random I/O
  • Read 256 KB random I/O
  • Read 8 KB sequential I/O
  • Read 64 KB sequential I/O
  • Read 256 KB sequential I/O
  • Read 1024 KB sequential I/O. IOmeter changes this entry to 1 MB.
  • Write 8 KB random I/O
  • Write 32 KB random I/O
  • Write 64 KB random I/O
  • Write 256 KB random I/O
  • Write 8 KB sequential I/O
  • Write 32 KB sequential I/O
  • Write 64 KB sequential I/O
  • Write 256 KB sequential I/O

Measures

Setup a local Performance Monitor to local disk in blg-files.
Measure Logical Disk, not Physical Disk.

Manual Execution

The IOmeter status shows "Preparing Drives" while creating the file. And it will take quite some time. You should plan on a few hours.
After the file is created it is reused for each run om the same disk. Even if the testing parameters are altered.

Let each test run for about ten minutes.

Cleanup

The test file "iobw.tst" is in the root of the drive. When IOmeter is stopped this file can be deleted. The deletion requires administrator rights.
Be nice and clean up after yourself ;-)

Discussion

The IOmeter execution could be automated like I have done before with SQLIO. But as a manual evaluation is needed for the adjustments on outstanding I/Os it is practically not of much use to build an automated execution.

To be fair I have not discovered the detailed values in handling IOmeter myself. This has been delivered by Michael Frandsen (LinkedIn: michaelfrandsendk) in other context than this.