Best Approach to Brownfield Database Development in SQL Server 2000/2005

Question

I've recently taken over development on a SQL Server 2000 database that needs some help. We're planning on upgrading it to SQL Server 2005 soon. This database has no audit fields on the tables (CreatedBy, CreatedDate, etc.), no foreign keys, and terrible overall design. There are half a dozen programs that directly access the database using inline SQL, and other old/bad practices.

I would like to clean up the schema, and the data access. Do you have any suggestions for a good place to start? This is a production database, and it has to continue to work while it's being improved. Thanks.

Answer 1

You're probably going to have to start with the applications that access the database. More than likely you will find that any changes to the database schema will break those other applications. The most common culprit I've found is select * sql followed by accessing the data based on column position. If you insert a column before the last column, that code will break. Also, unless you use default values for your new columns, any insert commands will fail.

Your best bet is to understand how those external programs are using the database, then design a new database and then migrate each of those programs over to the new database one at a time.

Making a change to this database while it is in production is almost guaranteed to break the other applications.

Answer 2

You may be able to correct, analyse, normalise etc the schema while maintaining the current schema/interface behind views.

Using before trigger on the views can ensure apps write as well as read as they expect.

This way you can start to migrate the client apps onto new schema whilst allowing the current app to work. And your data is safer (DRI,FK,DF,CK etc) in it's new schema.

This also keeps the interface contract consistent for that unexpected spreadsheet that runs once a month and no-one knows about it is essential for that end-of-month report...