A Fine Slice Of SQL Server
Chrissy LeMaire notes a slew of dbatools updates:
And don’t forget, you can use Invoke-DbatoolsRenameHelper to rename commands in your scripts.
|
|
There is a slew of new functionality, but this little two-line mention is probably the best of the bunch. Of course somebody was going to go and automate the renaming of cmdlet as a cmdlet.
Comments closedRob Sewell points out an addition to dbachecks:
This will show you
- the UniqueTag which will enable you to run only that check if you wish
- AllTags which shows which tags will include that check
- Config will show you which configuration items can be set for this check
The trace flag checks require the app.sqlinstance configuration which is the list of SQL instances that the checks will run against. You can also specify the instances as a parameter for Invoke-DbCheck as well.
Click through for an example.
Comments closedShane O’Neill compromises with a co-worker on building a Powershell function:
As you may have guessed…
…this did not go down well with my co-worker.
That?! How are we supposed to know to write “!?” there? How does that even mean help anyway? Can we not just get the help message to show up on it’s own!
To which I said…
We can but it’s going to create a massive bottleneck! If we try and put in the help messages then it’s going to slow things down eventually because we’re not going to be able to run this without someone babysitting it! Do you want to be the person that has to sit there watching this every single time it’s run?
Compromise is a wonderful thing though and we eventually managed to merge the best of both worlds…
Shane gives us a solution and has a couple of updates for simpler solutions to boot.
Comments closedEmin Atac reviews a couple of 64- versus 32-bit Powershell oddities:
Context
My colleagues send an message with a link that points to a script located on a shared drive to help our users reinstall their software.
Our users just click on the link in their Outlook and got a message saying:
\\servername.fqdn\share\softwarename\install.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at https:/go.microsoft.com/fwlink/?LinkID=135170.
- Issue
Users use Outlook that is a 32-bit process. If they click on link that points to a script, it will spawn a 32-bit console and run a 32-bit powershell.exe child process.
It appears that the ExecutionPolicy isn’t defined in the 32-bit PowerShell and set to its default value: “Restricted” although it’s defined in the 64-bit Powershell.
Read on for the solution to this issue as well as a second, similar issue.
Comments closedChrissy LeMaire shows how you can make DR a lot easier with dbatools:
When we talk about Disaster Recovery or DR, it’s often coupled with the term High Availability or HA. Here are some definitions from my graduate course on HADR.
high availability
- Deals with minor outages, and failover solutions are automated
- The goal is to restore full system functionality in a short time
disaster recovery
- Deals with major outages such as natural and man-made disasters
- Focuses on manual processes and procedures to restore systems back to their original state
- Characterized by a phased approach to restoring the primary site
In the context of SQL Server, HA would be Availability Groups (AG), Failover Clustering (FCI), Log Shipping and more. I won’t be addressing High Availability in this post, however.
Chrissy has a demo of everything in action, including running a series of tests to ensure that your DR site actually has everything.
Comments closedJess Pomfret shows us how we can use dbatools to test Availability Group read-only routing:
The other part I needed to set up was read-only routing, this enables SQL Server to reroute those read only connections to the appropriate replica. You can also list the read only replicas by priority if you have multiple available or you can group them to enable load-balancing.
Although this seems to be setup correctly so that connections that specify their application intent of read only will be routed to the secondary node I wanted to prove it.
Read on to see how Jess is able to prove it.
Comments closedChrissy LeMaire announces new changes to dbatools:
Marching onward to dbatools 1.0, a ton of commands have been renamed to align with our now mature naming scheme. These changes were made in today’s release, version 0.9.410 aka regularlegs, now available on GitHub and the PowerShell Gallery.
Here’s the general idea:
DbaDatabase has mostly been renamed to DbaDb with a couple exceptions
DbaSql has been changed to just Dba, no exceptions. Think of Dba as “Sql”.
DbaConfig has been renamed to DbatoolsConfig
TempDbConfiguration has been renamed to TempdbConfig
All Configuration commands are Config except SpConfigure
DbaDacpac has been renamed to DbaDacPackage. Dac is the prefix for our data-tier application commands.
DbaDbQueryStoreOptions has been renamed to DbaDbQueryStoreOption
Some of this is in preparation for breaking changes in dbatools 1.0. There’s a lot of stuff in this release, so check it out.
Comments closedChrissy 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.