mcrmg
asked on
monitor SQL
Hi,
We have two dbs on one server, one is development, one is staging, I use staging and the other guy is using dev, when he is done with dev, he dump data into staging, and I will verify the data before publish it to pdn.
It has happened 3 times already, some of the data in staging got changed, he said he did not change it, my question is, is there a software that I can install to monitor every move for sql?
we are using SQL2008 on Win2008 thx
We have two dbs on one server, one is development, one is staging, I use staging and the other guy is using dev, when he is done with dev, he dump data into staging, and I will verify the data before publish it to pdn.
It has happened 3 times already, some of the data in staging got changed, he said he did not change it, my question is, is there a software that I can install to monitor every move for sql?
we are using SQL2008 on Win2008 thx
lof
SQL Profiler, a tool shipped with SQL Server will show you all that's going on on an SQL Server
You can do some audits, sql2008 does have new constructs for Audit, and there are third party products like red hat and apexsql.
Is it just data or is it also structures ?
There is a good introduction to SQL2008 Audit via articles : https://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Audit-in-SQL-Server-2008.html
And for database structural changes, then can always use the good old DDL triggers at the database level :
-- create a log table to record all DDL events
CREATE TABLE ddl_log (ChangedOn datetime, ChangedBy nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
-- create a DDL trigger on this database
CREATE TRIGGER log ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log (ChangedOn, ChangedBy, Event, TSQL)
VALUES (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER), @data.value('(/EVENT_INSTA
GO
-- The above two items are a once off task. Once run, they are part of the database, and will start collecting information.
--
-- Test the trigger by doing a few different DDL acttivities
CREATE TABLE My_DDL_Testing (a int);
GO
ALTER TABLE My_DDL_Testing add b varchar(100);
GO
CREATE VIEW vw_My_DDL_Testing as
SELECT * from My_DDL_Testing
GO
ALTER VIEW vw_My_DDL_Testing as
select a,b from My_DDL_Testing
GO
CREATE PROCEDURE usp_get_My_DDL_Testing(@a int)
as
begin
select * from My_DDL_Testing where a = isnull(@a,a)
end
GO
-- now tidy up example DDL activities
DROP VIEW vw_My_DDL_Testing;
DROP PROCEDURE usp_get_My_DDL_Testing;
DROP TABLE My_DDL_Testing ;
GO
-- now let us look at what was logged...
SELECT * FROM ddl_log ;
GO
-- finish tidy up of our testing... have to drop the trigger first, otherwise will try to fire with the drop table...
DROP TRIGGER log ON DATABASE
GO
DROP TABLE ddl_log
GO
Is it just data or is it also structures ?
There is a good introduction to SQL2008 Audit via articles : https://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Audit-in-SQL-Server-2008.html
And for database structural changes, then can always use the good old DDL triggers at the database level :
-- create a log table to record all DDL events
CREATE TABLE ddl_log (ChangedOn datetime, ChangedBy nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
-- create a DDL trigger on this database
CREATE TRIGGER log ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log (ChangedOn, ChangedBy, Event, TSQL)
VALUES (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER), @data.value('(/EVENT_INSTA
GO
-- The above two items are a once off task. Once run, they are part of the database, and will start collecting information.
--
-- Test the trigger by doing a few different DDL acttivities
CREATE TABLE My_DDL_Testing (a int);
GO
ALTER TABLE My_DDL_Testing add b varchar(100);
GO
CREATE VIEW vw_My_DDL_Testing as
SELECT * from My_DDL_Testing
GO
ALTER VIEW vw_My_DDL_Testing as
select a,b from My_DDL_Testing
GO
CREATE PROCEDURE usp_get_My_DDL_Testing(@a int)
as
begin
select * from My_DDL_Testing where a = isnull(@a,a)
end
GO
-- now tidy up example DDL activities
DROP VIEW vw_My_DDL_Testing;
DROP PROCEDURE usp_get_My_DDL_Testing;
DROP TABLE My_DDL_Testing ;
GO
-- now let us look at what was logged...
SELECT * FROM ddl_log ;
GO
-- finish tidy up of our testing... have to drop the trigger first, otherwise will try to fire with the drop table...
DROP TRIGGER log ON DATABASE
GO
DROP TABLE ddl_log
GO
ASKER
Wow this is useful, at this point, I am focusing on the data changed, any third party software u would suggest? Thx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you very much
Very happy to have been of help, and if it is OK with you, would like to hear back from you which direction you ended up taking...
Cheers,
Mark Wills
Cheers,
Mark Wills
ASKER
I am sorry to bother you again, when I try to create a new audit on 2008, I am getting SQL audit is not available on this SQL verison???!!!
I have SQL 08STD on win 08...thx
I have SQL 08STD on win 08...thx
ASKER
trying the free one first.....SQL2008 Audit facility LOL
I have been using red gate sql data compare for about 5 years...like it very miuch
I have been using red gate sql data compare for about 5 years...like it very miuch
Ooopppsss thought it was Enterprise - the new Audit is in Enterprise or Developer edition
It is described here : http://msdn.microsoft.com/en-us/library/cc280386.aspx note the first link about databse audit : http://msdn.microsoft.com/en-us/library/cc280526.aspx will leead you to and goes through trace and triggers.
Trace is now known as part of profiler, and you can get some details / create your own trace - have a look at the trace events possible : http://msdn.microsoft.com/en-us/library/ms186265.aspx
Given the above, you may want to open this question again for more discussion...
It is described here : http://msdn.microsoft.com/en-us/library/cc280386.aspx note the first link about databse audit : http://msdn.microsoft.com/en-us/library/cc280526.aspx will leead you to and goes through trace and triggers.
Trace is now known as part of profiler, and you can get some details / create your own trace - have a look at the trace events possible : http://msdn.microsoft.com/en-us/library/ms186265.aspx
Given the above, you may want to open this question again for more discussion...
ASKER
okay..thx