Get Diretory Information For SSAS

Jens Vestergaard shows us how to get the Data, Log, Temp, and Backup directories for Analysis Services using Powershell:

Just recently a reply was made to the Connect item, highlighting the fact, that the current values of the Data/Log/Temp and Backup Directories – meaning the currently configured values – is exposed through the Server.ServerProperties collection. According to the answer, only public property values are exposed.

Using PowerShell, we can now retrieve the desired information from any given instance of Analysis Services. Doing so would look something like this:

It’s good to know that this information is available via Powershell.

Dynamic CSV Imports

Mike Fal wants to automate loading multiple CSV files:

Once the staging table is in place, the load is actually fairly simple. You have lots of options, but the easiest one for my money is to build a BCP call and execute that. BCP is a simple utility that has been around for a while, mostly because it is effective. The build is not difficult:

It’s quick, dirty, and functional.

Get SQL Server Configuration Aliases

Chris Bell shows us how to get the list of aliases set up on a server:

It is also a pain to sit and transcribe the various alias settings to be able to rebuild them all on the next machine.

There is an export list option for the aliases on your server, that’s nice and all, but there isn’t a corresponding import option.

Plus you have to deal with 32 and 64 bit lists.

The very simple script below helps since you can use to get the details of both the 32 and 64bit SQL Server aliases you have setup on your system.

Ready for it? It’s a long convoluted one:

If you use server aliases, you’ll want to check out this script.


Chrissy LeMaire wants Microsoft to fix three things with SQLPS:

SQLPS has a lot of of bugs that need to be addressed (I’ll get to that soon), but I propose we start with these three.

  1. SQLPS module is slow to load
  2. Loading SQLPS module changes current directory to PS SQLSERVER:\>
  3. SQLPS module uses unapproved PowerShell verbs

Each item even has suggested fixes. The fixes are pretty straightforward (said the DBA who doesn’t do QA). Bugs 1 and 2 suggest modifying a few lines in SqlPsPostScript.ps1, while number 3 probably requires a recompile and we’re not really sure how challenging that will be.

Please upvote the Connect items if you use SQLPS…or don’t but would if it worked better.

Don’t Use Write-Host

Steve Jones warns us away from Write-Host in Powershell:

I’ve written a few scripts and programs lately, mostly just for fun. In those scripts, I’ve used Write-Host to return output. To me, it’s been like “Print” in various languages where I can get output of a program. Often I’ll use a method/function to get info and then use print to output that to the caller.

However a few people noted that in my last script, Write-host wasn’t necessary. When I asked why, both Mike Fal and Drew Furgiule responded.

Mike and Drew are smart cookies.  Write-Host has some major limitations which hinder developers’ abilities to modularize and package viable code.


Richie Lee shows off Get-TimeSpan and New-TimeSpan:

As is the case with most things, when I find a way for getting something done in a script that is “good enough”, I’ll tend to stick with that method until that method no longer becomes fit for purpose. One such method is printing out the time that something took in PowerShell: many of the scripts on my site use this method to get the duration of a task, and I’ve been using this since PowerShell 1.0

This is certainly an improvement over the old version.

Database File Info

Mike Fal shows us how to get database file information using Powershell:

To do this, I dove into the SMO object model. This gets a little /Net-y, but the good news is there’s lots of properties we can use to get the information we are looking for. If we look at both the DataFile and LogFile classes, there are properties readily available for us. Both classes have UsedSpace and Size properties (both measured in KB), from which we can derive both available space and percentage used. All it takes is wrapping some collection and formatting logic around these objects and we’re good to go. You can see my full function up on GitHub.

This is a nice example of using the Powershell pipeline to build an end product, in this case an HMTL report of log and file usage.

Improving SMO Performance

Cody Konior shows how to use GetDefaultInitFields in SMO to improve performance, and also explains a bug when you try to do this:

It turns out has no default properties set, and the exception seems to occur after you try to set some. Interestingly though the way that boolean flag works is to set default properties on each SMO type; which means the solution is to turn it on for everything and then only disable it for this specific type; retaining any of the performance improvements throughout the rest of SMO.

Anything that makes SMO faster gets a vote of approval from me.

Simulate SQL Server Connections

Kenneth Fisher shows us how to generate multiple connections using Powershell:

As with most of these types of things, I had a need. I want to show how using sys.dm_exec_[requests/sessions/connections] is better than sp_who. Particularly when you have a large number of connections. Well in order to do that I need a large number of connections right? Now I’m sure someone out there has a script to generate somewhat random connections but writing one myself would be good practice and I’d like to get better at Powershell anyway. In the end I need some help and as aways it was plentiful and easy to find. So thanks to Derik Hammer (b/t), Drew Furgiuele(b/t), and of course it wouldn’t be a PoSH script if I didn’t get help from Mike Fal (b/t). (To be honest Mike actually wrote most of the final script)

This is great for demonstrations, and with a few tweaks you can turn this into a very poor man’s load tester.

Documenting Permissions

Chris Bell has a new script to audit SQL Server permissions:

I wanted the script to do a few things. Tell me who is in a AD Group that was granted rights. IT is one thing to see the group name, but that doesn’t really tell me who has access. I also wanted the output to be a little more user readable, so I formatted the output some. There are other things I did too, but you can fun reading through the code and comparing the 2 sources.

Knowing who’s allowed to do what is key to having a successful security posture.  This script won’t tell you object-level permissions, but at least gives you an idea about role and group membership.


August 2019
« Jul