Top 10 Mistakes When Building and Maintaining a Database


Building and maintain a SQL Server database environment takes a lot of work. There are many things to consider when you are designing, supporting and troubleshooting your environment. This article identifies a top ten list of mistakes, or things that sometimes are overlooked when supporting a database environment.

#1 Inappropriate Sizing of DB

If you are new to managing SQL Server databases, it is easy to fall into the
trap of using SQL Server Management Studio to create your database. We all do
it. When using SQL Server Management Studio you have many options you can set,
or you can take the defaults. The mistake many DBAs make when sizing a database
is to take the default sizing options. There are two sizing options you need to
consider, initial size and autogrowth.

The initial size is how much space the initial database will take up. The
default is 3 MBs for Data file and 1 MB for the log file. Now these could be
the appropriate size for your database. However, if your database is expected
to have 300,000 or millions of rows of data shortly after being created then a
larger initial size for data might be appropriate. You need to determine how
much space you will need initially and the growth rate of your database to set
the initial size and growth rate appropriately for your Data file. Sizing the
log file should also be considered and should be based on how much change
occurs in your database. If you are performing lots of inserts and updates then
a larger log file will be needed, verses a database that changes very little.

#2 No Database Backup Plan

The worst thing you can do is not have any backups of your databases. The
next worse thing you can do is not testing to see if you can restore your
databases from backup. As a DBA, if you cannot recover your corporate data then
you had better get your resume out on the streets, because you most likely will
need to be looking for a new job.

There are three different backup types: full, differential and transaction
logs. In order to determine which ones to use, and how often they should be run
depends on your backup requirements. Work with your customers to define their
recovery point objective and then at a minimum make sure you have database
backups to meet their requirements.

You should periodically test restoring databases from these backups just to
make sure they work. Ideally, you would perform some disaster recovery testing
in conjunction with your customers at a secondary location. However, at a
minimum you might want to test the recovery process on some isolated test box.

Lastly, you might want to consider an alternative storage location for your
backup files. Do not just leave them on the server where the databases exist.
If that server melts down all your backups are gone. Make sure you copy your
backups to an alternative storage location. That alternative location could be
another machine, tape, or some external storage device. You might also want to
consider offsite storage for these copies of your database backups.

#3 Poor Security Model

Your best line of defense against security breaches is to have a good
security model. Do not use the SA account for anything. When you install SQL
Server set the SA account password to something complicated, write it down,
store it in a secure location and never use it again, except in an emergency.
SQL Server has two different security models you can use, SQL Server and
Windows Authentication. SQL Server is the least secure. Use these different
authentication methods appropriately in your environment.

When you provide access to a database and/or database objects, you should
only give people the kind of access they need to do their job. Do not give
developer DBO access in production, unless it is their job to maintain a
database. Only allow people to have update rights to tables if they need to
have that kind of access to do their job.

You also want to develop a security model that is easy to maintain. Consider
using Windows Groups and Database Roles to help organize your security rules.
Using groups and roles allows you to provide similar access to people by simply
dropping them into a Windows Group or Database role. Organizing access rights
using group and roles can make security administration much easier.

#4 Use of Adhoc Queries

Do not allowing applications to submit adhoc queries. When you have adhoc
queries, you have to provide permissions to tables and views in order for these
adhoc queries to run. This means that users will need to have SELECT, INSERT,
UPDATE, and DELETED permissions to your database tables in order to run these
adhoc queries. This kind of access allows individuals to write their own code
against the database, circumventing using an application to access and update a
database.

It is better to encapsulate your application code into stored procedures.
This will allow you to only give users EXECUTE permissions. Doing this means
users will not be able to access tables directly outside the applications using
SELECT, INSERT, UPDATE, and DELETE statements. Additionally, stored procedures
can provide better performance through compiling and caching the execution
plans.

#5 No Data Integrity Rules

Do not rely on your application to enforce all data integrity rules. A SQL
Server database can be designed to enforce some data integrity rules. Take
advantage of these rules when you build your database.

If your application requires a given table to have a unique record then make
sure you provide a unique constraint on that table. In addition, if your
business rules say that a parent record needs to exist prior to creating a
child record then make sure you create a foreign key relationship between the
parent table and the primary key of the child table. You can also provide
additional data integrity by providing default values and check constraints to
make sure your data matches your application business rules.

#6 No Testing or Limited Testing

It is poor practice to put code into production without testing it. This
goes for all changes, even a small logic change. When you do not test your
code, you run the risk of it not working, or more importantly introducing
additional problems.

When building your code it is common practice to run your code against a
small development database. This provides for faster testing turnaround times,
and allows you to use fewer resources. The down side of this is your
application might not scale well. Therefore, prior to moving a new application
into production you should test your application against a production size
database. This will allow you to find those performance issues you might have
related to large tables.

#7 Lack of Monitoring

What happens when you fill a glass of water and you do not watch the level
of the water while it is filling? The glass will fill up and overflows if you
do not turn off the water in time. If you do not monitor your database system,
you might also fill up your capacity. By not monitoring your environment, you
run the risk of having poorly performing applications that will eat up all your
hardware capacity (CPU, I/O, and disk space).

By monitoring your application for poorly performing queries, you can
identify performance opportunities. These opportunities will allow you to
re-write these poorly performing queries or add additional indexes to optimize
them. By monitoring and tuning up your poorly performing queries, you reduce
the CPU and I/O required by your application.

You do not want your databases to run out of disk space, just like you would
not want your glass of water to overflow. To keep your disk drives from filling
up you need to monitor them to ensure there is adequate disk space on your
physical drives. Ideally, you should track the amount of disk space growth over
time. By doing this you can predict when you will run out of disk space. By
proactively managing the growth rate, you help minimize the possibility of
running out of disk space.

#8 No Periodic Rebuild or Reorganization of Indexes

As databases grow, indexes are constantly being updated. This constant
updating makes indexes grow and split, which leads to index fragmentation. In
order to optimize your indexes you need to periodically rebuild or reorganize
your indexes.

SQL Server provides us with index fragmentation information. You should
query this fragmentation information to determine which indexes should be
rebuilt or organized. By periodically rebuilding and reorganizing indexes, you
can improve your application performance.

#9 No Indexes or Too Many Indexes

Performance of your application is important. Indexes will help your
application quickly find the data they need. Having fast access to the data
provides your end users with a well performing application. However, if you
have no indexes or too many indexes your performance may suffer.

You need to monitor your database for missing indexes, or indexes that are
not being used. SQL Server provides you with index usage and missing index
information. If you find a missing index that could help a large number of
commands then you should consider adding that missing index. Monitoring can
also identify indexes that are never used. These are the indexes you should
consider removing. Be careful when monitoring and removing indexes because
these indexes might be only used for monthly, quarterly, or yearly processing.

#10 No Change Management Process

Maintaining a stable production environment is extremely important. Database
changes need to be well thought out and planned. Having a change management
process provides some structure around how changes are made.

You need to develop a change management process for your environment.
Changes should be tested in an isolated non-production environment. Once you
have thoroughly tested a change then you can plan for the production
implementation. When implementing changes into production, make sure you have a
fallback plan just in case your implementation into production does not go as
expected. By having a change management process, you are able to document every
change that goes into production, and make sure it goes through the appropriate
testing process. Doing this well help maintain a stable production environment.

»


See All Articles by Columnist

Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles