A Fine Slice Of SQL Server
Andrew Pruski learns how to run Powershell in Vim so that he doesn’t have to admit he can’t quit it:
I’ve been mucking about with Vim a bit recently and recently found myself (for reasons unknown tbh) writing powershell scripts in it.
Once I’d written a script, I would exit Vim to run it…however…that got me thinking, can I run powershell scripts directly in Vim?
Wait, vim on Windows? The madman!
Comments closedJeffrey Hicks has a two-parter on discovering Pester tags. Part one is Jeffrey’s take:
As I resolved at the end of last year, I am doing more with Pester in 2022. I’m getting a bit more comfortable with Pester 5 and as my tests grow in complexity I am embracing the use of tags. You can add tags to different Pester test elements. Then when you invoke a Pester test, you can filter and only run specific tests by their tag. As I was working, I realized it would be helpful to be able to identify all of the tags in a test script. After a bit of work, I came up with a PowerShell function.
Yesterday I shared some PowerShell code I wrote to discover tags in a Pester test. It works nicely and I have no reason to complain. But as usual, there is never simply one way to do something in PowerShell. I got a suggestion from @FrodeFlaten on Twitter on an approach using the new configuration object in Pester 5.2. I’ll readily admit that I am still getting up to speed on the latest version of Pester. That’s one of my goals for this year, so this was a great chance to learn something new.
Click through to see how both approaches work.
Comments closedJeffrey Hicks builds a useful snippet:
During this process, I decided I needed to help myself speed up the test writing phase. I have a standard set of tests that I like to use for functions in my module. But copying and pasting code snippets is tedious. I know I could create a set of VS Code snippets, but that feels limiting and I’d have to make sure the snippets are available on all systems where I might be running VS Code. Instead, I wrote a PowerShell function to accelerate developing Pester 5.x tests.
My function takes a module and extracts all of the public exported functions. For each function, it creates a set of standard Pester assertions. These are the baseline or boilerplate tests that I always want to run for each function. Each function is wrapped in a Describe block. Although, I can opt for a Context block instead. This command will also insert tags. Note that my code for the tag insertion relies on the ternary operator from PowerShell 7.
Click through for the code.
Comments closedRayis Imayev troubleshoots an Azure Data Factory deployment:
To run these scripts, you will need to have Azure PowerShell installed on your DevOps agents (Azure Pipeline Agents). If your pipeline agents are Microsoft-hosted, then you’re good and all maintenance and software updates are taken care of for you. However, when you implement and install self-hosted agents, then additional software and component installation is solely your responsibility to maintain.
Recently, while I was configuring those pre- and post-deployment scripts for my Azure Data Factory deployment, I received the following error message, “Could not find the modules: ‘Az.Accounts’ with Version: ””.
Rayis does have a working solution, but I do recommend against installing modules in System32 because that directory is supposed to be reserved for Windows. Instead, for multi-user Powerhsell modules, I’d drop them in %ProgramFiles%\WindowsPowerShell\Modules instead, following the general Powershell guidance.
David Alcock wants to move a login:
Migrating SQL databases is fun, depending on your definition of fun that is. The process can involve having to move things such as login details that have been around for that long that nobody has a clue what they are anymore.
With domain accounts that’s pretty straightforward, the passwords are managed in Active Directory and not held in SQL Server and it’s just a case of recreating the account on the new instance.
SQL authentication is different and migrating an account as is means you also have to recreate the password as is which could be difficult if you didn’t know what the password should be. It’s worth saying at this point that the preference should always be to use domain accounts, they’re more secure and much more manageable and migrations are ideal opportunities to refactor things to be better but for the sake of this article let’s proceed with the scenario of recreating a SQL authentication login with an unknown password, and we’ll need to get creative.
And as you’d expect, dbatools makes a dramatic appearance.
Comments closedTom Collins has an answer for us:
Do you have a powershell script or t-sql script to find if ssl encryption is enabled on SQL Server? I don’t want to check if connections are SSL enabled but actually check the Force Encryption flag is enabled
Click through for two answers, one in T-SQL and one in Powershell.
Comments closedThere are two main methods to fetch the Azure Active Directory information; Microsoft Graph, or PowerShell Cmdlets. Both methods are very useful. However, explaining both in one article will be overwhelming. In this article, I’ll focus on how you can fetch the information using PowerShell Cmdlets. The method I explain here is manual. However, the PowerShell scripts can be automated to run as a scheduled process (I might explain that later in another article too). Let’s see how it works.
The method explained here is exporting the AAD users into a CSV file first, and then Power BI imports data from the CSV. You can use any other intermediate data sources such as Excel, SQL Server, etc if you want to. You just need to use their PowerShell cmdlets or parameters to do that.
Special thanks to Aaron Nelson for helping on preparing the demo for this article. Anytime I have a PowerShell question, he is the master who just finds a way to do it in a few seconds. Connect with him using his blog, Twitter, GitHub, or LinkedIn profile.
Click through for the Powershell-based solution.
Comments closedWhen comparing two values in PowerShell, you’ll have to march to the beat of a different drum. The syntax is drastically different:
The short reason why Powershell uses equality operators like -eq is that Bash uses them. Though the funny thing is that Bash actually uses == for string equality comparison and only uses -eq for numeric equality comparisons. The norm for POSIX is =, adding yet another level of fun.
Dave Mason continues a quest into the bowels of Powershell:
Let’s talk a little bit about PowerShell variables and how long they exist within the scopes they’re defined. I’ve encountered some behavior that for me, was unexpected. It’s made my development efforts unproductive–especially when it comes to debugging.
Just like with notebooks, it’s important to remember that the Powershell prompt has a session, and that you aren’t running fresh every time. You can also use Dave’s solution to the problem, which makes sense as well.
Comments closedRobert Cain hits us again on the topic of enumerations in Powershell:
In a previous post, Fun with PowerShell Enums I introduced the concept of Enums.
In this post we’ll dive deeper into enums, taking a look at more of its properties a well as other ways to use enums.
Read on to see what happens when you accidentally include a particular value twice, as well as more about using enums.
Comments closed