A Fine Slice Of SQL Server
Richie Lee has a new set of functions for dbatools:
There are 3 modules included. I’m going to write a bigger piece about these functions elsewhere, but for now here is a link to the documentation –
Read on for more information and a link to the source code if you’re interested in learning more.
Comments closedThomas Raynor provides a quick tip for string formatting in Powershell:
And, for some reason, instead of the default output which is formatted like a table, I want output presented like this.
This is a silly example, but notice that even though there are extensions of varying length (.ps1 and .dll are four characters including the dot, and .xlsx is five), all of the “file extension: <number>” is aligned.
How’d I do that?
Read on to learn how.
Comments closedShane O’Neill wants to know what’s in your Powershell profile:
This brings me back to the main point. My profile does 3 things
- Changes the default colour of error and warning messages,
- Sets an alias for notepad to “n” since I use it so much
Set-Alias -Name n -Value notepad, and- loads up the dbatools prompt
I don’t import any modules because the two that I use the most are updated so frequently plus I’m currently using PowerShell 5 so they get automatically loaded when I type in one of their commands.
If you don’t already have a profile, read on and see how you can do it. And over-do it if you’re not careful.
Comments closedDrew Furgiuele has a rather interesting library that he’s released to the general public:
The base return is the TSQLFragment object, which in turn has a Batches object, which in turn holds… well it can hold a lot of different things. When the text is parsed, it will determine what type of object to return based on the statement it determines it is. For example, if it’s an insert statement it will be a certain type of object with a given set of properties and methods, and if it’s, say, a create index statement you’ll get different properties, such as which table or view is getting the index along with the indexed columns and included columns. It really is interesting.
But interesting can a double-edged sword: since the statement object that gets returned can be different for each parsed piece of code, that means to set up any type of intelligence around the stuff we’re dealing with, we need to check for very specific objects.
Unfortunately, I never got past the first animated GIF, whose subtitle was wrong. You, however, should read the whole thing.
Comments closedAndrew Pruski shows how to use Pester to audit SQL Server configuration settings:
One Pester test running!
What I like about this is that it can be easily dropped into a job scheduler (e.g.- Jenkins) and then you’ve got a way to routinely check (and correct) all the configuration settings of the SQL instances that you monitor.
Pester would not have been my first thought for configuration checking, but it does serve as another useful option.
Comments closedShane O’Neill shows off a Powershell script which allows you to simplify performance testing:
Apart from catching up on news during my commute I only really use notifications for a certain number of hashtags i.e. #SqlServer, #tsql2sday, #sqlhelp, and #PowerShell.
So during work, every so often a little notification will pop up on the bottom right of my window and I can quickly glance down and decide whether to ignore it or check it out.
That’s what happened with the following tweet:
Click through for Shane’s demo.
Comments closedStuart Moore shows off a few examples of advanced Restore-DbaDatabase usage:
No matter how hard the dbatools; team tries, there’s always someone who wants to do things we’d never thought. This is one of the great things with getting feedback direct from a great community. Unfortunately a lot of these ideas are either too niche to implement, or would be a lot of complex code for a single use case.
As part of the
Restore-DbaDatabasestack rewrite, I wanted to do make things easier for users to be able to get their hands dirty within the Restore stack. Not necessarily needing to dive into the core code and the world of GitHub Pull Requests, but by manipulating the data flowing through the pipeline using standard PowerShell techniques, all the while being able to do the heavy lifting without code.
Click through for several examples.
Comments closedRob Sewell shows how to iterate through a collection of Pester tests:
The problem with Test Cases is that we can only easily loop through one collection, but as Pester is just PowerShell we can simply use ForEach if we wanted to loop through multiple ones, like instances and then databases.
I like to use the ForEach method as it is slightly quicker than other methods. It will only work with PowerShell version 4 and above. Below that version you need to pipe the collection to For-EachObject.
Check it out for a good explanation of running groups of Powershell tests.
Comments closedFrank Gill has written a script to perform an Availability Group failover using Powershell:
The function takes a replica name as input and queries system tables for Availability Groups running as secondary that are online, healthy, and synchronous. For each AG found, the function generates an ALTER AVAILABILITY GROUP statement. If the -noexec parm is set to 0, the command will be executed. If -noexec is set to 1, the command will be written out to a file.
When writing the function, I started out trying to use the native PowerShell Availability Group cmdlets. After several false starts, I found it easier to develop the T-SQL code in Management Studio and use Invoke-Sqlcmd to execute the code. The code is available below. I hope you can put it to use.
Click through for the script.
Comments closedJason Squires has a series on database migrations using dbatools. The first part covers capacity planning:
Great results, I now know that I cannot move the database from one server to another on any of the drives shown without expanding the drive volume. Even though this was an awesome output, I wanted more. I know my data and log drives that I wanted to focus on so I decided to dump everything into an array.
The second part is the actual migration:
In this post, I am going to show you the steps that I decided to take to do this particular migration. This migration was going to be a bit different. I am merging two systems together, so I had to really think the process through and ensure I get everything I needed. I also did not want to rollback due to the systems being legacy and unsupported. Some of the hosts were going to be pushing storage limits so I had to ensure everything went flawlessly so I could decommission the legacy systems quickly.
Both of these have scripts and explanations attached, making it easy to follow along.
Comments closed