Keeping an audit trail of changes

The situation

You need to maintain an ongoing record of changes made to the database throughout your project. When you've got a dozen or more developers, under increasing time-pressure to deliver the latest phase of the project on time, it's too easy to put off this tedious and non-critical step.

Key challenges

• Fulfilling project requirements without increasing time overhead.
• Archiving all changes during database development and on live servers on a regular basis.

How we can solve it

You can address this issue in two ways. On your production servers by creating an automated task, and on your development servers using SQL Source Control.

In addition to helping you maintain an audit trail, SQL Source Control helps developers collaborate, and improves productivity, saving you time overall.

In Development Environments

Use SQL Source Control to keep a history of development changes.

SQL Source Control links your existing source control system with SQL Server Management Studio, bringing the benefits of version control and change management to database development. This means you have a record of who made each change, when, and why.

1. Download an install SQL Source Control
2. Link your database to source control.
3. Make development changes normally.
   When you are happy with your changes, commit them to source control.

In Production Environments

Use the SQL Compare command line interface to schedule a task to archive a report of the changes as a nightly job:

1. Create a script to execute SQL Compare to compare the schema of the current database with the schema of the previous version, produce a report of the differences, and archive the report.
2. Create a script to execute SQL Data Compare to compare the data in the databases, produce a report of the differences, and archive the report.
3. Add a scheduled task to run the scripts using the Windows Scheduled Task Wizard.

Benefits of our solution

• SQL Source Control integrates with your existing infrastructure, so you don't need to change the way you work.
• Automated task means no time-overhead for developers, and the task is not forgotten.
• Human error is eliminated.
• Database source control brings productivity benefits & helps teams stay in sync.

Support & documentation

• Getting started with SQL Source Control
• Compliance: what does it mean for databases?
• SQL Comparison SDK
• SQL Compare