Cody Konior Senior Database Administrator

Installing prerequisites for Master Data Services using PowerShell

Microsoft has a knowledge article about the Web Application requirements for Master Data Services, which describes a massive list of features and roles that need to be installed on Windows Server.

Master Data Services Silverlight error

This is a healthy server running SQL Server 2014 and Master Data Services.

SQL Server security and renamed logins

I've done some testing with what happens to Windows accounts which have been renamed after being added to SQL Server. I'm going to demonstrate using local user accounts and groups though the same behaviour will be apparent in Active Directory.

Preparing agent jobs for AlwaysOn Availability Groups

When moving application databases to AOAGs they often have a bunch of jobs that go with them (usually SSIS ETL packages). Most vendors leave these to fail whenever respective nodes aren't the primary and this leads to a messy system with spurious errors.

Experiments with master.sys.xp_delete_file

I discovered some servers a while ago that had a real problem, hundreds of thousands of maintenance plan text log files (and also agent job step output files) hoarded into the ERRORLOG directory.

Check if Database Mail is running

It's great to have SQL Server Agent Alerts set up to notify the DBAs when something is broken, but if you have emails set to send it's not much use if Database Mail isn't running; and it's not crazy that sometimes it can just stop and not start up.

Login errors with syspolicy_purge_history

In SQL Server there's a default SQL Agent job called syspolicy_purge_history. There's a good description of it here and another one here but it's also fairly well known for having a bunch of problems.

How to restart the default trace

While logging onto a SQL Server 2008 instance I went to look for something in the default trace and found that it wasn't running. The disk had filled previously, which stops the default trace, and it hadn't been restarted afterwards.

How to convert an SQL Server login SID to a readable string

Each SQL Server login has a SID associated but stored in Varbinary instead of the readable Microsoft string format; the latter being important if you need to compare SQL Server server principals against SIDs from Active Directory.

What's wrong with SQLPS?

Updated 2017-04-26: This appears to be fixed in SSMS 2017.01 and the PowerShell Gallery version of the SqlServer module.

Updated 2016-01-13: Added SQL Server 2016 comments and created a better, safer method of modifying the broken DLL.

I've seen a few reports of an error occurring when you use the SQL Server 2014 PowerShell provider to connect to an SQL Server 2012 instance. I have also encountered a few issues like this and did some investigation into the root cause.

Master Data Services error

I've been practicing with Master Data Services 2012 SP1 today by following along Profisee's free training course and came across two MDS bugs.

Scraping for iPhone Availability

When the iPhone 6 Plus came out availability was extremely limited, and you were meant to login to an Apple page around midnight every night to check for and reserve stock at your local store.

A common mistake when splitting strings

This is a mistake I make from time to time to my own detriment when making ad-hoc changes. Take a large string usually cut and pasted from somewhere that you want to break into an array of lines:

PowerShell with a focus on automation

Thanks to everyone that came to see my presentation at the Perth SQL Server User Group! Here was the recorded video with annotations and notes for all the miscellaneous errors that occurred during the demos.

RBAR and UDFs

I was reading this great article on Hidden RBAR: Triangular Joins from 2007 and a similar one on CTEs (who would have thought something everyone does is such bad practice?!)

Microsoft Exam 70-465 (2014) Study Guide

The official exam description for Designing Database Solutions for Microsoft SQL Server 2012 was pretty thorough but has an additional annotated list of changes covering the Server 2014 additions made in April 2014.

Microsoft Exam 70-464 (2014) Study Guide

This is a full list from the Microsoft website integrated with their published April 2014 update.

Testing some NOLOCK and READPAST

I'm doing some study on locking and read this blog post about NOLOCK and READPAST in SQL Server 2005. The test was straightforward:

Children, Hot Dogs, and Body Bags

I really messed up the opening and the ending but the middle was a pretty solid effort. I hadn't been up for 3 years and really enjoyed giving it another try. The other comedians backstage were really cool and my spot was about middle of the pack for the 16 acts that went up that night.

MSDAINITIALIZE minimum required permissions

I was setting up a new SQL Server 2014 (x64) instance and wanted to read some spreadsheets using SQL. This is fairly easy to do: