A Fine Slice Of SQL Server
Roman Gelman has a function which lets you highlight words in Powershell text:
Despite of the
Write-HostHighlightfunction intended to work with a text (it highlights a substring within a string), it can operate with objects too. Generally, it accepts any input from any cmdlet. The output is not always looks nice, but it works! Let see some examples.
This looks a bit like newer versions of grep which highlight matched patterns. I like it.
Comments closedAzure SQL Database – Managed Instance is fully-managed PaaS service that provides advanced disaster-recovery capabilities. Even if you accidentally drop the database or someone drops your database as part of security attack, Managed Instance will enable you to easily recover the dropped database.
Azure SQL Managed Instance performs automatic backups of you database every 5-10 minutes. If anything happens with your database and even if someone drops it, your data is not lost. Managed Instance enables you to easily re-create the dropped database from the automatic backups.
Click through for the Powershell script.
Comments closedJohn Morehouse walks us through the Get-ClusterLog cmdlet in Powershell:
Sometimes you know that a problem occurred, but the tools are not giving you the right information. If you ever look at the Cluster Failover Manager for a Windows Cluster, sometimes that can happen. The user interface won’t show you any errors, but you KNOW there was an issue. This is when knowing how to use other tools to extract information from the cluster log becomes useful.
You can choose to use either Powershell or a command at the command prompt. I tend to lean towards Powershell. I find it easier to utilize and gives me the most flexibility.
Click through for an example, including of a method which filters out everything but error messages.
Comments closedEmin Atac tests out a security change made in the January 2019 Latest Cumulative Update for Windows:
My first concern was: if it’s a security vulnerability, what’s its CVE? The blog post answer is: CVE-2019-0543 discovered by James Forshaw of Google Project Zero
My second concern was twofold. Is the chapter about A Least Privilege Model Implementation Using Windows PowerShell published in the PowerShell Conference Book impacted by this change? Should I stop deploying Windows 10 at work because the LCU of January 2019 breaks my loopback scenario?
The answer is no and explained by the blog post Windows Security change affecting PowerShell
you would not be affected by this change, unless you explicitly set up loopback endpoints on your machine to allow non-Administrator account access
Read on for some testing and digging into what works when and why.
Comments closedJosh Smith wants to find every installation of SQL Server Reporting Services on a machine:
This is one of those posts so I never have to google this again (one hopes). Here is the PS code to pull back a set of details about every SSRS instance installed on a server including the SSRS instance name, & the first URL port it is running on, the service name and the name of the report server database etc.
Click through for the Powershell script.
Comments closedAndrew Pruski shows us kube-shell:
One tool that I’ve recently come across is kube-shell, an integrated shell for working with Kubernetes. What’s great about it is that it’s cross-platform and has intellisense for kubectl.
Installation is a cinch! The prerequisites are python and pip, which can be downloaded from here.
That auto-complete is quite useful.
Comments closedJess Pomfret takes us on a journey to a desired state:
PowerShell DSC is a platform to support the concept of Infrastructure as Code (IaC). It uses declarative syntax instead of the usual imperative syntax of PowerShell. This means that you describe your desired state rather than the specific steps needed to get there. There are two modes for DSC, push and pull, although pull mode offers more features and scalability, we’ll look at writing our configuration and using push mode for this blog post to keep it simple.
This post covers initial installation and some of the initial configuration, so check it out if you’re new to DSC.
Comments closedI’ve long wanted to do this to help dbatools users easily create a non-production environment to test commands and safely explore our toolset. I finally made it a priority because I needed to ensure some Availability Group commands I was creating worked on Docker, too, and having some clean images permanently available was required. Also, in general, Docker is a just a good thing to know for both automation and career opportunities
Probably a little bit better to work on cmdlets you don’t know about in a sandboxed container rather than on production. Just a little bit.
Comments closedAndre Kamman walks through a particularly useful cmdlet in the dbatools package:
My answer to that is simple, I’m a major contributor to the awesome Powershell library dbatools. What I’ve contributed to that library are commands that will help automate the running and processing of queries from the DMV library of Glenn Berry
At some point in the life of a DBA we’ve all come accross his scripts. For the longest time I would advise people to google “Glenn Berry DMV”, and it will be the top result.
The scripts however, come in a single file per SQL Server version and you can’t run them all in one go. You would have to select a script, run it, and paste the result from Management Studio into an Excel sheet. Glenn provides an empty sheet with tabs ready to paste the various result sets in. I’ve automated this part, hope you like it!
Click through for a demonstration of this cmdlet and the useful output it generates.
Comments closedGrant Fritchey builds a quick script to simulate load using PowerShell:
The idea is to be able to easily do one of several different things. By commenting out different sections of the code, I can change the general behavior. Most of the work is done in the # Run forever section of the code.
First, I’ll randomly pick a modulus comparison. When that hits and the remainder is 0, then I randomly wait between 3 and 13 seconds. Clearly, any of these can be adjusted.
The query gets executed. Then, I have to options for dealing with the query in cache. I can clear cache on every execution. I’ve found this very useful when dealing with bad parameter sniffing (testing or generation). Or, I can use another random set of code to occasionally remove the procedure from cache.
Click through for the script and some more notes from Grant.
Comments closed