Most of create databases on a routine basis and some of us work in more
static environments, but either way creating a database is pretty simple stuff
right? Yes, creating a database is simple, but it's not a good idea to just type
in the database name without thinking about the rest of the details. So let's
walk through the process and see what we can learn.
But before we even open up the tools, we need think about the most important
question; on which server should we create the database? Hopefully you already
have a set of guidelines for deciding what goes where (even if not written
down), but if not, here are some points you want to think about:
- How much space do we think is going to be needed? This may rule out some
servers that have less space available or lack the ability to add space
(using internal storage).
- How many concurrent users do we expect?
- Is it going to be used internally only, extranet, or internet? This is a
security question, because we'll want to utilize predefined zones within our
firewall.
- Any other special security considerations? Perhaps it needs to be on
server that is configured for IPSEC or SSL security.
- Does it have a dependency on another database? I prefer to avoid cross
server dependencies for high use applications, but if I have to I'll look at
replicating key data so that it looks like it's all on one server.
- Do we have the appropriate maintenance window for this database? For
example, if I have a server that has a 2 hour maintenance window each week I
might want to put the database on a server that has a 2 hour window each
night to give me more flexibility about index rebuilds.
As in most of our decisions here we just do the best we can with what we
know, we can always change later! Now let's move into the details. For this
article we'll be using Management Studio, but the same details apply if you're
using TSQL, DMO, SMO, or even Enterprise Manager (allowing for changes across
versions of course). Here's the starting point for a new database (select
databases, right click, click Create):
Rule #1 for me is that the database name should not contain spaces. Putting
spaces in the name requires surrounding it with brackets [My Database] in TSQL
to avoid syntax errors and I prefer not to have to use the brackets. I follow
that by almost always setting the owner of the database to SA. In most cases on
systems I administer changes will always be made by someone in the sysadmin
group, rarely we might put someone into the db_owner role to let them make
whatever changes are needed. The only value derived from being the database
owner rather than a member of db_owner is that the owner is also dbo, which
means that if they don't qualify objects with a schema when created they will be
owned by dbo by default. Members of db_owner can use two part syntax to put
objects into the dbo schema (create table dbo.employess...). Note that you can
always change the database owner later using sp_changedbowner.
Full text indexing isn't commonly used, but we can activate now if we know it
will be needed, or it can activated later.
As you can see in the next image I'm going to call my database Test, and it
has automatically generated the logical names for the two files that get created
by default (one MDF and one LDF). I never change these names as the generated
one works fine. Moving to the right I've taken a guess at my database usage and
increased the database size to 100 MB and the log size to 10 MB. It's just a
guess though, so why bother? Mainly to avoid fragmentation by allocating a good
chunk of space at once.
Moving to the right another column I still have the default values for auto
growth. These should almost always be changed, so we'll click the ellipsis to
bring up the change dialog:
I rarely turn autogrow off, and contrary to what many recommend I typically
let the database grow if and when it's needed, not deliberately resizing the
database to maintain a given amount of free space. The advantage to doing it
deliberately is that you avoid the performance hit of waiting for the file to
grow if it happens during production hours, but that penalty has been greatly
reduced in SQL 2005 with
initialization, a feature change that causes SQL to initialize pages on
first write instead of during the auto grow. The advantage of doing it my way is
less time spent on a very mundane task. The more important decision is whether
to grow in percent or megabytes, and how much. There's no right answer, but in
general we want to grow in as large a chunk as makes sense to avoid physical
fragmentation on the drive. For this example I'm going to set both to grow at
10%, and leave file growth unrestricted.
Moving to the right again we come to the Path column, and this one is
important. The path below is the default path from an install on my laptop, but
in production you want those values to come from the server defaults. Right
click the server, select properties, then database settings to set those
defaults.
Now that we know how to adjust the defaults, it's time to decide if we should
use them or not. Typical best practices indicate we should put our data files on
one set of drives and the logs on another, both for performance and fault
tolerance. Hopefully we've set our defaults to match that, but there may be
times when we want to change the destination - the most common being a shortage
of disk space or an expectation that this will be a very large database that
might need it's own set of drives. For this article I'll leave the defaults in
place.
The final column is file name and it contains no value right now, and cannot
be edited via this dialog. The file names will be based on the logical names, so
we'll have TEST.MDF and TEST_LOG.LDF. MDF is used for the main data file,
normally we'll use NDF if we add additional files to the database. Logs are
created with the LDF extension. These extensions can be modified from the
default, I recommend against changing them just for the sake of change!
Let's move to the next tab, options:
In this dialog most of the defaults are correct. We almost always want the
recovery model to be set to FULL, but it's worth thinking about for a few
seconds. The most common reason I set a database to SIMPLE is because it will be
reloaded nightly and treated as read only during the day. Auto Close should be
false because we don't want the database being closed during a slow period and
having all our accrued data/plan cache removed, only to start rebuilding it the
next morning. Auto Shrink should be false because it could happen during our
busiest time and cause a lot of unanticipated disk IO. Auto Create Statistics
and Auto Update Statistics should both be True.
Moving down to the remainder of the page I leave the remainder set to the
defaults. I'm not opposed to you changing any of these settings as needed, just
saying that as far as a standard scenario these defaults work reasonably well.
The last option is to set up additional files and filegroups:
Here we have the default settings, which consist of a file group called
PRIMARY which will contain one file; our TEST.MDF. I never add additional
filegroups in the beginning, preferring to wait until I see the usage to decide
to take that step. Here is a link to some good
guidelines on storage if you'd like more information.
Visit my blog at
http://blogs.sqlservercentral.com/andy_warren/default.aspx
