Databases Reference
In-Depth Information
Deciding on Automation
How do you determine whether or not you should automate a task? There are
a few simple guidelines you should consider. First, how often do you perform
the task and under what circumstances? If it's something that you'll be doing
once, like creating a specific database table or adding a specific user to the data-
base, then you will usually want to do the job and be done with it. The same
is true for procedures that are rarely performed, or performed intermittently with
no set schedule.
The opposite is true for periodic procedures that are performed on a regu-
lar basis. Once again, let's consider database backups. Backups are an ongoing,
periodic activity, usually occurring on a set schedule. How often you back up
your database depends on factors such as how quickly the data changes, how
much data you can afford to lose (or repost), and what other mechanisms you
have in place to protect your data. At least part of the data in most databases is
backed up at least once a day and, in many cases, even more often than that.
Trying to run those backups manually would be a waste of effort, especially when
you consider that backups are usually run after hours whenever possible.
As is usually the case, there is an important exception to these automation
guidelines. It's sometimes better to automate the execution of a procedure, even
when setting up the automation takes longer than it would to just run it. This
is most often the case when running procedures that either interfere with user
access to the database or severely degrade server performance while they are run-
ning. Another possibility is that you are supporting multiple locations and you
want to ensure that a change is made to all of the locations, the same way, and
at the same time. If one database is updated and the others are not, you have a
consistency problem. You can avoid this by scheduling the change to happen
the same way and at the same time, in all locations.
As an example, think about table indexes. They can become fragmented over
time.
Fragmentation
is a condition in which the data is randomly spaced in
small pieces all over the hard disk rather than being located in one place. This
happens over time as data is added to and removed from a table. When frag-
mentation becomes severe enough, performance starts to degrade and you have
to rebuild the indexes to correct the problem. However, rebuilding indexes is a
resource-intensive process and can result in tables becoming temporarily unavail-
able. Often, the best solution is to schedule the rebuild to occur automatically
after hours when the impact on other operations is minimized.
9.3.3 Considering Ongoing Management Tasks
Automating some tasks requires more work than others. When you need to auto-
mate a task that requires multiple steps, you might want to create a script or
even a stored procedure that includes all of the steps. One advantage of creating
