Cody Konior Senior Database Administrator

Upgrading SQL 2005 to SQL 2014

I had an interesting discussion recently about whether this was still supported or not; it definitely was in SQL 2012 but in SQL 2014 it wasn't so clear because there are two sources of Microsoft documentation that are in conflict.

Iterating years in PowerShell

I was running a data extraction process which accepted an ISO format start date and end date but because of the time span involved it would timeout before completion.

Monty Hall simulation in PowerShell

Imagine this probability puzzle:

Check the error log for failed operations immediately before this error message

Have you seen this error in the SQL Server error log before?

Will alter procedure result in an execution failure?

It is obvious if you have scripts that are like IF EXISTS: DROP: CREATE PROCEDURE, and run this on a production system that is calling those procedures at the same time, that some will fail when the hit that period between the DROP and the CREATE.

Caveat of sp_rename

I was using sp_rename lately and came across this article that goes into the pitfalls.

Trigger deadlock testing with PowerShell

Call me crazy but triggers are usually bad news. In my experience they tend to rise to the top of the queue for bug fixing more frequently than their relatively small SLOC would belie, and often have performance problems that don't become apparent until months or years down the track as record counts increase.

Testing logins and passwords from a stored procedure

I recently wanted to know how given a table of user names (logins) and passwords how you could verify that list can connect to the local server from within a stored procedure. This is what I came up with, first the setup.

Taking down production using nested transactions

Most DBAs will have processes they need to run over a series of databases, and do so either with sys.sp_msforeachdb (which is bad, bad, bad because it can skip databases) or their own stored procedure which uses a static cursor over sys.databases.

How to add Brent Ozar or GoToWebinar calendar entries on a Mac

TL;DR version: If you encounter problems with GoToWebinar calendar entries on a Mac remove the ORGANIZER line from the file and try again. The full explanation follows.

Load Balancing SQL Server Backups


I recently saw an issue where a large number of databases were consolidated onto a single server. Though they were backed up on a proper full -> differential -> log cycle, and the starting day for each cycle was roughly spread evenly over the week, the large variance of characteristics between each database resulted in backups running far too long on some days and terminating too early on others.

Checking for null in PowerShell

Updated 29 Sep 2016: Fixed a bug in IsNull where Bruno Martins pointed out it returned True for 0. This said… this post is super old… and should be ignored.

Walking through SQL Server cursor design decisions

The Brief

Stop me if this sounds familiar.

How to get Kerberos for SQL Server running, fast

This is the accumulated hands-on knowledge of days of work and hours of technical Kerberos videos that I didn't (and still do not) understand.

PowerShell script to fix the end tags in FireFox bookmarks exported as HTML

I exported my bookmarks in HTML format from FireFox and wanted to do some processing on them, but parsers like the HTML Agility Pack could not handle it properly because the format does not include DT (Url blocks) or DD (Description blocks) end tags, and that in turn results in massively nested XPath lists.

A script to convert the JavaScriptSerializer JSON format to a PowerShell format

Continuing on from earlier, after I discovered that FireFox includes far more bookmark information in its native format (JSON) I wanted to create a parser for that instead. Normally PowerShell provides you two simple ways of doing this: ConvertFrom-Json and ConvertTo-Json.

PowerShell and Get-ChildItem problems

Today I needed to modify a function to remove some bad files that were causing an import to fail across a long list of servers (using Invoke-Command). Here's a sample I've constructed to show and test the removal part.

PowerShell FTP

I've tried a few different options for FTP with PowerShell and the best one so far is WinSCP with its .NET assembly.

Comparing varbinary data types in SQL Server

This is something to be very, very wary of when storing data blobs in SQL Server. If you compare varbinary fields they will match when one field has one or more extra zero bytes at the end, even if they are different lengths. Observe:

How to construct a string in PowerShell with a certain number of spaces

This is easy.