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

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.

Jumping Spider

Jumping Spider photo

Sac Spider

Sac Spider photo

Wasp

Wasp photo

Cobweb Spider

Cobweb Spider photo

A few quick notes on SQL Server 2012 slipstreaming

SQL Server 2008 allowed you to slipstream service packs and cumulative updates into the installation media. In 2012 an additional and better method was created called "Product Updates" and (later) "Servicing Updates". PSS wrote a decent blog post introducing the feature.

Leaf Curling Spider

Leaf Curling Spider photo

Unidentified Spider

Unidentified Spider photo

Jumping Spider

Jumping Spider photo

Three cases where Ola Hallengren's Maintenance Solution won't backup a database

We love Ola Hallengren's Maintenance Solution but you should always always double-check either the msdb backup history or the master.dbo.CommandLog table to make sure any important backup was taken. This is especially important if you trigger it manually and are relying on human input to get the parameters right.

Here are three easy to miss cases where the scripts won't backup a database. These absolutely, definitely, aren't bugs, they're idiosyncrasies with the underlying backup command and (sometimes) how the script works. But they're also much easier to miss in the verbose output of the script.

This is how to add a database to an Availability Group, in reverse!

Normally when you add a database to an Availability Group (AG) you need to start with the database on the primary node. You restore backups With Norecovery on the secondary (either manually or with the GUI), add the database to the AG on the primary, alter the database on the secondary to reference the AG, and your job is done.

Sometimes after all the preparation you discover the wrong node is the AG primary, and you can't justify failing everything over just to add one new database in. But if you can tolerate a short outage to the database you're adding, then you can add it to the AG "in reverse" using a tail-log backup!

Before migrating to Availability Groups check your log backups

Warning: Please read this thread first which indicates sys.fn_dump_dblog is an internal undocumented command which can cause serious issues due to a threading bug. It's "less buggy" as of SQL 2012 SP2 and SQL 2014.

Now on with the fun and possibly dangerous code you're totally going to run only in unimportant environments that you can restart afterwards!

Identify agent jobs running retrospectively

When investigating an issue one of the first things you might check for is which agent jobs were running at the time. Assuming your job history retention period is long enough, here's one way to do it.