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.
Windows Management and Scripting 