Transcript Value Database Property

Chapter Overview
•
Creating a User Database
•
Setting Database Options
• Managing User Database Size
•
Placing Database Files on Multiple Disks
1
User Database Creation
•
Unique name is required and should conform to
rules for identifiers.
• Default values will be used for all unspecified
parameters.
•
Only members of the sysadmin or dbcreator
server roles can create a new database.
2
Data File Default Values
Database Property
Value
Logical primary file name
DBName_Data
Physical primary file name
DBName_Data.mdf
Autogrow
Enabled
Growth increment
10%
Maximum file growth size
Unlimited
3
Transaction Log File
Default Values
Database Property
Value
Logical transaction log file name DBName_Log
Physical transaction log file
DBName_Log.ldf
name
Autogrow
Enabled
Growth increment
10%
Maximum file growth size
Unlimited
4
Model Database
•
Database objects in the model DB are used to
populate all new databases.
• Database properties of the model DB are
inherited by all new databases.
•
Database options of the model DB are inherited
by all new databases.
•
Default collation of the model DB is inherited by
all new databases.
5
Database Creation Tools
•
Create Database wizard—useful for novices
creating simple databases
• Microsoft SQL Server Enterprise Manager
(directly)—graphical method for creating simple
or complex databases
•
Transact-SQL—script method for creating simple
or complex databases
6
Script Generation
•
SQL Server Enterprise Manager can create
Transact-SQL scripts for database objects
(including databases, tables, indexes, views, logins,
and groups).
•
Used to document existing objects for re-creation
on other servers or for disaster recovery.
•
Use a single script for all objects, or create multiple
scripts for different object types.
7
Database Options
8
Tools for Viewing and
Modifying Database Options
•
SQL Server Enterprise Manager
•
The DATABASEPROPERTYEX system function
• The ALTER DATABASE statement
•
The sp_dboption system stored procedure
9
Automatic File Growth
•
Default setting for all data and transaction log
files.
• In dedicated SQL Server environments, use only
as a safety valve so as not to cause a
performance hit and data file fragmentation.
•
Set growth increments large enough so that
growth occurs infrequently, and monitor when
the growth occurs.
10
Automatic File Shrinkage
•
Not the default setting for any data or
transaction log files.
• Useful for embedded applications and small
installations that are sharing drive space with
other applications.
•
In dedicated SQL Server environments, perform
shrinkage manually when the database is not
busy, if at all.
11
Manual Data File Size
Management
•
Monitor free space in data files.
•
To increase, use SQL Server Enterprise Manager
or the ALTER DATABASE statement.
•
To decrease, use SQL Server Enterprise
Manager, the DBCC SHRINKFILE statement, or
the DBCC SHRINKDATABASE statement.
12
Manual Transaction Log
File Size Management
•
Monitor free space in transaction log files.
•
Type of recovery model, level of database
activity, and transaction log backup schedule will
affect transaction log file growth.
•
To increase size, use SQL Server Enterprise
Manager or the ALTER DATABASE statement.
•
To decrease size, use SQL Server Enterprise
Manager, the DBCC SHRINKFILE statement, or
the DBCC SHRINKDATABASE statement.
13
Additional Data and
Transaction Log Files
•
Create additional data and transaction log files
on separate drives for more space and improved
performance.
•
Use SQL Server Enterprise Manager or the
ALTER DATABASE statement.
•
Create additional data files in the primary
filegroup or create secondary filegroups.
14
RAID
15
Filegroups
16
File Placement
17
Tempdb Placement
•
Fault tolerance is not an issue, as tempdb gets
rebuilt each time SQL Server starts.
• To increase performance, use a separate drive
for the tempdb data file.
•
Use RAID 0 if using a separate drive does not
eliminate tempdb as a bottleneck.
18
Moving Data and
Transaction Log Files
•
Data and transaction log files can be detached from
a SQL Server instance and then reattached to the
same or a different SQL Server instance.
•
If files are moved, ensure that the SQL Server
service account has Full Control permission under
NTFS to the files in the new location.
•
Detached files can be written to a compact disc for
read-only access to data.
•
Use SQL Server Enterprise Manager, sp_attach_db,
or CREATE DATABASE FOR ATTACH.
19
Chapter Summary
•
Default values will be used for all nonspecified
database properties.
• Use SQL Server Enterprise Manager to easily
create a new database.
•
Database options determine default behaviors.
• Use autogrowth as a safety valve only.
•
Use multiple drives and RAID for fault tolerance
and performance.
20