Transcript MCITP Administrator: Microsoft SQL Server 2005 Database

MCITP Administrator: Microsoft
SQL Server 2005 Database Server
Infrastructure Design Study
Guide (70-443)
Chapter 1: Designing the
Hardware and Software
Infrastructure
Principles of Infrastructure
• Planning is essential
• Important to understand underlying
premises, not a single set of rules
• Infrastructure desighn varies by
project – no one size fits all
© Wiley Inc. 2006. All Rights Reserved.
Analyzing Current
Configurtaion
• Inventory operating systems, service packs and
hotfixes running
• Confirm that all service packs and hotfixes
applied
• Identify compatibility issues
• Inventory SQL Server editions, versions, service
packs and hotfixes
• Update service packs and hotfixes as necessary
– for SQL Server these can be obtained ay
http://www.microsoft.com/sql/downloads/default.
mspx
© Wiley Inc. 2006. All Rights Reserved.
Analyzing Current
Configuration
• Inventory which SQL Server
services are running, especially
– SQL Server Engine
– SQL Server Agent
– SQL Server Analysis Services
– SQL Server Reporting Services
– SQL Server Integration Services
– SQL Server Browser
– SQL Server Full-Text Searc
© Wiley Inc. 2006. All Rights Reserved.
Analyzing Current
Configuration
• Inventory hardware, including disk subsystems,
CPU, network cards and power supplies on
servers.
• Note RAID and SCSI use, if any
• Record existing SQL Server configuration
settings
• Record SQL Server instances and where they
exist
• Record minimum and maximum settings, CPU
used by each instance
• Assess quality of database server
documentation
© Wiley Inc. 2006. All Rights Reserved.
Analyzing Current
Configuration
• Determine locations of transaction log
files and data files.
• Examine the use of filegroups.
• Assess is adequate data-file sizes are
allocated to databases.
• Verify AutoShrink property is set to false.
• Determine if disk maintenance activities
such as defragmentation are performed
routinely.
• Review Event Viewer records to identify
disk-storage related problems.
© Wiley Inc. 2006. All Rights Reserved.
Forecasting Growth
Requirements
• Is growth planned for or anticipated?
• Are there plans to utilize applictaions that
require additional dtabases
• Are there plans to improve the database
server hardware
• What changes in cost of hardwrae are
expected?
• What data archiving requirements exist?
• What are the regulatory requirements?
© Wiley Inc. 2006. All Rights Reserved.
Business Requirements to
Consider in planning
• Budgetary constraints
• Existing IT Policies
–
–
–
–
Remote Access Policies
Encryption
Service-Level Agreements
Standard hardware and software configurations
• Regulatory Requirements
• Data Security
–
–
–
–
Confidentiality agreements
Privacy restrictions
Data encryption needs
External regulations
• Data availability
© Wiley Inc. 2006. All Rights Reserved.
Analyzing Storage
Requirements
• Assess Current Storage Capacity
– Disk space capacity
– Disk throughput capacity
– Locations and roles of database
servers
© Wiley Inc. 2006. All Rights Reserved.
Forecasting/Planning Storage
Requirements
• Establish estinmation period
• Project Growth Rate of Required
Disk Space
– Linear growth
– Compound growth
– Geometric growth
© Wiley Inc. 2006. All Rights Reserved.
Assessing Impact of
Regulatory Requirements
• Regulatory requirements will affect
how long data needs to be retained,
the level of security and thus storage
requirements. Most common types
of impact:
– Longevity
– Privacy
– Security
© Wiley Inc. 2006. All Rights Reserved.
Analyzing Network
Requirements
• All DBAs need to have nuts and
bolts understanding
• All DBAs need to identify key factors
when analyzing current traffic
• All DBAs need to be able to estimate
future network requirements
© Wiley Inc. 2006. All Rights Reserved.
Identifying Factors Influencing
Database Network Traffic
• Create a network digram showing parts of
network that:
– Deliver relicated data rto other servers
– Backup files to network devices
– Provide data to client applications
• Identify following connectivity influencers:
– Local and remote connections between
servers
– Firewalls
– Antivirus applications
© Wiley Inc. 2006. All Rights Reserved.
Identifying Factors Influencing
Database Network Traffic
• Gather following information about
each database server:
– Number of SQL Server instances
– Instance names
– Installed SQL services
– Network protocols
© Wiley Inc. 2006. All Rights Reserved.
Analyzing Current Database
Network Traffic
•
•
•
•
•
•
Traffic between servers
Backup processes
Database mirroring
Replication
Traffic between clients and servers
Identify potential bottlenecks
© Wiley Inc. 2006. All Rights Reserved.
Forecasting & Planning Future
Network Requirements
• Make a growth estmaet for each
network type
• Establish a baseline and study
trends
• Understand specific business needs
and the expected workload for the
estimation period.
© Wiley Inc. 2006. All Rights Reserved.
Analyzing CPU Requirements
• Assess Current CPU Performance
by reviewing:
– Type of CPUs
– Affinity Mask settings
– Current CPU usage
– Identify bottlenecks
© Wiley Inc. 2006. All Rights Reserved.
Forecasting & Planning CPU
Requirements
•
•
•
•
Determine Estimation Period
Establish baseline of CPU usage
Identify factors effecting CPU usage
Confirm estimates by performing
load tests and by using sizing tools
© Wiley Inc. 2006. All Rights Reserved.
Memory Requirements:
Assess Current Status
• Determine how much physical memory is installed on the
server
• What processes make use of memory
• Use the following System Monitor counters to assess
potential bottlenecks:
–
–
–
–
–
–
Memory: Available Bytes
Memory: Pages/sec
SQL Server: Memory Manager
Process: Working Set
SQL Server: Buffer Manager
SQL Server: Buffer Manager: Page Life Expectancy
• Determine if database and memory size match is correct
• Determine amount of memory used by connections
© Wiley Inc. 2006. All Rights Reserved.
Memory Requirements:
Forecasting & Planning
• Determine the number of SQL
Server instances
• Estimate database growth
• Specify the number of concurrent
users
• Use baseline data
• Determine the rate of growth I
memory usage over time
© Wiley Inc. 2006. All Rights Reserved.
Specifying Software Versions
& Hardware Configurations
• Meet or exceed design requirements
• Perform cost-benefit analyses
• Choose from approved hardwrae &
software configurations
• Be prepared to justify variations from
standards
© Wiley Inc. 2006. All Rights Reserved.
Choosing Version and Edition
of Operating System
• The edition of SQL Server 2005 is
dependent of which OS and version
is in use
• Internet Explorer 6.0 SP1 or higher
is required
• Your machine must meet hardware
requirements
• Hardware requirements are different
for each edition
© Wiley Inc. 2006. All Rights Reserved.
Five Editions of SQL
Server
•
•
•
•
•
Express
Workgroup
Standard
Enterprise
Developer
© Wiley Inc. 2006. All Rights Reserved.
Standard/Enterprise/
Developer Editions
• For 32-bit CPUs
• 600 MHz Pentium III-compatible or faster processor; 1
GHz or faster processor recommended
• 512 MB of RAM minimum, 1 GB or more
recommended
• ~350 MB of available hard-disk space for the
recommended installation with ~425 MB of additional
space for Books Online, and sample databases
• Windows XP Service Pack 2 or later; Microsoft
Windows 2000 Server Service Pack 4 or later;
Windows Server 2003 Standard, Enterprise, or
Datacenter Edition with Service Pack 1 or later;
Windows Small Business Server 2003 with Service
Pack 1 or later
© Wiley Inc. 2006. All Rights Reserved.
Standard/Enterprise/
Developer Editions
• For x64 CPUs
• 1-GHz AMD Opteron, AMD Athlon 64, Intel Xeon
with Intel EM64T support, Intel Pentium IV with
EM64T support processor
• 512 MB of RAM minimum, 1 GB or more
recommended
• ~350 MB of available hard-disk space for the
recommended installation with ~425 MB of
additional space for Books Online, and sample
databases
• Microsoft Windows Server 2003 Standard x64
Edition, Enterprise x64 Edition, or Datacenter
x64 Edition with SP 1 or later; Windows XP
Professional x64 Edition or later
© Wiley Inc. 2006. All Rights Reserved.
Standard/Enterprise/
Developer Editions
• For Itanium CPUs
• 1-GHz Itanium or faster processor
• 512 MB of RAM minimum, 1 GB or more
recommended
• ~350 MB of available hard-disk space for the
recommended installation with ~425 MB of
additional space for Books Online, and sample
databases
• Microsoft Windows Server 2003 Enterprise
Edition or DataCenter Edition for Itanium-based
systems with SP 1 or later
© Wiley Inc. 2006. All Rights Reserved.
Express Edition
• 600 MHz Pentium III-compatible or faster processor; 1
GHz or faster processor recommended
• 192 MB of RAM minimum; 512 MB or more
recommended
• ~350 MB of available hard-disk space for the
recommended installation with ~425 MB of additional
space for Books Online, and sample databases
• Windows XP Service Pack 2 or later; Microsoft
Windows 2000 Server Service Pack 4 or later;
Windows Server 2003 Standard, Enterprise, or
Datacenter Edition with Service Pack 1 or later;
Windows Small Business Server 2003 with Service
Pack 1 or later
© Wiley Inc. 2006. All Rights Reserved.
Workgroup Edition
• 600 MHz Pentium III-compatible or faster processor; 1
GHz or faster processor recommended
• 512 MB of RAM minimum, 1 GB or more
recommended
• ~350 MB of available hard-disk space for the
recommended installation with ~425 MB of additional
space for Books Online, and sample databases
• Windows XP Service Pack 2 or later; Microsoft
Windows 2000 Server Service Pack 4 or later;
Windows Server 2003 Standard, Enterprise, or
Datacenter Edition with Service Pack 1 or later;
Windows Small Business Server 2003 with Service
Pack 1 or later
© Wiley Inc. 2006. All Rights Reserved.