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.

2016-07-27

Drive info

The CIM CmdLets in PowerShell I think is a collection of great new features and a long needed replacements to WMI.
One very useful CmdLet is Get-CimAssociatedInstance that makes the hierarchy of classes nice and easy.
This I had use for lately for checking the drives on some new database servers. That was large servers with several drives on various storage technologies.

By travelling through the associated CIM objects I was able to get from the drive with a drive letter to the physical diskdrive.
The chain could be described like:

Drive or Volume - Logical Disk - Disk Partition - Disk Drive

The Volume (Win32_Volume) has no direct association to the Logical Disk (Win32_LogicalDisk), but the Logical Disk has the drive letter as key in the attribute DeviceID. The Volume object still holds some interesting attributes like BlockSize.

With a Logical Disk object I got the Disk Partition (Win32_DiskPartition). And from there I got the Disk Drive (Win32_DiskDrive).
The complete chain on the C-drive can look like this in PowerShell:

[String]$DriveLetter = 'C'

$WqlVolume = "SELECT * FROM Win32_Volume WHERE DriveLetter='$($DriveLetter):'"
$CimVolume = Get-CimInstance -Query $WqlVolume
$CimVolume | fl *

$WqlLogicalDisk = "SELECT * FROM Win32_LogicalDisk WHERE DeviceID='$($DriveLetter):'"
$CimDisk = Get-CimInstance -Query $WqlLogicalDisk
$CimDisk | fl *

$CimDiskPartition = Get-CimAssociatedInstance -InputObject $CimDisk -ResultClassName 'Win32_DiskPartition'
$CimDiskPartition | fl *

$CimDiskDrive = Get-CimAssociatedInstance -InputObject $CimDiskPartition -ResultClassName 'Win32_DiskDrive'
$CimDiskDrive | fl *


Now all the info is stored in variables, and can be accessed and processed directly.
If you like a selection of data can be combined in a custom PowerShell object (PSObject).

It is obvious that the classes for the more physical elements were defined for spinning plate disks, e.g. a SSD on SATA has tracks and cylinders according to Win32_PhysicalDrive.

2015-12-25

SQL Server 2016 installation notes

These are some personal notes I have made on installing SQL Server 2016. I intent to update the notes when there are new releases or I have looked deeper into a detail.

Choices

I have selected these SQL Server features:
  • Database Engine Services, no sub-features (FEATURE=SQLENGINE).
  • Client Tools Connectivity (FEATURE=CONN).
  • Client Tools Backwards Compatibility (FEATURE=BC).
  • Client Tools SDK (FEATURE=SDK).
  • Management Tools - Complete (FEATURE=ADV_SSMS).
  • SQL Client Connectivity SDK (FEATURE=SNAC_SDK).
SQL Server Management Studio (SSMS) is with RC0 not part of the general setup. This I look more into in a later section of this post.
The installation is with these products:
  • Windows Server 2012 R2 virtualized on vmware Workstation 12 Pro.
  • SQL Server 2016 RC1.
  • Windows 10 Pro virtualized on vmware Workstation 12 Pro.

Preparations

Before installation I make some notes on what is installed. How to do this is nicely described by Marc Carter in the blog entry „Use PowerShell to Find Installed Software“.
Also I make notes on which services are running. This can be collected with the cmdlet Get-Service.

Then I check if .NET 3.5 is enabled on the computer as this is a requirement for my SQL Server installation. If .NET 3.5 is not installed I have to do so.
This I have to describe more detailed...

Storage

There are several new features on storage configuration with SQL Server 2016, like configuring multiple datafiles for tempdb on installation. To test a SQL Server installation with multiple drives I have made these drives for the Windows Server installtion:
  • C-drive: System drive with Windows Server and basic application files.
  • D-drive: Program drive with application files. Also the SQLAdmin folder for local DBA files.
    • GPT partition.
  • E-drive: Data drive with data files for user databases.
    • GPT partition.
    • NTFS allocation unit is set to 64 KiB (KB).
  • F-drive: Transaction log drive with transaction log files for user databases and tempdb.
    • GPT partition.
    • NTFS allocation unit is set to 64 KiB (KB).
  • G-drive: tempdb drive with data files for tempdb database.
    • GPT partition.
    • NTFS allocation unit is set to 64 KiB (KB).
  • H-drive: Backup drive for any backup file.
    • GPT partition.
The Z-drive is reserved for removable drive.

Installation

The SQL Server installation is usually done by the wizard or by command-line. This I will look into the details later.

In this case I use the default service accounts given by the SQL Server installation, but I change the SQL Server Agent Startup Type to Automatic. These issues are subjects for many great discussions, but not here and now.
SQL Server 2016 gives the possibility to enable Database Instant File Initialization on installation, but I have chosen not to. The subject is greatly described by Kimberly Tripp in the blog post „Instant Initialization – What, Why and How?“.

The default collation for the SQL Server installation is still the SQL collation „SQL_Latin1_General_CP1_CI_AS“ where the recommendation for some years has been a Windows collation. Usually I recommend the SharePoint collation „Latin1_General_CI_AS_KS_WS“ to ensure a better information integration. In this case I changed to the collation „Latin1_General_100_CI_AS_KS_WS_SC“ to get the Special Characters available.

With SQL Server 2016 it is now possible to configure tempdb on installation. This is a really great feature – that I have not used in this case.

The installation took about 44 minutes by the summary log file.

Control

Like before the installation I note what is installed and which services that are running.
Also I take a look into the summary log file.

SQL Server Management Studio

As mentioned before SQL Server Management Studio (SSMS) is with RC0 removed from the general setup, and placed in "Tools\SSMS-Full-Setup.exe". When running the GUI setup, there are no selection of features.
With RC1 is SSMS removed from the installation set, but can be downloaded as described in BOL „Download SQL Server Management Studio (SSMS)“. There is a item in the setup GUI called "Install SQL Server Management Tools", but it just open the webpage I just mentioned.
As SSMS is removed from the SQL Server setup, the release notes for SSMS are also removed from the general SQL Server release notes. You might like to take a quick look at the SSMS Release Notes in "SQL Server Management Studio - Release Notes".

Post-Configuration

Usually I stop and disable the SQL Server VSS Writer service as I take backup with T-SQL through the Database Engine:
Get-Service -Name SQLWriter | Set-Service -StartupType Disabled -Status Stopped

Finally I run Windows Update to get the latest patches for the new components on the computer. In this case there were a Security Update and a regular Update for Visual Studio 2010 SP1 (KB2645410 & KB2635973).