Chapter 6: Creating and Configuring User Databases

1
Chapter 6 Creating and Configuring User Databases
2
Chapter Overview
P143

• Creating a User Database
• Setting Database Options
• Managing User Database Size
• Placing Database Files on Multiple Disks

3
User Database Creation
P144-145

• 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.

4
Data File Default Values
P145
5
Transaction Log File Default Values
6
Model Database

• Database objects in the model DB
• are used to populate all new databases.
• are inherited by all new databases.
• are inherited by all new databases.
• Default collation of the model DB is inherited by
  all new databases.

7
Database Creation Tools
P147-156

• Create Database wizarduseful for novices
  creating simple databases
• Microsoft SQL Server Enterprise Manager
  (directly)graphical method for creating simple
  or complex databases
• Transact-SQLscript method for creating simple or
  complex databases

8
Script Generation
P158-159

• 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.

9
Database Options
P161
10
Tools for Viewing and Modifying Database Options
P161-162

• SQL Server Enterprise Manager
• The DATABASEPROPERTYEX system function
• The ALTER DATABASE statement
• The sp_dboption system stored procedure

11
Automatic File Growth
P163-164

• 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.

12
Automatic File Shrinkage
P165

• 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.

13
Manual Data File Size Management
P165-166

• 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.

14
Manual Transaction Log File Size Management
P167

• 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.

15
Additional Data and Transaction Log Files
P168-169

• 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.

16
RAID
P170-171
17
Filegroups
P170-171
18
File Placement
P172-173
19
Tempdb Placement
P174

• 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.

20
Moving Data and Transaction Log Files
P174-176

• 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.

21
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.

22
Lab

• Lab 4 Creating and Working with Databases
• 75 minutes