Problem
We are starting to deploy SQL Server on Linux for our new applications. I have only been a Windows Server administrator and have not had any opportunity to work with Linux, but I am familiar with PowerShell for managing Windows and SQL Server. Can I work with PowerShell on Linux?
Solution
To continue this series on Introduction to PowerShell Core on Linux for the SQL Server DBA, we will look at installing and exploring the dbatools PowerShell module on Linux for managing SQL Server. In Part 1, you learned how to install and explore PowerShell Core on Linux. Part 2 explored leveraging the SQL Server PowerShell module on Linux. This tip will walk you through installing and using the dbatools PowerShell module to start managing SQL Server on Linux.
Install the dbatools PowerShell modules on Linux
dbatools is a free PowerShell module with over 500 SQL Server best practice, administration, development, and migration commands included. It was originally written with Windows PowerShell in mind but has been rewritten to support PowerShell Core, which means you can now run dbatools on Linux and macOS. dbatools simplify automating SQL Server tasks with PowerShell and should be included in your toolkit as a DBA. You should be using it regardless of the operating system that your SQL Server is running on.
Use the Install-Module PowerShell cmdlet to install the dbatools PowerShell module. You need to run the following commands on a PowerShell Core shell. And because you’re running these commands in the PowerShell Core shell, it will be the same regardless of the Linux distribution you will be working with. Run the command below to switch from the bash shell on Linux to the PowerShell Core shell.
pwsh
Run the command below to install the dbatools PowerShell module. This will download the dbatools PowerShell module from the PowerShell Gallery.
Install-Module -Name dbatools
You can start using the dbatools PowerShell cmdlets once the module has been downloaded and installed. Run the command below to import the dbatools PowerShell module.
Import-Module dbatools
Just like SQL Server PowerShell module for Linux, the dbatools PowerShell module for Linux have fewer cmdlets, although the goal is to eventually have the same cmdlets between Windows and Linux. Knowing which cmdlets are available can help prepare you on how to manage SQL Server on Linux using PowerShell Core. Run the command below to display all the dbatools PowerShell cmdlets. Start with the cmdlets that allow you to explore and retrieve SQL Server configuration. Typically, they start with the verb Get.
Get-Command -Module dbatools -Verb Get
Clearly, dbatools has more PowerShell cmdlets than the SQL Server PowerShell cmdlets from Microsoft – even on Linux. It’s another reason why you should really have dbatools in your DBA toolkit. Let’s start exploring the different dbatools PowerShell cmdlets.
Taking Simple Inventory of the SQL Server Instance
Similar to what was done in the previous tip, take inventory of the SQL Server instance running on Linux. You can use the Get-DbaInstanceProperty cmdlet to retrieve this information on one or multiple SQL Server on Linux instances as shown in the command below. The Get-Credential cmdlet is used to prompt for credentials instead of manually providing credentials in clear text. And, no, I won’t be using the sa login this time. I created a dedicated sysadmin login for managing the SQL Server on Linux instance.
$credential = Get-Credential Get-DbaInstanceProperty -SqlInstance <sqlinstance> -SqlCredential $credential
Since the command lists all the properties of the SQL Server instance, you can limit the properties you want returned by using the -InstanceProperty parameter.
Get-DbaInstanceProperty -SqlInstance <sqlinstance> -SqlCredential $credential -InstanceProperty NetName, OSVersion,Product, ProductLevel, VersionString,ProductUpdateLevel, Collation, Edition, DefaultFile, DefaultLog, HostDistribution, HostRelease, HostPlatform | Format-Table
You can list all the available properties of the SQL Server on Linux instance that you can pass to the -InstanceProperty parameter by running the command below.
Get-DbaInstanceProperty -SqlInstance <sqlinstance> -SqlCredential $credential | Select-Object Name
To take inventory of your SQL Server database, run the command below to use the Get-DbaDatabase cmdlet.
Get-DbaDatabase -SqlInstance <sqlinstance> -SqlCredential $credential | Format-Table
You can use the -ExcludeSystemparameter to display only user databases.
Get-DbaDatabase -SqlInstance <source> -SqlCredential $credential -ExcludeSystem | Format-Table
Perform Database Migration between SQL Server on Linux Instances
There are so may dbatools cmdlets that you can explore to accomplish different tasks. But the most common task to leverage dbatools with is database migration. For this, you can use a combination of the Backup-DbaDatabase cmdlet with the Restore-DbaDatabase cmdlet or the Copy-DbaDatabase cmdlet to do it in a single step. But before doing so, I highly recommend running the PowerShell Core shell using root. Only the mssql user and root have access to the /var/opt/mssql directory. Unfortunately, there are still issues running sudo from inside PowerShell Core.
sudo pwsh
Let’s explore using the Backup-DbaDatabase cmdlet with the Restore-DbaDatabase cmdlet to perform database migration. Also, assume that you modified the default backup directory as highlighted in this tip to /var/opt/mssql/backups. Use the command below to backup all the user databases on a SQL Server on Linux instance.
The -ExcludeDatabase property excludes all system databases.
Backup-DbaDatabase -SqlInstance <source> -SqlCredential $credential -ExcludeDatabase master,model,msdb,tempdb
You can copy all the database backups to the default backup directory of the destination SQL Server on Linux instance using the scp command below. Provide the password of the root user on the destination server.
scp /var/opt/mssql/backups/*.bak root@<destination>:/var/opt/mssql/backups/
Since the backup files were copied to the destination server using the root user, the root user owns those backup files. You need to change the ownership of the files to the mssql user to perform a restore. You can run the chown command remotely using the ssh command below so you don’t have to leave the existing PowerShell Core shell. Provide the password of the root user on the destination server.
ssh root@<destination> chown mssql:mssql /var/opt/mssql/backups/*
After copying all the database backups from the source to the destination, you can use the Restore-DbaDatabase cmdlet to restore all of the backups.
Restore-DbaDatabase -SqlInstance <destination> -Path /var/opt/mssql/backups/ -SqlCredential $credential
Be sure to copy SQL Server logins between SQL Server on Linux instances as part of the database migration process using either the Copy-DbaLogin cmdlet or this Microsoft KB article for transferring SQL Server logins.
Summary
This is just an overview of how you can start managing a SQL Server on Linux instance using PowerShell Core and the dbatools PowerShell module. This is barely scratching the surface. The dbatools PowerShell module is a very powerful toolkit for automating SQL Server tasks regardless of the server operating system.
Next Steps
- Read the Microsoft documentation on installing PowerShell Core on Linux
- Explore the available dbatools PowerShell cmdlets
- Review the previous tips on Windows PowerShell for the SQL Server DBA to compare the difference between Windows PowerShell and PowerShell Core on Linux
