sqlserver管理方案计划与维护.docVIP

/* 目录 TOC \o 1-3 \h \z \u 1 引言 2 1.1 编写目的 2 1.3 背景 2 2 sql server管理与维护 2 2.1需求的优化 2 2.2数据库访问IP控制 3 2.2.1创建登录触发器，限制IP地址登录 3 2.2.2删除登录触发器 5 2.3 数据库连接情况监控语句 7 2.4查看sql语句的执行时间 11 2.5数据库存储空间情况语句 11 1.查看整个数据库存储空间的使用情况： 11 2.查看指定数据库表的存储空间使用情况： 11 1 引言 1.1 编写目的 便于对数据库进行管理、维护 1.3 背景 以下6个需求： 数据库访问IP控制 数据库连接情况监控语句 找出SQL执行耗时操作语句 数据库整体资源负荷语句 数据库中杀死访问进程语句 数据库存储空间情况语句 2 sql server管理与维护 2.1需求的优化 数据库中杀死访问进程语句 在 数据库连接情况监控语句 可以实现，两个合并成一个。 数据库整体资源负荷语句， 分别在 数据库连接情况监控语句和数据库存储空间情况语句中实现,分别与另外两个合并 找出SQL执行耗时操作语句，语意不清晰，建议改成：查看sql语句的执行时间。 2.2数据库访问IP控制 2.2.1创建登录触发器，限制IP地址登录 步骤1.创建IP过滤表（仅允许表内的IP登录） USE cyt GO CREATE TABLE ValidIP ( IP NVARCHAR(15), ); GO 步骤2.插入过滤IP。例： USE cyt GO INSERT INTO dbo.ValidIP(IP) VALUES(192.168.1.94); 步骤3.创建登录触发器、 CREATE TRIGGER [tr_logon_CheckIP] ON ALL SERVER FOR LOGON AS BEGIN DECLARE @IP NVARCHAR(15); SET @IP = (SELECT EVENTDATA().value((/EVENT_INSTANCE/ClientHost)[1], NVARCHAR(15))); IF NOT EXISTS(SELECT IP FROM [cyt].[dbo].[ValidIP] WHERE IP = @IP) ROLLBACK; END; 步骤4.其他限制： 一）如果需要限制登录用户名使用如下的判断： ORIGINAL_LOGIN() = sa 例： CREATE TRIGGER [tr_connection_limit] ON ALL SERVER FOR LOGON AS BEGIN --限制test这个帐号的连接 IF ORIGINAL_LOGIN()= test ROLLBACK; END; 二）如果需要限制数据库角色使用如下的判断： IS_SRVROLEMEMBER(sysadmin) = 1 例： CREATE TRIGGER [tr_logon_CheckIP] ON ALL SERVER FOR LOGON AS BEGIN --限制数据库角色为sysadmin的用户登录 IF IS_SRVROLEMEMBER(sysadmin) = 1 BEGIN ROLLBACK; END; END; 2.2.2删除登录触发器 场景1.如果数据库依然能连接连接的话使用如下的语句对登录触发器进行删除： drop trigger登录触发器名 on all server 本例中对应的删除语句： drop trigger tr_logon_CheckIP on all server 场景2.如果数据库连接不上，在命令行通过使用DAC专用管理员连接，方法如下： 一、使用DAC专用管理员连接，在命令行输入如下命令： sqlcmd –A –S 服务器名称 查看sql server服务器名称使用如下的命令： select @@Servername 本地服务器的服务器名称是poem-PC 对应的命令如下： sqlcmd -A -S poem-PC 二、连接成功后输入上面删除登录触发器的语句： drop trigger tr_logon_CheckIP on all server go DAC连接成功的界面如下： DAC删除登录触发器删除成功的界面如下： 2.3 数据库连接情况监控语句 开始——所有程序——Microsoft sql server2012——SQL Serve