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)

 Filegroups
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.
  • Created by using FILEGROUP in a CREATE DATABASE or ALTER DATABASE
  • 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
USE MASTER;
GO
    -- create db with default data filegroup and one log file.
    -- specify growth increment and max fize for primary data file.
CREATE DATABASE MyDB
            ON PRIMARY
    ( name = 'MyDB_primary',
                filename =
        'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_Prm.mdf',
                size=4mb,
                maxsize=10mb,
                filegrowth=1mb),
            filegroup MyDB_fg1
    ( name = 'MyDB_FG1_Dat1',
                filename =
        'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG1_1.ndf',
                size=1mb,
                maxsize=10mb,
                filegrowth=1mb),
    ( name = 'MyDB_FG1_Dat2',
                filename =
        'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG1_2.ndf',
                SIZE = 1MB,
                MAXSIZE=10MB,
                FILEGROWTH=1MB)
            LOG ON
    ( name='MyDB_archlog1',
                filename =
        'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_archlog1.ldf',
                size=1mb,
                maxsize=10mb,
                filegrowth=1mb);
GO

    -- 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
ALTER DATABASE MyDB
            modify filegroup MyDB_FG1 DEFAULT;
GO

(4) Add a new data file to a filegroup
  ALTER DATABASE MyDB
    ADD FILE
      ( name = 'MyDB_FG1_Dat3',
        filename = 
         'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG1_3.ndf',
        size=1mb,
        maxsize=10mb,
        filegrowth=1mb)
     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.
  USE MyDB;
  CREATE TABLE emp
   ( empid INT PRIMARY KEY,
     name CHAR(20))
  ON MyDB_FG1;
  GO   

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

(7) Create a new filegroup
  -- Create a new filegroup
  ALTER DATABASE MyDB
    ADD filegroup MyDB_FG2;
    
  ALTER DATABASE MyDB
     ADD FILE  
     ( name = 'MyDB_FG2_Dat1',
       filename = 
         'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG2_1.ndf',
       size=1mb,
       maxsize=10mb,
       filegrowth=1mb)       
    TO filegroup MyDB_FG2;;    

sys.databases
sys.filegroups
sys.database_files
sys.master_files
sys.data_spaces
Now, check the database catalog for information on:
(a) Information on databases: sys.databases
USE MyDB;
GO

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
USE MyDB;
GO

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
USE MyDB;
GO

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:
USE MyDB;
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.)

Posted by Minima-Imagem
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)