I'm sort of in the middle of this as well. I'm in the middle of phasing out a middleware client/server product and replacing it with a homegrown one before a system upgrade. This is happening in two parts: I. Rewrite the client, II. Rewrite the server.
Writing the client was easy. The API stayed the same, and I simply wrote unit tests to mimic the API/behavour of the existing client. When all tests passed, the new client was ready to go.
The next part is even more joyous with unit tests. I wrote tests to mimic the expected behavour of the server I/O. Once those passed using the old software, the same tests were run using the new server software. ONce those passed, it was a whole lot easier to sleep at night after replacing the old server software with the new.
I can't really imagine doing any of that without unit tests.
I hear you... Just one point though - we are finding that once you have shipped a version then changing the schema becomes a very big deal. No customer is going to allow you to upgrade or fix if it means they lose their data.
Changing indexes is also another interesting issue - do you have tests that check query performance? How about interactions between different queries? We recently added a new index to a system which caused all sorts of locking problems when accessed by a couple of hundred clients (the customer was a TV company using the application to display live data on screen as well).
James.
Changing indexes + fine performance tuning is just an issue of the full time production database. From the other hand database schema - is the thing you begin your application from. It is not supposed to change.