使用 Windows 上的 PowerShell 管理 Linux 上的 SQL ServerUse PowerShell on Windows to Manage SQL Server on Linux

适用对象:是SQL Server(仅限 Linux)否Azure SQL 数据库 否Azure SQL 数据仓库 否并行数据仓库 APPLIES TO: yesSQL Server (Linux only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主题介绍 SQL Server PowerShell,并演示将其用于 Linux 上的 SQL Server 的几个示例。This article introduces SQL Server PowerShell and walks you through a couple of examples on how to use it with SQL Server on Linux. Windows、MacOS 和 Linux 上当前提供对 SQL Server 的 PowerShell 支持。PowerShell support for SQL Server is currently available on Windows, MacOS, & Linux. 本文将指导你使用 Windows 计算机连接到 Linux 上的远程 SQL Server 实例。This article walks you through using a Windows machine to connect to a remote SQL Server instance on Linux.

安装最新版本的 Windows 上的 SQL PowerShellInstall the newest version of SQL PowerShell on Windows

在 PowerShell 库中维护 Windows 上的 SQL PowerShellSQL PowerShell on Windows is maintained in the PowerShell Gallery. 使用 SQL Server 时,应始终使用最新版本的 SqlServer PowerShell 模块。When working with SQL Server, you should always use the most recent version of the SqlServer PowerShell module.

开始之前Before you begin

请阅读 Linux 上的 SQL Server 的已知问题Read the Known Issues for SQL Server on Linux.

启动 PowerShell 并导入 sqlserver 模块 Launch PowerShell and import the sqlserver module

首先启动 Windows 上的 PowerShell。Let's start by launching PowerShell on Windows. 在 Windows 计算机上使用 Win+R,并键入“PowerShell”以启动新的 Windows PowerShell 会话 。Use Win+R, on your Windows computer, and type PowerShell to launch a new Windows PowerShell session.

PowerShell

SQL Server 提供名为 SqlServer 的 PowerShell 模块 。SQL Server provides a PowerShell module named SqlServer. 可以使用 SqlServer 模块将 SQL Server 组件(SQL Server 提供程序和 cmdlet)导入到 PowerShell 环境或脚本 。You can use the SqlServer module to import the SQL Server components (SQL Server provider and cmdlets) into a PowerShell environment or script.

在 PowerShell 提示符处复制并粘贴以下命令,将 SqlServer 模块导入当前的 PowerShell 会话 :Copy and paste the following command at the PowerShell prompt to import the SqlServer module into your current PowerShell session:

Import-Module SqlServer

在 PowerShell 提示符处键入以下命令,验证是否已正确导入 SqlServer 模块 :Type the following command at the PowerShell prompt to verify that the SqlServer module was imported correctly:

Get-Module -Name SqlServer

PowerShell 应显示类似于以下输出的信息:PowerShell should display information similar to the following output:

ModuleType Version    Name          ExportedCommands
---------- -------    ----          ----------------
Script     21.1.18102 SqlServer     {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupList...

连接到 SQL Server 并获取服务器信息Connect to SQL Server and get server information

使用 Windows 上的 PowerShell 连接到 Linux 上的 SQL Server 实例,并显示几个服务器属性。Let's use PowerShell on Windows to connect to your SQL Server instance on Linux and display a couple of server properties.

在 PowerShell 提示符处复制并粘贴以下命令。Copy and paste the following commands at the PowerShell prompt. 运行这些命令时,PowerShell 将:When you run these commands, PowerShell will:

  • 显示提示输入实例的主机名或 IP 地址的对话框Display a dialog that prompts you for the hostname or IP address of your instance
  • 显示提示输入凭据的 Windows PowerShell 凭据请求对话框 。Display the Windows PowerShell credential request dialog, which prompts you for the credentials. 可以使用 SQL 用户名和 SQL 密码连接到 Linux 上的 SQL Server 实例 You can use your SQL username and SQL password to connect to your SQL Server instance on Linux
  • 使用 Get-SqlInstance cmdlet 连接到服务器,并显示一些属性 Use the Get-SqlInstance cmdlet to connect to the Server and display a few properties

也可选择仅将 $serverInstance 变量替换为 SQL Server 实例的 IP 地址或主机名。Optionally, you can just replace the $serverInstance variable with the IP address or the hostname of your SQL Server instance.

# Prompt for instance & credentials to login into SQL Server
$serverInstance = Read-Host "Enter the name of your instance"
$credential = Get-Credential

# Connect to the Server and get a few properties
Get-SqlInstance -ServerInstance $serverInstance -Credential $credential
# done

PowerShell 应显示类似于以下输出的信息:PowerShell should display information similar to the following output:

Instance Name                   Version    ProductLevel UpdateLevel  HostPlatform HostDistribution                
-------------                   -------    ------------ -----------  ------------ ----------------                
your_server_instance            14.0.3048  RTM          CU13         Linux        Ubuntu 

备注

如果没有显示这些值的内容,与目标 SQL Server 实例的连接可能已失败。If nothing is displayed for these values, the connection to the target SQL Server instance most likely failed. 请确保可以使用相同的连接信息从 SQL Server Management Studio 进行连接。Make sure that you can use the same connection information to connect from SQL Server Management Studio. 然后查看连接故障排除建议Then review the connection troubleshooting recommendations.

使用 SQL Server PowerShell 提供程序Using the SQL Server PowerShell Provider

连接到 SQL Server 实例的另一种方法是使用 SQL Server PowerShell 提供程序Another option for connecting to your SQL Server instance is to use the SQL Server PowerShell Provider. 使用此提供程序可以导航 SQL Server 实例,就像在对象资源管理器中(但在命令行中)导航树结构一样。This provider allows you to navigate SQL Server instance similar to as if you were navigating the tree structure in Object Explorer, but at the cmdline. 此提供程序默认显示为名为 SQLSERVER:\ 的 PSDrive,可用于连接和导航域帐户有权访问的 SQL Server 实例。By default this provider is presented as a PSDrive named SQLSERVER:\ which you can use to connect & navigate SQL Server instances that your domain account has access to. 有关如何为 Linux 上的 SQL Server 设置 Active Directory 身份验证的信息,请参阅配置步骤See Configuration steps for information on how to setup Active Directory authentication for SQL Server on Linux.

还可以使用 SQL Server PowerShell Provider 进行 SQL 身份验证。You can also use SQL authentication with the SQL Server PowerShell Provider. 为此,请使用 New-PSDrive cmdlet 创建新的 PSDrive,并提供适当的凭据以进行连接。To do this, use the New-PSDrive cmdlet to create a new PSDrive and supply the proper credentials in order to connect.

在下面的示例中,你将看到一个有关如何使用 SQL 身份验证创建新 PSDrive 的示例。In this example below, you will see one example of how to create a new PSDrive using SQL authentication.

# NOTE: We are reusing the values saved in the $credential variable from the above example.
New-PSDrive -Name SQLonDocker -PSProvider SqlServer -Root 'SQLSERVER:\SQL\localhost,10002\Default\' -Credential $credential

可以运行 Get-PSDrive cmdlet 来确认是否已创建驱动器。You can confirm that the drive was created by running the Get-PSDrive cmdlet.

Get-PSDrive

创建新的 PSDrive 后即可开始进行导航。Once you have created your new PSDrive, you can start navigating it.

dir SQLonDocker:\Databases

输出可能如下所示。Here is what the output might look like. 你可能会注意到,此输出类似于 SSMS 在数据库节点中显示的内容。You might notice the output is similar to what SSMS will display at the Databases node. 它显示用户数据库,而不显示系统数据库。It displays the user databases, but not the system databases.

Name                 Status           Size     Space  Recovery Compat. Owner
                                            Available  Model     Level
----                 ------           ---- ---------- -------- ------- -----
AdventureWorks2016   Normal      209.63 MB    1.31 MB Simple       130 sa
AdventureWorksDW2012 Normal      167.00 MB   32.47 MB Simple       110 sa
AdventureWorksDW2014 Normal      188.00 MB   78.10 MB Simple       120 sa
AdventureWorksDW2016 Normal      172.00 MB   74.76 MB Simple       130 sa
AdventureWorksDW2017 Normal      208.00 MB   40.57 MB Simple       140 sa

如果需要查看实例上的所有数据库,可以使用 Get-SqlDatabase cmdlet。If you need to see all databases on your instance, one option is to use the Get-SqlDatabase cmdlet.

检查 SQL Server 错误日志Examine SQL Server error logs

以下步骤使用 Windows 上的 PowerShell 检查连接到 Linux 上 SQL Server 实例的错误日志。The following steps use PowerShell on Windows to examine error logs connect on your SQL Server instance on Linux. 我们还将使用 Out-GridView cmdlet 以网格视图样式显示错误日志中的信息 。We will also use the Out-GridView cmdlet to show information from the error logs in a grid view display.

在 PowerShell 提示符处复制并粘贴以下命令。Copy and paste the following commands at the PowerShell prompt. 它们可能会运行几分钟。They might take a few minutes to run. 这些命令执行以下操作:These commands do the following:

  • 显示提示输入实例的主机名或 IP 地址的对话框Display a dialog that prompts you for the hostname or IP address of your instance
  • 显示提示输入凭据的 Windows PowerShell 凭据请求对话框 。Display the Windows PowerShell credential request dialog, which prompts you for the credentials. 可以使用 SQL 用户名和 SQL 密码连接到 Linux 上的 SQL Server 实例 You can use your SQL username and SQL password to connect to your SQL Server instance on Linux
  • 使用 Get-SqlErrorLog cmdlet 连接到 Linux 上的 SQL Server 实例,并检索自昨天起的错误日志 Use the Get-SqlErrorLog cmdlet to connect to the SQL Server instance on Linux and retrieve error logs since Yesterday
  • 将输出传送到 Out-GridView cmdlet Pipe the output to the Out-GridView cmdlet

也可以选择将 $serverInstance 变量替换为 SQL Server 实例的 IP 地址或主机名。Optionally, you can replace the $serverInstance variable with the IP address or the hostname of your SQL Server instance.

# Prompt for instance & credentials to login into SQL Server
$serverInstance = Read-Host "Enter the name of your instance"
$credential = Get-Credential

# Retrieve error logs since yesterday
Get-SqlErrorLog -ServerInstance $serverInstance -Credential $credential -Since Yesterday | Out-GridView
# done

另请参阅See also