Cody Konior Senior SQL Server Database Administrator Spider photographer, stand-up comedian, astronomer, and occasional beekeeper.

Upgrading an expired SQL Server 2016 Evaluation Edition

It's happened to almost everyone. Someone installs Evaluation Edition and now you need to upgrade it to a licensed copy using "Edition Upgrade" in the SQL Server installer. Microsoft would have you think this is easy even if the copy has expired but the truth is far more difficult.

Are your disks formatted with UseLargeFRS?

PureStorage has a pretty cool post that mentions the importance of formatting SQL Server disks with a 64KB clusters and the /L flag (also known as the UseLargeFRS switch on PowerShell's Format-Volume cmdlet).

Why UseLargeFRS? It's to help avoid DBCC CHECKDB failures on large/busy databases. But how do you work out whether it's enabled or not? PowerShell to the rescue!

Forgettable improvements to PowerShell over the ages

I've been using PowerShell 5.0 and 5.1 for a long time now and sometimes realise I'm still thinking of the 2.0 and 3.0 era. I wanted to revisit some of the more forgettable improvements.

Something you didn't know about validating full-text indexing

My favourite way to compare technical experience with others is to ask them about the ways in which something can fail. Anyone can recite BOL but it's edge-case knowledge that shows a DBA has been hardened through trench warfare. After all if you've seen my talk about operational validation, or better yet implemented it, you'll know that at scale, rare events aren't rare.

So do you know how full-text indexing can fail? If you don't, then read on.

Validating the new receive-side scaling bug in VMware Tools

You may (not) have seen the blog today where current VMware Tools on Windows have yet another a broken receive-side scaling implementation causing random network failures. The good news is that it's not enabled by default.

Availability group replica murdered in cold blood by WSFC

This problem was first flagged by a daily operational validation test I wrote using everything in my recent webinar to health check all of my AG components. My test wrapped some of the logic of the Test-Sql* functions built into the SqlServer module:

ERRORLOG records max out at 2049 characters

Stupid question… what's the schema of a table with sys.sp_readerrorlog output? Well you might be surprised if you're used to using nvarchar(max) or nvarchar(2048).

Webinar starting in 12 hours!

My webinar on operational validation of SQL Server at scale with Jenkins and PowerShell starts in 12 hours! Sign up here if you want to be a part of it.

Nothing is ever as easy as the demo because tests are hard

I just read PowerShell, Pester, and Ola Hallengren's Maintenance Solution by @sqldbawithbeard and I'm certainly going to give it and Mike Robbins' Operational Validation framework a try soon.

I like you, but I don't like your best practice (power plans)!

I saw this tweet from the always awesome @DBArgenis and @sqldbawithbeard and couldn't help but feel a bead of sweat track down my neck because I like them, but I don't like their best practice

Supercharge your PowerShell functions with a PoshRSJob template

Recently I migrated from my own runspace module to Boe Prox's PoshRSJob which is pretty much perfect. But today I wanted to share how to integrate PoshRSJob cleanly into your functions through a default -Parallel parameter and using a template.

You can very easily modify this for your own purposes however it's even more awesome as-is if you run parallelised tests for one major input (like a computer name) but where additional information might also be passed in through object properties on a pipeline (I'll explain why you'd want to do that later in the post). Here's what it looks like:

If you don't mind me saying, PowerShell Workflows are awfully broken

Six months ago I spent a lot of time trying to get PowerShell Workflows running but eventually gave up in frustration. Now I've revisited the issue in PowerShell 5 and found the root cause seems to be that Write-Verbose and Write-Debug can cause untrapped (and unexplained) early terminations and that the behaviour is almost arbitrary depending on exactly how and when they are called.

Recipe for an awesome (free) SQL Server workstation

  1. Windows 10. Where we're going we don't need legacy software, but if you do, then download PowerShell 5.0 instead.

  2. SQL Server Management Studio 2016 and SQL Sentry Plan Explorer (Free). Don't forget that you should use Import-Module SqlServer instead of SQLPS for new work.

    Did you know SSMS now has a built-in plan comparison tool? But SSMS also has query plan display bugs so for the time being you'll still need both

  3. DBA Tools for PowerShell. If you're not coasting to retirement then start scripting now before it's too late, PowerShell automation is a disruptive technology. Oh, and come join the SQLCommunity (#DBATools, #General and #SQLHelp) on Slack.

Revisiting SQL Server 2016 Master Data Services installation prerequisites

A year ago I wrote about installing MDS prerequisites using a PowerShell script but things have changed a little in SQL Server 2016 MDS and with ever more untested and ambiguous Microsoft documentation.

SQL Server 2016 Master Data Services service accounts are broken

Warning: This is allegedly fixed in SQL 2016 RTM CU1. While I haven't tested that I can confirm that post-patch you'll get an MDS error requiring the service account be granted permission on an additional temp directory. I haven't documented that step below.

When setting up the web application for MDS you must specify an Active Directory service account to use for the application pool. What's hilarious about this is that this will break your MDS installation unless you also add that user as a local Administrator (!) or otherwise jump through hoops to identify what else is required (which I'm going to do below).

Yellow Moth

Yellow Moth photo

Carpenter Ant Queen

Carpenter Ant Queen photo

Using PowerShell to generate a bulk insert format file

There are some limitations with doing bulk inserts into SQL Server that can make it a pain. Some of the biggest ones:

  • You can't import if you have columns that change order in the source file.
  • You can't import if you have two columns with the same name in the source file.
  • If you use a non-standard delimiter like tab you may need a Schema.ini file with an entry for every file you're going to load up.

The main way around this is to do a proper bulk insert with a corresponding format file but you usually don't get provided those and they're difficult to create when they have to manage each of the above situations. Also they look extremely hard to make and understand.

SQL Server 2016 GDR update

Upon release of RTM one of the first issues indicated in the release notes was that a Visual C++ 2013 update was required before installation. Their recommendation to determine whether it's required is to manually check DLL versions but it's much more easily done through PowerShell.

SQL Server 2016 demonstrating a Bulk Insert crash dump

I sure hope you didn't jump feet first into the SQL 2016 RTM because it has a slew of problems. One of them is a crash dump on very simple bulk inserts. I've logged a Connect item so please vote for it.