SQL Server database design disasters: How it all starts
Brian Walker, Contributor
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.
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
This was first published in September 2008
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
ABOUT THE AUTHOR
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.
MEMBER FEEDBACK TO THIS TIP
Do you have a comment on this tip? Let
us know.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation