And, for some reason, instead of the default output which is formatted like a table, I want output presented like this.
123 .ps1 file extension: 11.xlsx file extension: 3.dll file extension: 1
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.
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.
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.
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.
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.
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.
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.
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.
Jason 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.
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.
One barrier to entry is that the initial setup can be a little daunting, especially if you use a local connection groups or central management servers to keep track of registered connections in SQL Server Management Studio. You’d be in for a lot of manual clicking and typing of connections if you have a lot of saved connections. But there’s a better way: you can import all that saved information right into SQL Operations Studio, and it’s pretty painless, too. Buckle up, because this involves a little knowledge of how settings are saved in Operations Studio, and how we can quickly get saved connection information out of SSMS and into your new application. Spoiler alert, we’re going to use PowerShell.
I’d love to see CMS support in SQL Operations Studio. In the meantime, this is a more or less reasonable alternative, depending upon how many servers you have and how frequently they change.