?
Solved

monitor SQL

Posted on 2009-12-31
10
Medium Priority
?
317 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:mcrmg
  • 5
  • 4
10 Comments
 
LVL 10

Expert Comment

by:lof
ID: 26156058
SQL Profiler, a tool shipped with SQL Server will show you all that's going on on an SQL Server
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 26165110
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 : http://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_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
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



0
 

Author Comment

by:mcrmg
ID: 26165391
Wow this is useful, at this point, I am focusing on the data changed, any third party software u would suggest? Thx
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 26165483
Well the SQL2008 Audit facility is pretty darned good...

Then have reviewed this before and looks very good : http://www.apexsql.com/sql_tools_audit.asp

And if you want to be sneeky and keep your own database as a baseline (snapshot before dev upgrades), then look at data compare : http://www.red-gate.com/products/sql_professional_toolbelt/index.htm

And have also used - to take a snapshot and compare (mainly used for QA ) http://www.idera.com/Products/SQL-toolbox/SQL-comparison-toolset/

0
 

Author Comment

by:mcrmg
ID: 26165678
thank you very much
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 26165740
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
0
 

Author Comment

by:mcrmg
ID: 26165758
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
0
 

Author Comment

by:mcrmg
ID: 26165763
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 26166225
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...
0
 

Author Comment

by:mcrmg
ID: 26166516
okay..thx
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question