A Fine Slice Of SQL Server
Steve Jones looks at one Powershell function inside dbatools:
I like using PoSh for some tasks, especially when I don’t have an easy way to do something in SSMS or want to run a task across a variety of instances. In this case, as I glanced through the September updates, I found a good one.
Get-DbaTcpPort
I don’t love the mixed naming, and I’ll get used to it, but I do love the autocomplete in PoSh.
Steve has lots of screenshots walking you through this function.
Comments closedAaron Nelson reports that there are now Powershell cmdlets for SQL Server Reporting Services:
I have been testing these commands for several weeks and so far my favorite command is Write-RsFolderContent because it will allows you to write the .RDL & .RSD files from a directory on your machine to your SSRS folder. Like the whole thing. You don’t have to throw it into a loop or anything. Try it out!
This is a wonderful replacement for the old RSScripter app (of which I still have a copy squirreled away somewhere).
Comments closedPhil Factor combines SQLLite, Powershell, and SQL Server:
Although I dearly love using SQL Server, I wouldn’t use it in every circumstance; there are times, for example, when just isn’t necessary to use a Server-based RDBMS for a data-driven application. The open-source SQLite is arguably the most popular and well-tried-and-tested database ever. It is probably in your phone, and used by your browser. Your iTunes will use it. Most single-user applications that need to handle data will use SQLite because it is so reliable and easy to install.
It is specifically designed as a zero-configuration, embedded, relational database with full ACID compliance, and a good simple dialect of SQL92. The SQLite library accesses its storage files directly, using a single library, written in C, which contains the entire database system. Creating a SQLite database instance is as easy as opening a simple cross-platform file that contains the entire database instance. It requires no administration.
There’s a lot going on in this interesting article; I recommend giving it a read.
Comments closedRob Sewell uses Power BI to map where he’s been:
I am using the swarm API but the principle is the same for any other API that provides you with data. For example, I used the same principles to create the embedded reports on the PASS PowerShell Virtual Chapter page showing the status of the cards suggesting improvements to the sqlserver module for the product team to work on. Hopefully, this post will give you some ideas to work on and show you that it is quite easy to get excellent data visualisation from APIs
First up we need to get the data. I took a look at the Swarm developers page ( The Trello ishere by the way) I had to register for an app, which gave me a client id and a secret. I then followed the steps here to get my user token I was only interested in my own check ins so I used the steps under Token flow Client applications to get my access token which I used in an URL like this.
This post includes some Powershell and quite a few animated GIFs, making it easy to follow.
Comments closedAllison 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 closed