Configuring SQL Server: Data and Log files

Configure data files Configure log files 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.)

SQL Server: Database types

Two types of databases:

• (a) System databases: Used by SQL Server and SQL Server services.
• Four system databases: master, MSDB, model and tempdb
  contain configuration information about the SQL Server services and user databases.
• (b) User databases: Used to store user data.

System databases

Master database	MSDB database
Primary configuration database. Usually small. Data in: master.mdf Logs in: masterlog.ldf Used by SQL Server service. Systems table in the master database: keep information about users and databases within the instance. Needs to be available (not corrupted) for database startup.	Used by the SQL Server Agent service. Data here include: scheduled jobs, job parameters, alerts, operators. Data in: MSDBData.mdf Logs in: MSDBlog.ldf

USE MASTER;
EXEC sp_helpfile
name    fileid  filename                                               filegroup size    maxsize   growth usage
------  ------  -----------------------------------------------------  --------- ------- --------- ------ --------- 
master  1       C:\Program Files\...\MSSQL10...\MSSQL\DATA\master.mdf  PRIMARY   4096 KB Unlimited 10%    data only
mastlog 2       C:\Program Files\...\MSSQL10...\MSSQL\DATA\mastlog.ldf NULL      1024 KB Unlimited 10%    log only

USE MSDB;
EXEC sp_helpfile
name     fileid  filename                                                filegroup size     maxsize      growth  usage
------   ------  ------------------------------------------------------  --------- -------  ---------    ------- --------- 
MSDBData 1       C:\Program Files\...\MSSQL10...\MSSQL\DATA\MSDBData.mdf PRIMARY   15104 KB Unlimited   10%    data only
MSDBLog  2       C:\Program Files\...\MSSQL10...\MSSQL\DATA\MSDBLog.ldf NULL      5184 KB  2147483648 KB 10%    log only

Model database

Tempdb database

Model for new databases. Properties set in the model database will be automatically configured on new databases.

Used by SQL Server database engine tempdb is recreated every time SQL Server service starts. Therefore, any data in tempdb is LOST when the database shutdown. If you need an object to always exist in tempdb, it HAS to be recreated after instance startup. For this you can use a startup stored procedure, which are automatically executed when the SQL Server service starts up. Users may also create temporary objects in the tempd: i.e. USE tempdb; SELECT * INTO #tmp_orders   FROM InsideTSQL2008.Sales.Orders   WHERE custid = 10; -- copy all data from sales table into a temporary table #temp_table. The # sign directs the server to create a LOCAL temporary table in the tempdbdatabase. Local temporary tables: visible only in the current session. The ## sign directs the server to create a GLOBAL temporary table. Global temporary tables: visible to all sessions.

USE model;
EXEC sp_helpfile

USE tempdb;
EXEC sp_helpfile

name       fileid  filename                                                filegroup size     maxsize    growth  usage
--------   ------  ------------------------------------------------------  --------- -------  ---------  ------- --------- 
modeldev   1  C:\Program Files\...\MSSQL10...\MSSQL\DATA\model.mdf      PRIMARY   1280 KB  Unlimited  1024 KB data only
modellog   2  C:\Program Files\...\MSSQL10...\MSSQL\DATA\modellog.ldf  NULL      512  KB  Unlimited  10%     log only

name       fileid  filename                                                filegroup size     maxsize    growth  usage
--------   ------  ------------------------------------------------------  --------- -------  ---------  ------- --------- 
tempdev    1  C:\Program Files\...\MSSQL10...\MSSQL\DATA\tempdb.mdf     PRIMARY   8192 KB  Unlimited  10%     data only
templog    2  C:\Program Files\...\MSSQL10...\MSSQL\DATA\templog.ldf   NULL      512  KB  Unlimited  10%     log only

The full name of a temporary table as stored in the sysobjects table in tempdb. The full name is: table name specified in the CREATE TABLE statement + a system-generated numeric suffix.

USE tempdb;
  SELECT * FROM sysobjects
  WHERE name LIKE '#%';

If you need to move tempdb (to address performance issues due to disk contention), you can:

USE MASTER;
GO

ALTER DATABASE tempdb modify FILE
  (name = tempdev,
   filename = 'F:\tempdata\tempdb.mdf');
GO
ALTER DATABASE tempdb modify FILE
  (name = templog,
   filename = 'F:\tempdata\templog.ldf');
GO

SQL Server Architecture: Pages and Extents

Logical Architecture: Pages and Extents

Pages:

• Page is the fundamental unit of data storage in SQL Server (datablocks in Oracle).
• Eight(8) physically contiguous pages => One (1) EXTENT.
• Disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered from 0 to n.
• Disk I/O operations are performed at the page level. (SQL Server reads/writes WHOLE pages).
• Page size: 8kb. ==> 128 pages = 1Mb.

On SQL Server Pages

Header: 96-bytes. (page#, type, free space, allocation unit ID of the object that owns the page) data rows: inserted serially in the page. Types of Pages: Data Index Text/Image Global allocation Map: extent allocation info Shared allocation Map: extent allocation info Page Free Space: free space info Index allocation map: extents used by table or index per allocation unit. Bulk changed map: extents modified by BULK ops since last BACKUP LOG Differential Changed Map: extents modified since last BACKTUP DATABASE

Extents

• The basic unit of space management.
• One EXTENT = EIGHT (8) contiguous pages (64Kb). ==> 16 Extents = 1Mb

Types of extents: Mixed and Uniform
	(a) Uniform: owned by a single object (b) Mixed: shared by up to eight objects.