difference between primary&secondary files

[Kvara prasad]

For creating a user database three important files named primary, secondary & transaction log files. so my question is 'what is the difference between primary & secondary files?' and is 'what is the necessity of secondary files?'

[jagged]

For creating a user database three important files named primary, secondary & transaction log files. so my question is 'what is the difference between primary & secondary files?' and is 'what is the necessity of secondary files?'

Most of the time I only use the primary file groups. I do have some apps that use partitioning to move data to a secondary file group (which is stored on another disk) essentially to archive data that is X number of days old. In this case, and very simply put, querying the newest data (less than X days old) and the archived data together will be faster (than if only one disk was used) because I have two disks to share the workload.

There are also ways to recover data from only specific file groups. Using the same "setup" as above, if the server were to crash, I can recover and restore the application using the last X days of data a lot faster than if I had to recover ALL of the data (which could run into hundreds of gigs). Most of the time, people can wait until Monday to see data from 2002 but most people can't wait for info from yesterday.

Simple examples, but it gives you a basic idea.

[ck9663]

Start here first.

Here's some write ups I pick up here and there...


You can group data files for a database together into filegroups for allocation and administration purposes. In
some cases, you can improve performance by controlling the placement of data and indexes into specific
filegroups on specific disk drives.

.. Primary filegroup This filegroup contains the primary data file and any files not put into another specific
filegroup. All pages from system tables are always allocated from files in the primary filegroup.

.. User-defined filegroup You can create user-defined filegroups by using the FILEGROUP keyword in the
CREATE DATABASE or ALTER DATABASE statement.

.. Default filegroup This filegroup contains the pages for all tables and indexes that aren't placed in a
specific filegroup. Only one filegroup in each database can be the default filegroup. A database owner can
change which filegroup is the default by using the ALTER DATABASE statement. If no default filegroup
was speci-fied, the primary filegroup is also the default filegroup.


Why do you need more than one filegroups?


If you need to restore a database from a backup because of a disk crash, you should be aware
that the new database must contain the same number of files as the original. For example, if your
original database consisted of one large 12-GB file, you would need to restore it into a database
with one file of that size. If you don't have another 12-GB drive immediately available, you won't
be able to restore the database! If, however, you originally created the database on several smaller
files, you have added flexibility during a restoration. You might be more likely to have several 4-
GB drives available than one large 12-GB drive.

Spreading the database onto multiple files, even onto the same drive, gives you the
flexibility of easily moving the database onto separate drives if you modify your hardware
configuration in the future. Microsoft's internal SAP system uses a SQL Server 7 database created
on 12 files. Microsoft thought this would give it the ultimate flexibility. They could separate the
files into two groups of six, six groups of two, four groups of three, and so on. This allows them to
experiment with performance enhancements gained as files are spread over different numbers of
physical drives.


-- CK