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

SCOM configuration script for low-privilege environments

Updated 2016-02-09: While tracing a server today I saw some queries being sent from SCOM accessing a table which is not documented below. I've written a note about it on the TechNet forums but you may want to grant access to msdb.dbo.syspolicy_policy_execution_history_details_internal in addition to what I've listed below until it's fixed.

Burrowing Wolf Spider

Burrowing Wolf Spider photo

How to reset a Master Data Services service account password

Master Data Services must be run under a domain account and the credentials provided are used (in part) by configuring an IIS Application Pool to use them; which means one day the passwords are likely going to expire and need to be reset in IIS.

How to fix Integration Services when it won't start after patching

After server patching you might find that the Integration Services service will fail to start; not just SQL 2005 SP1 but others too. This is described in a Microsoft knowledge base article. The problem is that applying those instructions is a manual and error prone process.

How to slipstream SQL Server 2008 R2

There are two invaluable references on how to slipstream SQL Server 2008 R2 service packs and cumulative updates.

Out-File and Excel

In PowerShell if you have output a CSV (comma separated values) file and open it Excel only to see it all munged onto one line, then you're probably using the Out-File cmdlet instead of Set-Content.

Auditing Windows security policy settings with PowerShell

Updated 29 Jul 2015: Improved property name matching in the script and added an additional recommended settings spreadsheet.

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 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: