A Fine Slice Of SQL Server
Jack Vamvas shows us how to check the status of a Windows service using Powershell:
Question: How can I check for the SQL Server Reporting Service service status with Powershell? The status checks should be for remote servers
Click through for the answer.
Comments closedMichael Bourgon needs to get some data out of S3:
I was running into issues with the Linked Server lopping off long JSON that I’m having to pull out from the raw files. I can’t explain it – doesn’t appear to be SSMS. See previous post
But I needed to automate this, rather than use SQL Workbench, save to “Excel” (it was XML), then opening it again and saving it so that instead of 250mb, it’s 30mb. Runs against the previous month, one day at a time (walking the partitions), and then saves to a file. You got your Athena, your ODBC, your Export-Excel…
Incidentally, that previous post was around trying to use a linked server to pull the data in via SQL Server.
Comments closedEitan Blumin has a template for us:
If at any point an automated script fails for some reason, or does not behave as expected, it would be invaluable to have it produce and retain historical logs that could later be investigated for clues as to what it did, and where and why it failed.
Powershell has a few useful cmdlets for this, capable of writing an Output to any sort of destination, such as a log file. For example, Out-File.
However, in my personal experience, nothing beats the level of verbosity offered by a special cmdlet called Start-Transcript.
Read on for an explanation for each part of the template, and then the template itself.
Comments closedStuart Moore talks us through a (rare) gap in dbatools:
In the comments here I was asked about using Restore-DbaDatabase when all you have is blobs in an Azure Storage account. This can be done, involves a couple of non dbatools steps.
Restore-DbaDatabase doesn’t natively ‘talk’ Azure, nor do any of the other dbatools commands. The reason for this is that we didn’t want to force dbatools users to have to install the AzureRM/Az powershell modules just to use our module. We’ve gone to a lot of effort to make sure that dbatools is acceptable to Security Admins and that it has a small(ish) footprint, and adding those large modules as prerequisites would have broken that.
Read on for how you can get around that.
Comments closedJack Vamvas helps us with cmdlet lookup:
Question: I need to interrogate the module for a Powershell cmdlet but I can’t find the actual physical location of the file on the host server. I can execute the cmd through the Powershell command line OK , but can’t find the powershell cmd file. Is there a Powershell method of locating the Powershell module files?
Click through to see the answer.
Comments closedGijs Reijn shows how to automate the process of creating an Azure SQL Database using Powershell:
Before you can create an Azure SQL database, you must create an Azure SQL server to host it on. Assuming you’re already authenticated to Azure:
Open PowerShell on your local computer and create the Azure SQL server that will host the Azure SQL database.
The command below is creating an Azure SQL server called
sqlestatein the prerequisite resource group with a SQL admin username ofSqlAdministratorand a password ofAVeryStrongP@ssword0. The command is saving the output of theNew-AzSqlServercmdlet to use attributes from the server created later.
Read on for the step-by-step breakdown and full script.
Comments closedJess Pomfret has another way to search through stored procedure text:
When we’re looking for the command we need within dbatools to fulfil our needs I cannot recommend
Find-DbaCommandhighly enough. This command will search all other commands for the pattern you pass in. Today we know we want to find references in stored procedures so let’s see if there is a command that will help.
Seems like querying sys.sql_modules is a little easier, though if this is a step in a pipeline (such as finding old procedures based on some no-longer-appropriate code snippet and deploying new versions), this can be a good first step.
Chad Baldwin shows off the PipelineVariable parameter:
PowerShell has been a daily tool for me for at least 5 or 6 years at this point, so when I learn something new that seems fairly useful I figure it’s probably worth writing about. These posts also help me remember because they force me to do more research into it than I normally would.
TIL (Today I Learned) about the
-PipelineVariableparameter in PowerShell, known as a “Common Parameter”; which are automatically added by PowerShell to cmdlets that are decorated with the[cmdletbinding()]attribute.This is by no means a “new” feature,
-PipelineVariablewas added as a common parameter in 2017 for version v4.0.
Read on for a clear explanation of how it works and where it can be useful.
Comments closedPatrick Gruenauer shows how to configure network settings on a Windows machine using Powershell:
Today I would like to give a tutorial for a basic network configuration on Windows systems using PowerShell. We start with a fresh installation of a Windows operating system.
Read on for a fairly simple example, one which lays out the foundation for more complicated scenarios such as configuring networking settings for an Availability Group.
Comments closedJeffrey Hicks shows how you can use Powershell to manage Windows 10 taskbar settings:
When I’m working on a Pluralsight course, I tend to setup a virtual machine for recording. Although, lately I’ve been trying with Windows 10 Sandbox. This is handy when all I need is a Windows 10 desktop. When I setup the system, I have particular settings I need to configure. Naturally I use a PowerShell script to automate the process. One item that I wanted to address was Windows 10 taskbar. When I’m recording a course, I like to have it auto-hide. Sure, I could manually set it. But that’s no fun.
After a little online research I came across this page. In addition to the manual steps, the author also provided a snippet of PowerShell code! I assumed there would be registry setting I could configure and was hoping to find that. But this was even better. The author’s code was written to be used from a CMD prompt to invoke a few PowerShell commands. But since I’m already using PowerShell for my desktop configuration, I took his code and created re-usable PowerShell functions.
Read on for a lengthy but helpful script.
Comments closed