431. chapter2.configure log and datafiles

Operation sysytem file type: MDF & LDF
Database file type: MDF & NDF

 

=== Primary data file

Include startup infomation for the database catalog and points to the outher database files
Can also contain OBJ & data, that means can not contain OBJ & Data

After you create a custom file group and specify the file group as the default file group, all database objects are stored in the file group.

The primary data file is saved in the primary file group. The main file group is not necessarily the default file group, and the default file group is not necessarily the main file group

 

=== Secondary data file [Max: 32776]
OBJ & User Data
* ** Put each SDF on a different disk drive to boost performance

 

=== Log File
Records all database modifications that eachTransactionMake
Create multiple log files per database to facilitate faster recovery 

=== File group: A Logical Structure = PFG & UFG
Splitting database objects internal SS several filegroups, you can
# Take advantage of the differw.disk subsystems
# Allow SQL Server to perform paralled disk operations
# Back up and restore files individually

* ** Default filegroup
* ** Read-only filegroup

 

=== How to design table
One access-intensive table: create multiple second data file,
Store the files on different disk drives,
And group these files in a filegroup or multiple filegroups

 

=== How to design filegroup
Create at least one user-defined filegroup to hold seconday data files and database objects.
Configure this filegroup as the default filegroup so that SQL server will store all objects
You create in this filegroup

 

= How to design database files with RAID system

Raid10 ------ log files

RAID5 ------- data files

 

Configuration database files and filegroups
1 Create   Database   [ 70431 ]  
2 On
3 -- Primary data file
4 Primary  
5 (Name = N ' 70431_pdf ' , Filename = N ' D: \ DBF \ 70431_0000.mdf ' ,
6 Size = 10240kb, maxsize = 102400kb, filegrowth = 20480kb ),
7
8 -- Seconday Data File
9 Filegroup [ Usedefine1 ]  
10 (Name = N ' 70431_data1 ' , Filename = N ' E: \ projects \ SQL \ dbfiles \ 70431_data1.ndf ' ,
11 Size = 10240kb, maxsize = 102400kb, filegrowth = 20480kb ),
12 (Name = N ' 70431_data3 ' , Filename = N ' E: \ projects \ SQL \ dbfiles \ 70431_data3.ndf ' ,
13 Size = 10240kb, maxsize = 102400kb, filegrowth = 10240kb ),
14 Filegroup [ Userdefine2 ]  
15 (Name = N ' 70431_data2 ' , Filename = N ' E: \ projects \ SQL \ dbfiles \ 70431_data2.ndf ' ,
16 Size = 10240kb, maxsize = 102400kb, filegrowth = 10240kb ),
17 (Name = N ' 70431_data4 ' , Filename = N ' E: \ projects \ SQL \ dbfiles \ 70431_data4.ndf ' ,
18 Size = 10240kb, maxsize = 102400kb, filegrowth = 10240kb)
19
20 -- Log File
21 Log   On  
22 (Name = N ' 70431_log ' , Filename = N ' F: \ projects \ SQL \ lgfiles \ 70431_log.ldf ' ,
23 Size = 20480kb, maxsize = 204800kb, filegrowth = 20480kb ),
24 (Name = N ' 70431-log1 ' , Filename = N ' F: \ projects \ SQL \ lgfiles \ 70431-log1.ldf ' ,
25 Size = 20480kb, maxsize = 102400kb, filegrowth = 10240kb)
26
27
28 Go
29
30 Alter   Database   [ 70431 ]
31 Modify filegroup [ Usedefine1 ]   Default