CODEWITHSUNDEEP
sql-server
majjam
majjam

Reputation: 1326

Techniques for understanding complicated legacy database

I have inherited a complex, poorly documented SQL Server database and am trying to reverse engineer the tables, their relationships and in general how the data fits together. To do this I am drawing the table diagrams using Visio, attempting to determine the data relationships using the existing queries (there are no relationships actually defined) and using the application logic (what little there is) to help.

This is however quite challenging given the lack of documentation / obfuscated nature of this system and I was wondering if there are any techniques / approaches that can help?

Upvotes: 0

Views: 55

Answers (1)

Rhys Jones
Rhys Jones

Reputation: 5518

Not an easy job, and the bigger it is the harder it is. I do however have one small trick for discovering the necessary surface area of the database - remove all user access!

This sort of database often runs with full access (i.e. users are DBO level or above) so anyone can do anything. If you remove all user access (but not your own!), then start application testing and enable minimum permissions on a per object basis, you will eventually reveal the surface area of the database (the exposed objects). This information is very useful in helping to identify dead objects - your job is hard enough as it is without having to document SPs that are no longer used for example.

How successful this technique can be depends on how thorough you think you can be in your testing, how sure you can be that someone somewhere doesn't have an Excel workbook that connects to the database once a year to run some query that no-one else ever does.

Upvotes: 1

Related Questions