MCITP Self-Paced Training Kit (Exam 70-443) Designing a Database Server Infrastructure Using Microsoft SQL Server 2005

by Mackin, J.C.

• ISBN13: 9780735621732
• ISBN10: 073562173X
• Edition: 1st
• Format: Paperback
• Copyright: 2006-11-29
• Publisher: Microsoft Press

Summary

Get in-depth preparation for Exam 70-443, an exam for the new Microsoft Certified IT Professional: Database Administrator certificationand build real-world job skills. Includes test questions, reviews, troubleshooting labs, exam discount, and more.

Author Biography

J.C. Mackin, MCITP, MCSE, MCT, has 10+ years' experience with Microsoft networks and is an IT consultant, trainer, and author.

Table of Contents

Introduction	xxix
Hardware Requirements	xxix
Software Requirements	xxx
Using the CD and DVD	xxx
How to Install the Practice Tests	xxxi
How to Use the Practice Tests	xxxi
How to Uninstall the Practice Tests	xxxxiii
Microsoft Certified Professional Program	xxxiii
Technical Support	xxxiv
Evaluation Edition Software Support	xxxv
Planning a Database Server Infrastructure	1	(66)
Before You Begin	2	(1)
Lesson 1: Sizing a Database Server	3	(19)
What Is Capacity Planning?	3	(1)
Conducting Capacity Planning Interviews	3	(1)
Application and Query Tuning	4	(1)
Collecting Performance Data and Baselines	5	(3)
Projecting Trends	8	(3)
Server Sizing Methods	11	(7)
Practice: Analyzing Database Server Performance	18	(1)
Lesson Summary	19	(1)
Lesson Review	20	(2)
Lesson 2: Sizing the Processor Subsystem	22	(9)
Choosing a CPU Architecture	22	(5)
Features for Processor Consideration	27	(1)
Determining the Number of Processors	27	(1)
Practice: Determining the CPU Time of a Query	28	(1)
Lesson Summary	28	(1)
Lesson Review	29	(2)
Lesson 3: Sizing the Disk Subsystem	31	(14)
Disk Configuration	31	(3)
RAID Choice and I/Os	34	(1)
Database Disk Sizing Example: Meeting Throughput Requirements	35	(1)
Database Disk Sizing Example: Meeting Storage Requirements	36	(2)
Disk Sizing for the Operating System Partition	38	(1)
Disk Sizing for the Log File Partition	38	(2)
Additional I/O Sizing Considerations	40	(1)
Practice: Database Properties	41	(2)
Lesson Summary	43	(1)
Lesson Review	43	(2)
Lesson 4: Estimating Memory Requirements	45	(6)
Sizing Memory	45	(4)
Practice: Enabling AWE Memory for SQL Server 2005	49	(1)
Lesson Summary	50	(1)
Lesson Review	50	(1)
Lesson 5: Choosing a Version of Windows and SQL Server	51	(6)
Choosing a Version of SQL Server 2005	51	(2)
Choosing an Operating System for SQL Server 2005	53	(2)
Lesson Summary	55	(1)
Lesson Review	55	(2)
Chapter Review	57	(1)
Chapter Summary	57	(1)
Key Terms	57	(1)
Case Scenarios and the 70-443 Exam	58	(3)
Case Scenario Structure	59	(2)
Case Scenario: Spotting a Bottleneck	61	(3)
Background	61	(1)
Existing Data Environment	62	(1)
Business Requirements	63	(1)
Technical Requirements	63	(1)
Questions	64	(1)
Suggested Practices	64	(1)
Watch a Webcast	65	(1)
Design a Database Server	65	(1)
Take a Practice Test	65	(2)
Deploying and Consolidating Multiple Instances	67	(26)
Before You Begin	68	(1)
Lesson 1: Deploying Multiple Instances	69	(7)
Designing SQL Server Instances	69	(5)
Lesson Summary	74	(1)
Lesson Review	74	(2)
Lesson 2: Planning Database Consolidation	76	(11)
Consolidating Database Servers	76	(5)
Designing a Server Consolidation Plan	81	(3)
Practice: Planning a Server Consolidation	84	(1)
Lesson Summary	85	(1)
Lesson Review	85	(2)
Chapter Review	87	(1)
Chapter Summary	87	(1)
Key Terms	87	(1)
Case Scenario: Consolidating Dispersed Data	88	(4)
Background	88	(1)
Existing Data Environment	89	(1)
Proposed Environment	90	(1)
Business Requirements	90	(1)
Technical Requirements	90	(1)
Questions	91	(1)
Suggested Practices	92	(1)
Install Multiple Instances	92	(1)
Consolidate Two Instances	92	(1)
Take a Practice Test	92	(1)
Designing SQL Server Security in the Enterprise	93	(32)
Before You Begin	94	(1)
Lesson 1: Assessing Database Security Needs for Business	95	(8)
What Is a Security Policy?	95	(1)
Customizing a Security Policy	96	(1)
Protecting SQL Server from Network Attacks	97	(4)
Lesson Summary	101	(1)
Lesson Review	101	(2)
Lesson 2: Overview of SQL Server Security	103	(16)
Security Principals	103	(4)
SQL Server 2005 Authentication Modes	107	(2)
Integrating SQL Server into a Windows Domain Infrastructure	109	(3)
Active Directory Authentication and SQL Server	112	(1)
Authentication Guidelines for High-Availability Solutions	113	(2)
Practice: Creating a Windows Group for SQL Managers	115	(2)
Lesson Summary	117	(1)
Lesson Review	117	(2)
Chapter Review	119	(1)
Chapter Summary	119	(1)
Key Terms	120	(1)
Case Scenario: Designing an Authentication Method and Assigning Privileges	120	(4)
Questions	123	(1)
Suggested Practices	124	(1)
Watch SQL Server Security Webcasts	124	(1)
Create and Use Various Database Administrator Accounts	124	(1)
Take a Practice Test	124	(1)
Designing Database Server Security Policies	125	(44)
Before You Begin	125	(1)
Lesson 1: Enforcing SQL Server Security Through Group Policy	126	(12)
What Is Group Policy?	126	(4)
Password Policy	130	(3)
Practice: Enforcing Password Policy	133	(2)
Lesson Summary	135	(1)
Lesson Review	135	(3)
Lesson 2: Encrypting SQL Server Traffic	138	(8)
Server-Level Encryption	138	(6)
Practice: Encrypting Traffic to and from SQL Server	144	(1)
Lesson Summary	145	(1)
Lesson Review	145	(1)
Lesson 3: Reducing the Attack Surface of SQL Server 2005	146	(16)
Disabling Unneeded Services	146	(2)
Service Accounts	148	(4)
Security Best Practices	152	(5)
Practice: Reducing the Attack Surface of a Server	157	(2)
Lesson Summary	159	(1)
Lesson Review	159	(3)
Chapter Review	162	(1)
Chapter Summary	162	(1)
Key Terms	162	(1)
Case Scenario: Enforcing Server Security Policy	163	(4)
Background	163	(1)
Existing Data Environment	164	(1)
Existing Infrastructure	164	(1)
Business Requirements	165	(1)
Technical Requirements	165	(1)
Questions	166	(1)
Suggested Practices	167	(1)
Watch SQL Server Security Webcasts	167	(1)
Deploy Group Policy Through Active Directory	167	(1)
Deploy Windows Server Update Services	168	(1)
Take a Practice Test	168	(1)
Designing SQL Server Endpoints	169	(28)
Before You Begin	169	(1)
Lesson 1: Overview of Endpoints	170	(4)
Endpoint Types and Payloads	170	(1)
Endpoint Access	171	(1)
Practice: Inspecting Existing Endpoints	172	(1)
Lesson Summary	173	(1)
Lesson Review	173	(1)
Lesson 2: TCP Endpoints	174	(5)
TCP Protocol Arguments	174	(1)
Database Mirroring and Service Broker Common Arguments	175	(1)
Database Mirroring Specific Arguments	175	(1)
Service Broker--Specific Arguments	176	(1)
Lesson Summary	177	(1)
Lesson Review	178	(1)
Lesson 3: HTTP Endpoints	179	(10)
HTTP Endpoint Security	180	(5)
Creating an Endpoint	185	(1)
Lesson Summary	185	(1)
Lesson Review	186	(3)
Chapter Review	189	(1)
Chapter Summary	189	(1)
Key Terms	189	(1)
Case Scenario: Designing SQL Server Security	190	(4)
Background	190	(1)
Existing Data Environment	191	(1)
Existing Infrastructure	191	(1)
Business Requirements	192	(1)
Technical Requirements	193	(1)
Questions	193	(1)
Suggested Practices	194	(1)
Take a Practice Test	195	(2)
Designing Database Security	197	(40)
Before You Begin	198	(1)
Lesson 1: Designing Instance-level Security	199	(12)
Configuring Service Accounts	199	(2)
Instance-level Principals	201	(1)
Authentication Mode	202	(1)
Configuring the Attack Surface	203	(4)
Server Roles	207	(1)
SQL Server Agent Proxy Accounts	207	(1)
Designing Security for .NET Assemblies	207	(2)
Practice: Managing Accounts	209	(1)
Lesson Summary	210	(1)
Lesson Review	210	(1)
Lesson 2: Designing Database Security	211	(11)
Database Users and Roles	211	(1)
Designing Schemas	212	(2)
Defining Encryption	214	(2)
Designing DDL Triggers	216	(2)
Practice: Encryption	218	(2)
Lesson Summary	220	(1)
Lesson Review	220	(2)
Lesson 3: Securing Database Objects	222	(7)
Permission Scope	222	(1)
Execution Context	223	(1)
Encrypting Columns	224	(1)
Practice: Granting Permissions	225	(2)
Lesson Summary	227	(1)
Lesson Review	227	(2)
Chapter Review	229	(1)
Chapter Summary	229	(1)
Key Terms	229	(1)
Case Scenario: Designing SQL Server Security	230	(4)
Background	230	(1)
Existing Data Environment	231	(1)
Existing Infrastructure	232	(1)
Business Requirements	232	(1)
Technical Requirements	233	(1)
Questions	233	(1)
Suggested Practices	234	(1)
Instance Security	234	(1)
Database Security	235	(1)
Data Security	235	(1)
Take a Practice Test	235	(2)
Planning for High Availability in the Enterprise	237	(40)
Before You Begin	238	(1)
Lesson 1: Assessing Database Availability Requirements	239	(17)
Calculating Availability	239	(3)
High Availability Framework	242	(1)
Availability Requirements	242	(2)
Barriers to Availability	244	(7)
Cost of Downtime	251	(1)
Budget Restrictions	252	(3)
Lesson Summary	255	(1)
Lesson 2: Designing Database Storage	256	(10)
RAID Configurations	256	(5)
tempdb Placement	261	(1)
Filegroups	262	(1)
Practice: Adding Filegroups	262	(2)
Lesson Summary	264	(1)
Lesson Review	264	(2)
Lesson 3: Planning a Database Migration Strategy	266	(5)
Standby Categories	266	(1)
Process Changes	267	(1)
Technology Implementation	268	(1)
Lesson Summary	269	(1)
Lesson Review	269	(2)
Chapter Review	271	(1)
Key Terms	271	(1)
Case Scenario: Planning for High Availability	272	(3)
Background	272	(1)
Existing Data Environment	273	(1)
Proposed Environment	274	(1)
Business Requirements	274	(1)
Technical Requirements	274	(1)
Questions	274	(1)
Suggested Practices	275	(1)
Watch a Webcast	275	(1)
Take a Practice Test	276	(1)
Failover Clustering	277	(36)
Before You Begin	277	(2)
Lesson 1: Designing Windows Clustering	279	(18)
Windows Cluster Components	279	(2)
Types of Clusters	281	(2)
Security Configuration	283	(1)
Disk Configuration	283	(2)
Network Configuration	285	(1)
Cluster Resources	286	(1)
Cluster Groups	287	(1)
Practice: Creating a Windows Cluster	288	(7)
Lesson Summary	295	(1)
Lesson Review	296	(1)
Lesson 2: Designing SQL Server 2005 Failover Cluster Instances	297	(9)
Terminology	298	(1)
Failover Cluster Instance Components	298	(3)
Health Checks	301	(1)
Cluster Failover	301	(2)
Practice: Installing a SQL Server Failover Clustered Instance	303	(1)
Lesson Summary	304	(1)
Lesson Review	304	(2)
Chapter Review	306	(1)
Chapter Summary	306	(1)
Key Terms	306	(1)
Case Scenario: Failover Clustering	307	(3)
Background	307	(1)
Existing Data Environment	307	(2)
Proposed Environment	309	(1)
Business Requirements	309	(1)
Technical Requirements	309	(1)
Questions	310	(1)
Suggested Practices	310	(2)
Windows Clustering	310	(1)
SQL Server Failover Clustering	311	(1)
Watch a Webcast	311	(1)
Take a Practice Test	312	(1)
Database Mirroring	313	(36)
Before You Begin	314	(1)
Lesson 1: Overview of Database Mirroring	315	(13)
Database Mirroring Roles	315	(1)
Principal Role	316	(1)
Mirror Role	316	(1)
Witness Server	316	(1)
Database Mirroring Endpoints	317	(1)
Operating Modes	318	(4)
Caching	322	(1)
Transparent Client Redirect	323	(1)
Database Snapshots	323	(1)
Practice: Establishing Endpoints for Database Mirroring	324	(2)
Lesson Summary	326	(1)
Lesson Review	327	(1)
Lesson 2: Initializing Database Mirroring	328	(7)
Recovery Model	329	(1)
Backup and Restore	330	(1)
Copy System Objects	331	(1)
Practice: Configuring Database Mirroring	331	(2)
Lesson Summary	333	(1)
Lesson Review	334	(1)
Lesson 3: Designing Failover and Failback Strategies	335	(6)
Designing Mirroring Session Failover	335	(1)
Designing Mirroring Session Failback	336	(3)
Practice: Failover a Database Mirroring Session	339	(1)
Lesson Summary	339	(1)
Lesson Review	339	(2)
Chapter Review	341	(1)
Chapter Summary	341	(1)
Key Terms	341	(1)
Case Scenario: Database Mirroring	342	(4)
Background	342	(1)
Existing Data Environment	343	(1)
Proposed Environment	344	(1)
Business Requirements	344	(1)
Technical Requirements	345	(1)
Questions	345	(1)
Suggested Practices	346	(2)
Establishing Database Mirroring	346	(1)
Creating a Database Snapshot Against a Database Mirror	347	(1)
Watch a Webcast	347	(1)
Take a Practice Test	348	(1)
Log Shipping	349	(34)
Before You Begin	349	(1)
Lesson 1: Overview of Log Shipping	350	(5)
Log Shipping Scenarios	350	(1)
Log Shipping Components	351	(2)
Types of Log Shipping	353	(1)
Lesson Summary	354	(1)
Lesson Review	354	(1)
Lesson 2: Initializing Log Shipping	355	(12)
Log Shipping Initialization	355	(6)
Practice: Setting Up Log Shipping	361	(4)
Lesson Summary	365	(1)
Lesson Review	365	(2)
Lesson 3: Designing Failover and Failback Strategies	367	(7)
Log Shipping Failover	368	(2)
Log Shipping Failback	370	(2)
Practice: Log Shipping Failover	372	(1)
Lesson Summary	372	(1)
Lesson Review	373	(1)
Chapter Review	374	(1)
Chapter Summary	374	(1)
Key Terms	374	(1)
Case Scenario: Log Shipping	375	(6)
Background	375	(1)
Existing Data Environment	376	(1)
Proposed Environment	377	(1)
Business Requirements	378	(1)
Technical Requirements	378	(1)
Questions	379	(2)
Suggested Practices	381	(1)
Initiating Log Shipping	381	(1)
Failover and Failback Log Shipping	381	(1)
Take a Practice Test	382	(1)
Replication	383	(56)
Before You Begin	384	(1)
Lesson 1: Overview of Replication	385	(14)
Replication Components	385	(2)
Replication Roles	387	(1)
Replication Topologies	387	(2)
Replication Agents	389	(1)
Agent Profiles	390	(1)
Replication Methods	390	(3)
Data Conflicts	393	(3)
Practice: Configuring Publishing	396	(1)
Lesson Summary	397	(1)
Lesson Review	397	(2)
Lesson 2: Transactional Replication	399	(14)
Change Tracking	399	(2)
Transactional Options	401	(3)
Transactional Architectures	404	(2)
Monitoring	406	(1)
Validation	407	(1)
Practice: Implementing Transactional Replication	408	(3)
Lesson Summary	411	(1)
Lesson Review	412	(1)
Lesson 3: Merge Replication	413	(8)
Change Tracking	413	(3)
Validation	416	(1)
Practice: Implementing Merge Replication	417	(2)
Lesson Summary	419	(1)
Lesson Review	419	(2)
Lesson 4: Designing Failover and Failback Strategies	421	(6)
Failover	421	(3)
Failback	424	(1)
Lesson Summary	425	(1)
Lesson Review	425	(2)
Chapter Review	427	(1)
Chapter Summary	427	(1)
Key Terms	427	(1)
Case Scenario: Replication	428	(6)
Background	429	(1)
Existing Data Environment	429	(2)
Proposed Environment	431	(1)
Business Requirements	431	(1)
Technical Requirements	432	(1)
Questions	432	(2)
Suggested Practices	434	(4)
Transactional Replication	434	(1)
Merge Replication	435	(1)
Failover and Failback Replication	435	(3)
Take a Practice Test	438	(1)
Designing the Physical Database	439	(90)
Before You Begin	440	(1)
Lesson 1: Designing Database Tables	441	(39)
Normalization	444	(2)
Designing Data Types and Column Properties	446	(12)
Creating a Table	458	(5)
Constraints	463	(6)
Transact-SQL User-Defined Types	469	(1)
Common Language Runtime (CLR) UDTs	470	(1)
Creating a Partitioned Table, Index, or Indexed View	471	(2)
Practice: Creating a Table and Implementing Constraints	473	(4)
Lesson Summary	477	(1)
Lesson Review	478	(2)
Lesson 2: Designing Filegroups	480	(7)
Designing Filegroups	480	(5)
Lesson Summary	485	(1)
Lesson Review	485	(2)
Lesson 3: Planning Indexes	487	(12)
Index Structure	487	(4)
Clustered Indexes	491	(2)
Creating a Nonclustered Index	493	(3)
Practice: Creating Indexes	496	(1)
Lesson Summary	497	(1)
Lesson Review	498	(1)
Lesson 4: Designing Database Views	499	(9)
Creating a View	499	(2)
Ownership Chains	501	(1)
Updateable Views	502	(1)
Creating an Indexed View	503	(1)
Query Substitution	504	(1)
Practice: Creating a View	505	(1)
Lesson Summary	506	(1)
Lesson Review	507	(1)
Lesson 5: Designing Database Procedures	508	(8)
Source Code Control	509	(1)
Promoting Code	510	(2)
Change Control Procedures	512	(1)
Deployment Rollback	513	(2)
Lesson Summary	515	(1)
Lesson Review	515	(1)
Lesson 6: Creating Database Conventions	516	(5)
Synonyms	516	(1)
Naming Conventions	516	(1)
Coding Standards	517	(2)
Practice: Creating a Synonym	519	(1)
Lesson Summary	519	(1)
Lesson Review	520	(1)
Chapter Review	521	(1)
Chapter Summary	521	(1)
Key Terms	521	(1)
Case Scenario: Designing a SQL Server Environment	522	(5)
Background	522	(1)
Existing Data Environment	523	(1)
Existing Infrastructure	523	(1)
Business Requirements	524	(1)
Technical Requirements	525	(1)
Questions	525	(2)
Suggested Practices	527	(1)
Designing Tables	527	(1)
Designing Indexes	527	(1)
Designing Filegroups	527	(1)
Take a Practice Test	528	(1)
Designing a Data Recovery Strategy	529	(46)
Before You Begin	530	(1)
Lesson 1: Assessing Data Recovery Needs	531	(6)
Designing a Disaster Recovery Strategy	531	(5)
Lesson Summary	536	(1)
Lesson Review	536	(1)
Lesson 2: Understanding Backup Types	537	(9)
Database Backup Types	537	(5)
Practice: Verifying a Database Backup	542	(1)
Lesson Summary	543	(1)
Lesson Review	544	(2)
Lesson 3: Recovering a Database	546	(14)
Overview of Recovery Models	546	(5)
Planning for Disaster Recovery	551	(1)
Practice: Backing Up and Restoring a Database	552	(5)
Lesson Summary	557	(1)
Lesson Review	558	(2)
Lesson 4: Overview of Backup Media	560	(6)
Backup Storage Media	560	(2)
Media Sets	562	(2)
Lesson Summary	564	(1)
Lesson Review	564	(2)
Chapter Review	566	(1)
Chapter Summary	566	(1)
Key Terms	567	(1)
Case Scenario: Designing a Data Recovery Strategy	567	(5)
Background	567	(1)
Existing Data Environment	568	(1)
Existing Infrastructure	569	(1)
Business Requirements	570	(1)
Technical Requirements	570	(1)
Questions	571	(1)
Suggested Practices	572	(1)
Perform Backup and Restore Operations	572	(1)
Watch a Webcast	573	(1)
Take a Practice Test	573	(2)
Archiving Database Data	575	(34)
Before You Begin	576	(1)
Lesson 1: Planning a Data Archive	577	(9)
What Is Data Archiving?	577	(1)
Why Archive Data?	578	(1)
Designing a Data Archive Policy	579	(4)
Lesson Summary	583	(1)
Lesson Review	583	(3)
Lesson 2: Archiving by Using Partitions	586	(16)
Partitioning Overview	586	(10)
Practice: Sliding Window	596	(4)
Lesson Summary	600	(1)
Lesson Review	600	(2)
Chapter Review	602	(1)
Chapter Summary	602	(1)
Key Terms	602	(1)
Case Scenario	603	(5)
Background	603	(1)
Existing Data Environment	604	(1)
Existing Infrastructure	604	(1)
Business Requirements	605	(1)
Technical Requirements	606	(1)
Questions	607	(1)
Suggested Practices	608	(1)
Partitioning	608	(1)
Take a Practice Test	608	(1)
Answers	609	(62)
Glossary	671	(10)
Index	681