Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server database design disasters: How it all starts

Many businesses are running SQL Server databases in deplorable conditions. SQL Server architect Brian Walker explains how there's a snowball affect leading to these poorly performing databases and often starts with seemingly innocent Access database migrations. He also points out how a DBA's responsibilities have become murky. This is part 2 in this series from Walker who was inspired by what he recently found in some SQL Server shops. Ultimately, he shares advice for improved database design -- and SQL Server performance.

This is part two in Database Architect Brian Walker's series on poor database design. He was inspired by what he recently found in some SQL Server shops. Ultimately, he shares advice for improved database design -- and SQL Server performance.

What happened: The snowball effect

So, how do business databases get into such a deplorable, poor-performing condition? Many of them were born and raised to be that way. Think about it. They may have been born as Excel spreadsheets built by accountants, or as Access databases kludged together by some salespeople. When the volume of data increased, they may have been migrated to SQL Server in essentially the same form. When the "solutions" became widely available on actual servers, more people started using them. Eventually, businesses come to rely upon these mongrel databases.

Once a business comes to rely upon a database, the data collection grows and more applications are built to use it. The application developers, with only secondary database/SQL skills, add tables and other objects as they see fit. Nobody coordinates the database growth, but things get done. When database performance becomes problematic, the business hires a DBA. The DBA recommends a more powerful server. The DBA tweaks a few stored procedures and adds a few indexes. When performance improves, the business happily carries on.

As the database expands and the number of applications increases, the DBA is busy trying to address database performance issues. He or she recommends more servers and uses copies of the database to divide the load. Another DBA is hired to monitor server operations. The cycle of expansion continues for years and the business is now dependent upon the database. There are several powerful servers, the load is divided as much as possible, the DBA team is always busy fighting fires, and there are still database performance issues.

Finally, the DBA team acknowledges some of the performance issues might be caused by the database design itself. They set forth "Best Practices" for the application developers to follow. Alas, it's too little and much too late! Neither the DBA team nor the application developers are familiar with taking responsibility for database design tasks. Fixing the design flaws would require major changes to application code, and the business does not authorize the work. The mongrel database is set in stone.

Avoid trouble

Do you recognize your company as being at some stage in the progression outlined above? I hope not. I hope your company did things the right way. There's a right way to avoid the cycle of expansion and performance issues – a way to prevent being stuck with a mongrel database.

Build a solid database foundation from the start. Take the business logic from an infant Excel/Access tool and have an experienced database developer implement it within the framework of a proper relational database.

An experienced database developer can normalize tables, establish a standard table structure, implement complete DRI, create basic indexes, choose appropriate data types and define helpful naming conventions. If these things are done up front, the business can reap the rewards later.

The application developers can do their work more efficiently to make the database expansion more logical. New system functionality, such as archiving and purging, can be added quickly and easily. As a huge bonus, properly designed databases are much less susceptible to nasty performance issues. They are much more likely to successfully support increases in the quantity of data and/or the number of applications and users.

Do not assume that a typical DBA is a database developer. They are not the same thing. Their work may overlap somewhat, but they need different skills.

It's generally understood that two kinds of database administrators exist -- for production and for development. But the definition of each kind is not universal. If the difference were simply their environments (production or development) then there would be no need to distinguish between them.

I would like to propose a definition with a very clear distinction. A production DBA is concerned about everything outside the database. A development DBA (database developer) is concerned about everything inside the database.

A production DBA considers a database to be the smallest item to focus on. A production DBA handles storage space, database maintenance, disaster recovery, high availability, replication, log shipping, mirroring, logins/users, SQL Server Agent jobs, performance monitoring and maybe some performance tuning. The primary point of focus is a server or a SQL Server instance. A production DBA has very little need to understand how the business works.

A development DBA considers a database to be the largest item to focus on. A development DBA handles data modeling, normalization, table structure, DRI, indexes, data types, naming conventions, triggers, stored procedures, views, functions, code generation, data import/export, archiving, purging and auditing. The primary point of focus is a database or a set of databases. A development DBA (database developer) must understand how the business works.

The right way: production DBA and development DBA

There's certainly a need for both kinds of DBAs in the business world. However, if businesses involve development DBAs in the early stages of database projects, they get much stronger databases, save money on server hardware and have fewer problems for production DBAs to solve.

According to available job postings, there is no indication that businesses are ready to do things the right way. They are still seeking application developers with only secondary database/SQL skills and are still seeking production DBAs to handle problems. There are very few openings for development DBAs.

I'm afraid this means more business databases are being born and raised as mongrel databases. The deplorable conditions they create will hurt businesses in the future.

Personally, I often work as a production DBA or an application developer, but I love being a development DBA (database developer).

Check out Part 1: SQL Server database design disasters -- What not to do

Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.


Do you have a comment on this tip? Let us know.

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.