SqlServer:SqlServer(服务器磁盘监控,创建管理员账号分配权,添加链接服务器,查询CPU,查询内存)
1.服务器磁盘监控
(1)总链接服务上开启所有链接服务器的RPC:
----------------------总链接服务器上面,开启每个服务器的RPC --exec sp_serveroption @server='S32' , @optname= 'rpc', @optvalue ='TRUE' --exec sp_serveroption @server='S32' , @optname= 'rpc out', @optvalue='TRUE' SELECT 'exec sp_serveroption @server= '''+ name+''' , @optname= ''rpc'',@optvalue =''TRUE'' ' FROM sys.servers ORDER BY name SELECT 'exec sp_serveroption @server= '''+ name+''' , @optname= ''rpc out'',@optvalue =''TRUE'' ' FROM sys.servers ORDER BY name
(2)各个服务上创建监测(根据本地情况,需要改总链接服务器IP和本机IP):
------添加链接服务器 IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name like '%100.80.10.30%') BEGIN EXEC master.dbo.sp_addlinkedserver --服务器名称 @server = '100.80.10.30', --36-48,12-15, @srvproduct = N'', @provider = 'SQLOLEDB', --IP @datasrc = '100.80.10.30'; EXEC master.dbo.sp_addlinkedsrvlogin --IP '100.80.10.30', false, NULL, --账号 'sa', --密码 'hnsjt_lwsj@2018'; END; GO CREATE DATABASE [monitor]; GO USE [monitor]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO -- ============================================= -- Author: <Author,,Name> k -- Create date: <Create Date,,>20190218 -- Description: <Description,,>监控磁盘使用情况 -- ============================================= CREATE PROCEDURE disk_monitor AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --创建相关表 IF OBJECT_ID('tempdb..#server_disk_usage') IS NULL BEGIN CREATE TABLE #server_disk_usage ( [ip] [NVARCHAR](400) , [disk_num] [NVARCHAR](10) NOT NULL , [total_size_mb] [BIGINT] NOT NULL CONSTRAINT [DF_#server_disk_usage_total_size_mb] DEFAULT ( (0) ) , [free_siez_mb] [BIGINT] NOT NULL CONSTRAINT [DF_#server_disk_usage_free_siez_mb] DEFAULT ( (0) ) , [disk_info] [NVARCHAR](400) NOT NULL CONSTRAINT [DF_#server_disk_usage_disk_info] DEFAULT ( '' ) , [iswarning] [NVARCHAR](400) , [check_time] [DATETIME] NOT NULL CONSTRAINT [DF_#server_disk_usage_check_time] DEFAULT ( GETDATE() ) , CONSTRAINT [PK_#server_disk_usage] PRIMARY KEY CLUSTERED ( [disk_num] ASC ) ) ON [PRIMARY]; END; --========================================== --查看所有数据库使用到的磁盘剩余空间 DECLARE @disk TABLE ( [disk_num] VARCHAR(50) , [free_siez_mb] INT ); INSERT INTO @disk EXEC xp_fixeddrives; --更新当前磁盘的剩余空间信息 UPDATE M SET M.[free_siez_mb] = D.[free_siez_mb] FROM #server_disk_usage AS M INNER JOIN @disk AS D ON M.[disk_num] = D.[disk_num]; --插入新增磁盘的剩余空间信息 INSERT INTO #server_disk_usage ( [disk_num] , [free_siez_mb] ) SELECT [disk_num] , [free_siez_mb] FROM @disk AS D WHERE NOT EXISTS ( SELECT 1 FROM #server_disk_usage AS M WHERE M.[disk_num] = D.[disk_num] ); -------------------------------------------------------2 --开启CMDShell EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE; --======================================== --创建临时表用来存放每个盘符的数据 CREATE TABLE #tempDisks ( ID INT IDENTITY(1, 1) , DiskSpace NVARCHAR(200) ); --============================================ --将需要检查的磁盘放入临时表#checkDisks SELECT ROW_NUMBER() OVER ( ORDER BY [disk_num] ) AS RID , [disk_num] INTO #checkDisks FROM #server_disk_usage WHERE [total_size_mb] = 0; --============================================ --循环临时表#checkDisks检查每个磁盘的总量 DECLARE @disk_num NVARCHAR(20); DECLARE @total_size_mb INT; DECLARE @sql NVARCHAR(200); DECLARE @max INT; DECLARE @min INT; SELECT @max = MAX(RID) , @min = MIN(RID) FROM #checkDisks; WHILE ( @min <= @max ) BEGIN SELECT @disk_num = [disk_num] FROM #checkDisks WHERE RID = @min; SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree ' + @disk_num + ':' + ''''; PRINT @sql; INSERT INTO #tempDisks EXEC ( @sql ); SELECT @total_size_mb = CAST(( RIGHT(DiskSpace, LEN(DiskSpace) - CHARINDEX(': ', DiskSpace) - 1) ) AS BIGINT) / 1024 / 1024 FROM #tempDisks WHERE ID = 2; --SELECT @total_size_mb,@disk_num UPDATE #server_disk_usage SET [total_size_mb] = @total_size_mb WHERE [disk_num] = @disk_num; --SELECT * FROM #tempDisks TRUNCATE TABLE #tempDisks; SET @min = @min + 1; END; --========================================== --CMDShell EXEC sp_configure 'xp_cmdshell', 0; EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; DELETE FROM [100.80.10.30].[monitor].[dbo].[server_disk_usage] WHERE CONVERT(VARCHAR, check_time, 23) = CONVERT(VARCHAR, GETDATE(), 23) AND ip = '100.80.10.48'; INSERT INTO [100.80.10.30].[monitor].[dbo].[server_disk_usage] SELECT '100.80.10.48' AS IP , [disk_num] AS Drive_Name , CAST([total_size_mb] / 1024.0 AS NUMERIC(18, 2)) AS Total_Space_GB , CAST(( [total_size_mb] - [free_siez_mb] ) / 1024.0 AS NUMERIC(18, 2)) AS Used_Space_GB , CAST([free_siez_mb] / 1024.0 AS NUMERIC(18, 2)) AS Free_Space_GB , CAST([free_siez_mb] * 100 / [total_size_mb] AS NUMERIC(18, 2)) AS Free_Space_Percent , [disk_info] , [check_time] FROM #server_disk_usage; DROP TABLE #server_disk_usage; END; GO
(3)总链接服务器创建监测:
CREATE DATABASE [monitor]; GO
USE [monitor]; GO /****** Object: StoredProcedure [dbo].[get_disk_use_info] Script Date: 02/19/2019 09:46:23 ******/ SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO -- ============================================= -- Author: <Author,,Name> k -- Create date: <Create Date,,>20190218 -- Description: <Description,,>监控磁盘使用情况 -- ============================================= CREATE PROCEDURE [dbo].[get_disk_use_info] AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --****************************************************** --****************************************************** IF OBJECT_ID('server_disk_usage') IS NOT NULL BEGIN DROP TABLE server_disk_usage; END; --========================================== --创建相关表 IF OBJECT_ID('tempdb..#server_disk_usage') IS NULL BEGIN CREATE TABLE #server_disk_usage ( [disk_num] [NVARCHAR](10) NOT NULL , [total_size_mb] [BIGINT] NOT NULL CONSTRAINT [DF_#server_disk_usage_total_size_mb] DEFAULT ( (0) ) , [free_siez_mb] [BIGINT] NOT NULL CONSTRAINT [DF_#server_disk_usage_free_siez_mb] DEFAULT ( (0) ) , [disk_info] [NVARCHAR](400) NOT NULL CONSTRAINT [DF_#server_disk_usage_disk_info] DEFAULT ( '' ) , [check_time] [DATETIME] NOT NULL CONSTRAINT [DF_#server_disk_usage_check_time] DEFAULT ( GETDATE() ) , CONSTRAINT [PK_#server_disk_usage] PRIMARY KEY CLUSTERED ( [disk_num] ASC ) ) ON [PRIMARY]; END; --========================================== --查看所有数据库使用到的磁盘剩余空间 DECLARE @disk TABLE ( [disk_num] VARCHAR(50) , [free_siez_mb] INT ); INSERT INTO @disk EXEC xp_fixeddrives; --更新当前磁盘的剩余空间信息 UPDATE M SET M.[free_siez_mb] = D.[free_siez_mb] FROM #server_disk_usage AS M INNER JOIN @disk AS D ON M.[disk_num] = D.[disk_num]; --插入新增磁盘的剩余空间信息 INSERT INTO #server_disk_usage ( [disk_num] , [free_siez_mb] ) SELECT [disk_num] , [free_siez_mb] FROM @disk AS D WHERE NOT EXISTS ( SELECT 1 FROM #server_disk_usage AS M WHERE M.[disk_num] = D.[disk_num] ); -------------------------------------------------------2 --开启CMDShell EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE; --======================================== --创建临时表用来存放每个盘符的数据 CREATE TABLE #tempDisks ( ID INT IDENTITY(1, 1) , DiskSpace NVARCHAR(200) ); --============================================ --将需要检查的磁盘放入临时表#checkDisks SELECT ROW_NUMBER() OVER ( ORDER BY [disk_num] ) AS RID , [disk_num] INTO #checkDisks FROM #server_disk_usage WHERE [total_size_mb] = 0; --============================================ --循环临时表#checkDisks检查每个磁盘的总量 DECLARE @disk_num NVARCHAR(20); DECLARE @total_size_mb INT; DECLARE @sql NVARCHAR(200); DECLARE @max INT; DECLARE @min INT; SELECT @max = MAX(RID) , @min = MIN(RID) FROM #checkDisks; WHILE ( @min <= @max ) BEGIN SELECT @disk_num = [disk_num] FROM #checkDisks WHERE RID = @min; SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree ' + @disk_num + ':' + ''''; PRINT @sql; INSERT INTO #tempDisks EXEC ( @sql ); SELECT @total_size_mb = CAST(( RIGHT(DiskSpace, LEN(DiskSpace) - CHARINDEX(': ', DiskSpace) - 1) ) AS BIGINT) / 1024 / 1024 FROM #tempDisks WHERE ID = 2; --SELECT @total_size_mb,@disk_num UPDATE #server_disk_usage SET [total_size_mb] = @total_size_mb WHERE [disk_num] = @disk_num; --SELECT * FROM #tempDisks TRUNCATE TABLE #tempDisks; SET @min = @min + 1; END; --========================================== --CMDShell EXEC sp_configure 'xp_cmdshell', 0; EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; IF OBJECT_ID('server_disk_usage') IS NULL BEGIN CREATE TABLE server_disk_usage ( ip VARCHAR(200) , Drive_Name VARCHAR(200) , Total_Space_GB VARCHAR(200) , Used_Space_GB VARCHAR(200) , Free_Space_GB VARCHAR(200) , Free_Space_Percent VARCHAR(200) , [disk_info] VARCHAR(200) , [check_time] DATETIME ); END; INSERT INTO [server_disk_usage] SELECT '100.80.10.30' AS IP , [disk_num] AS Drive_Name , CAST([total_size_mb] / 1024.0 AS NUMERIC(18, 2)) AS Total_Space_GB , CAST(( [total_size_mb] - [free_siez_mb] ) / 1024.0 AS NUMERIC(18, 2)) AS Used_Space_GB , CAST([free_siez_mb] / 1024.0 AS NUMERIC(18, 2)) AS Free_Space_GB , CAST([free_siez_mb] * 100 / [total_size_mb] AS NUMERIC(18, 2)) AS Free_Space_Percent , [disk_info] , [check_time] FROM #server_disk_usage; DROP TABLE #server_disk_usage; END;
(4)在总链接服务器上创建调用的存储过程,调用各个服务器上的监控,更新各个服务器磁盘使用情况,存放至 [NETWORKING_AUDIT] 库的T_DISK_MONITOR表
-- ============================================= -- Author: k -- Create date: 20190219 -- Description: 更新各个服务器磁盘使用情况,存放至T_DISK_MONITOR表 -- ============================================= ALTER PROCEDURE [dbo].[updateDiskMonitorInfo] AS BEGIN -------------调用各个服务器上的监控 EXEC [monitor].[dbo].[get_disk_use_info] DECLARE @ServerName VARCHAR(255); DECLARE @sql VARCHAR(8000); DECLARE cur CURSOR FOR SELECT name FROM sys.servers WHERE name !='WIN-4AAGSGNR81A' OPEN cur; FETCH NEXT FROM cur INTO @ServerName; WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'EXEC ['+@ServerName+'].[monitor]..[disk_monitor];' EXEC (@sql); FETCH NEXT FROM cur INTO @ServerName; END; CLOSE cur; DEALLOCATE cur; -------------将磁盘使用情况存放至系统库[NETWORKING_AUDIT] IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T_DISK_MONITOR') BEGIN DROP TABLE [NETWORKING_AUDIT].[dbo].[T_DISK_MONITOR]; END SELECT * INTO [NETWORKING_AUDIT].[dbo].[T_DISK_MONITOR] from [monitor].[dbo].[server_disk_usage] ORDER BY ip END
(5)创建SQLSERVER定时作业,每天自动更新即可
如图:
2.创建管理员账号分配权限
(1)基本语法:
--查看数据库schema, user 的存储过程 select * from sys.database_principals select * from sys.schemas select * from sys.server_principals select * from sysusers select * from sys.syslogins --创建登陆帐户(create login) create login dba with password='abcd1234@', default_database=mydb --为登陆账户创建数据库用户(create user),在mydb数据库中的security中的user下可以找到新创建的dba create user dba for login dba with default_schema=dbo --通过加入数据库角色,赋予数据库用户“db_owner”权限 exec sp_addrolemember 'db_owner', 'dba' --让 SQL Server 登陆帐户“dba”访问多个数据库 use mydb2 go create user dba for login dba with default_schema=dbo go exec sp_addrolemember 'db_owner', 'dba'go --禁用登陆帐户 alter login dba disable --启用登陆帐户 alter login dba enable --登陆帐户改名 alter login dba with name=dba_tom --登陆帐户改密码: alter login dba with password='aabb@ccdd' --数据库用户改名: alter user dba with name=dba_tom --更改数据库用户 defult_schema: alter user dba with default_schema=sales --删除数据库用户: drop user dba --删除 SQL Server登陆帐户: drop login dba --创建角色 r_test EXEC sp_addrole 'r_test' --添加登录 l_test,设置密码为pwd,默认数据库为pubs EXEC sp_addlogin 'l_test','a@cd123','InsideTSQL2008' --为登录 l_test 在数据库 pubs 中添加安全账户 u_test EXEC sp_grantdbaccess 'l_test','u_test' --添加 u_test 为角色 r_test 的成员 EXEC sp_addrolemember 'r_test','u_test' --用l_test登陆,发现在SSMS中找不到仍和表,因此执行下述两条语句出错。 select * from Sales.Orders select * from HR.Employees --授予角色 r_test 对 HR.Employees 表的所有权限 GRANT ALL ON HR.Employees TO r_test --The ALL permission is deprecated and maintained only for compatibility. --It DOES NOT imply ALL permissions defined on the entity. --ALL 权限已不再推荐使用,并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。 --测试可以查询表HR.Employees,但是Sales.Orders无法查询 select * from HR.Employees --如果要收回权限,可以使用如下语句。(可选择执行) revoke all on HR.Employees from r_test --ALL 权限已不再推荐使用,并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。 --授予角色 r_test 对 Sales.Orders 表的 SELECT 权限 GRANT SELECT ON Sales.Orders TO r_test --用l_test登陆,发现可以查询Sales.Orders和HR.Employees两张表 select * from Sales.Orders select * from HR.Employees --拒绝安全账户 u_test 对 HR.Employees 表的 SELECT 权限 DENY SELECT ON HR.Employees TO u_test --再次执行查询HR.Employees表的语句,提示:拒绝了对对象 'Employees' (数据库 'InsideTSQL2008',架构 'HR')的 SELECT 权限。 select * from HR.Employees --重新授权 GRANT SELECT ON HR.Employees TO u_test --再次查询,可以查询出结果。 select * from HR.Employees USE InsideTSQL2008 --从数据库中删除安全账户,failed EXEC sp_revokedbaccess 'u_test' --删除角色 r_test,failed EXEC sp_droprole 'r_test' --删除登录 l_test,success EXEC sp_droplogin 'l_test'
(2)创建管理员用户和创建只读用户
--******************创建管理员账号******************-- --**************************************************-- USE master Go --创建登陆帐户(create login) create login lwsjbackgr with password='hnsjt_lwsj@2019', default_database=master --为登陆账户创建数据库用户(create user),在mydb数据库中的security中的user下可以找到新创建的dba create user lwsjbackgr for login lwsjbackgr with default_schema=dbo --添加服务器角色 EXEC master.dbo.sp_addsrvrolemember @loginame = N'lwsjbackgr', @rolename = N'sysadmin' --添加数据库角色 --use [国土资源厅] --create user sread for login sread with default_schema=dbo --此处分配只读 --exec sp_addrolemember 'db_datareader', 'sread' --禁用sa账号 alter login sa DISABLE --******************创建只读权限账号****************-- --**************************************************-- --------------------------强制中断连接中的数据库 CREATE TABLE #temt(spid NVARCHAR(max), ecid NVARCHAR(max), status NVARCHAR(max), logname NVARCHAR(max), hostname NVARCHAR(max), blk NVARCHAR(max), dbname NVARCHAR(max), cmd NVARCHAR(max), request_id NVARCHAR(max) ) INSERT INTO #temt exec sp_who IF EXISTS(SELECT 1 FROM #temt WHERE logname = 'sread') BEGIN DECLARE @spid NVARCHAR(200) DECLARE @ksql NVARCHAR(200) DECLARE cur CURSOR FOR SELECT spid FROM #temt WHERE logname = 'sread' OPEN cur; FETCH NEXT FROM cur INTO @spid; WHILE @@FETCH_STATUS = 0 BEGIN SET @ksql = 'KILL '+@spid EXEC (@ksql) FETCH NEXT FROM cur INTO @spid; END; CLOSE cur; DEALLOCATE cur; END DROP TABLE #temt --------------------------删除只读用户 IF EXISTS(Select 1 FROM sys.sysusers WHERE name ='sread') BEGIN DROP USER sread END IF EXISTS(Select 1 FROM sys.syslogins WHERE name ='sread') BEGIN DROP LOGIN sread END --创建只读用户(create login) create login sread with password='hnsjt_123456', default_database=master --为只读用户创建数据库用户(create user),在mydb数据库中的security中的user下可以找到新创建的dba create user sread for login sread with default_schema=dbo --------------------------每个数据库添加只读权限角色 DECLARE @DatabaseName VARCHAR(255); DECLARE @user VARCHAR(255); SET @user = 'sread' DECLARE @sql VARCHAR(8000); DECLARE cur CURSOR FOR SELECT name FROM sys.databases OPEN cur; FETCH NEXT FROM cur INTO @DatabaseName; WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'USE ['+@DatabaseName+'] IF EXISTS(Select 1 FROM sys.sysusers WHERE name = '''+@user+''') BEGIN EXEC sp_revokedbaccess '''+@user+''' END create user '+@user+' for login '+@user+' with default_schema=dbo EXEC sp_addrolemember ''db_datareader'','''+@user+''' ' EXEC (@sql); FETCH NEXT FROM cur INTO @DatabaseName; END; CLOSE cur; DEALLOCATE cur;
3.添加链接服务器
------查询现有链接服务器状态 SELECT * FROM SYS.servers --创删除远程链接服务器 --删除运行本地与远程之间的用户映射 execute sys.sp_droplinkedsrvlogin @rmtsrvname='100.80.10.30',@locallogin=null --删除链接服务器 execute sys.sp_dropserver @server='100.80.10.30',@droplogins='droplogins' --添加远程链接服务器 --创建远程链接服务器 execute sys.sp_addlinkedserver @server='100.80.10.30', --被访问的服务器别名 @srvproduct='', @provider='SQLOLEDB', @datasrc='100.80.10.30' --被访问的服务器地址(IP地址,端口号\服务器名称) --创建本地用户与远程服务器中用户之间的映射 execute sys.sp_addlinkedsrvlogin @rmtsrvname='100.80.10.30', --被访问的服务器别名 @useself='false', --是否通过模拟本地登录名或显式提交登录名和密码来连接到远程服务器 @locallogin=null, --本地登录 @rmtuser='lwsjbackgr', --用户名 @rmtpassword='hnsjt_lwsjdb@2019' --密码 go
4.查询CPU
DECLARE @ts_now bigint set @ts_now = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM master.sys.dm_os_sys_info WITH (NOLOCK)); SELECT DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] , SQLProcessUtilization AS [SQL Server Process CPU Utilization], SystemIdle AS [System Idle Process], (100 - SystemIdle - SQLProcessUtilization) AS [Other Process CPU Utilization] FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization], [timestamp] FROM ( SELECT [timestamp], CONVERT(xml, record) AS [record] FROM master.sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%%') AS x ) AS y ORDER BY record_id DESC;
5.查询内存
SELECT total_physical_memory_kb / 1024 AS [物理内存(MB)] , available_physical_memory_kb / 1024 AS [可用物理内存(MB)] , system_cache_kb / 1024 AS [系统缓存内存总量(MB)] , ( kernel_paged_pool_kb + kernel_nonpaged_pool_kb ) / 1024 AS [内核池内存总量(MB)] , total_page_file_kb / 1024 AS [操作系统报告的提交限制的大小(MB)] , available_page_file_kb / 1024 AS [未使用的页文件的总量(MB)] , system_memory_state_desc AS [内存状态说明] FROM sys.dm_os_sys_memory