Powershell

The problem Recently, I was performance tuning and enhancing the reliability of several SQL Server 2019 and 2022 databases for a client and discovered NOLOCK hints everywhere. Why are these bad, they seem to improve performance and remove blocking for most queries? 1. Dirty reads This allows data to be read that is currently being modified by another query leading to inconsistent results. If a row that has been read gets rolled back because of an error like a deadlock, or network issue, the data never existed! ...

Are you encountering the following error message when working with Azure in PowerShell and running the command Get-AzVirtualNetwork? 'Microsoft.Azure.Management.Network.Models.SecurityRule', on 'T MaxInteger[T](System.Collections.Generic.IEnumerable`1[T])' violates the constraint of type 'T'. This error can be caused by an incompatibility between PowerShell 7 and the Az Module and incompatibilty with .NET 7. To fix this issue, try the following steps: Upgrade to the latest versions of PowerShell 7 and the Az module. If the issue persists, remove all versions of the Az Module on your system by using the command Uninstall-Module Az -AllVersions. ...

What if you could do a dry run of your PowerShell script when making a change to your production environment? If you’ve read my other posts, you will know I am a big advocate of infrastructure-as-code. Most of my work is done in the Microsoft Azure cloud, and although I encourage source controlling all assets within Azure, sometimes developers create things manually through the portal. Yes, I know! 🙄 Removing Azure Resources safely A scheduled drift report should alert developers or DevOps engineers that the production state is out-of-sync with the source controlled code. If this happens, and some resources need removing, then the -WhatIf switch comes in handy and provides confidence to remove items in production that shouldn’t be there. ...

Today, the Az.Sql module would not load on my machine when attempting to manually fail over a SQL Managed Instance with this error: module could not be loaded I followed the advice of the error message and tried to import the module to get further information. module is already loaded Hmmm :thinking: I decided to uninstall the Azure PowerShell from my machine using the Windows 10 Settings App. Looks like an old version. ...

In February 2018, myself and Paul Anderton gave a presentation on how to correlate database deployments with performance issues within the context of a DevOps pipeline. We used Sentry One as our monitoring tool in a Performance Test environment so that we could catch badly performing deployments before they got to production and caused havoc. If you would like to see the recorded video, then you can download it from here: http://info.sentryone.com/partner-webinar-performance-problems ...

Overview Sentry One is a great tool for monitoring many servers. For new installations, it can be a bit of a bind to add your existing servers into the tool to be monitored. I have written a PowerShell module to make this much easier and to validate that servers that you thought were being monitored, are in fact monitored. There is full documentation for the module in the Sentry One user guide which explains how to use the functions within it, but a brief explanation is shown below. it is worth mentioning that all the PowerShell cmdlets are doing is calling the SentryOne compiled PowerShell module provided when you install SentryOne. ...

I was at a client site recently and implemented Sentry One for them, a great monitoring system for SQL Server. It proved challenging because some servers were in a DMZ on a separate network and domain and some servers were in the same domain. All servers connected via a router and were firewalled off from each other with only the minimum ports open required for them to fully function and communicate. ...

Clean Code I am a big proponent of clean code. I use PowerShell a lot for automation, and want code to be clean. You are automating everything, right? If not, please see a slide from a recent meetup: Disturbing For me, clean code in PowerShell means (and not limited to): Small self-contained functions that have a single responsibility Number of arguments to a function kept as small as possible Consistent formatting No duplication of code Modules that hide internal functions, and only expose what’s needed Common Parameters One way to make code a bit cleaner is to make use of PowerShell’s common parameters. If you would like a refresher as to what these are, there is extensive documentation in PowerShell itself which you can access with ...