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

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) - Linux适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) - Linux

本文介绍 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) 是 SQL 工具套件的一部分,Microsoft 免费提供此工具套件,用于满足开发和管理需求。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 数据库和 Azure Synapse Analytics。It also connects to Azure SQL Database and Azure Synapse Analytics. 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 地址和端口(格式为 IP,port)。Enter the name of the target Linux SQL Server machine, or its IP address and port in the format IP,port.
    身份验证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

连接到主数据库时,可以在服务器上创建数据库,并修改或删除现有数据库。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 时创建的服务器级别主体登录名)。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