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