book

Mastering SQL Server® 2008

by Michael Lee, Gentry Bieker

January 2009

Intermediate to advanced

792 pages

23h 8m

English

Sybex

Read now

Unlock full access

Who Should Read This BookThe Mastering SeriesWhat Is Covered in This BookHow to Contact the Authors

SQL Server in the Enterprise WorldInstalling SQL Server 2008The SQL Server Tool SetThe Bottom Line
Essential InternalsThe SQL Server System CatalogDatatypingThe Bottom Line
Planning a DatabaseCreating DatabasesManaging Databases, Files, and FilegroupsThe Bottom Line
Designing Data StorageCreating and Dropping SQL Server TablesSelecting Data from a TableModifying DataThe Bottom Line
Procedural StructuresStored ProceduresViewsUser-Defined FunctionsUsing SynonymsThe Bottom Line
Understanding Data Integrity ControlsWorking with ConstraintsWorking with DML TriggersRule and Default ObjectsThe Bottom Line
Returning XML from SQL Server Using FOR XMLThe xml DatatypeValidating XML with SchemasImplementing XQueryXML IndexesThe Bottom Line
The SQL Server Security InfrastructureImplementing SQL Server Principals and AuthenticationImplementing Permissions in SQL ServerThe Bottom Line
Transaction ArchitectureBackup and Restore StrategiesPerforming BackupsPerforming RestoresOther IssuesThe Bottom Line
Using System MonitorUsing SQL Profiler & TraceDDL TriggersEvent NotificationsThe Bottom Line
SQL Agent and AutomationCreating JobsCreating AlertsCreating OperatorsScripting the SQL AgentThe Bottom Line
Overview of SQL Server Availability and Distribution OptionsDatabase Log ShippingDatabase MirroringReplicationThe Bottom Line
Understanding the SQL Server Key InfrastructureUsing Transparent Data EncryptionEncrypting Data Using SQL ServerModule SigningThe Bottom Line
Index ArchitectureCreating and Managing IndexesSpecial Index ConsiderationsThe Bottom Line
The SQL Server Locking ArchitectureTransaction ManagementDistributed TransactionsGetting Transaction and Locking InformationThe Bottom Line
The Resource GovernorUnderstanding Policy-Based ManagementThe Bottom Line
Understanding ADO.NETSpecial Operations in ADO.NETIntroducing Language Integrated Query (LINQ)LINQ and the Entity FrameworkThe Bottom Line
CLR ConceptsCreating Managed Objects in SQL ServerDeploying Managed Objects in SQL ServerManaging CLR SecuritySecurity ConsiderationsThe Bottom Line
Spatial Data ConceptsImplementing the Geometry TypeImplementing the Geography TypeSpatial IndexesThe Bottom Line
The Essentials of Service-Oriented ArchitectureUnderstanding the Service BrokerImplementing Service BrokerThe Bottom Line
Full-Text ArchitectureCreating Full-Text IndexesWriting Full-Text QueriesThe Bottom Line
Chapter 1: Overview of Microsoft SQL Server 2008Chapter 2: Understanding SQL Server ArchitectureChapter 3: Databases, Files, and Resource ManagementChapter 4: Tables and CRUD OperationsChapter 5: Procedural ProgrammingChapter 6: Managing Data IntegrityChapter 7: SQL Server and XMLChapter 8: Managing User SecurityChapter 9: Data RecoveryChapter 10: Monitoring SQL Server ActivityChapter 11: Using the SQL Server Agent ServiceChapter 12: Data Availability and DistributionChapter 13: SQL Server Data SecurityChapter 14: Indexing Strategies for Query OptimizationChapter 15: Transactions and LockingChapter 16: Using the Resource Governor and Policy-Based ManagementChapter 17: SQL Server and the .NET ClientChapter 18: SQL Server and the Common Language RuntimeChapter 19: Using Spatial DataChapter 20: Service-Oriented Architecture and the Service BrokerChapter 21: Full-Text Search Services
Designing the Data WarehouseUnderstanding Analysis Services
Configuring Reporting ServicesCreating a Simple ReportDeploying the Report

Content preview from Mastering SQL Server® 2008

Chapter 2 Understanding SQL Server Architecture

If you're going on a road trip, you better bring a map. Without that map you could end up anywhere. If you travel without a map and without clear directions, you'll probably end up far from your intended destination. The same is true with SQL Server. Unless you understand SQL Server architecture, you'll never know where you might end up on your performance-tuning and database-development journey.

This chapter strives to be your road map so that you don't have to ask for directions. Although we could never exhaustively cover SQL Server internals and architecture in one chapter, we hope that this one will at least be a good map for you to follow as you learn more about how SQL Server behaves. In this ...