By: Edwin Sarmiento | Updated: 2020-03-24 | Comments | Related: 1 | 2 | 3 | > SQL Server on Linux
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 SQL Server PowerShell modules on Linux for managing SQL Server. In Part 1, you learned how to install and explore PowerShell Core on Linux. This tip will walk you through installing the SQL Server PowerShell modules to start managing SQL Server on Linux. You will also explore the different SQL Server PowerShell cmdlets available on PowerShell Core on Linux.
Install the SQL Server PowerShell modules on Linux
You will be using the Install-Module PowerShell cmdlet to install the SQL Server PowerShell modules. So, 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 to the PowerShell Core shell.
pwsh
Run the command below to install the SQL Server PowerShell modules. This will download the SQL Server PowerShell modules from the PowerShell Gallery and automatically install all of the dependencies.
Install-Module -Name SqlServer
You can start using the SQL Server PowerShell cmdlets once the SQL Server PowerShell modules have been downloaded and installed. Run the command below to import the SQL Server PowerShell modules.
Import-Module SqlServer
Just like PowerShell Core on Linux, the SQL Server PowerShell modules for Linux have fewer cmdlets. You can verify this by running the command below on a Windows machine and a Linux machine that has the SQL Server PowerShell cmdlets.
Get-Command -Module SqlServer | Measure-Object
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 of the SQL Server 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 SqlServer -Verb Get
Taking Simple Inventory of the SQL Server Instance
The first thing that SQL Server DBAs do when working with a new system is to take inventory of the SQL Server instance. This involves reviewing the instance name, what platform it is running on top of, patch level, default data and log file directory, and the likes. You can use the Get-SqlInstance cmdlet to retrieve this information. Run the command below to retrieve basic information about the SQL Server instance. The Get-Credential cmdlet is used to prompt for credentials instead of manually providing credentials in clear text.
$credential = Get-Credential Get-SqlInstance -ServerInstance <provide your instance name here> -Credential $credential
This command is run on the same machine as the SQL Server on Linux instance so you can replace the instance name with localhost. But if you are running this remotely, be sure that the machine that you are on can resolve the hostname of your Linux machine on the network and that the firewall rules allow traffic to and from the SQL Server on Linux instance.
The command returns a SQL Server instance object that you can further explore. For example, you can retrieve additional information about your SQL Server on Linux instance. Run the command below to retrieve additional information. The command is written in such a way that the pipeline (|) character acts as an escape character as well for easier formatting. But nothing is stopping you from writing the entire command in a single line.
Get-SqlInstance -ServerInstance ubuntusql01 -Credential $credential | Select-Object Name, Version, ProductLevel, ProductUpdateLevel, VersionString, Collation, Edition, DefaultFile, DefaultLog, HostPlatform, HostDistribution, HostRelease | Format-List
If you want to explore the different properties, you can pipe the output of the Get-SqlInstancecmdlet to the Get-Member cmdlet like the command below.
Get-SqlInstance -ServerInstance ubuntusql01 -Credential $credential | Get-Member -MemberType - Property
To take inventory of your SQL Server database, run the command below to use the Get-SqlDatabase cmdlet.
Get-SqlDatabase -ServerInstance ubuntusql01 -Credential $credential
Let’s say you want to generate a report of the databases that are in SIMPLE recovery model, you can pipe the result of the command to the Where-Object cmdlet like the command below.
Get-SqlDatabase -ServerInstance ubuntusql01 -Credential $credential | Where-Object {$_.RecoveryModel -eq "simple"}
Exploring the SQL Server PowerShell Provider
The SQL Server PowerShell Provider exposes the hierarchy of SQL Server objects in paths similar to your filesystem. This makes it easy to explore the different SQL Server objects for programmatic access. This is also described in the SMO Object Model Diagram.
Run the command below using the New-PSDrive cmdlet to create a new PSDrive that represents a SQL Server instance.
New-PSDrive -Name ubuntusql01 -PSProvider SqlServer -Root 'SQLSERVER:\SQL\ubuntusql01\Default\' -Credential $credential
With the SQL Server Linux instance represented by the new PSDrive, you can navigate the object model just like you would any file system. For example, you can run the command below to retrieve the list of databases in SIMPLE recovery model. Unlike the Get-SqlDatabase cmdlet, this will only display user databases.
dir ubuntusql01:\Databases | Where {$_.RecoveryModel -eq "simple"}
NOTE: Use either the dir command or the Get-ChildItem cmdlet and not the ls command. In Windows PowerShell, the ls command is an alias to the Get-ChildItem cmdlet. But in PowerShell Core on Linux, the ls command will get translated to the native Linux command. If you have gotten used to aliases in Windows PowerShell, be careful to avoid using those with an equivalent native Linux command. Don’t worry because Linux will tell you.
Taking Database Backups
The most common task that a DBA does is to take database backups. Run the command below using the Get-SqlBackupHistory cmdlet to retrieve the backup history of a database.
Get-SqlBackupHistory -ServerInstance ubuntusql01 -Credential $credential -DatabaseName AdventureWorksLT
Once you have an idea of when the last backup was taken, you can run the command below using the Backup-SqlDatabase cmdlet to take a full backup of the AdventureWorksLT (or any SQL Server database). The command will not return any output unless there is an error. No error means the command ran successfully.
Backup-SqlDatabase -ServerInstance ubuntusql01 -Credential $credential -Database "AdventureWorksLT"
This will backup the database to the default backup location /var/opt/mssql/data directory. By default, the mssql user owns this directory and the SQL Server daemon runs as the mssql user. Should you decide to store the backup file to a different directory, you need to grant the mssql user read and write permissions to this directory. The commands below will create a new directory /tmp/dbbackups and grants the mssql user the appropriate permission before running the Backup-SqlDatabase command. Unfortunately, since the Get-Acl and Set-Acl PowerShell cmdlets are not available in PowerShell Core, you need to use the native Linux commands to perform the task of assigning permissions to the directory.
#Create new directory using the New-Item PowerShell cmdlet New-Item /tmp/dbbackups -ItemType "directory" #Change ownership of the /tmp/dbbackups using the native Linux command chown to mssql user sudo chown mssql /tmp/dbbackups #Change group ownership of the /tmp/dbbackups using the native Linux command chgrp to mssql group sudo chgrp mssql /tmp/dbbackups #Backup database using Backup-SqlDatabase cmdlet Backup-SqlDatabase -ServerInstance ubuntusql01 -Credential $credential -Database "AdventureWorksLT" -BackupFile "/tmp/dbbackups/AdventureWorksLT.bak"
Run the command below if you want to backup all the database instead of just one. The command excludes the tempdb database.
Get-SqlDatabase -ServerInstance ubuntusql01 -Credential $credential | where {$_.Name -ne "tempdb"} | Backup-SqlDatabase
Summary
This is just an overview of how you can start managing a SQL Server on Linux instance using PowerShell Core and the SQL Server PowerShell modules. The next tip in this series will cover how to leverage dbatools, a more comprehensive collection of PowerShell cmdlets for SQL Server, to manage a SQL Server on Linux instance with PowerShell Core.
Next Steps
- Read the Microsoft documentation on installing PowerShell Core on Linux
- Read the Manage SQL Server on Linux with PowerShell Core
- Review the previous tips on Windows PowerShell for the SQL Server DBA to compare the difference between Windows PowerShell and PowerShell Core on Linux
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-03-24
