MCTS: Microsoft SQL Server 2005 Implementation and Maintenance Study Guide Exam 70-431

by Jorden, Joseph L.; Weyn, Dandy

• ISBN13: 9780470025659
• ISBN10: 0470025654
• Edition: 1st
• Format: Paperback
• Copyright: 2006-07-19
• Publisher: Sybex

Summary

With the release of SQL Server 2005, Microsoft is introducing a new multi-exam certification program. The Microsoft SQL Server 2005 Implementation and Maintenance exam (70-431) is the first stop for everyone entering this new certification track, and serves as both a single exam certification as well as the entry exam for the MCITP-level certifications. This book provides an introduction to the development and administrative aspects of SQL Server 2005 and features practical guidance for all aspects of the exam. Get a great start in your certification process today with this comprehensive guide which includes: Hundreds of challenging practice questions plus two bonus exams Leading-edge exam preparation software, including a test engine Authoritative coverage of all key exam objectives, including: Installing and Configuring SQL Server 2005 Implementing High Availability and Disaster Recovery Supporting Data Consumers Maintaining Databases Monitoring and Troubleshooting SQL Server Performance Creating and Implementing Database Objects Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Author Biography

Joseph L. Jorden, MCTS, MCSE, has been administering and developing databases for the last twelve years. Currently he is the Lead Developer for the Jelly Belly Candy Company.

Dandy Weyn, MCTS, MCSE, MCSA, MCDBA, MCDST, MCITP, is an independent SQL Server technologist based in Belgium, who delivers training and consultancy all over the world. He is a frequent speaker at Microsoft seminars and has more than ten years experience with relational databases in enterprise environments.

Table of Contents

Introduction	xxi
Assessment Test	xxviii
Installing Microsoft SQL Server 2005	1	(30)
Meeting the Prerequisites	2	(3)
Preparing to Install	5	(3)
Choosing Default Instances or Named Instances	5	(1)
Choosing Service Accounts	6	(1)
Selecting an Authentication Mode	6	(1)
Choosing a Collation Setting	7	(1)
Upgrading from a Previous Version	8	(2)
Installing SQL Server 2005	10	(9)
Installing a Second Instance	19	(3)
Troubleshooting the Installation	22	(1)
Summary	23	(1)
Exam Essentials	23	(1)
Review Questions	24	(4)
Answers to Review Questions	28	(3)
Creating and Configuring Databases	31	(30)
Planning Your Database	32	(2)
Introducing Database Files	32	(1)
Introducing Filegroups	33	(1)
Deciding on Database File Placement	34	(2)
Introducing RAID-0	35	(1)
Introducing RAID-1	35	(1)
Introducing RAID-5	35	(1)
Introducing RAID-10	35	(1)
Creating Data Storage Structures	36	(2)
Introducing Extents	36	(1)
Introducing Pages	37	(1)
Estimating Storage Requirements	38	(4)
Estimating Table Storage Requirements	38	(3)
Estimating Index Storage Requirements	41	(1)
Creating and Configuring Databases	42	(8)
Creating a Database	42	(2)
Gathering Information about Your Database	44	(2)
Setting Database Options	46	(4)
Summary	50	(1)
Exam Essentials	51	(1)
Review Questions	52	(7)
Answers to Review Questions	59	(2)
Working with Tables and Views	61	(46)
Planning Tables	62	(5)
Introducing Built-in Datatypes	63	(3)
Introducing Computed Columns	66	(1)
Creating Tables	67	(5)
Restricting the Data	72	(10)
Introducing Constraints	73	(7)
Partitioning Tables	80	(2)
Understanding Views	82	(2)
Modifying Data through a View	84	(4)
Working with Indexed Views	88	(5)
Summary	93	(1)
Exam Essentials	94	(2)
Review Questions	96	(9)
Answers to Review Questions	105	(2)
Performing Indexing and Full-Text Searching	107	(34)
Understanding Index Architecture	108	(14)
Understanding Heaps	109	(2)
Understanding Clustered Indexes	111	(5)
Understanding Nonclustered Indexes	116	(6)
Creating Indexes	122	(1)
Using Primary Keys	123	(3)
Using Full-Text Searching	126	(7)
Summary	133	(1)
Exam Essentials	133	(1)
Review Questions	134	(5)
Answers to Review Questions	139	(2)
Introducing More Database Objects	141	(48)
Introducing Stored Procedures	142	(8)
Understanding the Types of Stored Procedures	142	(3)
Creating Stored Procedures	145	(4)
Recompiling Stored Procedures	149	(1)
Introducing Triggers	150	(16)
Understanding the Types of Triggers	150	(1)
Understanding DML Triggers	151	(3)
Understanding DDL Triggers	154	(4)
Understanding Trigger Recursion and Nesting	158	(1)
Understanding Disabling Triggers	159	(1)
Understanding Event Notifications	159	(7)
Introducing Functions	166	(6)
Understanding the Types of Functions	166	(1)
Using Scalar Functions	166	(1)
Introducing Table-Valued Functions	167	(1)
Introducing Built-in Functions	167	(2)
Introducing CLR Functions	169	(2)
Introducing Deterministic and Nondeterministic Functions	171	(1)
Introducing User-Defined Types	172	(3)
Creating T-SQL User-Defined Types	172	(1)
Creating CLR User-Defined Types	173	(1)
Getting More CLR Functionality	174	(1)
Summary	175	(1)
Exam Essentials	175	(1)
Review Questions	176	(9)
Answers to Review Questions	185	(4)
Implementing Security in SQL Server 2005	189	(52)
Understanding Security Modes	190	(3)
Using Windows Authentication Mode	190	(1)
Using Mixed Mode	191	(1)
Setting the Authentication Mode	191	(2)
Understanding SQL Server Logins	193	(6)
Using Standard Logins	193	(3)
Using Windows Logins	196	(3)
Understanding the Items Common to All Logins	199	(1)
Understanding Fixed Server Roles	199	(3)
Creating Database User Accounts	202	(2)
Understanding Permissions	204	(6)
Applying Statement Permissions	205	(2)
Applying Object Permissions	207	(3)
Understanding Database Roles	210	(7)
Using Fixed Database Roles	210	(2)
Using Custom Database Roles	212	(3)
Using Application Roles	215	(2)
Understanding Permission States	217	(4)
Granting a Permission	217	(1)
Revoking a Permission	217	(1)
Denying a Permission	217	(4)
Introducing Ownership Chains	221	(2)
Introducing Linked Server Security	223	(1)
Introducing Encryption	224	(1)
Creating a Security Plan	225	(2)
Summary	227	(2)
Exam Essentials	229	(1)
Review Questions	230	(8)
Answers to Review Questions	238	(3)
Working with Relational Data	241	(40)
Understanding and Using Transactions	242	(5)
Executing Implicit and Explicit Transactions	243	(1)
Committing and Rolling Back	244	(2)
Executing Distributed Transactions	246	(1)
Populating Tables	247	(5)
Importing Data Using Bulk Insert	247	(1)
Importing Data Using the bcp Utility	248	(2)
Copying Data Using SSIS	250	(2)
Bulk Inserting XML Data	252	(1)
Supporting the Bulk-Logged Recovery Model	253	(2)
Supporting Different Collation Types and Orders When Querying Data	255	(11)
Formatting and Converting Datatypes	256	(5)
Casting and Converting	261	(3)
Understanding Datatype Precedence	264	(1)
Understanding Collations	265	(1)
Introducing Error Handling	266	(6)
Using RAISERROR	266	(1)
Using @@ERROR	267	(1)
Using Error Messages	267	(1)
Using TRY...CATCH Blocks	268	(4)
Summary	272	(1)
Exam Essentials	272	(2)
Review Questions	274	(4)
Answers to Review Questions	278	(3)
Working with XML Data	281	(30)
Understanding XML Data	282	(1)
Using the xml Datatype	283	(2)
Using Untyped XML	284	(1)
Using Typed XML	285	(1)
Working with XML Schema	285	(3)
Querying XML Data	288	(7)
Using the query Method	288	(1)
Using the value Method	289	(1)
Using the exist Method	290	(1)
Using the modify Method	290	(1)
Using the nodes Method	291	(4)
Creating XML Indexes	295	(2)
Summary	297	(1)
Exam Essentials	297	(1)
Review Questions	298	(10)
Answers to Review Questions	308	(3)
Working with Service Broker and HTTP	311	(36)
Understanding the SQL Server Service Broker Architecture	312	(1)
Working with Service Broker	313	(5)
Creating a Message Type	313	(1)
Creating a Queue	314	(1)
Creating a Contract	315	(1)
Creating a Service	316	(1)
Creating a Route	317	(1)
Using Service Broker	318	(10)
Sending Messages	318	(3)
Receiving Messages	321	(2)
Automating the Queue Processing	323	(5)
Introducing HTTP Endpoints	328	(6)
Configuring HTTP Endpoints	329	(5)
Securing HTTP Endpoints	334	(1)
Summary	334	(2)
Exam Essentials	336	(1)
Review Questions	337	(8)
Answers to Review Questions	345	(2)
Maintaining and Automating SQL Server	347	(68)
Maintaining Indexes	348	(6)
Understanding sys.DM_DB_INDEX_PHYSICAL_STATS	349	(3)
Reorganizing and Rebuilding Indexes	352	(2)
Maintaining Statistics	354	(1)
Maintaining Databases	355	(4)
Understanding DBCC CHECKDB	355	(2)
Shrinking Files	357	(2)
Understanding Automation Basics	359	(2)
Configuring Database Mail	361	(5)
Creating Operators	366	(3)
Creating Jobs	369	(7)
Creating Alerts	376	(13)
Creating Event Alerts Based on Standard Errors	377	(5)
Creating Event Alerts Based on Custom Errors	382	(3)
Creating Performance Alerts	385	(2)
Creating WMI Alerts	387	(2)
Using the Maintenance Plan Wizard	389	(10)
Copying Databases	399	(6)
Summary	405	(1)
Exam Essentials	406	(1)
Review Questions	407	(6)
Answers to Review Questions	413	(2)
Performing Backups and Restores	415	(48)
Backing Up Your Data	416	(22)
Understanding How Backups Work	417	(1)
Creating a Backup Device	418	(1)
Performing Full Backups	419	(4)
Performing Differential Backups	423	(3)
Performing Transaction Log Backups	426	(2)
Performing Filegroup Backups	428	(6)
Backing Up to Multiple Devices	434	(4)
Restoring Databases	438	(10)
Performing Standard Restores	439	(4)
Performing Point-in-Time Restores	443	(3)
Performing Piecemeal Restores	446	(2)
Devising a Backup Strategy	448	(4)
Planning for Full Backups Only	448	(1)
Planning for Full with Differential Backups	449	(1)
Planning for Full with Transaction Log Backups	450	(1)
Planning for Full, Differential, and Transaction Log Backups	451	(1)
Planning for Filegroup Backups	451	(1)
Summary	452	(1)
Exam Essentials	453	(1)
Review Questions	454	(6)
Answers to Review Questions	460	(3)
Achieving High Availability through Replication	463	(58)
Introducing Replication	464	(1)
Introducing the Publisher/Subscriber Metaphor	465	(2)
Introducing Articles	466	(1)
Introducing Publications	467	(1)
Understanding Replication Factors and Distribution Types	467	(5)
Using Distributed Transactions	468	(1)
Using Transactional Replication	469	(1)
Using Transactional Replication with Immediate Updating Subscribers	469	(1)
Using Snapshot Replication	470	(1)
Using Snapshot Replication with Immediate Updating Subscribers	470	(1)
Using Merge Replication	470	(1)
Using Queued Updating	471	(1)
Understanding Replication Internals	472	(6)
Understanding Merge Replication	473	(3)
Understanding Snapshot Replication	476	(1)
Understanding Transactional Replication	476	(2)
Considering Publication Issues	478	(1)
Considering Distributor Issues	479	(1)
Introducing Replication Models	480	(4)
Introducing Central Publisher/Central Distributor	480	(1)
Introducing Remote Distribution	481	(1)
Introducing Central Subscriber/Multiple Publishers	482	(1)
Introducing Multiple Publishers/Multiple Subscribers	483	(1)
Replicating over the Internet and to Heterogeneous Database Servers	484	(1)
Using Heterogeneous Replication	484	(1)
Using Internet Replication	484	(1)
Installing and Using Replication	485	(18)
Configuring SQL Server for Replication	485	(1)
Installing a Distribution Server	486	(4)
Adding a Publication	490	(6)
Creating a Subscription	496	(6)
Testing Replication	502	(1)
Managing Replication	503	(8)
Considering Administrative Issues	504	(1)
Considering Replication Backup Issues	504	(1)
Using the Replication Monitor	505	(3)
Working with Replication Scripts	508	(1)
Enhancing Replication Performance	509	(2)
Summary	511	(1)
Exam Essentials	512	(1)
Review Questions	513	(6)
Answers to Review Questions	519	(2)
Introducing More High-Availability Methods	521	(36)
Choosing the High-Availability Features You Need	522	(2)
Implementing Database Mirroring	523	(1)
Understanding Database Mirroring Concepts	524	(8)
Preparing for Mirroring	525	(1)
Creating Endpoints	526	(3)
Specifying Partners and Witnesses	529	(1)
Configuring the Operating Mode	530	(1)
Switching Roles	531	(1)
Implementing Log Shipping	532	(7)
Monitoring the Server	533	(1)
Configuring Log Shipping	533	(5)
Changing Roles	538	(1)
Monitoring Log Shipping	538	(1)
Managing Database Snapshots	539	(8)
Creating a Snapshot	540	(3)
Reverting from a Snapshot	543	(4)
Summary	547	(1)
Exam Essentials	548	(1)
Review Questions	549	(6)
Answers to Review Questions	555	(2)
Monitoring and Optimizing SQL Server 2005	557	(38)
Using System Monitor	559	(6)
Monitoring with SQL Profiler	565	(11)
Replaying a Trace File	570	(2)
Using the Database Engine Tuning Advisor	572	(4)
Troubleshooting SQL Server	576	(7)
Reading Error and Event Logs	577	(2)
Troubleshooting Blocks and Deadlocks	579	(3)
Troubleshooting Jobs	582	(1)
Using the Dedicated Administrator Connection	583	(2)
Summary	585	(2)
Exam Essentials	587	(1)
Review Questions	588	(5)
Answers to Review Questions	593	(2)
Glossary	595	(14)
Index	609