服务器运行多个jdk版本_如何使用中央管理服务器运行多个查询

最新推荐文章于 2024-04-20 10:34:31 发布

culuo4781

最新推荐文章于 2024-04-20 10:34:31 发布

阅读量314

点赞数

文章标签：数据库 python mysql java linux

原文链接：https://www.sqlshack.com/how-to-run-multiple-queries-using-the-central-management-server/

版权

服务器运行多个jdk版本

介绍 ( Introduction )

When you have thousands of SQL Servers, it is very hard to administer all of them. This article will show some tips to help you with these types of tasks.

当您有数千个SQL Server时，很难管理所有这些SQL Server。本文将显示一些技巧，以帮助您完成这些类型的任务。

In this new article, we will show how to run T-SQL scripts against multiple Servers using SQL Server Management Studio (SSMS). To do this, we will use the Central Management Server. The main idea of this feature is to administer multiple servers in a centralized way using queries or policies. This feature is available in SQL Server 2008 or later versions and cannot be applied in older versions.

在这篇新文章中，我们将展示如何使用SQL Server Management Studio（SSMS）对多个服务器运行T-SQL脚本。为此，我们将使用中央管理服务器。此功能的主要思想是使用查询或策略以集中方式管理多个服务器。此功能在SQL Server 2008或更高版本中可用，而在较早版本中不能应用。

要求 ( Requirements )

SQL Server 2008 or later (in this example, we are using SQL Server 2014).
SQL Server 2008或更高版本（在此示例中，我们使用的是SQL Server 2014）。
We are using 2 local SQL instances with the Adventureworks Database installed in each instance.
我们正在使用2个本地SQL实例，每个实例中均安装了Adventureworks数据库。

入门 ( Getting started )

Open the SSMS.
打开SSMS。
In the menu, go to View>Registered Servers

在菜单中，转到“查看”>“注册的服务器”

Figure 1. Registered Servers 图1.注册服务器
You can create New Server Groups or use the current Local Server Groups folder. In this example, we are going to use the existing folder group and add the new server registration to this group.

您可以创建新服务器组或使用当前的本地服务器组文件夹。在此示例中，我们将使用现有的文件夹组并将新的服务器注册添加到该组。

Figure 2. New Server Registration 图2.新服务器注册
You can add the authentication information of the servers to connect to it. You can test the connection and add a description.

您可以添加服务器的身份验证信息以与其连接。您可以测试连接并添加描述。

Figure 3. Connection properties 图3.连接属性
You can optionally define in the Connection Properties tab to which database connect by default, the network protocol (TCP-IP, Shared Memory or Named Pipes). You can also define the packet size. If you execute many bulk operations, it may be convenient to increase the packet size. If you do not perform many operations, the other hand may reduce the packet size reduced to increase the efficiency.

您可以选择在“连接属性”选项卡中定义默认情况下数据库连接的网络协议（TCP-IP，共享内存或命名管道）。您还可以定义数据包大小。如果执行许多批量操作，则增加数据包大小可能很方便。如果您不执行许多操作，另一方面可能会减小数据包大小，从而提高了效率。

You can also specify the connection timeout and execution timeout. It is also possible to encrypt the connection and select a custom color.

您还可以指定连接超时和执行超时。也可以加密连接并选择自定义颜色。

Figure 4. The Connection properties 图4.连接属性
Repeat the steps 3, 4 and 5 to add all the SQL Servers available.
重复步骤3、4和5，添加所有可用SQL Server。
Once you have all the SQL Servers added, right click on the Server Group and select New Query. With this option, you can run a T-SQL query over multiple SQL Servers.

添加完所有SQL Server后，右键单击“服务器组”，然后选择“ 新建查询”。 使用此选项，您可以在多个SQL Server上运行T-SQL查询。

Figure 5. Running a Query 图5.运行查询
Let’s start running a simple query showing the server names:

让我们开始运行一个简单的查询，显示服务器名称：
```
 
select @@servername
 
```
The query will show the different server names.

该查询将显示不同的服务器名称。

Figure 6. The SQL Server Names. 图6. SQL Server名称。
You could also create a database in each SQL Server:

您还可以在每个SQL Server中创建一个数据库：
```
 
create database db4
 
```
If you run the query, you will have a successful or failure message.

如果您运行查询，您将收到成功或失败的消息。

Figure 7. Creating Databases 图7.创建数据库
You can also check the sessions, users, processes blocks using the sp_who2 system procedure:
您还可以使用sp_who2系统过程检查会话，用户，进程块：
```
 
sp_who2
 
```
Figure 8. The current users, sessions and processes of all the SQL Server instances. 图8.所有SQL Server实例的当前用户，会话和进程。
If you have the same databases on the Server with the same tables, you can check the fragmentation in all the servers. The following sample shows how to check the average fragmentation percentage higher than 30% in the Person.Address table of the Adventureworks2014 database:

如果服务器上具有相同的数据库且具有相同的表，则可以检查所有服务器中的碎片。下面的示例演示如何检查Adventureworks2014数据库的Person.Address表中的平均碎片百分比是否高于30％：
```
 
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
 
SET @db_id = DB_ID(N'AdventureWorks2014');
SET @object_id = OBJECT_ID(N'AdventureWorks2014.Person.Address');
 
 
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED')
	where avg_fragmentation_in_percent>30
 
```

You can also verify the status of the SQL Server Agent jobs. For example, to verify the SQL jobs that failed in all the SQL Server instances, you can use the following T-SQL query:

您还可以验证SQL Server代理作业的状态。例如，要验证在所有SQL Server实例中失败SQL作业，可以使用以下T-SQL查询：

 
SELECT [instance_id]
      ,[job_id]
      ,[step_id]
      ,[step_name]
      ,[sql_message_id]
      ,[sql_severity]
      ,[message]
      ,[run_status]
      ,[run_date]
      ,[run_time]
      ,[run_duration]
      ,[operator_id_emailed]
      ,[operator_id_netsent]
      ,[operator_id_paged]
      ,[retries_attempted]
      ,[server]
  FROM [msdb].[dbo].[sysjobhistory]
  where run_status=0

The information of the job history is in the system table sysjobhistory. The run_status shows the status of the job. If the status of the job is 0, it means that the job failed. If the status is 1, it means that the job is run successfully. 2 is that the job retried and 4 that the job was cancelled.

作业历史记录的信息在系统表sysjobhistory中。 run_status显示作业的状态。如果作业的状态为0，则表示作业失败。如果状态为1，则表示作业已成功运行。 2是作业重试，4是作业被取消。

Figure 9. The status of the jobs 图9.作业状态
Another popular query is to check the database size of all the databases at the same time. The database size can be obtained from the sys.master_files. In the system table system databases, you can obtain all the databases. You sum all the sizes of the files because one database can have multiple data files and log files. The type 0 are datafiles and the type 1 are log files. The query would be the following:

另一个流行的查询是同时检查所有数据库的数据库大小。可以从sys.master_files获得数据库大小。在系统表系统数据库中，您可以获得所有数据库。由于一个数据库可以有多个数据文件和日志文件，所以您总结了文件的所有大小。类型0是数据文件，类型1是日志文件。该查询将是以下内容：
```
 
with f
as
(
    select database_id, type, size * 8.0 / 1024 filesize
    from sys.master_files
	
)
select 
    name,
    (select sum(filesize) from f where type = 0 and f.database_id = d.database_id) DataSize,
    (select sum(filesize) from f where type = 1 and f.database_id = d.database_id ) LogSize
from sys.databases d
 
```
The result of the query will show the data files and log files of all the databases of all the instances:

查询结果将显示所有实例的所有数据库的数据文件和日志文件：

Figure 图 10. The data file sizes and log sizes. 10.数据文件大小和日志大小。
The following command will enable advanced options in all the SQL Servers:

以下命令将在所有SQL Server中启用高级选项：
```
 
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE;
 
```
Once enabled, we will enable an advanced option xp_cmdshell. This extended stored procedure allows running the command line from the T-SQL. It is a very powerful feature that will work depending on the privileges of the account used to connect in the step 3 to the servers. You will need some administrative privileges to run the command. Let’s enable the xp_cmdshell procedure first:

启用后，我们将启用高级选项xp_cmdshell。此扩展的存储过程允许从T-SQL运行命令行。这是一项非常强大的功能，其功能取决于在步骤3中用于连接到服务器的帐户的特权。您将需要一些管理特权才能运行该命令。让我们首先启用xp_cmdshell过程：
```
 
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE WITH OVERRIDE;
 
```
The xp_cmdshell is a very powerful command, but a dangerous one for security reasons. That is why it is disabled by default. The following example creates a folder named backupsfolder in the c drive:

xp_cmdshell是一个非常强大的命令，但出于安全原因是危险的命令。这就是默认情况下禁用它的原因。以下示例在c驱动器中创建一个名为backupsfolder的文件夹：
```
 
xp_cmdshell 'mkdir c:\\backupsfolder'
 
```
If everything is OK, you will have a new folder in the Operative System:

如果一切正常，您将在Operative系统中拥有一个新文件夹：

Figure 11 . The folder backupsfolder created. 图11 。 文件夹 backupsfolder已 创建。

Finally, let’s run the backup in the folder created:

最后，让我们在创建的文件夹中运行备份：

 
BACKUP DATABASE [db4] TO  DISK = N'c:\\backupsfolder\db4_a.bak' WITH NOFORMAT, NOINIT,  NAME = N'db4-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

If everything is OK, you will have a backup created for all the SQL Server instances.

如果一切正常，则将为所有SQL Server实例创建一个备份。

Figure 12. The database backup 图12.数据库备份

结论 ( Conclusion )

In this chapter, we showed how to run different queries in multiple SQL Server instances. We verified the server names, we created a database in all the SQL Server instances, we verified the current users, sessions and processes, we checked the fragmentation higher than 30 of a specific table in all the instances, we also verified if the SQL Agent Jobs have some errors in the execution. We finally enabled advanced configuration options and created a folder in all the SQL Servers. The last step was to create the backup in the new folder. As you can see, administering all the servers in a centralized way can be a straightforward task.

在本章中，我们展示了如何在多个SQL Server实例中运行不同的查询。我们验证了服务器名称，在所有SQL Server实例中创建了数据库，验证了当前用户，会话和进程，在所有实例中检查了高于特定表30的碎片，还验证了SQL Agent是否作业在执行中有一些错误。最后，我们启用了高级配置选项，并在所有SQL Server中创建了一个文件夹。最后一步是在新文件夹中创建备份。如您所见，以集中方式管理所有服务器可能是一项简单的任务。

You can also apply SQL policies to multiple Server at the same time. The evaluating policies on multiple instances article will show you how to do it.

您还可以同时将SQL策略应用于多个服务器。关于多个实例的评估策略文章将向您展示如何实现。

翻译自: https://www.sqlshack.com/how-to-run-multiple-queries-using-the-central-management-server/

服务器运行多个jdk版本

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
服务器运行多个jdk版本_如何使用中央管理服务器运行多个查询

本地服务器运行多个项目介绍 ( Introduction) When you have thousands of SQL Servers, it is very hard to administer all of them. This article will show some tips to help you with these types of tasks. 当您...
复制链接

扫一扫