A Fine Slice Of SQL Server
Andrew 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 closedOne 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.
Comments closedStuart Moore describes the updated Restore-DbaDatabase cmdlet:
The biggest change is that Restore-DbaDatabase is now a wrapper around 5 public functions. The 5 functions are:
- Get-DbabackupInformation
- Select-DbabackupInformation
- Format–DbabackupInformation
- Test–DbabackupInformation
- Invoke-DbaAdvancedRestore
These can be used individually for advanced restore scenarios, I’ll go through some examples in a later post.
Stuart then provides additional information at the various steps, explaining at a high level how things work.
Comments closedRob Sewell shows us how to compare SQL Agent jobs across Availability Group replicas:
On the plane home from PAS Summit I was sat next to someone who had also attended and when he saw on my laptop that I was part of the SQL Community we struck up a conversation. He asked me how he could compare SQL Agent Jobs across availability group replicas to ensure that they were the same.
He already knew that he could use Copy-DbaAgentJob from dbatools to copy the jobs between replicas and we discussed how to set up an Agent job to accomplish this. The best way to run an Agent Job with a PowerShell script is described here
Maybe I’m out in la-la land, but wouldn’t this be a perfect use case for MSX/TSX?
Comments closedConstantine Kokkinos shows how easy it is to use dbatools to script out database objects:
@brianszadek says: I am looking to automate the scripting of objects with PowerShell for easier source control integration.
I thought this would be a little bit easier, but it looks like the SMO way of doing this is still fairly complicated. Maybe something I can look at simplifying for the project in a bit
If you want to get your database into source control but feel like it’s going to be a time-consuming challenge, this isn’t the time-consuming part.
Comments closed