A Fine Slice Of SQL Server
Kevin Hill takes us through a nice use of dbatools:
My client asked me for a way to automate test restores and CheckDB either ad hoc or on a schedule, but not for certain databases that are close to or over 1TB in size.
Read on to see how Kevin pulled it off, all in under 4 hours.
Comments closedSheldon Hull takes us through using Pester to automate operations tasks:
In my example, let’s start small and say you just have PowerShell, and some servers.
What I’ve discovered is that to actual validate DevOps oriented work is completed, you typically go through the equivalent of what a Cucumber test would have. This “checklist” of validations is often manually performed, lacking consistency and the ability to scale or repeat with minimal effort.
Consider an alternative approach to helping solve this issue, and expanding your ability to automate the tedious testing and validation of changes made.
Read on for an example as well as some additional thoughts from Sheldon.
Comments closedIt has always been a problem that documentation in the source, where it should be, is not then passed into the live database when the build script is executed. In a table, you have columns, constraints and indexes that you are likely to document using line-ending comments and block comments. You probably have a big block comment at the start, explaining the table. This information should be available in the live database. Microsoft don’t have a good answer and vaguely go on about adding comments in extended properties. Well, that’s fine but it hasn’t happened, unsurprisingly: Have you ever tried to do it? It is an almost impossible task, even with SQL Doc.
My solution is to execute my finely-documented build script as usual to create the latest version of the database, and then process the same script in PowerShell to add all the comments and documentation as extended properties in the right place in the live database.
It’s an interesting approach to a classic problem.
Comments closedThe first hurdle I encountered was the fact that there was already an older version installed. After some fiddling, trying to uninstall the old modules and going through the fixed described below, I figured the documentation was probably the best start, and it was. 🙂
Nicky documents several issues but was ultimately able to pull it in.
Comments closedPatrick Gruenauer show off a pair of new operators in Powershell 7:
With PowerShell 7 new operators were introduced. We call them chain operators. Chain operators enables you to do something after doing something. They use the $? and $LASTEXITCODE variable to determine whether a command on the left hand of the pipe failed or succeded.
Let’s cover this topic by demonstrating some examples to fully understand the new pipeline technology.
This is definitely Bash-inspired and I’m happy they made this move.
Comments closedSo I figured I’d take a look and see what was happening and how we could fix it. Now I’m going to be honest with you, my usual method of debugging involves adding
Write-Host 'Hi‘, or piping objects toOut-GridView. I did start down this route, but theGet-DbaRegServerfunction calls an internal function, and things quickly got complicated.Luckily, the PowerShell extension for VSCode includes a debugger so we can level up our game and use that to track down our issues.
Click through to see how it works.
Comments closedStuart Moore introduces dbaSecurityScan:
How easy it to audit them? If someone asks you the DBA exactly who has access to object A, can you tell them? How do people get access to that object, is it via a role, a schema or an explicit permission?
Is that information in an easy to read or manipulate manner?
How do you ensure that permissions persist between upgrades? I’ve certainly seen 3rd party upgrades that have reset database level permissions. Do you have a mechanism to check every permission and put them back as they were?
We’re all doing the devops these days. Our database schema is source controlled, and we’re deploying it incrementally in pipelines and testing it. But are we doing that with our database security?
So in the classic open source way, I decided to scratch my own itch by writing something. That something is dbaSecurityScan, a PowerShell module that aims to offer a solution for all of the above.
Click through to see what dbaSecurityScan covers today, how to call it, and what you can do to get more info.
Comments closedMark Broadbent solves a problem for us:
I have been experiencing a problem for quite a while now in my current environment, in that some of our old builds cannot be deleted. When you attempt to do so it results in the following error:
One or more builds associated with the requested pipeline(s) are retained by a release. The pipeline(s) and builds will not be deleted.Many of our pipelines have undergone a lot of change over time to the degree it is not even obvious anymore why (or indeed where) these builds are being prevented from being dropped. The only thing that is clear is that until they can be, the old build definitions will remain.
Regardless of the reason why, Mark has the answer for how.
Comments closedMax Trinidad gives us a primer on SMO with Powershell 7:
There are two ways you could use SMO in PowerShell 7 (cross-platform):
1. Installing the SMO NuGet packages, two packages are requiered:
a. Microsoft.SqlServer.SqlManagementObjects Version 150.18208.0 (as of 03/23/2020)
b. Microsoft.Data.SqlClient Version 1.1.1 (recommended)2. Installing the PowerShell Module: SqlServer Version 21.1.18221 (as of 03/23/2020)
Keep in mind, once the packages and/or modules are installed, you need to update them manually.
Read the whole thing.
Comments closedPatrick Gruenauer shows how to make your Powershell functions pipeline-capable:
You probably have already created your first function in PowerShell and now you want that your function is capable of pipeline input. Right? Ok, you’ve come to the right place. In this blog post I will carry out creating a sample function that accepts pipeline input by value.
Patrick also shows us one of the earlier hang-ups of pipeline development if you don’t have the correct process blocks.
Comments closed