Configuring SQL Server: Data and Log files

  1. Configure data files
  2. Configure log files
  3. Configure filegroups

Files (three types:)
Primary data files: (.mdf) Secondary data files: (.ndf) Log files: (.ldf)
Mandatory. Is the database "starting point".
Holds pointers to other files in the database.
Contain schema (structure) and database properties information
Contains startup information for the database catalog.
May (but should not) also contain objects and user data.
Keep only catalog objects in the primary file: reduce disk contention.
These are all data files, other then the primary.
Optional and user-defined.
Contains objects and user data.
Can (should) be distributed across disks/devices to improve access performance.
A database may have up to 32,766 secondary data files.
AT LEAST one log file is needed for each database.
Used for recovery.
Default setting: logs in the same disk in which datafiles are created.
Should be stored in separate disks, unless the database is read-only (no contention, then).

When does the DB Engine uses file location info from primary file (.mdf)?
  • when attaching a database using CREATE DATABASE stmt with either FOR ATTACH or FOR ATTACH_REBUILD_LOG options.
  • When upgrading from SQL Server 2000 or 7.0
  • When restoring the master database.
Note: SQL Server x Oracle
  • In an Oracle database, the list of datafile locations is maintained in the control file, and the information is used for database startup and recovery.
  • In this sense, the primary data file of a SQL Server database, holding file location info, performs a role similar to that performed by Oracle's control file.

  • The locations of all the files in a database are recorded in the primary file AND in the master database.
  • Most of the time, SQLServer DB Engine uses info from the master database.
  • File Logical name: used to refer to the physical files in all Transact-SQL stmts.
  • File Physical (OS) name:
  • NTFS File system recommended.
  • Each instance has its own default directory.
  • Shared files for ALL INSTANCES are located at:
  • disk:\Program Files\Microsoft SQL Server\100\

Data file pages
  • Pages as numbered sequentially (0,1,..)
  • Each file has a unique File ID number
  • To find a page: File ID + page number
  • First page of file: header page.
  • Other system info pages:
    • database boot page: info about the database attributes
    • AIM (allocation information map

(source: SQl Server books online)

Datafiles can be grouped in filegroups for easier administration and better performance.
Filegroup is a logical structure: database objects can be allocated to filegroups, and can be splitted across various filegroups.

Note: SQL Server x Oracle
  • SQL Server: filegroups ==> Oracle: tablespaces
  • An Oracle database, TABLESPACESare logical structures similar to filegroups.
  • Oracle: You assign database objects to a TABLESPACE.
  • SQL Server: You assign database objects to a FILEGROUP.
  • A TABLESPACE may have multiple datafiles, split across different disks or storage devices, for performance improvement.
  • TABLESPACEScan also be backed up or restored as a unit.
  • One object (i.e. table, index) CAN span multiple TABLESPACES, when it is PARTITIONED.
  • Oracle: Default Tablespace => SQL Server: Default filegroup
  • Handling temporary objects:
    • Oracle: Temporary Tablespace => SQL Server: Tempdb database

Primary filegroups:
  • Contains the primary datafile + any secondary datafile not allocated to a filegroup.
  • All system tables are allocated to the primary filegroup.
  • Primary filegroup equivalent to the Oracle SYSTEM TABLESPACE.

Secondary (user-defined) filegroup:
  • User-defined.
  • A databaes may have up to 32,766 secondary filegroups.
  • Log files are never part of a filegroup.
  • Each file belongs to ONLY ONE filegroup.
  • If a filegroup has more than one data file, SQL Server distributes the data across the various files.
  • SQL Server keeps the amount of data in each file proportional to the file size.

Filegroup properties:
  • Can be set or changed from SSMS or using ALTER DATABASE statement
  • Default filegroup: One filegroup is designated the DEFAULT filegroup.
  • Members of the db_owner database role can switch the default filegroup.
  • A filegroup can be made READ-ONLY
  • Tables (and other objects) are stored in filegroups, not in files.

Create database MyDB
(1) Define primary filegroup (logical name, physical name, file size, growth policy)
(2) Define secondary filegroup
    -- create db with default data filegroup and one log file.
    -- specify growth increment and max fize for primary data file.
            ON PRIMARY
    ( name = 'MyDB_primary',
                filename =
        'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_Prm.mdf',
            filegroup MyDB_fg1
    ( name = 'MyDB_FG1_Dat1',
                filename =
        'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG1_1.ndf',
    ( name = 'MyDB_FG1_Dat2',
                filename =
        'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG1_2.ndf',
                SIZE = 1MB,
            LOG ON
    ( name='MyDB_archlog1',
                filename =
        'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_archlog1.ldf',

    -- NOTE: In a production database, the transaction logs SHOULD be stored on a physical disk OTHER THAN 
    -- the one in which the data files are located.

(3) Alter database define default filegroup.
  -- define the default filegroup
            modify filegroup MyDB_FG1 DEFAULT;

(4) Add a new data file to a filegroup
      ( name = 'MyDB_FG1_Dat3',
        filename = 
         'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG1_3.ndf',
     TO filegroup MyDB_FG1;    

(5) Create table EMP on MyDB
(6) Check table information in the database
  -- create table in the user-defined filegroup
  -- Table EMP created in the dbo schema.
   ( empid INT PRIMARY KEY,
     name CHAR(20))
  ON MyDB_FG1;

  EXEC sp_columns @table_name='emp', @table_owner='dbo'

(7) Create a new filegroup
  -- Create a new filegroup
    ADD filegroup MyDB_FG2;
     ADD FILE  
     ( name = 'MyDB_FG2_Dat1',
       filename = 
         'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG2_1.ndf',
    TO filegroup MyDB_FG2;;    


Now, check the database catalog for information on:
(a) Information on databases: sys.databases

SELECT name, create_date, user_access_desc,
       state_desc, recovery_model_desc
FROM sys.databases;
name create_date user_access_desc state_desc recovery_model_desc ------- ---------------------- ---------------- ---------- -------------- master 2003-04-08 09:13:36.390 MULTI_USER ONLINE SIMPLE tempdb 2011-10-06 14:56:52.290 MULTI_USER ONLINE SIMPLE model 2003-04-08 09:13:36.390 MULTI_USER ONLINE FULL msdb 2010-04-02 17:35:08.970 MULTI_USER ONLINE SIMPLE TESTSQL 2011-10-08 10:43:13.347 MULTI_USER ONLINE FULL MyDB 2011-10-08 19:12:38.427 MULTI_USER ONLINE FULL

(b) Information on filegroups: sys.filegroups

SELECT * FROM sys.filegroups;
name data_space_id type type_desc is_default filegroup_guid log_filegroup_id is_read_only ---------- ------------- ---- ---------------- ---------- ------------------------------------ ---------------- ------------ PRIMARY 1 FG ROWS_FILEGROUP 0 NULL NULL 0 MyDB_fg1 2 FG ROWS_FILEGROUP 1 803DD155-A6A1-4CB5-B183-3FB16D561B13 NULL 0 MyDB_FG2 3 FG ROWS_FILEGROUP 0 D167E2CC-DBB6-45B2-8197-2FF1EA827940 NULL 0 (3 row(s) affected)

(c) Information on data files: sys.database_files

SELECT name, type_desc, physical_name, state_desc, size
FROM sys.database_files;
name type_desc physical_name state_desc size ------------- --------- ------------------------------------------------------------------ ---------- ------ MyDB_primary ROWS c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_Prm.mdf ONLINE 512 MyDB_archlog1 LOG c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_archlog1.ldf ONLINE 128 MyDB_FG1_Dat1 ROWS c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_FG1_1.ndf ONLINE 128 MyDB_FG1_Dat2 ROWS c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_FG1_2.ndf ONLINE 128 MyDB_FG1_Dat3 ROWS c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_FG1_3.ndf ONLINE 128 MyDB_FG2_Dat1 ROWS c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_FG2_1.ndf ONLINE 128

(d) To list the names of the files of all databases in a server:sys.master_files
SELECT DB_NAME(database_id) AS 'db_name', name AS 'logical filename', 
       physical_name, type_desc
FROM sys.master_files;
db_name logical filename physical_name type_desc ---------- ---------------- ------------------------------------------------------- ---------- master master C:\Progr...\MSSQL10...\MSSQL\DATA\master.mdf ROWS master mastlog C:\Progr...\MSSQL10...\MSSQL\DATA\mastlog.ldf LOG tempdb tempdev C:\Progr...\MSSQL10...\MSSQL\DATA\tempdb.mdf ROWS tempdb templog C:\Progr...\MSSQL10...\MSSQL\DATA\templog.ldf LOG model modeldev C:\Progr...\MSSQL10...\MSSQL\DATA\model.mdf ROWS model modellog C:\Progr...\MSSQL10...\MSSQL\DATA\modellog.ldf LOG msdb MSDBData C:\Progr...\MSSQL10...\MSSQL\DATA\MSDBData.mdf ROWS msdb MSDBLog C:\Progr...\MSSQL10...\MSSQL\DATA\MSDBLog.ldf LOG TESTSQL2008 TESTSQL2008 C:\Progr...\MSSQL10...\MSSQL\DATA\InsideTSQL2008.mdf ROWS TESTSQL2008 TESTSQL2008_log C:\Progr...\MSSQL10...\MSSQL\DATA\InsideTSQL2008_log.LDF LOG MyDB MyDB_primary c:\Progr...\MSSQL10...\MSSQL\data\MyDB_Prm.mdf ROWS MyDB MyDB_archlog1 c:\Progr...\MSSQL10...\MSSQL\data\MyDB_archlog1.ldf LOG MyDB MyDB_FG1_Dat1 c:\Progr...\MSSQL10...\MSSQL\data\MyDB_FG1_1.ndf ROWS MyDB MyDB_FG1_Dat2 c:\Progr...\MSSQL10...\MSSQL\data\MyDB_FG1_2.ndf ROWS MyDB MyDB_FG1_Dat3 c:\Progr...\MSSQL10...\MSSQL\data\MyDB_FG1_3.ndf ROWS

(e) check data spaces:
SELECT name, type_desc FROM sys.data_spaces;
name type_desc -------- -------------- PRIMARY ROWS_FILEGROUP MyDB_fg1 ROWS_FILEGROUP MyDB_FG2 ROWS_FILEGROUP

Database files and filegroups
(figure modified from here.)

No comments:

Post a Comment