使用 Windows 上的 SQL Server Management Studio 來管理 Linux 上的 SQL ServerUse SQL Server Management Studio on Windows to manage SQL Server on Linux

適用於: 是SQL Server (僅限 Linux) 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: YesSQL Server (Linux only) NoAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

本文介紹 SQL Server Management Studio (SSMS),並逐步引導您完成幾個常見工作。This article introduces SQL Server Management Studio (SSMS) and walks you through a couple of common tasks. SSMS 是 Windows 應用程式,因此當您的 Windows 電腦可以連線到 Linux 遠端 SQL Server 執行個體時,請使用 SSMS。SSMS is a Windows application, so use SSMS when you have a Windows machine that can connect to a remote SQL Server instance on Linux.

提示

如果您沒有要在其上執行 SSMS 的 Windows 電腦,請考慮使用新的 Azure Data StudioIf you do not have a Windows machine to run SSMS on, consider the new Azure Data Studio. 它提供用來管理 SQL Server 的圖形化工具,並可在 Linux 和 Windows 上執行。It provides a graphical tool for managing SQL Server and runs on both Linux and Windows.

SQL Server Management Studio (SSMS) 是 Microsoft 針對開發和管理需求免費提供的部分 SQL 工具套件。SQL Server Management Studio (SSMS) is part of a suite of SQL tools that Microsoft offers free of charge for your development and management needs. SSMS 是一種整合式環境,可存取、設定、管理及開發 SQL Server 的所有元件。SSMS is an integrated environment to access, configure, manage, administer, and develop all components of SQL Server. 它可以連線到內部部署、Docker 容器和雲端中任何平台上執行的 SQL Server。It can connect to SQL Server running on any platform both on-premises, in Docker containers, and in the cloud. 它也會連線到 Azure SQL Database 和 Azure SQL 資料倉儲。It also connects to Azure SQL Database and Azure SQL Data Warehouse. SSMS 使用許多豐富指令碼編輯器來合併一群非常廣泛的圖形工具,使所有技術層級的開發人員及系統管理員都能夠存取 SQL Server。SSMS combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.

SSMS 為 SQL Server 提供一組廣泛的開發和管理功能,包括可執行下列作業的工具:SSMS offers a broad set of development and management capabilities for SQL Server, including tools to:

  • 設定、監視及管理一或多個 SQL Server 執行個體Configure, monitor, and administer single or multiple instances of SQL Server
  • 部署、監視及升級資料層元件,例如資料庫和資料倉儲Deploy, monitor, and upgrade data-tier components such as databases and data warehouses
  • 備份及還原資料庫Backup and restore databases
  • 建立及執行 T-SQL 查詢和指令碼並查看結果Build and execute T-SQL queries and scripts and see results
  • 產生資料庫物件的 T-SQL 指令碼Generate T-SQL scripts for database objects
  • 檢視及編輯資料庫中的資料View and edit data in databases
  • 以視覺化方式設計 T-SQL 查詢和資料庫物件,例如檢視、資料表和預存程序Visually design T-SQL queries and database objects such as views, tables, and stored procedures

如需 SSMS 的相關資訊,請參閱什麼是 SSMS?See What is SSMS? for more information on SSMS.

下載最新版的 SQL Server Management Studio (SSMS)Install the newest version of SQL Server Management Studio (SSMS)

使用 SQL Server 時,您應該一律使用最新版本的 SQL Server Management Studio (SSMS)。When working with SQL Server, you should always use the most recent version of SQL Server Management Studio (SSMS). 最新版本的 SSMS 會持續更新和最佳化,目前可與 Linux 上的 SQL Server 搭配使用。The latest version of SSMS is continually updated and optimized and currently works with SQL Server on Linux. 若要下載並安裝最新版本,請參閱下載 SQL Server Management StudioTo download and install the latest version, see Download SQL Server Management Studio. 為了保持最新狀態,只要有新版本可供下載時,最新版本的 SSMS 就會提示您。To stay up-to-date, the latest version of SSMS prompts you when there is a new version available to download.

注意

使用 SSMS 來管理 Linux 之前,請先檢閱 Linux 上 SSMS 的已知問題Before using SSMS to manage Linux, review the known issues for SSMS on Linux.

連線到 Linux上的 SQL ServerConnect to SQL Server on Linux

使用下列基本步驟來進行連線:Use the following basic steps to get connected:

  1. 在 Windows 搜尋方塊中鍵入 Microsoft SQL Server Management Studio 來啟動 SSMS,然後按一下傳統型應用程式。Start SSMS by typing Microsoft SQL Server Management Studio in the Windows search box, and then click the desktop app.

    SQL Server Management Studio

  2. 在 [連線到伺服器] 視窗中,輸入下列資訊 (如果 SSMS 已在執行中,請按一下 [連線] > [資料庫引擎] 來開啟 [連線到伺服器] 視窗):In the Connect to Server window, enter the following information (if SSMS is already running, click Connect > Database Engine to open the Connect to Server window):

    設定Setting 描述Description
    伺服器類型Server type 預設值為資料庫引擎;請勿變更此值。The default is database engine; do not change this value.
    伺服器名稱Server name 輸入目標 Linux SQL Server 電腦的名稱或其 IP 位址。Enter the name of the target Linux SQL Server machine or its IP address.
    驗證Authentication 如果是 Linux 上的 SQL Server,請使用 [SQL Server 驗證] 。For SQL Server on Linux, use SQL Server Authentication.
    登入Login 輸入有權存取伺服器上資料庫的使用者名稱 (例如,安裝期間建立的預設 SA 帳戶)。Enter the name of a user with access to a database on the server (for example, the default SA account created during setup).
    密碼Password 輸入指定使用者的密碼 (針對 SA 帳戶,您在安裝期間建立了此密碼)。Enter the password for the specified user (for the SA account, you created this during setup).

    SQL Server Management Studio:連線到 SQL Database 伺服器

  3. 按一下 [ 連接]。Click Connect.

    提示

    如果您收到連線失敗,請先嘗試從錯誤訊息診斷問題。If you get a connection failure, first attempt to diagnose the problem from the error message. 然後檢閱連線疑難排解建議Then review the connection troubleshooting recommendations.

  4. 成功連線到 SQL Server 之後,[物件總管] 隨即開啟,您現在就可以存取資料庫來執行系統管理工作或查詢資料。After successfully connecting to your SQL Server, Object Explorer opens and you can now access your database to perform administrative tasks or query data.

執行 Transact-SQL 查詢Run Transact-SQL queries

連線到伺服器之後,您就可以連線到資料庫並執行 Transact-SQL 查詢。After you connect to your server, you can connect to a database and run Transact-SQL queries. Transact-SQL 查詢幾乎可用於任何資料庫工作。Transact-SQL queries can be used for almost any database task.

  1. 在 [物件總管] 中,巡覽至伺服器上的目標資料庫。In Object Explorer, navigate to the target database on the server. 例如,展開 [系統資料庫] 以使用 master 資料庫。For example, expand System Databases to work with the master database.

  2. 以滑鼠右鍵按一下資料庫,然後選取 [新增查詢] 。Right-click the database and then select New Query.

  3. 在查詢視窗中,撰寫 Transact-SQL 查詢以選擇傳回伺服器上所有資料庫的名稱。In the query window, write a Transact-SQL query to select return the names of all databases on your server.

    SELECT [Name]
    FROM sys.Databases
    

    如果您不熟悉撰寫查詢,請參閱撰寫 Transact-SQL 陳述式If you are new to writing queries, see Writing Transact-SQL Statements.

  4. 按一下 [執行] 按鈕以執行查詢並查看結果。Click the Execute button to run the query and see the results.

    成功。

雖然您幾乎可以使用 Transact-SQL 查詢進行任何管理工作,但 SSMS 是一種圖形化工具,可讓您更輕鬆地管理 SQL Server。Although it is possible to do almost any management task with Transact-SQL queries, SSMS is a graphical tool that makes is easier to manage SQL Server. 下列各節提供使用圖形化使用者介面的一些範例。The following sections provide some examples of using the graphical user interface.

建立及管理資料庫Create and manage databases

連線到 master 資料庫時,您可以在伺服器上建立資料庫並修改或卸除現有的資料庫。While connected to the master database, you can create databases on the server and modify or drop existing databases. 下列步驟描述如何透過 Management Studio 完成數個常見的資料庫管理工作。The following steps describe how to accomplish several common database management tasks through Management Studio. 若要執行這些工作,請確定您已使用設定 Linux 上的 SQL Server 時所建立的伺服器層級主體登入,連線到 master 資料庫。To perform these tasks, make sure you are connected to the master database with the server-level principal login that you created when you set up SQL Server on Linux.

建立新資料庫Create a new database

  1. 啟動 SSMS 並連線到您在 Linux 上 SQL Server 中的伺服器Start SSMS and connect to your server in SQL Server on Linux

  2. 在 [物件總管] 中,以滑鼠右鍵按一下 [資料庫] 資料夾,然後按一下 [新增資料庫...]In Object Explorer, right-click on the Databases folder, and then click *New Database..."

  3. 在 [新增資料庫] 對話方塊中,輸入新資料庫的名稱,然後按一下 [確定] In the New Database dialog, enter a name for your new database, and then click OK

隨即在您的伺服器中成功建立新資料庫。The new database is successfully created in your server. 如果您想要使用 T-SQL 建立新的資料庫,請參閱 CREATE DATABASE (SQL Server Transact-SQL)If you prefer to create a new database using T-SQL, then see CREATE DATABASE (SQL Server Transact-SQL).

卸除資料庫Drop a database

  1. 啟動 SSMS 並連線到您在 Linux 上 SQL Server 中的伺服器Start SSMS and connect to your server in SQL Server on Linux

  2. 在 [物件總管] 中,展開 [資料庫] 資料夾,以查看伺服器上所有資料庫的清單。In Object Explorer, expand the Databases folder to see a list of all the database on the server.

  3. 在 [物件總管] 中,以滑鼠右鍵按一下您想要卸除的資料庫,然後按一下 [刪除] In Object Explorer, right-click on the database you wish to drop, and then click Delete

  4. 在 [刪除物件] 對話方塊中,選取 [關閉現有的連線] ,然後按一下 [確定] In the Delete Object dialog, check Close existing connections and then click OK

隨即從您的伺服器成功卸除資料庫。The database is successfully dropped from your server. 如果您想要使用 T-SQL 卸除資料庫,請參閱 DROP DATABASE (SQL Server Transact-SQL)If you prefer to drop a database using T-SQL, then see DROP DATABASE (SQL Server Transact-SQL).

使用活動監視器來查看 SQL Server 活動的相關資訊Use Activity Monitor to see information about SQL Server activity

活動監視器工具內建在 SQL Server Management Studio (SSMS) 中,並顯示 SQL Server 處理序的相關資訊,以及這些處理序如何影響目前的 SQL Server 執行個體。The Activity Monitor tool is built into SQL Server Management Studio (SSMS) and displays information about SQL Server processes and how these processes affect the current instance of SQL Server.

  1. 啟動 SSMS 並連線到您在 Linux 上 SQL Server 中的伺服器Start SSMS and connect to your server in SQL Server on Linux

  2. 在 [物件總管] 中,以滑鼠右鍵按一下 [伺服器] 節點,然後按一下 [活動監視器] In Object Explorer, right-click the server node, and then click Activity Monitor

活動監視器會顯示可展開且可摺疊的窗格,其中包含下列資訊:Activity Monitor shows expandable and collapsible panes with the following information:

  • 概觀Overview
  • 處理序Processes
  • 資源等候Resource Waits
  • 資料檔案 I/OData File I/O
  • 佔用大量資源的最近查詢Recent Expensive Queries
  • 佔用大量資源的使用中查詢Active Expensive Queries

展開窗格時,活動監視器會查詢執行個體以便取得相關資訊。When a pane is expanded, Activity Monitor queries the instance for information. 摺疊某個窗格時,該窗格的所有查詢活動就會停止。When a pane is collapsed, all querying activity stops for that pane. 您可以同時展開一或多個窗格,以便檢視不同種類的執行個體活動。You can expand one or more panes at the same time to view different kinds of activity on the instance.

另請參閱See also