• Adam Thurgar

Database consolidation

When I started work at a music subscription download service, they had multiple databases. After I sat back for a few weeks it became obvious that there was no great methodology for which tables went into which database. The development team was never sure what table was in what database. They used Elmah for error logging and it was in a separate database, but this was different from where other information was kept. The whole system had a lot of cross database joins occurring. For some reason they also had linked servers set up - maybe so they could easily move databases from one server to another, but once again not ideal for query performance. These were not overly large databases, 10GB to 20GB each. Would it be easier, simpler and better to consolidate these databases? Potentially start using schemas to identify the different functional areas. This was my decision to consolidate and if it didn't get the results, then I would be looking for a new job.

The consolidation wasn't easy, but was made easier by all logic being in the database (mainly stored procedures). It was a slow process, but the fallback plan was easy - revert back to the old code and online the old offlined database. There was a bit of pain in doing the migration and consolidation, but when it was finished we had a simpler, faster database. Moral to the story is that having a large number of databases may not be better than having one large database - horses for courses.


Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t