Refactoring Legacy Databases

• Know your enemy - It might not always seem so, but legacy code was written with a process in mind. Determining what the code was originally designed to do it the most import step toward improving it
• Small bites - How do you eat an elephant. One bite at a time. Choose process to refactor rather than diving straight into an unstructured rewrite
• Retire old processes - If the process is obsolete, kill it off
• Start with the end in mind - Work with the system stakeholders to agree the projects goals
• Set standards. Old code can be in conflict with your current coding standards. (You do have defined coding standards don't you?). A clean code approach will make the job refactoring your code easier when the days comes that your code is itself recfactored.
• Stop digging - Don't let developers submit code which contains lazy hardcoding or other bad practices.
• Engage - Talk to your developers. Explain what constitutes good practice. Educate them.
• Process - If you've got developers adding poor quality code to your database put a code review process in place. (You do have a code review process don't you?)
• Permissions - Often developers who've worked with a system for years feel they know it so well they can jump on the box and commit a code change. This is how spaghetti code begins. If the softly, softly approach doesn't work revoke the developers permissions to commit change to your database.
• Evolve - Your unlikely to get executive sign-off on a project to go an clean up existing code especially in a hectic environment. So take a medium term view. Tackle the worse code first and keep steady pressure on to continually improve coding standards.
• Test-driven refactoring - Changes you make to a legacy system often have to produce the same result otherwise you've just broken a live application. So using tests is vital to ensure the quality of the refactoring process just as TDD is a mainstay of modern application development.

Check out these links for useful resources;

RedGate to the rescue once again with their excellent toolset here and advice on how to use it here. I specifically like the ability to format code, auto-generate aliases, encapsulate code in stored procedures and qualify object names. This tool saves so much time.

http://databaserefactoring.com/ - Lot's of good specific advice here.

http://www.amazon.com/dp/0321293533/ - A good reference