Once you have configured your SQL Server instances,
you will want to continue to prepare your server for production.
Depending on your environment, configuring SQL Server for production
will require various processes and procedures. The following sections
will provide you with some of the steps that we go through prior to
deployment of our SQL Server instances into production from a server and
database perspective.
1. Server Tasks
Following are some things
you should do, or at least consider doing, from a server level prior to
placing your server into production:
Apply Patches:
Before your new SQL Server is added to production, ensure that the
patch level of the server is at its correct level. We are not
recommending that you install a service pack that your environment is
not ready for, but consider applying less risky patches like security
patches. Applying currently available patches before going live in
production should increase the uptime of your new SQL Server because you
will only have to bring the server down afterward to apply new patches
as they are released by Microsoft.
If you have not simulated a workload
on your IO subsystem, then we encourage you to go out on the Web and
download some free testing tools. Do not wait until SQL Server is
running in production to identify problems with the setup and
configuration of your IO subsystem. Spend a little time up front, learn
how to effectively utilize an IO-testing tool, and make sure that your
system is ready for production.
Run the Best Practices Analyzer:
SQL Server 2000 and SQL Server 2005 both had applications published by
Microsoft that compared the SQL Server and Windows configuration
settings against Microsoft's recommended best practices to determine if
there were potential issues. In SQL Server 2008, use some of the
predefined policies of Policy Based Management
to ensure that your SQL Server instance is in line with Microsoft best
practices. You can also review the options checked by SQL Server 2005's
Best Practices Analyzer and ensure that you are following the best
practice items that you are concerned about. Make the time to validate
your server against Microsoft's best practices, even if doing so
requires more work than you would prefer. The amount of time that a best
practices check can potentially save you in the long run is well worth
the effort.
2. Database Tasks
Following are some tasks
that you should perform for each database that you plan to deploy into
production. The tasks are not mandatory, but they can prove beneficial
and can save you headaches down the road.
Size data and log files:
When creating new databases, options exist to determine the initial
size of the database data and log files to the size of your choice. If
you have a general idea about the expected size of the database, then
take advantage of this option and allocate space for the data and log
files, respectively. By initializing the data and log files to a
projected size, you will minimize the number of data and log file
growths because of the database needing more space. Saving data and log
file growths will ultimately save CPU cycles and disk IO, dedicating
more resources to processing application requests.
Enable Autogrowth:
Many database administrators argue that you should not set up your
database data and log files to autogrow by a certain percentage or fixed
size. We have heard many administrators say that you should monitor
your files enough so that you can grow them when necessary. Our advice
to you is to set up the autogrowth option in whatever way that enables
you to keep production running (see Figure 1
for an example). If you manage multiple servers and are constantly
fighting fires, chances are that you don't have time to monitor data and
log files to grow them manually. So set up the autopilot and ensure
that your production databases keep running.
The flip side of that coin is
to periodically review the autogrowth setting that you have set up to
ensure that you are not wasting large amounts of space and increasing
disk defragmentation. Growing your database and log files by 10% to 20%
may have made sense when your database was 10 to 20 GB. Now that your
database is 100 GB, you may want to consider revising some of those
percentages. Regardless of what option you select, make sure you educate
yourself enough to understand what options are available to you in the
event that you manage the data growth. Keep in mind, your data drive and
log drive may be running out of space because of the autogrowth option
selected. Modifying how your database and log files grow may buy you
some more time before needing to purchase additional space.
Disable autoshrink:
Shrinking your data or log files is a process that removes any unused
free space from those files. Configuring the database to automatically
shrink the data and log files is simply a bad idea. The main reason why
we do not like to enable this option stems from the lack of control over
when the process will start. Unfortunately, we have seen this option
cause problems in our environment.
Set key database options:
In SQL Server, options are available that allow you to configure the
behavior of a database. Following are some of the options that we
frequently specify.
Auto Create Statistics:
Automatically creates statistics on columns without indexes that are used in the where clause of queries.
Auto Update Statistics:
Automatically updates statistics needed by queries by the optimizer based on changes within the data.
Cursor Default:
Allows cursors created within a single procedure or trigger to be referenced by any process using that connection.
Parameterization:
Determines how queries will be parameterized via default rules for the database or all queries in the database.
Recovery:
Based on your data recovery requirements, this option determines how the transaction log will be used.
SQL Options:
Various options that can be
enabled or disabled for the entire database, depending on the
requirements you have been supplied with.
3. Maintenance and Monitoring
Database administrators
must ensure that their databases are available to respond to application
queries at the required response rate and that data can be recovered in
the event of server failure. To that end, you should at least do the
following before going into production with a new server and database:
Create a backup and recovery strategy:
Fortunately, Microsoft provides methods for backing up and maintaining
your databases. However, at a minimum, before going into production, you
definitely want to create a backup strategy that meets the business
requirements.
Create a plan to maintain indexes and integrity:
Commands and features exist to maintain indexes and check the integrity
of your databases. You should put together a plan to accomplish these.
Create processes to monitor your server:
Setting up processes to monitor and capture SQL Server performance is
critical to supporting and base-lining the application. In a perfect
world, organizations would purchase software to aid in the monitoring of
your server. Unfortunately, purchasing software is not always an
option, but expectations still exist for you to proactively monitor your
system to identify problems.
