Michael Aubert's fifth article in the SQL Server 2000 Administration in 15 Minutes a Week series covers the files that make up a database and also steps through the process of creating a database.
Welcome to the fifth article in my series SQL Server Administration in 15
Minutes a Week. Last week we took a look at the SQL Server Enterprise Manager.
We learned how to register our SQL Servers, how to organize Servers into groups,
and we learned about the databases that are installed by default. This week we
are going to learn about the files that make up a database and we will also
create our first database. The topics for this article include:
- Database Files
- Database Filegroups
- Creating a Database
in SQL Server 2000 have an underlying data storage structure made up of two or
more system files. Each database must have at least one data file, used for
storing database objects (tables, views, stored procedures, etc), and one log
file, used for storing transaction information. In addition, each database must
have its own files; you can't share files between databases.
files store objects and the data inside objects. The first data file you create
is known as the "Primary data file." If you need to create additional data files
for your database, they must be created as "Secondary data files."
files, on the other hand, are used to track changes in the database and have
only one file type. In the event of a system failure, the log files can be used
to repair the database. We will look at how SQL Server tracks database
modifications when we start talking about transactions.
following table summarizes the three file types.
Primary data file
database must have one, and only one, Primary data file. This file type
stores the system tables, data, and also keeps track of the other files that
makeup the database. Primary data files use the extension .MDF
Secondary data files
database can have one or more Secondary data files, but none are required.
This file type stores data and uses the extension .NDF
Transaction log files
database must have one or more Transaction log files. This file type is used
to store Transaction information and can be used to recover the database.
Transaction log files use the extension .LDF
database may have all its data stored in the Primary data file and may only have
one log file. On the other hand, a more complex database may have the Primary
data file, three Secondary data files, and two log files. By using multiple
files a more complex database can spread its load across as many physical disks
we know about the files that makeup our database, let's look at how our data is
being stored inside the files.
is stored, it is placed in an 8 kilobyte contiguous block of disk space known as
a page. Once you remove the overhead needed to manage the page, 8060 bytes are
left for storing data. This brings up an important note and something to
consider, rows can't span multiple pages. Therefore the maximum size a row can
be is 8060 bytes.
the pages in a data file, pages are grouped into "extents." Extents are made up
of a grouping of 8 contiguous data pages (64 kilobytes). Extents can then be
broken into two categories:
mixed extents, which contain
data from up to eight objects in the database, or uniform extents, which contain
data from only one object in the database. When you create a new object it is
allocated space from a mixed extent. Once a table has grown larger than eight
data pages it is from then on allocated uniform extents. By using mixed and
uniform extents, SQL Server can save disk space when an object is very small.
Page 2: Database Filegroups
See All Articles by Columnist Michael Aubert
to manage data files they need to be placed into "filegroups." By placing files
into filegroups, we can control how data is spread over multiple files and we
can also direct specific tables to specific files or sets of files.
Server allocates extents to tables, it does so by the proportional amount of the
free space in all files in a filegroup. For example, let's say we had a
filegroup that was made up of one file that had 50 MB free and another file that
had 100 MB free. For every extent that was allocated from the first file, two
extents would be allocated from the second file. Therefore the two data files
would fill up at approximately the same time.
Filegroups also allow control over what files a table will be stored in. For
example, say we had 4 hard disks with 1 secondary data file on each disk. In our
database let's say we have two large tables that we need to perform frequent
join operations on. In order to get the best performance we can setup two file
groups each containing two of the four data files. Next we can place each table
in one of the filegroups to give us maximum performance. Note that we will see
how to place tables and indexes in specific filegroups when we start creating
tables in an upcoming article.
a few specific filegroups we need to know about before we start creating our
database. First, the Primary data file is always created as part of the "Primary
filegroup" and it can't be removed from this group. The Primary filegroup
contains all the system objects (system objects are objects that store
information about the database) in the database. The system objects stored in
the Primary filegroup can't be removed from this group.
create a new object it is automatically placed into the "Default" filegroup
unless you specify otherwise. The Default filegroup can be set to any group you
wish, but by default, the Default filegroup is set to the Primary filegroup (got
that?). For example, if you created a new group called "MainStore" and set it as
the Default filegroup, any new tables you create would automatically be placed
into the "MainStore" filegroup.
come back to the concept of filegroups when we start creating objects. Also, I
will show you how to perform tasks such as moving objects between file groups
then. So, if you don't have a total grasp on how filegroups fit in...don't worry.
In a later article we will also take a more in-depth look at using files and
filegroups to optimize database performance.
Page 3: Creating a Database
See All Articles by Columnist Michael Aubert
Creating a Database
enough with that stuff...let's create our first database! Start by opening
Enterprise manager and expand the tree view until you see the "Databases" folder
of your server.
Click to Enlarge
Click the Databases folder and select "New Database..."
Name textbox enter "FirstDB"
to note on this screen is the ability to set the collation for this database
other than the server default. If you don't remember what the collation is, go
back and look over my "Basic Installations" article. For this example we will
leave it as the server default.
"Data Files" tab.
portion of the Data Files tab shows you all the data files that make up this
database. As you can see Enterprise Manager has already added the Primary Data
file for us. The logical file name is FirstDB_Data and the physical file is
named FirstDB_Data.MDF located in the Data folder that was specified when we
installed SQL Server. The logical file name is the name that SQL Server uses
internally to reference the file, whereas the physical file name is the name of
the operating system file.
addition to the logical file name and physical name/location, we also can set
the Initial size for each file. If you know you will be loading data into your
database and have a general idea of its size, you can set this value to avoid
automatic file growth (more on this in a second). The last column allows the
selection and creation of file groups.
half of the Data Files tab allows you to set the Automatic file growth for each
file that makes up the database. When a database has used all available space in
a data file, you can have SQL Server automatically expand the file as needed.
The File Growth option allows you to set how much SQL Server will expand the
data file. You can select a fixed amount in megabytes or enter a percentage of
the current file size to grow the file by. You can also set a maximum file size
for the data file. Once a file reaches its maximum size, no more automatic file
growth will take place.
general rule, it is best to have files expand as few times as possible because
expanding files causes a performance hit to SQL Server.
"Transaction Log" tab.
options on the Transaction Log tab are very similar to the Data Files tab with
the only exception being the lacking of a File Group option. Filegroups are only
used for data files, not log files. All other options on the Transaction Log tab
are the same as the Data Files tab.
to accept the defaults.
database now appears in the Databases folder. If you go exploring into the
database we have just created you will notice it looks a lot like the model
database. This is because all new databases we create are a copy of the model
database. All the system objects and other objects we create in the model
database are added automatically to all the new databases we create.
way to create a database is by using the Create Database Wizard. To start the
Create Database Wizard, in Enterprise Manager Select "Wizards" from the "Tools"
expand "Database", select "Create Database Wizard", and click OK.
wizard then walks you through the steps to create a database.
again, that will finish it up for this week. Next week we will spend some more
time looking at database management.
As always, if you have any technical questions please post them on the SQL message board.Please send any non-technical questions, comments, and feedback to my email. I hope you are finding this
series to be a useful one, and I'm looking forward to your feedback.
See All Articles by Columnist Michael Aubert