|
MCTS: Microsoft SQL Server 2005 Implementation and Maintenance Study Guide (Exam 70-431)
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. You can 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; and creating and implementing database objects. Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.
Contents:
Introduction.Assessment Test.Chapter 1 Installing Microsoft SQL Server 2005.Meeting the Prerequisites.Preparing to Install.Choosing Default Instances or Named Instances.Choosing Service Accounts.Selecting an Authentication Mode.Choosing a Collation Setting.Upgrading from a Previous Version.Installing SQL Server 2005.Installing a Second Instance.Troubleshooting the Installation.Chapter 2 Creating and Configuring Databases.Planning Your Database.Introducing Database Files.Introducing Filegroups.Deciding on Database File Placement.Introducing RAID 0.Introducing RAID 1.Introducing RAID 5.Introducing RAID 10.Creating Data Storage Structures.Introducing Extents.Introducing Pages.Estimating Storage Requirements.Estimating Table Storage Requirements.Estimating Index Storage Requirements.Creating and Configuring Databases.Creating a Database.Gathering Information about Your Database.Setting Database Options.Chapter 3 Working with Tables and Views.Planning Tables.Introducing Built in Datatypes.Introducing Computed Columns.Creating Tables.Restricting the Data.Introducing Constraints.Partitioning Tables.Understanding Views.Modifying Data through a View.Working with Indexed Views.Chapter 4 Performing Indexing and Full Text Searching.Understanding Index Architecture.Understanding Heaps.Understanding Clustered Indexes.Understanding Nonclustered Indexes.Creating Indexes.Using Primary Keys.Using Full Text Searching.Chapter 5 Introducing More Database Objects.Introducing Stored Procedures.Understanding the Types of Stored Procedures.Creating Stored Procedures.Recompiling Stored Procedures.Introducing Triggers.Understanding the Types of Triggers.Understanding DML Triggers.Understanding DDL Triggers.Understanding Trigger Recursion and Nesting.Understanding Disabling Triggers.Understanding Event Notifications.Introducing Functions.Understanding the Types of Functions.Using Scalar Functions.Introducing Table Valued Functions.Introducing Built in Functions.Introducing CLR Functions.Introducing Deterministic and Nondeterministic Functions.Introducing User Defined Types.Creating T SQL User Defined Types.Creating CLR User Defined Types.Getting More CLR Functionality.Chapter 6 Implementing Security in SQL Server 2005.Understanding Security Modes 190Using Windows Authentication Mode 190Using Mixed Mode 191Setting the Authentication Mode 191Understanding SQL Server Logins 193Using Standard Logins 193Using Windows Logins 196Understanding the Items Common to All Logins 199Understanding Fixed Server Roles 199Creating Database User Accounts 202Understanding Permissions 204Applying Statement Permissions 205Applying Object Permissions 207Understanding Database Roles 210Using Fixed Database Roles 210Using Custom Database Roles 212Using Application Roles 215Understanding Permission States 217Granting a Permission 217Revoking a Permission 217Denying a Permission 217Introducing Ownership Chains 221Introducing Linked Server Security 223Introducing Encryption 224Creating a Security Plan.Chapter 7 Working with Relational Data.Understanding and Using Transactions 242Executing Implicit and Explicit Transactions 243Committing and Rolling Back 244Executing Distributed Transactions 246Populating Tables 247Importing Data Using Bulk Insert 247Importing Data Using the bcp Utility 248Copying Data Using SSIS 250Bulk Inserting XML Data 252Supporting the Bulk Logged Recovery Model 253Supporting Different Collation Types and OrdersWhen Querying Data 255Formatting and Converting Datatypes 256Casting and Converting 261Understanding Datatype Precedence 264Understanding Collations 265Introducing Error Handling 266Using RAISERROR.Using @@ERRORUsing Error Messages 267UsingTRY CATCHBlocks.Chapter 8 Working with XML Data.Understanding XML Data 282Using the xml Datatype 283Using Untyped XML 284Using Typed XML 285Working with XML Schema 285Querying XML Data 288Using the query Method.Using the value Method.Using the exist Method.Using the modify Method.Using the nodes Method.Creating XML Indexes.Chapter 9 Working with Service Broker and HTTP.Understanding the SQL Server Service Broker Architecture.Working with Service Broker.Creating a Message Type.Creating a Queue.Creating a Contract.Creating a Service.Creating a Route.Using Service Broker.Sending Messages.Receiving Messages.Automating the Queue Processing.Introducing HTTP Endpoints.Configuring HTTP Endpoints.Securing HTTP Endpoints.Chapter 10 Maintaining and Automating SQL Server.Maintaining Indexes.Understanding sys.DM DB INDEX PHYSICAL STATS.Reorganizing and Rebuilding Indexes.Maintaining Statistics.Maintaining Databases.Understanding DBCC CHECKDB.Shrinking Files.Understanding Automation Basics.Configuring Database Mail.Creating Operators.Creating Jobs.Creating Alerts.Creating Event Alerts Based on Standard Errors.Creating Event Alerts Based on Custom Errors.Creating Performance Alerts.Creating WMI Alerts.Using the Maintenance Plan Wizard.Copying Databases.Chapter 11 Performing Backups and Restores.Backing Up Your Data.Understanding How Backups Work.Creating a Backup Device.Performing Full Backups.Performing Differential Backups.Performing Transaction Log Backups.Performing Filegroup Backups.Backing Up to Multiple Devices.Restoring Databases.Performing Standard Restores.Performing Point in Time Restores.Performing Piecemeal Restores.Devising a Backup Strategy.Planning for Full Backups Only.Planning for Full with Differential Backups.Planning for Full with Transaction Log Backups.Planning for Full, Differential, and Transaction Log Backups.Planning for Filegroup Backups.Chapter 12 Achieving High Availability through Replication.Introducing Replication.Introducing the Publisher/Subscriber Metaphor.Introducing Articles.Introducing Publications.Understanding Replication Factors and Distribution Types.Using Distributed Transactions.Using Transactional Replication.Using Transactional Replication with Immediate Updating Subscribers.Using Snapshot Replication.Using Snapshot Replication with Immediate Updating Subscribers.Using Merge Replication.Using Queued Updating.Understanding Replication Internals.Understanding Merge Replication.Understanding Snapshot Replication.Understanding Transactional Replication.Considering Publication Issues.Considering Distributor Issues.Introducing Replication Models.Introducing Central Publisher/Central Distributor.Introducing Remote Distribution.Introducing Central Subscriber/Multiple Publishers.Introducing Multiple Publishers/Multiple Subscribers.Replicating over the Internet and to Heterogeneous Database Servers.Using Heterogeneous Replication.Using Internet Replication.Installing and Using Replication.Configuring SQL Server for Replication.Installing a Distribution Server.Adding a Publication.Creating a Subscription.Testing Replication.Managing Replication.Considering Administrative Issues.Considering Replication Backup Issues.Using the Replication Monitor.Working with Replication Scripts.Enhancing Replication Performance.Chapter 13 Introducing More High Availability Methods.Choosing the High Availability Features You Need.Implementing Database Mirroring.Understanding Database Mirroring Concepts.Preparing for Mirroring.Creating Endpoints.Specifying Partners and Witnesses.Configuring the Operating Mode.Switching Roles.Implementing Log Shipping.Monitoring the Server.Configuring Log Shipping.Changing Roles.Monitoring Log Shipping.Managing Database Snapshots.Creating a Snapshot.Reverting from a Snapshot.Chapter 14 Monitoring and Optimizing SQL Server 2005.Using System Monitor.Monitoring with SQL Profiler.Replaying a Trace File.Using the Database Engine Tuning Advisor.Troubleshooting SQL Server.Reading Error and Event Logs.Troubleshooting Blocks and Deadlocks.Troubleshooting Jobs.Using the Dedicated Administrator Connection.Glossary.Index.
Brief Description:
With the release of SQL Server 2005, Microsoft introduced a new multi-exam certification program. This book provides an introduction to the development and administrative aspects of SQL Server 2005 and features practical guidance for the various aspects of the exam. It also includes challenging practice questions and two bonus exams.
|
