Somehow I have become the R DBA at my job which I don’t mind, I plan on taking Microsoft’s Professional Program on Data Science to be familiar with it. But recently I’ve had to upload files to our R servers which the first time wasn’t too bad. Copy these files to six different servers but come the second time around it became apparent that the Predictive Analytics Manger was going to be asking me to do this more frequently than I wanted to to it manually. So I wrote a quick PowerShell function to take care of this added to our module we use in house. It unzips the file provided to the correct location. It does assume you have administrative rights to your server i.e. you can use the admin shares (c$) for example on the server. You will need to get the function Get-CMSHost from my Running SQL Scripts Against Multiple Servers Using PowerShell post to run the code below.
UPDATE: If you install 9.1 R services on your existing 2016 R server you will end up two paths to your libraries. To fix this we just need to add a TOP 1 to the cmdlet to select the path for the 9.1 libraries.
.SYNOPSIS This function unzips the R Library packages to the appropriate location on the servers specified.
.PARAMETER zipfile R LIbrary zip file to be loaded
.PARAMETER searchPattern !!NOT INJECTION SAFE!! this parameter simply gets inserted into a wildcard query on the list of available instances on the CMS. This parameter accepts a pipe delimeter list of patterns, allowing you to match instance names on multiple conditions.
.PARAMETER cleanup Clean up current libraries
.EXAMPLE This will return all instances that start with 'Pattern' and are on SQL 2016 RTM
Copy-RLibrary -zipFile '\\share\DemandForecast_Library.zip' -searchPattern 'SQL-RServer' -cleanup $false
[string]$cleanup = $false
$servers = Get-CmsHosts -searchPattern $searchPattern
ForEach ( $server in $servers )
$query = @"
SET NOCOUNT ON
CREATE TABLE #path
INSERT INTO #path
EXECUTE sp_execute_external_script @language = N'R'
, @script = N'OutputDataSet <- data.frame(.libPaths());'
SELECT TOP 1 '\\$($server)\' + REPLACE(REPLACE(path,'/','\'),':','$') as path
DROP TABLE #path
$dt = Invoke-Sqlcmd -Query $query -ServerInstance $server -Database master
$path = $dt.path
if ($cleanup -eq $true)
Remove-Item ($path)\* -Force -Recurse
Expand-Archive -LiteralPath $zipfile -DestinationPath $path -Force
She also tinkered with databases in middle school to keep her baseball card collection organized.
Her passion outside of SQL Server is volunteering with foster children as their advocate in court through http://www.casaforchildren.org.
Latest posts by Tracy Boggiano (see all)
- Check Backup Encryption Sizes - June 14, 2017
- PowerShell Function to Get Query Store Data Across Multiple Databases - June 2, 2017
- Uploading New R Library Packages - May 23, 2017