A Fine Slice Of SQL Server
Allison Tharp shows how to send automated e-mails with Powershell:
The update has two parts: how I feel about my work and how I feel about my department. For each of these, I wrote a few ‘beginning’ sentences and a few ‘ending’ sentences. The script picks a random beginning and ending sentence for each category (work and department), color codes it, and sends the email to my personal and my work emails.
I love the randomization.
Comments closedConstantine Kokkinos has a list of automatic variables in Powershell:
Been a few days of learning since I last wrote one of these, but I have come back to the automatic variables page on the PowerShell documentation enough times that I think I should just blog the important parts for myself.
$?– TRUE/FALSE if the last thing you did succeeded.
$_– Something everyone uses in posh, current pipeline object.
$Args– all the undeclared params passed to a function, try to avoid.
$Error– the array of error objects that represent a stack of the most recent errors. use $Errors0 to get the most recent error.
Read on for more variables.
Comments closedCody Konior walks through using PoshRSJob with a custom function:
Recently I migrated from my own runspace module to Boe Prox’s PoshRSJob which is pretty much perfect. But today I wanted to share how to integrate PoshRSJob cleanly into your functions through a default -Parallel parameter and using a template.
You can very easily modify this for your own purposes however it’s even more awesome as-is if you run parallelised tests for one major input (like a computer name) but where additional information might also be passed in through object properties on a pipeline (I’ll explain why you’d want to do that later in the post). Here’s what it looks like:
Read on for code and explanation. Powershell parallelism is something that I’ve never been good at, so hopefully this makes it easier for me…
Comments closedRichie Lee has a Powershell cmdlet to trim a string:
When building up urls from different parameters in something like TeamCity, or Octopus, it’s simple enough to get double “//” in urls if the parameters are not consistent. So little helper functions are always useful to have imported to manage such things. Below is an example of such a thing!
Click through for the function.
Comments closedSanjay Mishra alerts us to new Powershell cmdlets for enabling Always Encrypted on columns:
The July 2016 release of SSMS (and later versions) introduced a set of PowerShell cmdlets through a new ‘SqlServer’ module. This pagedescribes the various capabilities that these cmdlets bring to the table. Of most interest to the specific scenario described above is the Set-SqlColumnEncryption cmdlet. In the post below, we will walk through the steps required to use this – first from a PowerShell session to test the capability, and then finally from a C# application which is using PowerShell Automation to invoke the cmdlets from an application.
As a side note it is worth knowing that the cmdlets in the ‘SqlServer’ PowerShell module can also be used for automating key setup and management (and are, in many ways, more powerful than SSMS – they expose more granular tasks, and thus can be used to achieve role separation and to develop a custom key management workflow – but that is likely a topic for a separate post!)
Sanjay also includes a sample Powershell script to show how it works.
Comments closedSteve Jones shows how to rename a server using Powershell:
I’m sure many people are experimenting with VMs and SQL Server. If you’re like me, many of you just default to installing Windows 7/10 or Windows Server xx Standard for your testing. Those systems work fine, but I’ve been trying to build slimmer systems, which means looking at Server Core. Installing Server Core is much the same as other versions, though you end up with only a command line. If you’re like me, using VMWare, you also might end up with a server name like “WIN-LKR3R4FfL5T”.
I want to change that. It’s a fine name if I’m working locally. It’s not to much fun connecting across a network. This post looks at how to rename that machine.
This is probably a good idea to do before installing any major software. Renaming a server under SQL Server is possible, but there are a few extra steps to the process.
Comments closedChris Bell has a Powershell script to find all sysadmins on a SQL Server instance:
The script below identifies the accounts on your SQL Server that have full sysadmin rights, either on their own or via an Active Directory Group.
To run this, you need a few things setup first.
A file named Instances.txt that has each instance you are going to check on its own line. Just the name, nothing more. You can see the reference to the location at the beginning of the script, just change it to wherever you put your file.
Rights to read the AD information for the domain. This way we can get the members of any groups granted access to your SQL environment.
Click through for the script.
Comments closedFirst I thought about what I would look for in SSMS when I had installed the maintenance solution and made a list of the things that I would check which looked something like this. This would be the checklist you would create (or have already created) for yourself or a junior following this install. This is how easy you can turn that checklist into a Pester Test and remove the human element and open your install for automated testing
SQL Server Agent is running – Otherwise the jobs won’t run🙂
We should have 4 backup jobs with a name of
DatabaseBackup – SYSTEM_DATABASES – FULL
DatabaseBackup – USER_DATABASES – FULL
DatabaseBackup – USER_DATABASES – DIFF
DatabaseBackup – USER_DATABASES – LOG
We should have Integrity Check and Index Optimisation Jobs
We should have the clean up jobs
All jobs should be scheduled
All jobs should be enabled
The jobs should have succeeded
There’s a very nice script and walkthrough of the process if you click through.
Comments closedIf you created an SQL Server VM via azure portal, there will be a section called “SQL Server Configuration” which was introduced via blog “Introducing a simplified configuration experience for SQL Server in Azure Virtual Machines”. Here is a screenshot of that setting. It allows you to configure various things like auto backup, patching or storage etc.
I got a customer who created a SQL VM via powershell. But that VM doesn’t have the section “SQL Server Configuration”. Using his powershell script, I was able to reproduce the behavior. When I created via portal UI, I got the “SQL Server Configuration”.
Read on for the solution.
Comments closedMax Trinidad finishes his series on Powershell in Visual Studio Code. Here’s part 2:
VS Code – Code Runner Extensions
We need to proceed to install the “Code Runner” Extension. Take a look at this extension information which can be use with many other script languages.
VS Code – Terminal session
In Windows, we are configuring the VS Code “Integrated Terminal” to instead of executing Windows Cmd shell or Linux Bash, to use PowerShell Console.Then again, this is a quick change in the user “settings.json” in your script working folder.
It’s still amazing how far Microsoft has come from the Ballmer days.
Comments closed