Cody Konior Senior Database Administrator

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.

SQL Server 2016 MDS upgrade errors

If you're doing an upgrade of the Master Data Services database to SQL 2016, there were some radical schema changes inside, and the upgrade may fail with an error:

Be careful if you've manually edited your SQLPS files

The SQLPS module has been slow to load for years now and has finally been fixed in the April 2016 release. But most of us couldn't wait a few years and edited the SqlPSPostscript.ps1 file that sits in the module directory.

Huntsman Spider

Huntsman Spider photo

Making SMO faster while avoiding an undocumented bug

Jon Sayce wrote an article in 2008 on how to improve SMO performance using the SetDefaultInitFields method on the Server object to pull back more than one property at a time; Diana Moldovan explored it a little further here. Unfortunately this setting can trigger a bug that cost me a few hours today.

Don't forget to reboot after installing SQL Server 2016 CTP 3.3

On a SQL 2016 CTP 3.2 server I was writing PowerShell scripts to bulk copy data into a database with simple ADO.NET classes. It was working perfectly fine to start with.

Wolf Spider

Wolf Spider photo

When a login looks fine but cannot log in

Updated 2016-02-09: This also applies when granting database-level permissions, not just roles. Added an example at the end. Also fixed up the final search procedure for servers with case-sensitive collations.

Sometimes after provisioning a login and user it cannot access a database even though everything looks fine, and it's inevitably caused by a missing connect permission on that database. I encountered the issue this week and after having a read of a post by Derek Hammer I wanted to test it for myself also.

Prowling Spider

Prowling Spider photo

Wolf Spider

Wolf Spider photo

Constructing a SQL Server 2016 temporal table using SMO

Temporal tables are arguably one of the coolest new features in 2016 that will finally put the nail into the coffin of homemade change tracking with triggers. Today I had to build one of these tables programmatically using SMO (SQL Server Management Objects) and could not find an existing demo; so I made one!

This is how to fix R Services after an in-place SQL Server 2016 CTP 3.2 upgrade

If you were using CTP 3.0 and later ran an in-place upgrade to CTP 3.2 this will silently break R Services. Uninstalling and reinstalling the R component will not fix the problem, but it can be fixed. There are a few interrelated issues here so bear with me.