监控数据库的数据更新

[足球不是方的]

需求：
监控ip端，20.20.3 开头的ip对数据库A的所有insert,update,delete 操作
解决方案1：
proflie
过滤条件 ：1.databasename=‘A’;2.hostname=（ip端内所有的主机），3.textdata like '%insert,update,delete%'

优势：持续监控，不会漏数据
缺点：一直启动profile监控，很吃性能

方案2：
sql+作业
declare @n int=0
while @n<10
begin
insert into [DBA_Minitor].[dbo].[table1]([Database],[Individual Query],[Parent Query] ,[Program] ,[Hostname],[Start_Time],[Client_Net_Address])
Select
[Database] = Db_Name(Sp.Dbid)
, [Individual Query] = Substring (Qt.Text,
Er.Statement_Start_Offset/2,
(Case When Er.Statement_End_Offset = -1
Then Len(Convert(nvarchar(max), Qt.Text)) * 2
Else Er.Statement_End_Offset End -
Er.Statement_Start_Offset)/2)
,[Parent Query] = Qt.Text
, Program = Program_Name
, Hostname
, Start_Time
,c.Client_Net_Address
From Sys.Dm_Exec_Requests Er
Inner Join Sys.Sysprocesses Sp On Er.Session_Id=Sp.Spid
inner join Sys.Dm_Exec_Connections C On Er.Session_Id=C.Session_Id
Cross Apply Sys.Dm_Exec_Sql_Text(Er.Sql_Handle)As Qt
Where Er.Session_Id > 50 -- Ignore system spids.
And Er.Session_Id Not In (@@Spid) -- Ignore this current statement.
and (Qt.text like '%insert%' or Qt.text like '%update%' or Qt.text like '%delete%' )
and Sp.Dbid in (11)
and Client_Net_Address like '20.20.3%'
set @n=@n+1
waitfor delay '00:00:00.800'

end

作业：1分钟执行一次

优势：数据库性能不会太受影响
缺点：无法做到实时监控，会漏掉数据


问题：
1.对于方案一:启动profile监控，忘记关闭跟踪导致服务器空间被占满，那如果是我本机开profile监控服务器的数据库，一直在监控，是影响我本机吧？对服务器有什么影响吗？
对于方案二：怎么优化能让数据漏掉最少
2.是否可用数据库触发器来实现以上功能？对于触发器只能用ddl事件，dml事件不能针对整个数据库。

[吉普赛的歌]

你数据库的版本是多少？

select @@version

profiler 对服务器性能影响很不大， 不建议。 如果是 2005 ， 那没很好的办法， 用触发器吧。 2008或更高版本， 可以用 CDC: http://blog.csdn.net/dba_huangzj/article/details/8130448 2016或更高版本， 可以用历史表： http://blog.csdn.net/burgess_liu/article/details/51557520

[OwenZeng_DBA]

引用 楼主 beck464771647 的回复:

需求： 监控ip端，20.20.3 开头的ip对数据库A的所有insert,update,delete 操作 解决方案1： proflie 过滤条件 ：1.databasename=‘A’;2.hostname=（ip端内所有的主机），3.textdata like '%insert,update,delete%' 优势：持续监控，不会漏数据 缺点：一直启动profile监控，很吃性能 方案2： sql+作业 declare @n int=0 while @n<10 begin insert into [DBA_Minitor].[dbo].[table1]([Database],[Individual Query],[Parent Query] ,[Program] ,[Hostname],[Start_Time],[Client_Net_Address]) Select [Database] = Db_Name(Sp.Dbid) , [Individual Query] = Substring (Qt.Text, Er.Statement_Start_Offset/2, (Case When Er.Statement_End_Offset = -1 Then Len(Convert(nvarchar(max), Qt.Text)) * 2 Else Er.Statement_End_Offset End - Er.Statement_Start_Offset)/2) ,[Parent Query] = Qt.Text , Program = Program_Name , Hostname , Start_Time ,c.Client_Net_Address From Sys.Dm_Exec_Requests Er Inner Join Sys.Sysprocesses Sp On Er.Session_Id=Sp.Spid inner join Sys.Dm_Exec_Connections C On Er.Session_Id=C.Session_Id Cross Apply Sys.Dm_Exec_Sql_Text(Er.Sql_Handle)As Qt Where Er.Session_Id > 50 -- Ignore system spids. And Er.Session_Id Not In (@@Spid) -- Ignore this current statement. and (Qt.text like '%insert%' or Qt.text like '%update%' or Qt.text like '%delete%' ) and Sp.Dbid in (11) and Client_Net_Address like '20.20.3%' set @n=@n+1 waitfor delay '00:00:00.800' end 作业：1分钟执行一次 优势：数据库性能不会太受影响 缺点：无法做到实时监控，会漏掉数据 问题： 1.对于方案一:启动profile监控，忘记关闭跟踪导致服务器空间被占满，那如果是我本机开profile监控服务器的数据库，一直在监控，是影响我本机吧？对服务器有什么影响吗？ 对于方案二：怎么优化能让数据漏掉最少 2.是否可用数据库触发器来实现以上功能？对于触发器只能用ddl事件，dml事件不能针对整个数据库。

引用 楼主 beck464771647 的回复:

需求： 监控ip端，20.20.3 开头的ip对数据库A的所有insert,update,delete 操作 解决方案1： proflie 过滤条件 ：1.databasename=‘A’;2.hostname=（ip端内所有的主机），3.textdata like '%insert,update,delete%' 优势：持续监控，不会漏数据 缺点：一直启动profile监控，很吃性能 方案2： sql+作业 declare @n int=0 while @n<10 begin insert into [DBA_Minitor].[dbo].[table1]([Database],[Individual Query],[Parent Query] ,[Program] ,[Hostname],[Start_Time],[Client_Net_Address]) Select [Database] = Db_Name(Sp.Dbid) , [Individual Query] = Substring (Qt.Text, Er.Statement_Start_Offset/2, (Case When Er.Statement_End_Offset = -1 Then Len(Convert(nvarchar(max), Qt.Text)) * 2 Else Er.Statement_End_Offset End - Er.Statement_Start_Offset)/2) ,[Parent Query] = Qt.Text , Program = Program_Name , Hostname , Start_Time ,c.Client_Net_Address From Sys.Dm_Exec_Requests Er Inner Join Sys.Sysprocesses Sp On Er.Session_Id=Sp.Spid inner join Sys.Dm_Exec_Connections C On Er.Session_Id=C.Session_Id Cross Apply Sys.Dm_Exec_Sql_Text(Er.Sql_Handle)As Qt Where Er.Session_Id > 50 -- Ignore system spids. And Er.Session_Id Not In (@@Spid) -- Ignore this current statement. and (Qt.text like '%insert%' or Qt.text like '%update%' or Qt.text like '%delete%' ) and Sp.Dbid in (11) and Client_Net_Address like '20.20.3%' set @n=@n+1 waitfor delay '00:00:00.800' end 作业：1分钟执行一次 优势：数据库性能不会太受影响 缺点：无法做到实时监控，会漏掉数据 问题： 1.对于方案一:启动profile监控，忘记关闭跟踪导致服务器空间被占满，那如果是我本机开profile监控服务器的数据库，一直在监控，是影响我本机吧？对服务器有什么影响吗？ 对于方案二：怎么优化能让数据漏掉最少 2.是否可用数据库触发器来实现以上功能？对于触发器只能用ddl事件，dml事件不能针对整个数据库。

你是想做数据库的审计吗？

[OwenZeng_DBA]

引用 8 楼 beck464771647 的回复:

数据库审核对这个需求不太符合，主要有2个限制，第一个iP，需要监控ip段内所有的主机。第二个，是对整个数据库表的监控，cdc和数据库审核这2中方法都是针对单个表，当数据库的表很多的时候就不行了。

那就用trace吧（不开界面那种），不是profiler。可以单独在找台记录里运行trace，比如你的本机。如果这个IP段的DML操作不是特别频繁的话，性能应该还能接受

[足球不是方的]

其实我想要这样的结果，监控开发人员更新数据库的操作。是整个库的表，用cdc的话针对单个表还行，针对整个数据库就有点麻烦了。现在数据库版本是2012 企业版

[足球不是方的]

图片不清晰，换个

[OwenZeng_DBA]

引用 4 楼 beck464771647 的回复:

图片不清晰，换个

为什么要把数据库的所有更新操作都记录下来啊，，这样的开销太大

[足球不是方的]

没法啊，老板的需求。。。。

[OwenZeng_DBA]

引用 6 楼 beck464771647 的回复:

没法啊，老板的需求。。。。

看看SQL SERVER AUDIT.数据库审核的功能 http://www.cnblogs.com/Joe-T/p/4312878.html

[足球不是方的]

数据库审核对这个需求不太符合，主要有2个限制，第一个iP，需要监控ip段内所有的主机。第二个，是对整个数据库表的监控，cdc和数据库审核这2中方法都是针对单个表，当数据库的表很多的时候就不行了。

[fabulasia]

kafka ogg可以做到