| 使用T | 您所在的位置：网站首页 › sql监控脚本怎么用的 › 使用T |
使用T
|
Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays. 监视SQL Server数据库是数据库管理的重要组成部分。 90％的组织期望在未来的12个月中数据库数量会增加。 数据量的增加可能会对数据库的可用性产生负面影响。 因此，SQL Server数据库监视被认为是数据库管理员的一项重要职责。 组织倾向于将大量资金用于企业解决方案。 而且由于业务和用户需求的敏感和不断增长的性质，如今应用程序的可用性非常重要。 It’s also equally important to get alerted or notified when a database is inaccessible, due to any of the myriad of reasons we can think of. This article deals with one of the solutions to monitor the status of the SQL Server database using T-SQL or PowerShell with the help of the native programming and alerting techniques. 由于无法想到的多种原因，在无法访问数据库时发出警报或通知也同样重要。 本文介绍了一种解决方案之一，借助本机编程和警报技术，可以使用T-SQL或PowerShell监视SQL Server数据库的状态。 Some of us may have many questions regarding this: How do we decide on the monitoring strategies for different environments? How do we automate and manage SQL Server databases? Can the monitoring task be automated using T-SQL or PowerShell or any other tools or techniques? Does the SQL engine provide the required capabilities to schedule a job and run it across multiple servers? What are the customization options available? Do we have a robust method to perform the backup activity? 我们当中有些人可能对此有很多疑问：我们如何确定不同环境的监视策略？ 我们如何自动化和管理SQL Server数据库？ 可以使用T-SQL或PowerShell或任何其他工具或技术自动执行监视任务吗？ SQL引擎是否提供计划作业并在多台服务器上运行所需的功能？ 有哪些自定义选项？ 我们是否有一种健壮的方法来执行备份活动？ The monitoring techniques using T-SQL, XML, and PowerShell are used to monitor a SQL Server database using native methods are discussed in this article. 本文讨论了使用T-SQL，XML和PowerShell的监视技术，用于使用本机方法监视SQL Server数据库。 Topics covered in this resource include: 本资源涵盖的主题包括： Discuss the implementation architecture 讨论实施架构 XML string manipulation XML字符串操作 Prepare the SQL and PowerShell scripts 准备SQL和PowerShell脚本 Create SQL Jobs 创建SQL作业 And more… 和更多… 数据流程图 (Data Flow Diagram)Let’s look at the high-level view of this implementation: 让我们看一下该实现的高级视图：
The instance details are the source for this implementation. The server details are stored in a text file. On the dedicated instance, the SQL job is scheduled to traverse across the entire list of servers. The SQL file is copied to a shared path and is executed on each server to generate the XML data for only the offline databases. The generated XML data is then fed as an input parameter to the stored procedure for further data manipulation. In the Stored Procedure, using dynamic SQL, an HTML is prepared and is sent as an email to the DBA’s. 实例详细信息是此实现的源。 服务器详细信息存储在文本文件中。 在专用实例上，计划将SQL作业遍历整个服务器列表。 该SQL文件被复制到共享路径，并在每个服务器上执行以仅为脱机数据库生成XML数据。 然后将生成的XML数据作为输入参数输入到存储过程中，以进行进一步的数据处理。 在存储过程中，使用动态SQL编写HTML，并将其作为电子邮件发送给DBA。 The implementation can also be done using JSON transformation. JSON is supported starting from SQL 2016. Since JSON penetration is not good enough at the time, XML is the preferred choice for data interchange format for now—until JSON takes over as the de-facto format. 也可以使用JSON转换来实现。 从SQL 2016开始支持JSON。由于JSON当时还不够好，因此XML是目前数据交换格式的首选-直到JSON成为事实上的格式为止。 准备脚本 (Prepare the script)Let’s now go through the steps to create the script. It’s good to have an understanding of the database states, to understand the script. 现在让我们完成创建脚本的步骤。 了解数据库状态 ，了解脚本是很好的。 select a.name, a.state_desc, case When a.state = 0 then 'ONLINE' when a.state = 1 then 'RESTORING' when a.state = 2 then 'RECOVERING' when a.state = 3 then 'RECOVERY_PENDING' when a.state = 4 then 'SUSPECT' when a.state = 5 then 'EMERGENCY' when a.state = 6 then 'OFFLINE' when a.state = 7 then 'COPYING - SQL AZURE' when a.state = 10 then 'OFLINE_SECONDARY - SQL AZURE' end from sys.databases a
Step 1: Prepare XML Data 步骤1：准备XML数据 The XML data is generated by querying the sys.databases system object for offline databases. The query results are then transformed as nested XML elements. The simple AUTO option is very useful in generating the raw data. 通过查询sys.databases系统对象中的脱机数据库来生成XML数据。 然后将查询结果转换为嵌套的XML元素。 简单的AUTO选项在生成原始数据时非常有用。 The below T-SQL statements are placed in an SQL file. This file will be executed across all the SQL instances and the output is then fed to the stored procedure. 下面的T-SQL语句放置在一个SQL文件中。 该文件将在所有SQL实例上执行，然后将输出馈送到存储过程。 --declare table variable to hold multiple resultset DECLARE @T table (X nvarchar(MAX)); --Variable declaration to hold XML data DECLARE @XML nvarchar(MAX) DECLARE @XML_content xml --Declare table variable DECLARE @DatabaseState table (DBName varchar(100),DBState varchar(50)) INSERT INTO @DatabaseState select name, state_desc from sys.databases IF (SELECT COUNT(*) FROM @DatabaseState WHERE DBState 'Online') > 0 BEGIN WITH SQLShackDemoCTE AS ( select @@servername ServerName,DBName, DBState FROM @DatabaseState status where DBState 'Online' ) insert into @T(X) select ( select * from SQLShackDemoCTE FOR XML AUTO ); select @xml=X from @T END SET @xml = N''+@XML+'' --print @xml SET @XML_content=@XML SELECT @XML_content
When you click the above XML tag, the data shown like below 当您单击上面的XML标签时，数据如下所示
Step 2: Create Stored Procedure USP_InsertDBStatus 步骤2：创建存储过程USP_InsertDBStatus The XML raw output from the SQL File is fed as an input parameter to the stored procedure. SQL文件的XML原始输出作为输入参数输入到存储过程。 This stored procedure has two sections: 此存储过程分为两部分： Preparing the XML 准备XML The XML raw-data is processed using the OPENXML clause. The OPENXML clause uses xml-document ID and XML text as inputs and uses the MSXL parser to parse the text and return each element for further transformation or data manipulation. 使用OPENXML子句处理XML原始数据。 OPENXML子句使用xml文档ID和XML文本作为输入，并使用MSXL解析器解析文本并返回每个元素以进行进一步转换或数据处理。 Preparing the Email 准备电子邮件 The FOR XML PATH clause is used to generate an HTML table of data. The XML tags are then transformed as SQL columns. The data is then parsed as valid HTML tags. Using Database Mail, an email is sent to intended recipients with the data. FOR XML PATH子句用于生成HTML数据表。 然后将XML标记转换为SQL列。 然后，将数据解析为有效HTML标签。 使用数据库邮件 ，电子邮件将与数据一起发送给预期的收件人。 Note: The same output can also derived by using the concept of XML nodes for efficient data processing using the OPENXML clause. 注意：也可以通过使用XML节点的概念来导出相同的输出，以便使用OPENXML子句进行有效的数据处理。 CREATE PROCEDURE [dbo].[USP_InsertDBStatus] ( @XmlData XML ) AS BEGIN SET NOCOUNT ON; DECLARE @hdoc AS INT; declare @State varchar(10) declare @Database_Name varchar(100) declare @Server_Name varchar(100) declare @EmailBody varchar(100) declare @DBName varchar(max) declare @Html nvarchar(max) set @State = '' declare @DBStatusCheck table ( [Server] varchar(25) NOT NULL, [DBStatusID] INT IDENTITY(1, 1) NOT NULL ,[StatusCheckDateTime] DATETIME NOT NULL ,[DBName] VARCHAR(128) NULL ,[DBState] VARCHAR(128) NULL ) --Create an internal representation of the XML document. EXECUTE [dbo].[sp_xml_preparedocument] @hdoc OUTPUT, @XmlData; INSERT INTO @DBStatusCheck ( [Server] ,[StatusCheckDateTime] ,[DBName] ,[DBState] ) SELECT [ServerName]=DBstatus.[Servername] ,[CheckDate] = CURRENT_TIMESTAMP ,[DBName] = DBstatus.[DBName] ,[DBState] = DBstatus.[DBState] FROM OPENXML(@hdoc,'//SQLShackDemoCTE') WITH (ServerName varchar(100), DBName VARCHAR(130), DBState VARCHAR(130)) AS DBstatus; EXECUTE [dbo].[sp_xml_removedocument] @hdoc; select @Server_Name= server from @DBStatusCheck where [DBStatusID]=1 if (select COUNT(*) from @DBStatusCheck) > 0 begin SET @HTML = N'Databases not online on '+@Server_Name+'' + N'' + N' Server Name Database Name State StatusCheck DateTime' + CAST ( ( SELECT td= Server, '', td = DBName, '', td = DBState, '', td=StatusCheckDateTime, '' FROM @DBStatusCheck FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'' ; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'PowerSQL', @recipients = '[email protected]', @body = @Html, @body_format = 'HTML', @subject = 'HIGH Alert : Database Not Online'; End END;
Here are some of the points we need to keep in mind: 我们需要牢记以下几点： Invoke-SQLcmd can be executed to generate XML raw-data Invoke-SQLcmd来生成XML原始数据 The PowerShell script is used to call the stored procedures which have already been created in a dedicated SQL instance. PowerShell脚本用于调用已在专用SQL实例中创建的存储过程。 Import-csv \\hq6021\C$\InputServer.csv|%{ If ((Test-Connection $_.ServerName -count 1 -quiet)) { write-host $_.Servername $sqlcmd=Invoke-Sqlcmd -InputFile \\hqdbt01\F$\PowerSQL\DBStatusSQLQuery.sql -ServerInstance $_.Servername -Database master $xmldata=$sqlcmd.Column1 if($xmldata -ne '') { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection; $SqlConnection.ConnectionString = "Server=HQDBT01;Database=SQLShackDemo;Integrated Security=TRUE;"; $SqlConnection.Open(); $SqlCommand = New-Object System.Data.SqlClient.SqlCommand; $SqlCommand.CommandTimeout = 120; $SqlCommand.Connection = $SqlConnection; $SqlCommand.CommandText = "EXECUTE [dbo].[USP_InsertDBStatus] @XmlData = N'$XmlData';"; $Result = $SqlCommand.ExecuteNonQuery(); $SqlConnection.Close(); } Create the SQL Job 创建SQL作业Follow the job creation process steps to create the SQL job. 按照作业创建过程中的步骤创建SQL作业。 Step name and choose 步骤名称，然后从“ Transact-SQL Script (T-SQL) from the 类型”下拉列表中选择“ Type dropdown Transact-SQL脚本（T-SQL） ” Command, call the PowerShell function 命令中 ，调用PowerShell函数 Click OK 点击确定
The aforementioned steps result in a Multi-Server-DBStatus-Check-Alert job. 前述步骤将导致“ 多服务器DBStatus-Check-Alert”作业。
Right-click and run the job. You can also schedule it to run across all the instances, at a ten-minute frequency. 右键单击并运行作业。 您还可以安排它以十分钟的频率在所有实例上运行。
The output reveals shows the statuses of the databases. 输出显示了数据库的状态。
In an environment that relies on SQL-managed native methodologies, we could use PowerShell scripts using SMO or T-SQL. Using native methods, sometimes it’s tedious to measure every aspect of the SQL Server database status monitoring. The native methods have a few limitations, which would have to be kept in mind when proceeding. In some cases, this leads to substantial reliance on scripting, which may not be every administrator’s cup of tea. However, there are several third party monitoring solutions available in today’s market, which integrate alerts to ticketing system or send email notifications when the state of a database is not online. It really is about what suits our requirements. We must judge the environment, and work through it. I hope the script in the article helps towards getting an availability report about the managed databases. 在依赖SQL管理的本机方法的环境中，我们可以使用通过SMO或T-SQL使用PowerShell脚本。 使用本机方法，有时对SQL Server数据库状态监视的各个方面进行度量很繁琐。 本机方法有一些限制，在继续操作时必须牢记这些限制。 在某些情况下，这会导致严重依赖脚本编写，而脚本编写可能并不是每个管理员都需要的。 但是，当今市场上有几种第三方监视解决方案，它们可以将警报集成到票务系统中，或者在数据库状态不在线时发送电子邮件通知。 确实是什么适合我们的要求。 我们必须判断环境，并努力解决它。 我希望本文中的脚本有助于获取有关托管数据库的可用性报告。 翻译自: https://www.sqlshack.com/monitoring-sql-server-database-status-changes-using-t-sql-powershell-scripts/ |
【本文地址】