Cody Konior Senior Database Administrator

Exporting configuration data from SQL Sentry

SQL Sentry is a pretty cool monitoring system but there's a downside to having so many knobs to turn: it's next to impossible to find out which knobs have been turned. Let's look at an example.

First I built a five replica multi-subnet Availability Group using my PowerShell module OftenOn. Then I've manually installed SentryOne and watched all the replicas while splitting them between two sites; Seattle and Dallas. I could only get an evaluation license which covers one replica so the rest are not being actively monitored but that's not important at the moment.

Don't go to the PASS Summit speed networking session

Up until now I've been enjoying my first PASS Summit and doing my own light networking.

  • At breakfast and lunch there's a sea of open tables. I've found it's fine to sit down and introduce yourself to strangers at a distance, share what you do, and maybe hand them a card so they can reach out if they want to.
  • There was a nice networking afternoon at Top Pot doughnuts sponsored by MidnightDBA.
  • And a night of networking dinners organised by Lisa Bohm.

These were all great. I'm a bit introverted but I love networking! There's no pressure on either side, you're all surrounded by co-workers or PASS buddies, and if you wanted to stand up and leave nobody would particularly notice unless you wanted them to. Easy.

Not so with this one specific speed networking session sponsored by Solarwinds:

SMO "leaks" SQL login passwords (in memory) and (some) SecureStrings in clear-text

Updated 2018-11-04: Expanded test code, renamed the title of the post so it's clearer.

SMO (SQL Server Management Objects) are the .NET classes underpinning SSMS (SQL Server Management Studio) and all good PowerShell that interfaces with SQL Server.

SMO connects to SQL Server using the ADO.NET SQLClient library which has 13+ years of features which help mask the passwords you pass in for SQL Authentication. SMO bypasses some of those features to often leak the passwords in clear-text.

We'll prove it through repeatable tests that can be used to track if Microsoft fix the problem or not.

Does the log file growth for multiples bug still exist in SQL Server 2012?

Nope.

Availability Group round-robin read-only routing isn't magic

I've been hearing about round-robin read-only routing ever since SQL 2016 came out but whenever I tried to test if it's working it never seemed to be. But now I know exactly how it works and there's a few loopholes where it may not trigger, and they're not the documented ones you're thinking of.

To test the limits of it you're going to need:

  • PowerShell 5.1
  • Pester 4 (Install-Module Pester -Force)
  • DbData (Install-Module DbData -Force)

I'll explain any of the Pester and DbData bits along the way so don't worry. They're minor framework stuff.

Introducing Jojoba 4, the multi-threaded testing framework for PowerShell

I've been hard at work this weekend updating Jojoba to version 4 and which is now available on the PowerShell Gallery.

Populating PowerShell module FunctionsToExport automatically

Updated 2018-02-25: Maybe don't do this if you have special configuration data in the PSData.PrivateData section. Update-ModuleManifest (and New-ModuleManifest) remove many properties from the PrivateData hash table and puts them in the PSData hash table instead. Awful!

If you've written a PowerShell module you'll be familiar with the FunctionsToExport portion of the .psd1 module manifest that starts out like this:

Jumping Spider

Jumping Spider photo

Upgrading an expired SQL Server 2016 Evaluation Edition

It's happened to almost everyone. Someone installs Evaluation Edition and now you need to upgrade it to a licensed copy using "Edition Upgrade" in the SQL Server installer. Microsoft would have you think this is easy even if the copy has expired but the truth is far more difficult.

Are your disks formatted with UseLargeFRS?

PureStorage has a pretty cool post that mentions the importance of formatting SQL Server disks with a 64KB clusters and the /L flag (also known as the UseLargeFRS switch on PowerShell's Format-Volume cmdlet).

Why UseLargeFRS? It's to help avoid DBCC CHECKDB failures on large/busy databases. But how do you work out whether it's enabled or not? PowerShell to the rescue!

Forgettable improvements to PowerShell over the ages

I've been using PowerShell 5.0 and 5.1 for a long time now and sometimes realise I'm still thinking of the 2.0 and 3.0 era. I wanted to revisit some of the more forgettable improvements.

Something you didn't know about validating full-text indexing

My favourite way to compare technical experience with others is to ask them about the ways in which something can fail. Anyone can recite BOL but it's edge-case knowledge that shows a DBA has been hardened through trench warfare. After all if you've seen my talk about operational validation, or better yet implemented it, you'll know that at scale, rare events aren't rare.

So do you know how full-text indexing can fail? If you don't, then read on.

Validating the new receive-side scaling bug in VMware Tools

You may (not) have seen the blog today where current VMware Tools on Windows have yet another a broken receive-side scaling implementation causing random network failures. The good news is that it's not enabled by default.

Availability group replica murdered in cold blood by WSFC

This problem was first flagged by a daily operational validation test I wrote using everything in my recent webinar to health check all of my AG components. My test wrapped some of the logic of the Test-Sql* functions built into the SqlServer module:

ERRORLOG records max out at 2049 characters

Stupid question… what's the schema of a table with sys.sp_readerrorlog output? Well you might be surprised if you're used to using nvarchar(max) or nvarchar(2048).

Webinar starting in 12 hours!

My webinar on operational validation of SQL Server at scale with Jenkins and PowerShell starts in 12 hours! Sign up here if you want to be a part of it.

Nothing is ever as easy as the demo because tests are hard

I just read PowerShell, Pester, and Ola Hallengren's Maintenance Solution by @sqldbawithbeard and I'm certainly going to give it and Mike Robbins' Operational Validation framework a try soon.

I like you, but I don't like your best practice (power plans)!

I saw this tweet from the always awesome @DBArgenis and @sqldbawithbeard and couldn't help but feel a bead of sweat track down my neck because I like them, but I don't like their best practice

Supercharge your PowerShell functions with a PoshRSJob template

Recently I migrated from my own runspace module to Boe Prox's PoshRSJob which is pretty much perfect. But today I wanted to share how to integrate PoshRSJob cleanly into your functions through a default -Parallel parameter and using a template.

You can very easily modify this for your own purposes however it's even more awesome as-is if you run parallelised tests for one major input (like a computer name) but where additional information might also be passed in through object properties on a pipeline (I'll explain why you'd want to do that later in the post). Here's what it looks like:

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.