Monthly Archives: September 2014

  • 1

[Updated] Citrix PVS: Sync Local vDisk Store #PowerShell

The other evening, I’ve updated the Sync-PvsLocalStore.ps1 script in order to support multiple Citrix Provisioning Services (PVS) vDisks due to customer request.

The purpose of this script is to copy or rather sync changed and new Versions of one or more given vDisks between the local Stores within a Farm of two or more PVS servers. Basically, you can think of the Sync-PvsLocalStore.ps1 as a wrapper for Robocopy.exe /MIR with some extra brains on top. That is because it is able to detect and exclude a Maintenance Version of a vDisk from the copy process, meaning that the script only spreads out the latest Production and Test versions of a vDisk while it doesn’t bloat the stores with Maintenance versions that is work in progress typically.

The usage is very simple. Look at this example:

You need to specify one ‘MasterServer’, one or more ‘MemberServer’, the path of the Store (needs to be the same on each server), one or more vDisk names, the name of the corresponding Site and Store. The two latter help the script to identify any Maintenance Version.

Sync-PvsLocalStore.ps1 needs to be run on a system where the PVS Console or rather its command-line interface MCLI.EXE is installed. (The script utilizes MCLI.EXE because to date there’s no advantage in using the PVS PowerShell Snapin.)


  • 0

Why Merge Hash Tables #PowerShell

As part of building an automation framework, typically you’re facing the challenge to separate the data from logic as this is the key to an agile and re-usable solution. The automation logic by itself should “only” know how to process data within a workflow, but the logic by itself shouldn’t know any (hard-coded) value. Instead, the logic should get data values from separate resources like configuration files, registry, databases, whatever. With separation of data and logic it’s almost a no-brainer to set up the solution for another environment, not to mention maintenance that is as easy as pushing DEV into the git repo and pulling the changes into TEST and PROD for example. I personally like to maintain data in hashtables, to be more precise I usually have for example config-global.ps1 and a config-abbreviation.ps1 file per environment that 1. adds specific environment settings and 2. overrides baseline settings from the global config. These ps1 files only contain a (usually nested) hash table. In order to import the data I dot-source both the global config and the environment config. The next step is the creation of a single data resource from both hash tables; meaning that I’m able to access the keys and their values through a single variable like $ConfigData afterwards.

Merging hash tables is straightforward as long as each hash table contains different keys: it’s simply $hashTable1 + $hashTable2. In case of duplicate keys this approach will fail because PowerShell isn’t able to resolve that conflict by deciding which key takes precedence. That’s why I wrote the function Merge-Hashtables (get your copy from the TechNet Gallery). Merge-Hashtable creates a single hash table from two given hash tables. The function considers the first hash table as baseline and its key values are allowed to be overridden by the second hash table. Internally, Merge-Hashtables uses two sub functions. The first sub function detects duplicates and adds for each conflict the key of the second hash table to the resulting hash table. The second sub function adds additional keys from the second hash table to the resulting hash table. Both sub functions are designed to support nested hash tables through recursive calls.

Hope this helps.


  • 0

Simple SQL Scripting Framework #PowerShell

With this post I share my approach to facilitate the re-usage of SQL commands within PowerShell scripts. You should continue reading if you often deal with SQL. And even if you’re not about to use PowerShell scripts against SQL databases you can take some inspiration on how to build a smart automation solution.

My solution uses a PowerShell function Invoke-SQL and a hash table that contains generalized SQL commands. First things first…

In order to issue SQL command text against an ODBC database connection I prefer my function Invoke-SQL. The function either accepts an existing OdbcConnection object or a connection string in order to create a connection on the fly. By default the function returns $true if the execution of the given SQL statement succeeded. With the -PassThru switch the function loads the results into a DataTable object and returns it for further processing. I uploaded Invoke-SQL to the Microsoft TechNet Gallery. Get your copy from there.

Before I proceed to the SqlCommand hash table let me explain why you need it. The Invoke-SQL example below shows how to pass a simple SELECT statement to an existing ODBC connection ($DBConnection) and save the query’s result into the DBResult variable:

So much for the simple scenario. As you know SQL command texts can be far more complex than SELECT-foo-FROM-bar and often span multiple lines. With PowerShell it is good practice to use here-strings to deal with multi-line SQL commands. Take a look at the next example that shows the concept (meaning that you shouldn’t care about the content of the SQL query):

Ok. And now put the case that you have 15, 20 and more of such rather sophisticated SQL commands and you have to re-use them over and over but with different values. Take a look at the value for Location.ID in the previous example. It is hard coded. Therefore, in order to re-use the $SelectVMM here-string you need to leverage the copy-paste-align method (which is error-prone and bloats scripts with redundant code). Or is there another, better, smarter way? Yes, there is. Take a look at the slightly altered example below:

As you can see I replaced the hard coded value from the here-string with the placeholder {0}. And afterwards, in order to re-use the here-string, I used PowerShell’s format operator to replace that placeholder with a specific value, and saved the resulting here-string into a new variable. That’s nice. There’s still room for improvement though. Finally, I bring that SqlCommand hash table into play…

Basically, the hash table is a collection of named here-strings each containing a generalized SQL command text like above. It could look like this for example:

With that SqlCommand hash table in memory SQL scripting is as easy as:

Key take-away:

In order to re-use SQL commands, create for each a generalized here-string and provide them through a hash table.

Hope this helps.