Helping the SQL Server community……where i can!

Category: Powershell

During a bit of work I’ve been doing this evening for SQLRelay, I used something I have in my arsenal of PowerShell scripts which I thought I’d share because I love it’s simplicity. It’s nothing big and fancy but something that is extremely useful. Tasked with merging a large number of csv files there (as always) is a quick and easy way to do this with PowerShell:

I had a request this morning for something I though was actually very simple:

Client: “Can you extract all data for these particular tables including column headers to a tab delimited .txt file?”Chris: “Sure, no problem, I’ll just run bcp querying sys.tables using a COALESCE loop to output the statements”Client: “Top stuff, let me know when it’s done”

So, away I went generating my script which took a matter of minutes and run it…….where’s the column headers? Bugger, forgot that bcp doesn’t output column headers without doing some funky stuff by creating a header record in a separate file and merging that with the file of data.

With this in mind I knew creating a SSIS package (or using export data to generate – very manual unless I delved into the realms of BIML) could do this but I thought I’d have a look at powershell invoking sqlcmd.

Again, this all seemed to be going very well until I came to outputting the data to a tab delimited .txt file. As far as I’m aware Powershell does not have an Export-Txt so I had to look into how I can use the Export-Csv to actualy output to .txt tab delimited as opposed to comma separated and found the parameter -delimiter “`t” – Excellent!!! Added this in and run the script…………and the first row consisted of “#TYPE System.Data.DataRow” – wft!?!?!?!?!

Quick search on my search engine of choice showed that there is a parameter that you can pass in to remove this from the export -NoTypeInformation.

Run it again with -NoTypeInformation and everything worked as expected apart from all column headers and data had quotes (“) around them which was not part of the requirement. Unfortunately (as far as I know) there is no switch, parameter or the likes that does this so I had to change the Export-Csv to ConvertTo-Csv and run a Replace on ‘”‘ with ” which managed to do the trick.

Sorry for another Powershell post but I’ve been doing a lot of it recently and coming up with (what i think are) a few nifty tricks.

One of the issues I encountered recently was with Kerberos delegation whilst trying to automate Log Shipping. What I was trying to do was use an OPENROWSET query to run against the Primary and Secondary servers in order to obtain the Primary_id and Secondary_id in order to pass to the script to be ran on the monitor server. However, seeing as the environment was not setup for Kerberos I encountered the “double-hop” issue.

Enabling Kerberos delegation for the service account would be too high a risk without thorough testing so wasn’t an option in this instance so I decided to look into using invoke-sqlcmd against each of the servers to get the IDs required and pass it to the monitor script.

So how did I go about doing this you ask, well its actually really simple. After a bit of googling I came across this blog by Allen White which gave me a starting block.

Firstly, you have to amend your TSQL script to SELECT the parameter you want to output and use within the rest of the script, something like this:

TSQL snippet to be ran against the Primary Server:

--Cut down version of the script for readability
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N'$(Database)'
...
,@primary_id = @LS_PrimaryId OUTPUT --This is what we want
,@overwrite = 1
,@ignoreremotemonitor = 1
--Need to output this in order for powershell to take it and use it in the monitor script
SELECT @LS_PrimaryId as LS_PrimaryId

Do the same for the script to run on the secondary server but obviously for the secondary_id 🙂

So, now you’ve setup the TSQL side of things, you need to then call these from Powershell and assign the output parameter to a Powershell variable like so:

So, relatively simple. Basically your setting the output to a Powershell variable. keeping things tidy, re-assign it to another variable and something to note is that the output is actually a DataTable object. Make sure you use the name of the alias you used in your last TSQL statement.

$PID = $PrimaryID.LS_PrimaryId
$SID = $SecondaryID.LS_SecondaryId

Once this is done then you can use this in your script to run against the monitor server

As I imagine that the majority of people who are reading this will have some level of SSIS knowledge, I’ll not go into explanations about package configurations in SSIS and its various methods but rather jump straight to it.

The majority of environments I’ve worked in where SSIS packages are utilised, tend to sway down the XML Config package configuration method. As many of you are aware, in multi-tier SQL Environments (ie. Integration, QA, UAT etc etc) this can be a pain when deploying the packages to the relevant environments because you have to at some stage reconfigure the XML configuration file to have the correct parameters to pass into the package for each environment. This can become an even worse scenario when you have tens if not hundreds of SSIS packages (and corresponding dtsConfig’s) and are upgrading your infrastructure with new servers (and/or instance names) as well as drive configurations.

If you don’t have the time to be re-working the SSIS packages to use a SQL table (depending on your process this could take a while to get through development, testing etc) to hold the configuration parameters which makes it easy to script, deploy and update then here’s a simple trick using Powershell (i’m still at the very basic Powershell level so bare with me!!) you can use for your existing dtsConfig files. The sample i’ll be using is for a dtsConfig used in a Restore package.

Unfortunately you’re still going to have to do some initial amending of one config file here :(.

Firstly, lets amend the relevant parameter values to have a standard name for each, as an (snipit) example:

I’ve used a * to prefix and suffix the parameter so that you don’t amend anything that may have a similar name.

By doing this, you can run a bit of Powershell to update the relevant element parameter values for each instance by using the code below, NOTE i’ve excluded the setting of the parameters etc as I use this as a module and don’t want to waste space:

#Create Copy for dtsConfigs by piping the date from the static file (with *'d parameters to the new file to be used
(Get-Content $RestoreDatabasesDynamicConfig) |
Set-Content $RestoreDatabasesDynamicConfig_New
#Amend the dtsConfig's and create new files to use
$restoreArray = ("SQLInstance", "DataFilesDir", "LogFilesDir", "SSISRestoreLocationDBs")
#Loop through the array to replace each parameter listed
foreach($Arr in $restoreArray){
$Replace = "\*"+$Arr+"\*" #This sets the parameter with the * prefix and suffix
$WithThis = "$"+$Arr #What parameter name passed in to replace the text with, ie. $SQLInstance, $DataFilesDir
switch ($Arr)
{
# Use $ExecutionContext.InvokeCommand.ExpandString($WithThis) to evaluate the string (ie $SQLInstance)
"DataFilesDir" {$WithThis = $SQLInstallLocation+$ExecutionContext.InvokeCommand.ExpandString($WithThis)}
"LogFilesDir" {$WithThis = $SQLInstallLocation+$ExecutionContext.InvokeCommand.ExpandString($WithThis)}
#I've left the above in as an example of how to set the files to a folder location passed in as a Parameter ($SQLInstallLocation)
default {$WithThis = $ExecutionContext.InvokeCommand.ExpandString($WithThis)}
}
#Now create the new dtsConfig file with updated Parameter information
(Get-Content $RestoreDatabasesDynamicConfig_New) |
Foreach-Object {$_ -replace "$Replace", $WithThis} |
Set-Content $RestoreDatabasesDynamicConfig_New
}

In short, this script is taking the amended dynamic file with all the parameters with their *’s prefixed and creating a new dtsConfig file. It then builds an array of parameters to work through and do the replacement, the values of these are(or indeed can be…) passed through to the function/module. I’ve put a switch in there to check for particular items in the array as in my example i wanted to append a folder location to the value passed in. you don’t necessarily have to do this but left it in to show it can be done.

Another example of using this is for a silent SQL Server 2008 install. Once you have a ConfigurationFile.ini then you can follow the same process to put a standard tag in the file and use powershell to find and replace it with a parameter value – works an absolute treat when installing many instances

I’m sure there’ll be someone far more clever than me that can somehow the file for the element and replace any value without standardising the parameter values but I’m no expert with Powershell and learning everyday and hope some others out there can get some use out of this…..and yes, I also realise that you can do a “Find and Replace in Files” with Notepad++ but this technique is great for automating!

I’ve recently decided I’ve got to get with the times and started dabbling with Powershell. Up until now i’ve not really seen any use (as a DBA) for it from a SQL Server perspective as I’ve been able to achieve pretty much everything in the past using TSQL, VBScript and/or SMO etc but the client stated they wanted Powershell to script out all the objects from all instances in their large estate.

I’ll not bore you too much with what its doing but in a nutshell it’ll loop a list of servers from a Central Management Server (CMS) but this can be amended to loop a textfile or indeed a text list. From this, the script will create a folder for the server, loop all databases (incl system but this can be turned off) creating a folder for each and generate a .sql file for procedures, views and functions each within their own folders within the database. You can add/remove the ability to script other objects such as tables, indexes etc but it wasn’t a requirement at the time so I’ve not included it.

I’m not going to pretend that this script is top notch but for a beginner I’m quite proud of it :).
There are a number of similar scripts on the web but none of them did quite what I was after, hopefully It’ll be of some use to some.

Will I be using Powershell again? Definitely! I know I could have knocked this up quicker using other technologies but where’s the fun in that 🙂 ????

And here it is, there are 2 scripts: One which is a function to get the objects called (funnily enough) getSQLObjects (fn_getSQLObjects.ps1) and the other is a script to get the server list and call the function (Run_getSQLObjects.ps1). These could have been combined and a parameter used to dictate whether to use text file, CMS or other method of listing the SQL Servers but I decided to keep them separate.

One other thing to note is that I used SQLPS to run this but the script could be tweaked to use a relevant connection to the CMS to get the serverlist.