Transcript MCITP Administrator: Microsoft SQL Server 2005 Database

MCITP Administrator: Microsoft
SQL Server 2005 Database Server
Infrastructure Design Study
Guide (70-443)
Chapter 2: Designing Physical
Storage
Types of Files
• Primary Data files (.mdf)
• Secondary Data files (.ndf)
• Transaction log files (.ldf)
© Wiley Inc. 2006. All Rights Reserved.
Filegrpoups
• Logical groupings of secondary data
files
• Useful for VLDBs
© Wiley Inc. 2006. All Rights Reserved.
Pages
• Smallest unoit of storage in a SQL
Server Data File
• 8192 bytes each
• Start with 96 byte header
© Wiley Inc. 2006. All Rights Reserved.
Types of Pages
•
•
•
•
•
•
Data
Text/Image
Index
Global Allocation Map
Index Allocation Map
Page Free Space
© Wiley Inc. 2006. All Rights Reserved.
Extents
• Collection of eight contiguous pages
• Two types of extents:
– Uniform
– Mixed
© Wiley Inc. 2006. All Rights Reserved.
Estimating Database Size
1. Calculate record size of table by adding
the size if each column
2. Divide 8096 by row size, rounding down
to nearest number
3. Divide number of rows you expect by by
result from step #2. Round down
4. Multiply results from step 23 by 8192
5. Result is number of bytes tour table will
take up on the disk
© Wiley Inc. 2006. All Rights Reserved.
RAID
•
•
•
•
RAID-0 or disk striping
RAID-1 or disk mirroring
RAID-5 or stripe set with parity
RAID-10 combination of RAID-1 and
RAID-5
© Wiley Inc. 2006. All Rights Reserved.
Designing Transaction Log
Storgae
• Transaction log records all
transactions and database
modifications made by each
transaction.
• Can be used to return database to
consistent state
• Should not be deleted or removed
without full understanding of effects
© Wiley Inc. 2006. All Rights Reserved.
Transaction Log Operations
• Recover individual transactions
• Recover ll incomplete transactions
when QL Server is started
• Rolling back a restored database,
file, filegroup or page forward
• Support transactional replication
• Supprot standby server solutions
© Wiley Inc. 2006. All Rights Reserved.
Truncating Transaction Log
• Can be set as a property
• Does not reduce size of physical log
file
• Logs automatically truncated under
Simple Recovery Model
• Logs must be specifically backed up
under Full and Bulk-logged recovery
models
© Wiley Inc. 2006. All Rights Reserved.
Monitor Log Space Usage
• Monitor log space usage using the
command DBCC SQLPERF
(LOGSPACE)
• You can also use sys.database_files
to determine current size as well
other options and porperties for a log
file.
© Wiley Inc. 2006. All Rights Reserved.
Shrinking Log File
• Truncating does no reduce physical
size of log file
• Shrinking removes one or more
inactive virtual log files
© Wiley Inc. 2006. All Rights Reserved.
Adding or Enlarging Log File
• Adding or enlarging a log file is a way to
gain space without shrinking
• Adequate available disk space needs to
be available
• To add, use ADD LOG FILE clause of
ALTER DATABASE command
• To enlarge, use MODIFY FILE clause of
ALTER DATABASE statement, specifying
SIZE and MAXSIZE syntax
© Wiley Inc. 2006. All Rights Reserved.
Transaction Log Storage
• Store transaction logs and data files on
separate disk volumes
• Locate transaction log files on drives
supporting sequential write operations
• Store data files on drives supporting
random read and write operations
• Use fault tolerant storage
• Make multiple backup copies of
transaction log
• Bakup up to separate device such as a
tape or separate disk
© Wiley Inc. 2006. All Rights Reserved.
Back-up File Storage
• SQL Serrver only allows placing of
active files to what it deems to be a
local hard disk.
• Local hard disks are either on the
local machine or on a hardware
device connected directly to SQL
Server machine
• This limitation does not apply to
backups.
© Wiley Inc. 2006. All Rights Reserved.
Backup Management – Best
Practices
• Complete history of backup is stored in
msdb database
• Store backups in secure place
• Label backup media. Include expiration
dates
• Jeep older backups for a designated
period of time in case of damage or loss
to current backup
• Consider using RAID-10
• Write to disks attached to local machine
to improve speed
© Wiley Inc. 2006. All Rights Reserved.
Maintaining Transaction Log
Backups
• Three types of Transaction log
backup:
– Pure Log Backup
– Bulk Log Backup
– Tail Log Backup
© Wiley Inc. 2006. All Rights Reserved.
Deciding Where to Install
Operating System
• Install OS on a partition separate from
data and applications
• Place OS on a separate partition with or
without SQL Server executables. Partition
should also host paging file.
• Do not use file encryption or compression
since they are IO intensive and provide
no benefit to SQL Server, but can reduce
performance
© Wiley Inc. 2006. All Rights Reserved.
Where to Place SQL Server
Service Executables
• Only install services you plan to use
• SQL Server and SQL Server Agent run as
a Microsoft Windows services
• Must be assigned a Windows user
account
• Programs and data files can’t be installed
on
– Removable disk drive
– Compressed volume
– Shared drives ina failover cluster instance
© Wiley Inc. 2006. All Rights Reserved.
Number & Placement of Files
for Each Database
• Location of all files in a database are
recorded in the primary file of the
database and the master database
• Master database is primary source
• Primary file used to specify location when:
– Using CREATE DATABASE
– Upgrading from SQL Server 2000 or SQL
Server 7.0 to SQL Server 2005
– Restoring master database
© Wiley Inc. 2006. All Rights Reserved.
Filenames
• Logical_file_name is used to refer to
tehe physical file in all Transact-SQL
statements
• OS_file_name is the name of the
physical file, including the directory
path
© Wiley Inc. 2006. All Rights Reserved.
Setting File Size
• Files can grow beyond specified
start size
• Growth is based on sepcified growth
increment
• Maximum file size can be set
© Wiley Inc. 2006. All Rights Reserved.
Setting up Filegroups
• Two types of filegroups
– Primary filegroup
– User defined filegroups
• One filegroup in each database is
designated the default filegroup. If
none is designated the primary
filegroup serves as the default
filegroup
© Wiley Inc. 2006. All Rights Reserved.
Designing Instances
• Two types of SQL Server 2005
instances:
– Default
– Named
© Wiley Inc. 2006. All Rights Reserved.
Designing Instances –
Guidelines
• If upgrading from SQL Server 7.0 the
upgraded instance must be a default
instance.
• If only one instance is planned it should
be a default instance
• If supporting slinet connectiosn from SQL
server 7.0 use a default instance
• Any application installing SQL Servr
Express Edition should do so as a named
instance
• Instances can be installed at any time
© Wiley Inc. 2006. All Rights Reserved.
Deciding on number of
instances
• Determined by available resources
• Typically one SQL Server instance
will outperform two or more
instances on same hardware
• Use multiple instances to isolate
databases on a server
© Wiley Inc. 2006. All Rights Reserved.
Instances per SQL Server
Edition and Component
SQL Server Database
2005 edition Engine
instances
Analysis
Services
instances
Reporting
Services
instances
Enterprise
Edition,
Developer
Edition
50
50
50
Standard
Edition,
Workgroup
Edition,
Express
Edition
16
16
16
© Wiley Inc. 2006. All Rights Reserved.
Naming instances
• Limited to 16 characters
• Names are case insensitive
• Once named, an instance cannot be
renamed
• Can’t contain default, MSSQLServer or
other reserved keywords
• First character must be letter or
underscore
• Embedded space or other special
characters aren’t allowed
• Cannot include \ : ; ‘ & @ or ,
© Wiley Inc. 2006. All Rights Reserved.
How Many Physical Servers?
• How busy is each database
• What is the size of the database in
relation to available disk space
• What is the total load on the server
© Wiley Inc. 2006. All Rights Reserved.
System Databases
When an instance of SQL Server is installed,
Setup creates the database and log files as
shown below:
Database
master
model
Msdb
tempdb
Database file
Log file
Master.mdf
Mastlog.ldf
Model.mdf
Modellog.ldf
Msdbdata.mdf
Msdblog.ldf
Tempdb.mdf
Templog.ldf
© Wiley Inc. 2006. All Rights Reserved.
Deciding Where to Place
System Databases
• Normally left in the default
installation directory
• May need to be moved due to:
– Failure recovery
– Planned relocation
– Relocation for scheduled disk
maintenance
© Wiley Inc. 2006. All Rights Reserved.
Physical Storage for tempdb
Database
• Size and physical placement can
effect performance
• Optimal size and location
determined by many factors,
including workload, installed
components, etc.
• No hard and fast rule
© Wiley Inc. 2006. All Rights Reserved.
Physical Storage for tempdb
Database - Guidelines
• Place tempdb on fast I/O disk
subsystem
• Use disk striping
• Avoid placing on same disk as user
database
© Wiley Inc. 2006. All Rights Reserved.
Optimizing Configuration of
tempdb
• Set recovery model to Simple
• Set file to automatically grow
• Set growth increment to a
reasonable level
• Preallocate space for tempdb by
setting large initial file size
• Create one tempdb data file per
CPU
© Wiley Inc. 2006. All Rights Reserved.
Establishing Service
Requirements
• Install only Services needed,
disabling others not in use
• Don’t install SQL Server on a
domain controller
• Run separate SQL Server services
under separate Windows accounts
• If multitier environment run Web
logic and business logic on separate
computers
© Wiley Inc. 2006. All Rights Reserved.
Specifying Instance
Configurations
• Configuration Manger tool can be
used to
– Manage services
– Change accounts used by services
– Configure network protocols
– Manage network connectivity
configuration
for each instance
© Wiley Inc. 2006. All Rights Reserved.