Notes on SQL

Background

I need to install a new PowerShell module onto a server that has no access to the Internet, so I am unable to use the Install-Module command.

 

Description

For a task that I have, I require the ‘SQLServer’ PowerShell module, as the older ‘SQLPS’ module does not have the CmdLets that I’m after.

The ultimate installation target (after local development and testing) is a server that has no Internet access, for security reasons. Therefore I cannot simply use the PowerShell ‘Install-Module’ command, which would take care of the download and installation.

I have read several strange articles on various blog sites, describing methods of achieving this that I (with my limited PowerShell experience) could not understand.

With some vague, hinted-at solutions I have discovered that in reality it is very simple and worth documenting for others that encounter a similar situation.

Demonstration

For this demonstration I’m using a laptop that has a virtual machine (VM) installed, which I can treat as two different environments – the VM, which will have internet access disabled and the laptop, which will have internet access.

First of all, the particular module that I’m after requires a minimum PowerShell version of 5, so I’ll check what version I have installed on my VM test system.

Of course, initially the module has to be downloaded, using a machine that does have internet access. For this demonstration, that is the laptop.

First of all, I’ll confirm that I don’t already have the module installed on the server, by using ‘Import-Module’.

The message ‘The specified module ‘sqlserver’ was not loaded because no valid module file was found in any module directory’ means that it couldn’t find the module in any of the directories it uses, or it has been installed but PowerShell is not a high enough version to access it. That is why we have the check on the version at the beginning.

So where does PowerShell load modules from?

PowerShell has a list of directories that it uses for loading modules, which can be examined with the following command. This shows all of the directories that PowerShell will use for locating the required module.

Downloading the module

To download the module to a specified folder, the ‘Save-Module’ command is used. In the example I specify the destination as ‘C:\TempDB’, because I want to compress and copy it elsewhere afterwards.

Because this laptop has never downloaded via PowerShell before I also see a message that requests I also install the required Nuget provider – you might not see this on your system. In my example I confirm I require that too, in which case it is installed automatically and then downloads the module that I requested.

After this download completes, there is now a folder named ‘SQLServer’ within ‘C:\Temp’. Next, I compress this into ‘SQLServer.Zip’, to make it easier to copy to the destination location.

Copying the module to the target server

There are several ways to copy the file from one server to another. Within my test environment I copy the zipped file from the laptop to the VM by using the ‘Copy-VMFile’ command. In this example the VM is named “Humongous” and the file is being copied from “C:\Temp” on the laptop to “C:\Temp” on the VM.

Now, within the VM, there is a file “C:\Temp\SQLServer.zip”. I extract these, within the “C:\Temp” folder, producing the PowerShell module (a folder named ‘SQLServer’ and several sub-folders), although currently in the wrong location.

Where should the files be installed?

PowerShell uses certain folders when a module is used. To find which libraries it uses, the PowerShell environmental variable ‘PSModulePath’ can be called. Split the response up by the ‘;’ to make it easier to read.

The first folder listed is local to the account used (‘Student’), so this folder is only available to the ‘Student’ account. To ensure the module is available to all users, we’ll place it in the second folder listed – ‘Program Files\WindowsPowerShell\Modules’.

Final Step for Installation

To make the module available for PowerShell, all that is now needed is to copy the ‘SQLServer’ folder from the ‘C:\Temp’ folder to ‘Program Files\WindowsPowerShell\Modules’.

Confirming the Installation

Now that the module is in a library that PowerShell uses, it can be loaded.

Import-Module will load the module and Get-Module will then list the commands that are available.

Conclusion

Although having Internet access for a PowerShell system is nice, it is not vital and it is relatively easy to install a module without direct Internet access.