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

当用户自己创建用户自定义文件组，并制定该文件组为默认文件组之后，所有的数据库对象将存在该文件组中

主数据文件保存在主文件组中。主文件组不一定是默认文件组，默认文件组也不一定是主文件组

 

===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 each transaction make
create multiple log files per database to facilitate faster recovery 

===file group: a logical structure = pfg&ufg
splitting database objects across several filegroups, you can
#take advantage of the differrentdisk 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
 1CREATE DATABASE [70431] 
 2ON  
 3--primary data file
 4PRIMARY 
 5    ( NAME = N'70431_pdf', FILENAME = N'D:\DBF\70431_pdf.mdf' , 
 6    SIZE = 10240KB , MAXSIZE = 102400KB , FILEGROWTH = 20480KB ), 
 7
 8--seconday data file 
 9FILEGROUP [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 ), 
14FILEGROUP [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 
21LOG 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
28GO
29
30ALTER DATABASE [70431]
31MODIFY FILEGROUP [USEDEFINE1] DEFAULT

 

 

转载于:https://www.cnblogs.com/yang_sy/archive/2008/08/17/1269581.html