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:
Install-Module Pester -Force)
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.
I've been hard at work this weekend updating Jojoba to version 4 and which is now available on the PowerShell Gallery.
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!
Removed "gpupdate /force" from the end of the sample script. This can overwrite the changes you just made with the group policy you were trying to avoid in the first place!
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:
This array defines what functions should be accessible from outside of the module, being almost everything if not everything. You can put @("*") here but sometimes this will result in auto-complete of function names not working until you've imported the module at least once, and then one day auto-complete may just stop working entirely due to caching bugs.
This demonstrates the problem using a fresh module named AutoFunction which holds a dummy function named Start-AutoFunction:
The "best practice" and proper way of handling this then is to add an entry to the array in this file every time you create a new function. Manually. What a pain in the ass right? But there's a better way!
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.
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!
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.
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.
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.
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:
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).
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.
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.
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:
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.
DBA Tools for PowerShell. If you're not coasting to retirement then start scripting now before it's too late, PowerShell automation is a disruptive technology. Oh, and come join the SQLCommunity (#DBATools, #General and #SQLHelp) on Slack.
A year ago I wrote about installing MDS prerequisites using a PowerShell script but things have changed a little in SQL Server 2016 MDS and with ever more untested and ambiguous Microsoft documentation.
Warning: This is allegedly fixed in SQL 2016 RTM CU1. While I haven't tested that I can confirm that post-patch you'll get an MDS error requiring the service account be granted permission on an additional temp directory. I haven't documented that step below.
When setting up the web application for MDS you must specify an Active Directory service account to use for the application pool. What's hilarious about this is that this will break your MDS installation unless you also add that user as a local Administrator (!) or otherwise jump through hoops to identify what else is required (which I'm going to do below).