SQLStarters

I was reading this blog post by Thomas LaRock (@SQLRockstar) about a database design mistakes and I’ll warmly recommend that anyone who is involved with database design should read it as well. It also got me into thinking about one database design issue which, in my opinion, is not taken into consideration often enough; Database Administration.

Commonly when discussing about database design the focus tends to be in the indexing strategies or in the table and a query design. And this is completely fine, because it’s these design decisions that form the foundation for the performance and the reliability to your database. But designing a database with fast running queries and a table structure that ensures the accuracy of your data is not enough.

Yes, you read that correctly, designing a database that is reliable and has a good performance is not enough.

When the database goes into production there’s suddenly a number of new issues to consider, such as the availability and safety of the database and it’s contents. These are usually part of the core responsibilities of the production DBA, but they should also be acknowledged during the database design phase. In fact I’d go as far as to say that they should be considered to be an essential component in ensuring that the database going into production can meet the customers requirements.

The database administration is generally not a problem during the testing phase or when the database has just gone live. There’s usually not lot of data to work with in the production database early on and most database administration tasks can be performed with out of the box solutions. The testing phase usually doesn’t include testing administrative tasks and even if it does, there’s usually not enough test data in comparison to what the production database will eventually have.

What happens when your database grows?

When that happens and the basic maintenance solutions won’t be enough for the DBA to meet the requirements of the Service Level Agreements (SLA) there’s two ways to deal with the problem. First one is to introduce new technologies to the environment which usually adds to the both, the complexity and to the cost of the solution. The second one is to alter the database design but these changes are usually much more difficult to implement on a production environment than the design phase.

If you haven’t planned ahead you’re most likely to end up with an environment that is a combination of both methods, with emphasis on having the additional technologies in the environment. It’s not necessarily a bad thing, but in my experience unnecessary complexity should always be avoided.

Database administration issues to consider during the design phase.

This can and will vary depending on the database and the application(s) using it, but generally the issues I’d consider to be important are be the ones that have to do with the SLA. Most commonly these include some or all of the following:

• Index and Database Maintenance.
• Backup and Restore Strategy.
• Data loading into or out of the database.
• Software and Database updates.

While there’s a definitely many other administrative tasks involved in daily DBA work, if your database design has taken these four administrative issues into consideration, you’re already doing a great job. These are also subjects where you can greatly benefit from the opinions of an experienced DBA, so make sure to involve them as early as the design phase.