Audit SQL schema changes from day-to-day
Sandy F.
Posts: 7 Bronze 2
in SQL Compare
Here’s the scenario:
Management is required by auditing to keep track of any SQL database changes to verify we followed our established Project Life Cycle process (i.e. was the correct documentation in our Project Management system updated when deploying a SQL change to production?). Currently we have a process where we use the SQL Compare CLI to create a snapshot of each production database at the same time each night. An interactive HTML rendering of the SQL Compare differences between the two night’s snapshots is saved. This can be opened to see the differences as if you were in the SQL Compare tool. Any differences (new objects, deleted objects or changed objects) are also thrown into a log.
Task:
I’ve been tasked with figuring out a better way to crack this nut. The current process requires a member of the SQL team to do some steps manually. That can't continue!
Additional Info:
• We have the SQL toolbelt. We use Prompt, Compare, Data Compare and Search.
• SQL deployment scripts are created using SQL Compare and run by our Operations team.
• We don't current use Source Control, DLM Dashboard, octopus deploy. Don't scold me for that, we know we need to change that!
What confuses me is that I'm not sure changing our deployment process will open up opportunies to crack this nut better than we already are. If it will, I will hold off on this change until we change our deployment process.
We tossed around using a DDL Audit. Colleen Morrow had given a great presentation about a DDL Audit solution at PASS Summit a few years ago (http://colleenmorrow.com/2012/07/31/sql-audit-201-creating-an-audit-solution/). There are quite a few drawbacks to that option for this problem though.
Does anybody else have a similar requirement from their auditors? And if so, how do you handle it?
Thanks to all!
Management is required by auditing to keep track of any SQL database changes to verify we followed our established Project Life Cycle process (i.e. was the correct documentation in our Project Management system updated when deploying a SQL change to production?). Currently we have a process where we use the SQL Compare CLI to create a snapshot of each production database at the same time each night. An interactive HTML rendering of the SQL Compare differences between the two night’s snapshots is saved. This can be opened to see the differences as if you were in the SQL Compare tool. Any differences (new objects, deleted objects or changed objects) are also thrown into a log.
Task:
I’ve been tasked with figuring out a better way to crack this nut. The current process requires a member of the SQL team to do some steps manually. That can't continue!
Additional Info:
• We have the SQL toolbelt. We use Prompt, Compare, Data Compare and Search.
• SQL deployment scripts are created using SQL Compare and run by our Operations team.
• We don't current use Source Control, DLM Dashboard, octopus deploy. Don't scold me for that, we know we need to change that!
What confuses me is that I'm not sure changing our deployment process will open up opportunies to crack this nut better than we already are. If it will, I will hold off on this change until we change our deployment process.
We tossed around using a DDL Audit. Colleen Morrow had given a great presentation about a DDL Audit solution at PASS Summit a few years ago (http://colleenmorrow.com/2012/07/31/sql-audit-201-creating-an-audit-solution/). There are quite a few drawbacks to that option for this problem though.
Does anybody else have a similar requirement from their auditors? And if so, how do you handle it?
Thanks to all!
Tagged:
Comments
I work on Redgate's Foundry team and we've been looking into problems around audit and compliance for a little while. We’re interested in finding out a bit more about your problems in this area:
You mentioned the pain of someone having to do some steps manually, are those manual steps kicking off the comparison and report generation or reviewing the reports themselves or something else?
What are the drawbacks for you in using a DDL audit mechanism?
If you’d like to talk to us in more detail then we could jump on Skype and have a chat. You can get in touch with the team directly by emailing foundry@red-gate.com.
You can also sign up for updates on our progress on the Foundry site (where you can also find some audit and compliance concept demos).
Thanks
Ali
My apologies for not responding sooner! I've been pulled into a "hot" development project so I haven't been back to this. Once that project wraps up, I will take a look at the links you sent and get back to you. Thanks!