MCITP Administrator: Microsoft SQL Server 2005 Database Server Infrastructure Design Study Guide (Exam 70-443) [Multiple-component retail product]

Introduction		xxi
Assessment Test		xxix
Designing the Hardware and Software Infrastructure		1	(38)
Analyzing the Current Configuration		3	(5)
Thinking Holistically		3	(1)
Assessing How Good the Current Configuration Is		3	(2)
Forecasting and Incorporating Anticipated Growth Requirements into the Capacity Requirements		5	(3)
Designing for Capacity Requirements		8	(15)
Analyzing Storage Requirements		8	(7)
Analyzing Network Requirements		15	(3)
Analyzing CPU Requirements		18	(2)
Analyzing Memory Requirements		20	(3)
Specifying Software Versions and Hardware Configurations		23	(7)
Following Best Practices		23	(1)
Choosing a Version and Edition of the Operating System		24	(2)
Choosing an Edition of SQL Server 2005		26	(1)
Choosing a CPU Type		27	(1)
Choosing Memory Options		28	(1)
Choosing a Type of Storage		29	(1)
Summary		30	(1)
Exam Essentials		31	(1)
Review Questions		32	(4)
Answers to Review Questions		36	(3)
Designing Physical Storage		39	(38)
Understanding SQL Server Storage Concepts		40	(3)
Understanding Data Files and Transaction Log Files		41	(1)
Understanding Pages		42	(1)
Understanding Extents		43	(1)
Estimating Database Size		43	(3)
Planning for Capacity		44	(1)
Understanding RAID		45	(1)
Designing Transaction Log Storage		46	(6)
Managing Transaction Log File Size		47	(5)
Understanding Transaction-Log Storage		52	(1)
Designing Backup-File Storage		52	(2)
Managing Your Backups		52	(1)
Maintaining Transaction-Log Backups		53	(1)
Deciding Where to Install the Operating System		54	(1)
Deciding Where to Place SQL Server Service Executables		54	(1)
Specifying the Number and Placement of Files for Each Database		55	(2)
Setting Up Database Files		55	(1)
Setting Up Filenames		56	(1)
Setting Up File Size		56	(1)
Setting Up Database Filegroups		56	(1)
Designing Instances		57	(1)
Deciding on the Number of Instances		58	(1)
Deciding How to Name Instances		59	(2)
Deciding How Many Physical Servers You Need for Instances and How Many Databases to Create		61	(1)
Deciding Where to Place System Databases for Each Instance		61	(3)
Deciding on the Physical Storage for the tempdb Database for Each Instance		64	(3)
Establishing Service Requirements		67	(1)
Specifying Instance Configurations		67	(2)
Summary		69	(1)
Exam Essentials		70	(1)
Key Terms		71	(1)
Case Study: Mullen Enterprises		71	(1)
Planned Changes		71	(1)
Existing Data Environment		71	(1)
Business Requirements		71	(1)
Performance		71	(1)
Review Questions		72	(3)
Answers to Review Questions		75	(2)
Designing a Consolidation Strategy		77	(42)
Phase 1: Envisioning		78	(21)
Forming a Team		79	(1)
Making the Decision to Consolidate		79	(6)
Developing the Goals		85	(1)
Developing Guidelines for the Consolidation Project		86	(1)
Analyzing Your Environment		86	(13)
Phase 2: Planning		99	(9)
Evaluating Your Data		99	(2)
Planning Your Disk Subsystem		101	(1)
Making Initial Decisions about the Plan		102	(1)
Running the Upgrade Advisor		102	(1)
Choosing to Use Multiple Instances		103	(1)
Addressing 64-bit SQL Server		103	(1)
Considering High Availability		104	(1)
Going through Multiple Iterations		104	(1)
Sizing Hardware		105	(1)
Planning to Migrate Applications		106	(2)
Phase 3: Developing		108	(3)
Acquiring Your Hardware		109	(1)
Creating the Proof of Concept		109	(1)
Creating the Pilot		110	(1)
Reexamining Your Design		110	(1)
Phase 4: Deploying		111	(1)
Summary		111	(1)
Exam Essentials		112	(1)
Case Study: dkRanch Cabinets		112	(3)
Planned Changes		112	(1)
Existing Data Environment		112	(1)
Existing Infrastructure		113	(1)
Business Requirements		114	(1)
Technical Requirements		114	(1)
Review Questions		115	(2)
Answers to Review Questions		117	(2)
Designing Database-Level Security		119	(30)
Gathering Your Security Requirements		120	(8)
Understanding Security Scope		122	(1)
Analyzing Your Security Requirements		123	(1)
Dealing with Conflicting Requirements		124	(1)
Analyzing the Cost of Requirements		125	(3)
Integrating with the Enterprise		128	(5)
Choosing an Authentication Method		129	(1)
Setting Up Using Groups and Roles		130	(1)
Understanding Kerberos		131	(1)
Implementing Administrative Security		131	(1)
Implementing Application Roles Securely		132	(1)
Allowing Impersonation and Delegation Securely		132	(1)
Assessing the Impact of Network Policies		133	(2)
Achieving High Availability in a Secure Way		135	(1)
Mitigating Server Attacks		136	(4)
Preventing SQL Injection Attacks		138	(1)
Managing Source Code		138	(1)
Guarding Against HTTP Attacks		139	(1)
Thwarting Password Cracking		139	(1)
Protecting Backups		139	(1)
Auditing Access		140	(1)
Making Security Recommendations		140	(1)
Performing Ongoing Reviews		141	(1)
Summary		141	(1)
Exam Essentials		142	(1)
Case Study: Delaney's Simulations		142	(3)
Planned Changes		142	(1)
Existing Data Environment		143	(1)
Existing Infrastructure		143	(1)
Business Requirements		143	(1)
Technical Requirements		143	(2)
Review Questions		145	(3)
Answers to Review Questions		148	(1)
Designing Windows Server-Level Security Processes		149	(34)
Understanding Password Rules		150	(4)
Enforcing the Password Policy		151	(1)
Enforcing Password Expiration		152	(1)
Enforcing a Password Change at the Next Login		152	(1)
Following Password Best Practices		153	(1)
Setting Up the Encryption Policy		154	(6)
Understanding the Encryption Hierarchy		154	(1)
Using Symmetric and Asymmetric Keys		155	(1)
Using Certificates		156	(1)
Considering Performance Issues		157	(2)
Developing an Encryption Policy		159	(1)
Introducing SQL Server Service Accounts		160	(10)
Understanding the SQL Server Services		161	(2)
Choosing a Service Account		163	(3)
Changing Service Accounts		166	(4)
Setting Up Antivirus Software		170	(2)
Working with Services		172	(3)
Configuring Server Firewalls		175	(1)
Physically Securing Your Servers		176	(1)
Summary		176	(1)
Exam Essentials		177	(1)
Case Study: The Ever-Growing Wealth Company		177	(2)
Planned Changes		177	(1)
Existing Data Environment		177	(1)
Existing Infrastructure		178	(1)
Business Requirements		178	(1)
Technical Requirements		178	(1)
Case Exhibits		179	(1)
Review Questions		179	(3)
Answers to Review Questions		182	(1)
Designing SQL Server Service-Level Security		183	(38)
Creating Logins		184	(3)
Granting Server Roles		187	(3)
Mapping Database Users to Roles		190	(2)
Securing Schemas		192	(3)
Granting Database Roles		195	(8)
Working with Fixed Database Roles		196	(2)
Working with User-Defined Roles		198	(4)
Using Application Roles		202	(1)
Introducing DDL Triggers		203	(3)
Understanding DDL Trigger Scope		203	(1)
Specifying DDL Trigger Events		204	(1)
Defining a DDL Trigger Policy		205	(1)
Defining a Database-Level Encryption Policy		206	(1)
Securing Endpoints		207	(4)
Introducing TDS Endpoints		208	(1)
Using SOAP/Web Service Endpoints		209	(1)
Working with Service Broker and Database Mirroring Endpoints		210	(1)
Defining an Endpoint Policy		210	(1)
Granting SQL Server Agent Job Roles		211	(2)
Designing .NET Assembly Security		213	(1)
Setting SAFE		213	(1)
Setting EXTERNAL_ACCESS		213	(1)
Setting UNSAFE		213	(1)
Summary		214	(1)
Exam Essentials		214	(1)
Case Study: Herd of Two		215	(2)
Planned Changes		215	(1)
Existing Data Environment		215	(1)
Existing Infrastructure		215	(1)
Business Requirements		216	(1)
Technical Requirements		216	(1)
Review Questions		217	(2)
Answers to Review Questions		219	(2)
Designing SQL Server Object-Level Security		221	(30)
Developing a Permissions Strategy		222	(4)
Understanding Permissions		224	(1)
Applying Specific Permissions		225	(1)
Analyzing Existing Permissions		226	(2)
Specifying the Execution Context		228	(7)
Implementing EXECUTE AS for an Object		228	(2)
Implementing EXECUTE AS in Batches		230	(5)
Specifying Column-Level Encryption		235	(4)
Choosing Keys		235	(1)
Deploying Encryption		236	(3)
Using CLR Security		239	(5)
Creating Assemblies		240	(1)
Accessing External Resources		241	(1)
Enabling Trusted Assemblies		241	(1)
Using Application Domains		242	(1)
Using Module Signing		242	(1)
Developing a CLR Policy		242	(2)
Summary		244	(1)
Exam Essentials		244	(1)
Case Study: Jack's Steamed Shrimp		245	(2)
Planned Changes		245	(1)
Existing Data Environment		245	(1)
Existing Infrastructure		245	(1)
Business Requirements		246	(1)
Technical Requirements		246	(1)
Review Questions		247	(2)
Answers to Review Questions		249	(2)
Designing a Physical Database		251	(44)
Modifying a Database Design Based on Performance and Business Requirements		253	(6)
Planning a Database		253	(1)
Ensuring That a Database Is Normalized		254	(1)
Allowing Selected Denormalization for Performance Purposes		255	(1)
Ensuring That the Database Is Documented and Diagrammed		256	(3)
Designing Tables		259	(11)
Deciding Whether Partitioning Is Appropriate		260	(2)
Specifying Primary and Foreign Keys		262	(2)
Specifying Column Datatypes and Constraints		264	(3)
Using Constraints		267	(2)
Deciding Whether to Persist Computed Columns		269	(1)
Specifying Physical Location of Tables, Including Filegroups and a Partitioning Scheme		270	(1)
Designing Filegroups		270	(2)
Designing Filegroups for Performance		271	(1)
Designing Filegroups for Recoverability		271	(1)
Designing Filegroups for Partitioning		272	(1)
Designing Index Usage		272	(9)
Designing Indexes to Make Data Access Faster and to Improve Data Modification		274	(1)
Creating Indexes with the Database Tuning Advisor		274	(6)
Specifying Physical Placement of Indexes		280	(1)
Designing Views		281	(5)
Analyzing Business Requirements		282	(1)
Choosing the Type of View		282	(1)
Specifying Row and Column Filtering		283	(3)
Summary		286	(1)
Exam Essentials		287	(1)
Case Study: Trevallyn Travel		288	(2)
Planned Changes		288	(1)
Existing Data Environment		288	(1)
Existing Infrastructure		289	(1)
Business Requirements		289	(1)
Review Questions		290	(3)
Answers to Review Questions		293	(2)
Creating Database Conventions and Standards		295	(26)
Understanding the Benefits of Database Naming Conventions		296	(1)
Establishing and Disseminating Naming Conventions		297	(7)
Providing Naming Conventions for Database Objects		298	(2)
Avoiding Pitfalls and Dangers with Naming Conventions		300	(2)
Documenting and Communicating Database Naming Conventions		302	(2)
Defining Database Standards		304	(8)
Enforcing Transact-SQL Coding Standards		305	(3)
Enforcing Database Access Standards		308	(1)
Enforcing Deployment Process Standards		309	(2)
Enforcing Database Security Standards		311	(1)
Summary		312	(1)
Exam Essentials		312	(2)
Review Questions		314	(4)
Answers to Review Questions		318	(3)
Designing a SQL Server 2005 Solution for High Availability		321	(46)
Examining High-Availability Technologies		323	(4)
Identifying Single Points of Failure		323	(1)
Setting High-Availability System Goals		324	(2)
Recognizing High-Availability System Limitations		326	(1)
Understanding Failover Clustering		327	(6)
Understanding Clustering Requirements		328	(1)
Designing a Clustering Solution		328	(5)
Understanding Database Mirroring		333	(11)
Designing Server Roles for Database Mirroring		335	(1)
Understanding Protection Levels		335	(2)
Designing a Database Mirroring Solution		337	(1)
Configuring a Database Mirroring Solution		338	(5)
Testing Database Mirroring		343	(1)
Understanding Log Shipping		344	(4)
Choosing Log-Shipping Roles		345	(1)
Switching Log-Shipping Roles		346	(1)
Reconnecting Client Applications		347	(1)
Understanding Replication		348	(2)
Implementing High Availability with Transactional Replication		348	(2)
Implementing High Availability with Merge Replication		350	(1)
Designing Highly Available Storage		350	(5)
Designing RAID Arrays		351	(1)
Designing a RAID Array		352	(2)
Designing a SAN Storage Array		354	(1)
Designing a High-Availability Solution		355	(3)
Planning for Nontechnical Issues		356	(1)
Considering Reporting Issues		357	(1)
Developing a Migration Strategy		358	(2)
Testing Your Migration		358	(1)
Minimizing Downtime		358	(1)
Implementing Address Abstraction		359	(1)
Training Your Staff		359	(1)
Summary		360	(1)
Exam Essentials		360	(1)
Case Study: Ed's Heavy Equipment		361	(2)
Planned Changes		361	(1)
Existing Data Environment		361	(1)
Existing Infrastructure		361	(1)
Business Requirements		361	(1)
Technical Requirements		362	(1)
Review Questions		363	(2)
Answers to Review Questions		365	(2)
Designing a Data Recovery Solution for a Database		367	(40)
Backing Up Data		369	(10)
Creating a Backup Device		369	(2)
Performing Full Backups		371	(4)
Performing Differential Database Backups		375	(1)
Performing Transaction Log Backups		376	(2)
Performing Filegroup Backups		378	(1)
Restoring Databases		379	(5)
Understanding the General Restore Steps		379	(1)
Performing Standard Restores		380	(3)
Performing Point-in-Time Restores		383	(1)
Performing Piecemeal Restores		384	(1)
Devising a Backup Strategy		384	(3)
Performing Full Backups Only		384	(1)
Performing Full with Differential Backups		385	(1)
Performing Full with Transaction Log Backups		385	(1)
Performing Full, Differential, and Transaction Log Backups		386	(1)
Performing Filegroup Backups		386	(1)
Performing Partial and Partial Differential Backups		386	(1)
Designing a Backup and Restore Strategy: The Process		387	(7)
Analyzing Business Requirements		388	(1)
Categorizing Databases Based on Recovery Criteria		388	(1)
Choosing a Recovery Model		389	(2)
Specifying What Backups Are Needed to Support Each Category		391	(1)
Specifying the Backup Frequency		392	(1)
Setting the Backup Security Policy		392	(1)
Documenting the Backup Strategy		393	(1)
Creating a Backup Validation and Testing Policy		393	(1)
Developing a Database Disaster Recovery Plan		394	(6)
Categorizing the Information		394	(2)
Creating a Disaster Recovery Decision Tree		396	(4)
Summary		400	(1)
Exam Essentials		401	(1)
Case Study: Waves Styles on George		402	(1)
Planned Changes		402	(1)
Existing Data Environment		402	(1)
Existing Infrastructure		402	(1)
Business Requirements		402	(1)
Technical Requirements		402	(1)
Review Questions		403	(3)
Answers to Review Questions		406	(1)
Designing a Data-Archiving Solution		407	(28)
Why Archive Data?		408	(1)
Determining Business and Regulatory Requirements		409	(3)
Determining What Data Will Be Archived		412	(3)
Structuring Archived Data		413	(1)
Choosing Which Structure to Use		414	(1)
Selecting a Storage Format		415	(1)
Developing a Data-Movement Strategy		416	(2)
Designing a Replication Topology		418	(10)
Understanding and Administering Replication Topologies		419	(1)
Understanding the Publisher/Subscriber Metaphor		419	(1)
Understanding Replication Types		420	(3)
Managing a Replication Topology		423	(2)
Designing a Replication Strategy		425	(3)
Accessing Archived Data		428	(1)
Summary		428	(1)
Exam Essentials		429	(1)
Case Study: Developing an Archive Plan		430	(1)
Review Questions		431	(2)
Answers to Review Questions		433	(2)
Glossary		435	(18)
Index		453

Steve Jones has been working with SQL Server for more than a decade, starting with v4.2 on OS/2 and enjoying the new features and capabilities of every version since. After working as a DBA and developer for a variety of companies, Steve founded SQLServerCentral.com along with Brian Knight and Andy Warren in 2001. SQLServerCentral.com has grown into a wonderful SQL Server community that provides daily articles and questions on all aspects of SQL Server to over 300,000 members. Starting in 2004, Steve became the full-time editor of the community and ensures it continues to evolve into the best resource possible for SQL Server professionals. Over the last decade, Steve has written more than 200 articles about SQL Server for SQLServerCentral.com, the SQL Server Standard magazine, SQL Server Magazine, and Database Journal. teve has spoken at the PASS Summits where SQLServerCentral.com sponsors an opening reception every year as well as written a prior book for Sybex on SQL Server 2000. David W. Tschanz is the coauthor of the recent Sybex book Mastering SQL Server 2005. He has been working with and managing large datasets for four decades. His work has included analysis of population dynamics, voting behavior, and epidemiological data. He has been writing on computer topics for the past several years, including 4 books and about 100 articles in the area. e is also a regular contributor to Redmond magazine. Dave currently lives outside the United States, where his eclectic nature allows him to pursue projects involving databases, IT infrastructure, web development, archaeology, the ancient Nabataean capital of Petra, medical history, military science, and demography. He can be reached by e-mail at desertwriter1121@yahoo.com, or look for him in Connecticut, Saudi Arabia, or Tasmania, his three favorite haunts.