Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring and Recording DDL changes on SQL 2005 (NARC)


Monitoring and Recording DDL changes on SQL 2005 (NARC)

Author
Message
James Greaves
James Greaves
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 457
Comments posted to this topic are about the item Monitoring and Recording DDL changes on SQL 2005 (NARC)



Fatherjack
Fatherjack
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 627
Great example of something i have been wanting to implement for a long time.

Step 2 mentions 'attached SQL' but i cant see it, what am i doing wrong?
awdolan
awdolan
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
How can this monitor if the DBA can easily either turn it off or delete items from the output table? Seems to me that if you want a solution like this, the DBAs can't be the ones controlling what is recorded.
Jason S
Jason S
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 33
Where can I download the SQL code? I see it mentioned in the article, but no link to download.
yonision
yonision
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 164
There's a nice product that's using this exact technology, but already delivers everything to you with a nice GUI, easily viewable logs and all, and saves you all the hassble (also pushes changes to SourceSafe for you)

http://www.nobhillsoft.com/Randolph.aspx

thanks



pkenny
pkenny
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 22
What is unique about server 2005? Can't this be done with previous versions of SQL. Nice solution BTW! Presumably the new database is secured in some way eg. you are the DBO?
Peter.
Fatherjack
Fatherjack
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 627
yonision (2/29/2008)
There's a nice product that's using this exact technology, but already delivers everything to you with a nice GUI, easily viewable logs and all, and saves you all the hassble (also pushes changes to SourceSafe for you)

http://www.nobhillsoft.com/Randolph.aspx

thanks


I see the product you mention and am sure its a worthy tool for deployment where the budget is available. I have taken the code from the article and created the objects concerned in my 'server statistics' database. I have then built two SSRS reports, based on the views created and i now have an easy to access (from anywhere on our WAN) method of seeing whats being changed on my databases. Total work time - maybe an hour in total (if i include the two posts here as well!!)
There's a place for quick and dirty solutions beside the paid-for third party tools.

thanks again for the article.
webooth
webooth
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 580
Good job! Nice to know someone has some of the same problems I do.

BTW, you might be interested in looking at this PASS demo. It has some good ideas.

The SQL 2005 DDL Audit Samples are a collection of sample applications for configuring DDL (Data Definition Language) auditing on one or more SQL Server 2005 instances. These applications were first presented at the PASS 2006 Community Summit in my presentation Building a DDL Audit Solution using SQL Server 2005. They build on top of the Service Broker and Server Event Notification infrastructure available in SQL Server 2005 to provide a means of collecting all server and database DDL events in a central database for viewing and reporting. Full source code is provided for all sample applications to allow you to better understand the technologies and easily build on or modify these samples for use in your environment.

http://www.sqldbatips.com/showarticle.asp?ID=113



yonision
yonision
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 164
Hi Jonathan,
Sure, there is room for ad-hoc implementation as well. I only wanted to show another option. This one's price really low (500$) does a whole lot more than what this article talks about, and actually frees you to focus on what you do best (your specific project) instead of worrying about issues like version control, which have been solved many times already.
But naturally, if you feel your ad-hoc solution answers ALL your needs, there's no need to spend more money!

Jonathan



James Reed
James Reed
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 164
I like what it does and i also created 2 SSRS reports but my question is how do you make this work with users that are not sa and may not have permission to the audit db? that is the only issue i have. I love the out put it gives us and it is very scaleable.

Jim
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search