A Fine Slice Of SQL Server
Chrissy LeMaire shares some great community-driven Powershell modules:
dbops
dbops was created by Kirill Kravtsov.
dbops is a Powershell module that provides Continuous Integration/Continuous Deployment capabilities for SQL database deployments.
It is based on DbUp, which is DbUp is an open source .NET library that helps you to deploy changes to SQL Server databases. dbops currently supports both SQL Server and Oracle.
Read on for links to several more projects.
Comments closedShane O’Neill shows off how easy it is to add confirmation checkpoints into Powershell code:
Finally it works… BUT…
- It’s 26 lines long for this piece of code. If we have multiple then this is going to blow up size wise, and
- There’s definitely more but I think I’ve gotten the point across (or I hope I have).
So let’s try and use what’s built in to PowerShell.
The built-in version is 3 lines of code and provides more functionality. You probably want to use that version; click through to see this all in action.
Comments closedCurrently, the output from the following commands is supported:
- Get-DbaAgentJobHistory
- Get-DbaBackupHistory
You will run the above commands as you would normally do but pipe the output to
ConvertTo-DbaTimeline, the same way as you would with any otherConverTo-*PowerShell function. The output is a string that most of the time you will save as file using theOut-Filecommand in order to open it in a browser.
Then, with the ConvertTo-DbaTimeline cmdlet, you can convert that into an HTML page which looks pretty good.
Rob Sewell shows us how to automate Power BI Report Server deployments:
But I dont want to have to do this each time and there will be multiple pbix files, so I wanted to automate the solution. The end result was a VSTS or TFS release process so that I could simply drop the pbix into a git repository, commit my changes, sync them and have the system deploy them automatically.
As with all good ideas, I started with a google and found this post by Bill Anton which gave me a good start ( I could not get the connection string change to work in my test environment but this was not required so I didnt really examine why)
I wrote a function that I can use via TFS or VSTS by embedding it in a PowerShell script.
Click through for the script.
Comments closedChrissy LeMaire explains what $PSDefaultParameterValues is and places where she finds it useful:
$PSDefaultParameterValues is a hashtable available in PowerShell that can set defaults for any command that you run. In it’s simplest form, setting a default parameter value can look like this:
After running the above code, Get-DbaDatabase will show verbose output every time it’s executed, without me having to specify -Verbose. If I need to override that verbose flag for some reason, I can simply add -Verbose:$false to my Get-DbaDatabase command.
Read on for plenty of good use cases and additional resources from sharp people.
Comments closedAdam Saxton shares a video covering what is available in the MicrosoftPowerBIMgmt Powershell module:
The Power BI PowerShell and the Admin API allow you to quickly inventory your organization and manage workspaces and access. Forget the hastle of setting up the App registration within Azure Active Directory for API access, the Power BI PowerShell cmdlets take care of it for you. Just install and start using today!
Click through for that video.
Comments closedI had a lot of issues when I created my first one, and after discussing with some folks, they had the same issues. I searched for the best blog posts that I could find on the subject, and the one I LOVED the most was here: Arctic DBA. He broke it down so simply, that I finally created my own pseudo installer and I wanted to share it with all of you. Please, bear in mind, these code snippets may fail at anytime due to changes in Azure.
**IMPORTANT**
These next steps assume the following:
You have created/configured your Azure Automation Account and credential to use to execute this runbook.
Read on for a reasonably short Powershell script and a modified version of Ola Hallengren’s index maintenance procedures.
Comments closedGarry Bargsley shows us how to provision and build a VM in Azure using nothing but Powershell:
I spent the bulk of my day Wednesday going through the Prelab steps outlined in the lab. I was extremely impressed by this lab and how every step was correct and accurate down to the letter. Then the more I thought about it, the steps are built around using an Azure Virtual Machine. With this you get a common machine, framework and steps to build around. You do not have to worry about the users’ local settings or scenario. You are starting from the exact same point of reference every time. So that was fun to connect via SSH to a Linux machine and install SQL Server 2017 and Docker from the command line. While I know it was easy because someone was telling me what to type, it was still fun to see how the other side (Linux People) live.
Today I was in an adventurous mood to try something new. I had been wanting to put together a PowerShell script that would deploy an Azure Virtual Machine. I started down the path a couple time and got stuck so I lost interest. I thought this was the perfect opportunity to get over the hurdle and combine the Prelab steps in this lab with doing those steps with PowerShell. So below you will find my first go at building an Azure Virtual Machine using PowerShell to replace the manual steps in the Prelab process. Not that there was anything wrong with those steps, I just want to try and use a tool that I have been working to learn and use on a day to day basis. Wish me luck.
Read on for a step-by-step guide.
Comments closedRob Sewell shows us how to use Pester to ensure that a set of SQL scripts are valid T-SQL:
This is a quick Pester test I wrote to ensure that some SQL Scripts in a directory would parse so there was some guarantee that they were valid T-SQL. It uses the SQLParser.dll and because it was using a build server without SQL Server I have to load the required DLLs from the dbatools module (Thank you dbatools 🙂 )
It simply runs through all of the .sql files and runs the parser against them and checks the errors. In the case of failures it will output where it failed in the error message in the failed Pester result as well.
This particular example doesn’t ensure that the scripts do what you want them to do, but hey, Pester was built for that as well.
Comments closedShane O’Neill walks us through the concept of script blocks in Powershell:
…recently, I ran into an issue in PowerShell that, if it had been in SQL, I would have solved it quite handily with some Dynamic SQL.
“Alas, this is PowerShell” I thought to myself. “And there is no way that one knows of that one can create dynamic commands that can be built up itself!“.
Now, there is two things that you have to realise for when I’m thinking to myself:
- I think more fancy that I am in real life, and
- I’m nearly always wrong!
So please see below for my example problem and the “dynamic PowerShell” created to overcome the issue!
Check it out, and then imagine how to perform Powershell injection.
Comments closed