Microsoft® SQL Server® 2008 Bible

Chapter 54. Schema Audit Triggers

IN THIS CHAPTER

• Tracking schema changes

In some shops, the path to production can be complicated. For a recent contract, the client had 20 servers dedicated to the project, which included a dev environment, a QA testing environment, an integration testing environment, a performance testing environment, and a production environment. Because the project scaled out using four servers, each environment had four identical servers. Adding my development notebook to the mix pushed us to 21 machines.

At least once, as I was deploying change scripts, I missed a server—and of course I wouldn't catch it in my testing. If I was lucky, another DBA would catch it before it broke something for one of the app programmers.

No doubt you've been there too.

While the schema audit trigger presented in this chapter couldn't have prevented my error, it would have made it much easier to diagnose and correct.

Note

This chapter builds on its sister chapter, Chapter 27, "Creating DDL Triggers." That chapter demonstrates how to create a DDL trigger, while this chapter applies the technology for a specific purpose.

DDL triggers are easy enough to code, but it's not a common task, so I've tried to automate the process for you with SchemaAudit, an open-source schema auditing script available for download from my website, www.sqlserverbible.com, or from CodePlex.com. The SchemaAudit script creates a SchemaAudit table and installs the DDL trigger to track all schema changes.

Note

You can ...