使用 PowerShell Core 管理 Linux 上的 SQL ServerManage SQL Server on Linux with PowerShell Core

此文章介紹 SQL Server PowerShell,並逐步引導您了解如何在 macOS 與 Linux 上搭配 PowerShell Core (PS Core) 使用的幾個範例。This article introduces SQL Server PowerShell and walks you through a couple of examples on how to use it with PowerShell Core (PS Core) on macOS & Linux. PowerShell Core 現在是 GitHub 上的開放原始碼專案。PowerShell Core is now an Open Source project on GitHub.

跨平台編輯器選項Cross-platform editor options

以下所有的 PowerShell Core 步驟都適用於一般終端,您也可以從 VS Code 或 Azure Data Studio 內的終端執行。All of the steps PowerShell Core below will work in a regular terminal, or you can run them from a terminal within VS Code or Azure Data Studio. VS Code 和 Azure Data Studio 都可在 macOS 和 Linux 上使用。Both VS Code and Azure Data Studio are available on macOS and Linux. 如需 Azure Data Studio 的詳細資訊,請參閱此快速入門 (部分機器翻譯)。For more information on Azure Data Studio, see this quickstart. 您也可能想要考慮為它使用 PowerShell 延伸模組 (部分機器翻譯)。You may also want to consider using the PowerShell extension for it.

安裝 PowerShell CoreInstalling PowerShell Core

如需在各種支援和實驗性平台上安裝 PowerShell Core 的詳細資訊,請參閱下列文章:For more information on installing PowerShell Core on various supported and experimental platforms, see the following articles:

安裝 SqlServer 模組Install the SqlServer module

SqlServer 模組會保留在 PowerShell 資源庫 (英文) 中。The SqlServer module 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.

若要安裝 SqlServer 模組,請開啟 PowerShell Core 工作階段,然後執行下列程式碼:To install the SqlServer module, open a PowerShell Core session and run the following code:

Install-Module -Name SqlServer

如需有關如何從 PowerShell 資源庫安裝 SqlServer 模組的詳細資訊,請參閱此頁面For more information on how to install the SqlServer module from the PowerShell Gallery, see this page.

使用 SqlServer 模組Using the SqlServer module

讓我們從啟動 PowerShell Core 開始。Let's start by launching PowerShell Core. 如果您是在 macOS 或 Linux 上,請在您的電腦上開啟終端工作階段 ,然後輸入 pwsh 以啟動新的 PowerShell Core 工作階段。If you are on macOS or Linux, Open a terminal session on your computer, and type pwsh to launch a new PowerShell Core session. 在 Windows 上,請使用 Win+R,然後輸入 pwsh 以啟動新的 PowerShell Core 工作階段。On Windows, use Win+R, and type pwsh to launch a new PowerShell Core session.

pwsh

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

下列步驟使用 PowerShell Core 連線至您在 Linux 上的 SQL Server 執行個體,並顯示幾個伺服器屬性。The following steps use PowerShell Core 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
  • 顯示 [PowerShell 認證要求] 對話方塊,它會提示您輸入認證。Display the 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 return 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 執行個體,如同您在 [物件總管] 中瀏覽樹狀結構一樣,但是在 cmdline 上。Using the 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 驗證的詳細資訊,請參閱 Configuration steps (設定步驟)。See Configuration steps for information on how to setup Active Directory authentication for SQL Server on Linux.

您也可以搭配 SQL Server PowerShell 提供者使用 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 to connect.

在下面的範例中,您會看到如何使用 SQL 驗證建立新 PSDrive 的範例。In this example below, you will see an 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 this 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.

取得資料庫Get Databases

要知道的一個重要的 Cmdlet 是 Get-SqlDatabaseAn important cmdlet to know is the Get-SqlDatabase. 對於涉及某個資料庫的許多作業,或某個資料庫內的許多物件,可以使用 Get-SqlDatabase Cmdlet。For many operations that involve a database, or objects within a database, the Get-SqlDatabase cmdlet can be used. 如果您同時提供 -ServerInstance-Database 參數的值,只會擷取一個資料庫物件。If you supply values for both the -ServerInstance and -Database parameters, only that one database object will be retrieved. 不過,如果您只指定 -ServerInstance 參數,就會傳回該執行個體上所有資料庫的完整清單。However, if you specify only the -ServerInstance parameter, a full list of all databases on that instance will be returned.

# NOTE: We are reusing the values saved in the $credential variable from the above example.

# Connect to the Instance and retrieve all databases
Get-SqlDatabase -ServerInstance ServerB -Credential $credential

以下是上述 Get-SqlDatabase 命令可能傳回之內容的範例:Here is a sample of what might be returned by the Get-SqlDatabase command above:

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.88 MB Simple       130 sa
AdventureWorksDW2017 Normal      208.00 MB   40.63 MB Simple       140 sa
master               Normal        6.00 MB  600.00 KB Simple       140 sa
model                Normal       16.00 MB    5.70 MB Full         140 sa
msdb                 Normal       15.50 MB    1.14 MB Simple       140 sa
tempdb               Normal       16.00 MB    5.49 MB Simple       140 sa

檢查 SQL Server 錯誤記錄檔Examine SQL Server error logs

下列步驟會使用 PowerShell Core 來檢查 Linux 上 SQL Server 執行個體上的錯誤記錄檔連線。The following steps use PowerShell Core to examine error logs connect on your SQL Server instance on Linux.

在 PowerShell 提示字元中複製並貼上下列命令。Copy and paste the following commands at the PowerShell prompt. 可能需要幾分鐘的時間執行。They might take a few minutes to run. 這些命令會執行下列步驟:These commands do the following steps:

  • 顯示對話方塊,提示您輸入執行個體的主機名稱或 IP 位址Display a dialog that prompts you for the hostname or IP address of your instance
  • 顯示 [PowerShell 認證要求] 對話方塊,它會提示您輸入認證。Display the PowerShell credential request dialog that 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

(選擇性) 您可以將 $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
# done

探索 PS Core 中目前可用的 CmdletExplore cmdlets currently available in PS Core

雖然 SqlServer 模組目前在 Windows PowerShell 中有 109 個 Cmdlet,但 PSCore 只提供 109 個中的 62 個。While the SqlServer module currently has 109 cmdlets available in Windows PowerShell, only 62 of the 109 are available in PSCore. 以下包含目前可用的 62 個 Cmdlet 完整清單。A full list of 62 cmdlets currently available is included below. 如需 SqlServer 模組中所有 Cmdlet 的深入文件,請參閱 SqlServer Cmdlet 參考 (英文)。For in-depth documentation of all cmdlets in the SqlServer module, see the SqlServer cmdlet reference.

下列命令會顯示您所使用的 PowerShell 版本上所有可用的 Cmdlet。The following command will show you all of the cmdlets available on the version of PowerShell you are using.

Get-Command -Module SqlServer -CommandType Cmdlet |
SORT -Property Noun |
SELECT Name
  • ConvertFrom-EncodedSqlNameConvertFrom-EncodedSqlName
  • ConvertTo-EncodedSqlNameConvertTo-EncodedSqlName
  • Get-SqlAgentGet-SqlAgent
  • Get-SqlAgentJobGet-SqlAgentJob
  • Get-SqlAgentJobHistoryGet-SqlAgentJobHistory
  • Get-SqlAgentJobScheduleGet-SqlAgentJobSchedule
  • Get-SqlAgentJobStepGet-SqlAgentJobStep
  • Get-SqlAgentScheduleGet-SqlAgentSchedule
  • Invoke-SqlAssessmentInvoke-SqlAssessment
  • Get-SqlAssessmentItemGet-SqlAssessmentItem
  • Remove-SqlAvailabilityDatabaseRemove-SqlAvailabilityDatabase
  • Resume-SqlAvailabilityDatabaseResume-SqlAvailabilityDatabase
  • Add-SqlAvailabilityDatabaseAdd-SqlAvailabilityDatabase
  • Suspend-SqlAvailabilityDatabaseSuspend-SqlAvailabilityDatabase
  • New-SqlAvailabilityGroupNew-SqlAvailabilityGroup
  • Set-SqlAvailabilityGroupSet-SqlAvailabilityGroup
  • Remove-SqlAvailabilityGroupRemove-SqlAvailabilityGroup
  • Switch-SqlAvailabilityGroupSwitch-SqlAvailabilityGroup
  • Join-SqlAvailabilityGroupJoin-SqlAvailabilityGroup
  • Revoke-SqlAvailabilityGroupCreateAnyDatabaseRevoke-SqlAvailabilityGroupCreateAnyDatabase
  • Grant-SqlAvailabilityGroupCreateAnyDatabaseGrant-SqlAvailabilityGroupCreateAnyDatabase
  • New-SqlAvailabilityGroupListenerNew-SqlAvailabilityGroupListener
  • Set-SqlAvailabilityGroupListenerSet-SqlAvailabilityGroupListener
  • Add-SqlAvailabilityGroupListenerStaticIpAdd-SqlAvailabilityGroupListenerStaticIp
  • Set-SqlAvailabilityReplicaSet-SqlAvailabilityReplica
  • Remove-SqlAvailabilityReplicaRemove-SqlAvailabilityReplica
  • New-SqlAvailabilityReplicaNew-SqlAvailabilityReplica
  • Set-SqlAvailabilityReplicaRoleToSecondarySet-SqlAvailabilityReplicaRoleToSecondary
  • New-SqlBackupEncryptionOptionNew-SqlBackupEncryptionOption
  • Get-SqlBackupHistoryGet-SqlBackupHistory
  • Invoke-SqlcmdInvoke-Sqlcmd
  • New-SqlCngColumnMasterKeySettingsNew-SqlCngColumnMasterKeySettings
  • Remove-SqlColumnEncryptionKeyRemove-SqlColumnEncryptionKey
  • Get-SqlColumnEncryptionKeyGet-SqlColumnEncryptionKey
  • Remove-SqlColumnEncryptionKeyValueRemove-SqlColumnEncryptionKeyValue
  • Add-SqlColumnEncryptionKeyValueAdd-SqlColumnEncryptionKeyValue
  • Get-SqlColumnMasterKeyGet-SqlColumnMasterKey
  • Remove-SqlColumnMasterKeyRemove-SqlColumnMasterKey
  • New-SqlColumnMasterKeyNew-SqlColumnMasterKey
  • Get-SqlCredentialGet-SqlCredential
  • Set-SqlCredentialSet-SqlCredential
  • New-SqlCredentialNew-SqlCredential
  • Remove-SqlCredentialRemove-SqlCredential
  • New-SqlCspColumnMasterKeySettingsNew-SqlCspColumnMasterKeySettings
  • Get-SqlDatabaseGet-SqlDatabase
  • Restore-SqlDatabaseRestore-SqlDatabase
  • Backup-SqlDatabaseBackup-SqlDatabase
  • Set-SqlErrorLogSet-SqlErrorLog
  • Get-SqlErrorLogGet-SqlErrorLog
  • New-SqlHADREndpointNew-SqlHADREndpoint
  • Set-SqlHADREndpointSet-SqlHADREndpoint
  • Get-SqlInstanceGet-SqlInstance
  • Add-SqlLoginAdd-SqlLogin
  • Remove-SqlLoginRemove-SqlLogin
  • Get-SqlLoginGet-SqlLogin
  • Set-SqlSmartAdminSet-SqlSmartAdmin
  • Get-SqlSmartAdminGet-SqlSmartAdmin
  • Read-SqlTableDataRead-SqlTableData
  • Write-SqlTableDataWrite-SqlTableData
  • Read-SqlViewDataRead-SqlViewData
  • Read-SqlXEventRead-SqlXEvent
  • Convert-UrnToPathConvert-UrnToPath

另請參閱See also