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
