A customer of ours manages all SQL Servers from a central management server and would like to use the PowerShell module dbatools for this purpose in the future. So far, so good. The only problem is that there is no internet connection on this management server. The only possibility is to start a browser on another computer and use it to load files from the internet and store them in a file share.
In this article I will show which files are needed and how they can be used to set up dbatools in such an environment and also how to update it regularly.
Installation of NuGet
To allow installation and updating of PowerShell modules using Install-Module and Update-Module, we have set up a local PowerShellGet repository based on a file share. This file share can then be used to store and use the packages manually downloaded from the PowerShell Gallery.
Before this, however, the NuGet provider must be updated, which is carried out automatically on request for systems connected to the Internet. But even without an Internet connection, this is done with a few steps. Only the directory “C:\Program Files\PackageManagement\ProviderAssemblies\NuGet\2.8.5.208” with the file “Microsoft.PackageManagement.NuGetProvider.dll” is needed. The file can either be transferred from another system where NuGet has been set up using the Install-PackageProvider -Name NuGet command, or alternatively downloaded from https://onegetcdn.azureedge.net/providers/Microsoft.PackageManagement.NuGetProvider-2.8.5.208.dll and renamed.
Setup of the local repository
The official PowerShell Gallery (https://www.powershellgallery.com/) uses the name PSGallery, so we have chosen the name "MyPSGallery", but you can of course choose another name as well. While the repository can also be set up in a local directory, we used a file share suitable for this purpose. The setup as a trusted source is then done with:
$repoLocation = '\\fileserver\Software\MyPSGallery'
Register-PSRepository -Name MyPSGallery -SourceLocation $repoLocation -PublishLocation $repoLocation -InstallationPolicy Trusted
The NuGet package from dbatools is then placed there, which can be downloaded via "Manual Download" on the https://www.powershellgallery.com/packages/dbatools page. If further packages are needed, they can also be downloaded and provided by this way.
[Update 05/10/2023]: Since version 2.0, the dbatools are made up of two modules, as all libraries have been moved to the dbatools.library module. Therefore, for this type of installation, the current version of this module must also be downloaded from https://www.powershellgallery.com/packages/dbatools.library and provided in the same directory. However, the further process of the installation does not change.
Installing and updating dbatools
The installation can then be done as usual, only the repository has to be specified:
Install-Module -Name dbatools -Repository MyPSGallery
Before updating, the NuGet package must first be downloaded manually from the PowerShell Gallery website and placed in our own repository. Afterward, the update is done as usual:
Update-Module -Name dbatools
Specifying the repository is neither necessary nor possible here. PowerShell knows the source of each installed module and also uses it for updating.
Update of the build reference table
Should dbatools also be used to check whether the SQL Server instances have the latest build installed? Then a regular update of the reference table with information about all published builds is necessary. New builds are added by the dbatools developers regularly and are published at this URL: https://sqlcollaborative.github.io/assets/dbatools-buildref-index.json
In order to copy the file to the appropriate place, the command Update-DbaBuildReference is available, which normally downloads this file from the web page. However, the command has recently got the new parameter LocalFile, with which we can process the file downloaded via browser:
Update-DbaBuildReference -LocalFile '\\fileserver\Software\dbatools\dbatools-buildref-index.json'
Installing and updating additional tools
There are a number of free tools that, in my opinion, no SQL Server should be missing. I would like to mention three of them: The First Responder Kit by Brent Ozar (https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit), the Maintenance Solution by Ola Hallengren (https://github.com/olahallengren/sql-server-maintenance-solution) and sp_WhoIsActive by Adam Machanic (https://github.com/amachanic/sp_whoisactive). These three tools (and some others) have in common that the source code is maintained on GitHub and there is a dbatools command for the installation.
Even in environments with an Internet connection, it should be noted that the commands for installation only download the current version from GitHub on their first call or when using the Force Parameter. All further calls then install from the local cache. It is the administrator's duty to always keep this local cache up-to-date when learning about new versions.
Since there are always the same steps to perform, there is a central dbatools command Save-DbaCommunitySoftware that is responsible for downloading from GitHub and updating the local cache. In addition, this command has a parameter LocalFile to update the local cache without using the internet based on a local file.
For the First Responder Kit, note that you need the version from GitHub (whether as a release or as a branch doesn't matter), since the version distributed via Brent Ozar's website has a different structure. For the Maintenance Solution there are no releases, there you download the master branch as zip.
Here are the commands for processing the files:
Save-DbaCommunitySoftware -Software FirstResponderKit -LocalFile '\\fileserver\Software\dbatools\SQL-Server-First-Responder-Kit-20211106.zip'
Save-DbaCommunitySoftware -Software MaintenanceSolution -LocalFile '\\fileserver\Software\dbatools\sql-server-maintenance-solution-master.zip'
Save-DbaCommunitySoftware -Software WhoIsActive -LocalFile '\\fileserver\Software\dbatools\sp_whoisactive-12.00.zip'
By this action the local cache is updated, the installation can then be done without access to the internet with Install-DbaFirstResponderKit, Install-DbaMaintenanceSolution and Install-DbaWhoIsActive.
Conclusion
Setting up an infrastructure to work with dbatools is possible even without an internet connection. All that is needed are a few manual steps and, of course, a computer with an internet connection somewhere else. Do you require more information? Should this process be adapted to your environment? Contact us, we will be happy to help.