book

Beginning, Microsoft® SQL Server® 2008 Administration

by Chris Leiter, Dan Wood, Albert Boettger, Michael Cierkowski

April 2009

Beginner

815 pages

22h 35m

English

Wrox

Read now

Unlock full access

Who This Book Is ForA Note about This Second EditionAssumptionsWhat This Book CoversHow This Book Is StructuredWhat You Need to Use This BookConventionsSource CodeErratap2p.wrox.com
1.1. A Condensed History of SQL Server1.1.1. In the Beginning1.1.2. The Evolution of a Database1.1.3. Microsoft Goes It Alone1.1.4. BI for the Masses1.1.5. 2008 ... and Beyond!1.2. What Is SQL Server 2008?1.2.1. Database Engine1.2.2. Integration Services1.2.3. Analysis Services1.2.4. Reporting Services1.2.5. Service Broker1.2.6. Data Tier Web Services1.2.7. Replication Services1.2.8. Multiple Instances1.2.9. Database Mail1.2.10. A Note about Notification Services1.3. SQL Server 2008 Editions1.3.1. SQL Server Compact 3.5 SP11.3.2. SQL Server 2008 Express Edition1.3.3. SQL Server 2008 Web Edition1.3.4. SQL Server 2008 Workgroup Edition1.3.5. SQL Server 2008 Standard Edition1.3.6. SQL Server 2008 Enterprise Edition1.4. SQL Server 2008 Architecture1.4.1. SQL Server 2008 Communication1.4.1.1. Supported Languages1.4.1.2. SQL Server Programming Object Models1.4.2. SQL Server 2008 Services1.4.2.1. MSSQLServer (SQL Server)1.4.2.2. SQLServerAgent (SQL Server Agent)1.4.2.3. MSSQLServerADHelper100 (SQL Server Active Directory Helper)1.4.2.4. MSSQLServerOLAPService (SQL Server Analysis Services)1.4.2.5. SQLBrowser (SQL Server Browser)1.4.2.6. MSSQLFDLauncher (SQL Full-Text Filter Daemon Launcher)1.4.2.7. MSDTSServer100 (SQL Server Integration Services)1.4.2.8. ReportingServicesServer (SQL Server Reporting Services)1.4.2.9. SQLWriter (SQL Server VSS Writer)1.4.2.10. MSDTC (Distributed Transaction Coordinator)1.5. SQL Server 2008 Database Objects1.5.1. Server1.5.2. Database1.5.3. Schema1.5.4. Object Names1.6. SQL Server 2008 Databases1.6.1. System Databases1.6.1.1. The master Database1.6.1.2. The model Database1.6.1.3. The msdb Database1.6.1.4. The tempdb Database1.6.1.5. The resource Database1.6.2. User Databases1.6.3. Distribution Databases1.7. SQL Server 2008 Database Storage1.7.1. Data Files and Filegroups1.7.2. Log Files1.8. SQL Server Security1.8.1. Windows Authentication Mode1.8.2. SQL Server and Windows Authentication Mode (Mixed Mode)1.9. Summary
2.1. SQL Server Installation Planning2.1.1. Hardware Considerations2.1.2. Processor Considerations2.1.3. Memory Considerations2.1.4. Storage Considerations2.1.4.1. SAN and NAS versus Local Disk Storage2.1.4.2. Storage Area Network (SAN)2.1.4.3. Network Attached Storage (NAS)2.1.4.4. Local Attached Disk Array2.1.5. Virtualization Considerations2.1.6. Software Prerequisites2.2. SQL Server Installation Center2.2.1. Setup Support Rules (for Setup Support Files)2.2.2. Setup Support Rules (for Installation)2.2.3. Feature Selection2.2.3.1. Instance Configuration2.2.3.2. Server Configuration2.2.3.3. Collation Settings2.2.3.4. Database Engine Configuration2.2.3.5. Analysis Services Configuration2.2.3.6. Reporting Services Configuration2.2.3.7. Error and Usage Reporting2.2.3.8. Installation Rules2.2.3.9. Final Steps2.2.4. Installing to a Windows Cluster2.2.5. Configuring the Virtual Server Name2.2.6. Sample Databases2.3. Installation Review2.4. Summary
3.1. SQL Server Management Studio3.1.1. Tool Windows3.1.1.1. Object Explorer3.1.1.2. Code Editor3.1.1.3. Solution Explorer3.1.1.4. Properties Window3.1.1.5. Registered Servers3.1.1.6. Bookmark Window3.1.1.7. Toolbox3.1.1.8. Error List3.1.1.9. Object Explorer Details3.1.1.10. Web Browser3.1.1.11. Template Explorer3.1.2. Toolbars3.1.2.1. Database Diagram Toolbar3.1.2.2. Debug Toolbar3.1.2.3. Debug Location Toolbar3.1.2.4. Help Toolbar3.1.2.5. Query Designer Toolbar3.1.2.6. Source Control Toolbar3.1.2.7. SQL Editor Toolbar3.1.2.8. SQL Server Analysis Services Editors Toolbar3.1.2.9. SQL Server Compact Edition Editor Toolbar3.1.2.10. Standard Toolbar3.1.2.11. Table Designer Toolbar3.1.2.12. Text Editor Toolbar3.1.2.13. View Designer Toolbar3.1.2.14. XML Editor Toolbar3.1.3. SQL Server Management Studio Configuration3.1.3.1. Environment3.1.3.2. Text Editor3.1.3.3. Query Execution3.1.3.4. Query Results3.1.3.5. SQL Server Object Explorer3.1.3.6. Designers3.1.3.7. Source Control3.2. Log File Viewer3.3. SQL Server Business Intelligence Development Studio3.4. SQL Server Profiler3.4.1. SQL Server Trace3.4.2. Trace Properties3.4.2.1. General Tab3.4.2.2. Events Selection Tab3.4.2.3. Events Extraction Settings Tab3.5. Database Engine Tuning Advisor3.5.1. General Tab3.5.2. Tuning Options Tab3.6. SQL Server Configuration Manager3.7. Reporting Services Configuration Manager3.8. Command-Line Tools3.8.1. SQLCMD3.8.1.1. Dedicated Administrator Connection (DAC)3.8.2. Bulk Copy Program (BCP)3.8.2.1. Non-XML Format File Example3.8.2.2. XML Format File Example3.8.2.3. Export a Table to a Flat-File Example3.8.2.4. Import Flat-File Example with a Format File3.8.3. PowerShell3.9. Summary
4.1. The Resource Database4.1.1. The sys Schema4.1.1.1. Dynamic Management Views and Functions4.2. SQL Server Database Physical Structure4.2.1. Physical Storage Data Types4.2.1.1. Fixed-Length Data Types4.2.1.2. Variable-Length and Large Object Data Types4.2.1.3. CLR Data Types4.2.1.4. In-Row Data4.2.2. FILESTREAM Data4.2.3. Other Data Types4.2.4. SQL Server Database Files4.2.5. Data Files4.2.5.1. Extents4.2.5.2. Pages4.2.5.2.1. Data Pages4.2.5.2.2. Index Pages4.2.5.2.3. Text/Image Pages4.2.5.2.4. Global Allocation Map (GAM) and Secondary Global Allocation Map (SGAM) Pages4.2.5.2.5. Page Free Space (PFS) Pages4.2.5.2.6. Index Allocation Map (IAM) Pages4.2.5.2.7. Bulk Changed Map (BCM) Pages4.2.5.2.8. Differential Changed Map (DCM) Pages4.2.6. Transaction Log4.2.6.1. Transactions4.2.6.1.1. Auto-Commit4.2.6.1.2. Implicit4.2.6.1.3. Explicit4.2.6.2. Recording Transactions4.2.6.3. Transaction Log Physical Characteristics4.3. Summary
5.1. System Databases5.2. User Databases5.3. Database Planning5.3.1. Capacity Planning5.4. Creating Databases5.4.1. Getting Started5.4.2. Creating a New Database5.4.2.1. Database Files5.4.2.2. Filegroups5.4.2.3. Maintenance or Performance?5.4.2.4. File Size5.4.2.5. Autogrowth5.4.2.6. Path5.4.2.7. Database Options5.4.2.7.1. Collation5.4.2.7.2. Recovery Model5.4.2.7.3. Compatibility Level5.4.2.8. Other Options5.4.2.8.1. ANSI NULL Default5.4.2.8.2. ANSI NULLS Enabled5.4.2.8.3. ANSI Padding Enabled5.4.2.8.4. ANSI Warnings Enabled5.4.2.8.5. Arithmetic Abort Enabled5.4.2.8.6. Auto Close5.4.2.8.7. Auto Create Statistics5.4.2.8.8. Auto Shrink5.4.2.8.9. Auto Update Statistics5.4.2.8.10. Auto Update Statistics Asynchronously5.4.2.8.11. Broker Enabled5.4.2.8.12. Close Cursor on Commit Enabled5.4.2.8.13. Concatenate Null Yields Null5.4.2.8.14. Cross-database Ownership Chaining Enabled5.4.2.8.15. Database Read-Only5.4.2.8.16. Database State5.4.2.8.17. Date Correlation Optimization Enabled5.4.2.8.18. Default Cursor5.4.2.8.19. Encryption Enabled5.4.2.8.20. Honor Broker Priority5.4.2.8.21. Numeric Round-Abort5.4.2.8.22. Page Verify5.4.2.8.23. Parameterization5.4.2.8.24. Quoted Identifiers Enabled5.4.2.8.25. Recursive Triggers Enabled5.4.2.8.26. Restrict Access5.4.2.8.27. Service Broker Identifier5.4.2.8.28. Trustworthy5.4.2.8.29. VarDecimal Storage Format Enabled5.4.2.9. Generating Database Creation Scripts5.4.3. Schemas5.4.3.1. Schemas and Name Resolution5.4.3.2. Schema Creation5.4.3.3. Schema Maintenance5.4.4. Tables5.4.4.1. Table Collation5.4.4.2. Table Architecture5.4.4.3. Partitioning Tables5.4.4.4. Data Compression5.4.4.4.1. Row Compression5.4.5. Indexes5.4.5.1. Clustered Indexes5.4.5.2. Non-Clustered Indexes5.4.5.2.1. Non-Clustered Indexes on Heaps5.4.5.2.2. Non-Clustered Indexes on Clustered Indexes5.4.5.3. Included Columns5.4.5.4. Filtered Indexes5.4.5.5. Hierarchal Indexes5.4.5.5.1. Breadth-First Indexes5.4.5.5.2. Depth-First Indexes5.4.5.6. Spatial Indexes5.4.5.7. XML Indexes5.4.5.7.1. Primary XML Indexes5.4.5.7.2. Secondary XML PATH Indexes5.4.5.7.3. Secondary XML VALUE Indexes5.4.5.7.4. Secondary XML PROPERTY Indexes5.4.5.8. Maintaining Tables5.4.5.8.1. Index Fragmentation5.4.5.8.2. Mitigating Fragmentation with Fill-Factor5.4.5.8.3. Removing Fragmentation5.4.5.8.4. Create Index with Drop Existing5.4.5.8.5. Rebuilding Indexes5.4.5.8.6. Reorganizing Indexes5.4.5.8.7. Statistics5.4.6. Enforcing Data Integrity5.4.6.1. Primary Key Constraints5.4.6.2. Unique Constraints5.4.6.3. Foreign Key Constraints5.4.6.3.1. Foreign Key Constraint Options5.4.6.3.2. WITH CHECK5.4.6.3.3. WITH NOCHECK5.4.6.3.4. Cascading Constraints5.4.6.3.5. ON DELETE CASCADE5.4.6.3.6. ON UPDATE CASCADE5.4.6.3.7. ON DELETE SET NULL5.4.6.3.8. ON UPDATE SET NULL5.4.6.3.9. ON DELETE SET DEFAULT5.4.6.3.10. ON UPDATE SET DEFAULT5.4.6.4. Check Constraints5.4.6.5. Default Constraints5.5. Database Diagrams5.6. Views5.6.1. System Views5.7. Synonyms5.8. Programming Objects5.8.1. Stored Procedures5.8.2. Functions5.8.3. Triggers5.8.3.1. DML Triggers5.8.3.1.1. After Triggers5.8.3.1.2. "Instead Of" Triggers5.8.3.2. DDL Triggers5.8.4. Assemblies5.8.5. Types5.8.5.1. User-Defined Data Types5.8.5.2. User-Defined Table Types5.8.5.3. User-Defined Types5.8.6. Defaults5.8.7. Rules5.9. Summary

6.1. SQL Server Authentication Modes6.1.1. Changing the Authentication Mode from Management Studio6.1.2. Using the xp_instance_regwrite Extended Stored Procedure6.2. Principals6.2.1. Logins6.2.1.1. Creating Logins in Management Studio6.2.1.2. Creating Logins Using T-SQL6.2.1.3. Managing Logins6.2.1.4. Using CREATE LOGIN6.2.1.5. For More Information6.2.2. Credentials6.2.2.1. Creating a New Credential6.2.2.2. Using Transact-SQL6.2.3. Server Roles6.2.3.1. Using Fixed Server Roles6.2.3.2. For More Information6.2.4. Database Users6.2.4.1. CREATE USER6.2.5. Fixed Database Roles6.2.5.1. Creating a New User-Defined Database Role in Management Studio6.2.5.2. CREATE ROLE6.2.5.3. Application Roles6.2.5.4. Using CREATE APPLICATION ROLE6.2.5.5. For More Information6.3. Permissions6.3.1. Server Permissions6.3.2. Database Scope Permissions6.3.3. Schema Scope Permissions6.3.4. Using SQL Server Management Studio for Managing Permissions6.4. SQL Server Encryption6.4.1. Extensible Key Management (EKM)6.4.2. Encryption Tools6.4.2.1. Symmetric Keys6.4.2.2. Asymmetric Keys6.4.2.3. Certificates6.4.2.4. Encrypting Data6.4.2.5. Transparent Data Encryption6.4.2.6. Digital Signatures6.5. Best Practices6.6. Summary
7.1. SQL Server 2008 Network Protocols7.1.1. Shared Memory7.1.2. Named Pipes7.1.3. TCP/IP7.1.4. Virtual Interface Adapter (VIA)7.2. SQL Native Client Configuration7.3. SQL Server Endpoints7.3.1. Default TSQL Endpoints7.3.1.1. TSQL Default TCP7.3.1.2. TSQL Default VIA7.3.1.3. TSQL Named Pipes7.3.1.4. TSQL Local Machine7.3.1.5. Dedicated Administrator Connection (DAC)7.3.2. TSQL TCP Endpoints7.3.3. Database Mirroring Endpoints7.3.4. SOAP Endpoints7.3.5. Service Broker Endpoints7.3.6. Securing Endpoints7.4. Summary
8.1. Policy-Based Management8.1.1. Targets8.1.2. Facets8.1.3. Conditions8.1.4. Policies8.1.5. Policy Categories8.1.6. Effective Policies8.2. Central Management Servers8.3. Database Mail8.3.1. How It Works8.3.2. How to Configure Database Mail8.3.3. Configuring Database Mail Options8.3.4. Managing Profiles and Accounts8.3.4.1. sysmail_add_profile_sp8.3.4.2. sysmail_add_account_sp8.3.4.3. sysmail_add_profileaccount_sp8.3.4.4. sysmail_update_profile_sp8.3.4.5. sysmail_update_account_sp8.3.4.6. sysmail_update_profileaccount_sp8.3.4.7. sysmail_add_principalprofile_sp8.3.4.8. sysmail_update_principalprofile_sp8.3.4.9. sysmail_delete_principalprofile_sp8.3.4.10. sysmail_delete_profileaccount_sp8.3.4.11. sysmail_delete_account_sp8.3.4.12. sysmail_delete_profile_sp8.3.5. Guidelines for Deleting Mail Objects8.3.6. Sending Mail8.3.6.1. sp_send_dbmail8.3.7. Managing Messages8.4. Event Notifications8.5. SQL Server Agent8.5.1. Configuring the SQL Server Agent Service8.5.1.1. General Properties8.5.1.2. Advanced Properties8.5.1.3. Alert System Properties8.5.1.4. Job System Properties8.5.1.5. Agent Connection Properties8.5.1.6. Job History Properties8.5.2. SQL Server Agent Security8.5.2.1. SQLAgentUserRole8.5.2.2. SQLAgentReaderRole8.5.2.3. SQLAgentOperatorRole8.5.3. Creating Jobs8.5.3.1. Categories8.5.3.2. Creating Job Steps8.5.3.3. sp_add_jobstep8.5.3.4. Token Replacement8.5.3.5. Using Tokens in Job Steps8.5.4. Creating Schedules8.5.4.1. sp_add_schedule8.5.4.2. sp_attach_schedule8.5.5. Creating Operators8.5.5.1. sp_add_operator8.5.5.2. The Fail-Safe Operator8.5.6. Creating Alerts8.5.6.1. SQL Server Event-Based Alerts8.5.6.2. Performance Condition Alerts8.5.6.3. WMI Event Alerts8.5.7. Creating Proxies8.5.8. Multi-Server Jobs8.6. Maintenance Plans8.6.1. Maintenance Plan Wizard8.6.2. Maintenance Plan Designer8.7. Best Practices8.8. Summary
9.1. Chapter Preparation9.2. Database Recovery Models9.2.1. Full Recovery Model9.2.2. Bulk-Logged Recovery Model9.2.3. Simple Recovery Model9.3. SQL Server 2008 Database Backup9.3.1. Backup Devices9.4. SQL Server 2008 Backup Types9.4.1. Full Backup9.4.2. Differential Backup9.4.3. File/Filegroup Backup9.4.3.1. File/Filegroup with Differential9.4.4. Transaction Log Backup9.4.5. Partial Backup9.4.6. Copy Only Backup9.5. Backup Options9.5.1. Backup Stripe9.5.2. Mirrored Backup9.5.3. Compressed Backup9.5.4. WITH Options9.6. Backup Strategies9.6.1. Full Backup Only9.6.2. Full Backup with Differential9.6.3. Full Backup with Transaction Log9.6.4. Full and Differential Backup with Transaction Log9.6.5. File and Filegroup Backup9.6.6. Filegroup with Differential9.6.7. Partial Backup9.6.8. Backup Summary9.7. Restoring Databases9.7.1. Restore Process9.7.2. Delaying Recovery9.8. RESTORE Command9.8.1. RESTORE DATABASE database_name9.8.1.1. FILE9.8.1.2. FILEGROUP9.8.1.3. READ_WRITE_FILEGROUPS9.8.1.4. PAGE9.8.1.5. RESTORE LOG database_name9.8.2. FROM Options9.8.2.1. FROM DISK9.8.2.2. FROM TAPE9.8.2.3. FROM DATABASE_SNAPSHOT9.8.3. WITH Clause9.8.3.1. RECOVERY | NORECOVERY9.8.3.2. STANDBY9.8.3.3. CHECKSUM | NO_CHECKSUM9.8.3.4. CONTINUE_AFTER_ERROR | STOP_ON_ERROR9.8.3.5. FILE9.8.3.6. MOVE...TO...9.8.3.7. PARTIAL9.8.3.8. REPLACE9.8.4. Database Restore Preparation9.8.4.1. Isolate the Database9.8.4.2. Capture Recent Activity9.8.4.3. Gather Backup Information9.8.5. Restoring User Databases9.8.5.1. Full Restore9.8.5.2. Full with Differential Restore9.8.5.3. Full with Transaction Log Restore9.8.5.4. Full and Differential with Transaction Log Restore9.8.5.5. File and Filegroup Restore9.8.5.6. Partial Restore9.8.5.7. Point-in-Time Restore9.8.6. Recovering System Databases9.8.6.1. Recovering the Master Database9.8.6.2. Orphaned Users9.8.7. Database Restore Summary9.9. Database Snapshots9.9.1. Database Snapshot Limitations9.9.2. Disaster Recovery and Database Snapshots9.9.2.1. Undoing Updates9.9.2.2. Undoing Deletes9.9.2.3. Undoing Drops9.9.2.4. Restoring from Snapshots9.10. Summary
10.1. Performance Monitoring10.1.1. Performance Monitoring Strategy10.1.2. Creating a Performance Baseline10.1.2.1. Performance Counters10.1.2.1.1. Processor Counters10.1.2.1.2. Disk Counters10.1.2.1.3. Memory Counters10.1.2.1.4. Network Counters10.1.2.1.5. SQL Server Counters10.1.2.2. Dynamic Management Views10.2. Tools and Techniques for Monitoring10.2.1. Log File Viewer10.2.2. Activity Monitor10.2.3. System Stored Procedures10.2.3.1.10.2.3.1.1. sp_who and sp_who210.2.3.1.2. sp_lock10.2.3.2. SQL Server Locking10.2.3.2.1. KILL10.2.4. Using Profiler10.2.4.1. Detect and Analyze Long-Running Queries with Profiler10.2.5. Monitoring Files10.2.5.1. Disk Usage Report10.2.5.1.1. sp_spaceused10.2.5.1.2. sys.sysfiles10.2.5.2. Monitoring Files with Performance Monitor10.3. Auditing10.3.1. SQL Server Audit10.3.1.1. Audit Package10.3.1.2. Server Audit Specification10.3.1.3. Database Audit Specification10.3.1.4. Audit Destination10.3.1.5. Targeting the Security Log10.3.2. Login Auditing10.3.3. C2 Audit Mode10.3.4. Security Audit Event Category10.3.5. SQL Trace10.4. Tracking Changes10.4.1. Change Data Capture10.4.1.1. Configuration10.4.1.2. Using Change Data Capture10.4.1.3. Job Control10.4.1.4. Related Dynamic Management Views10.4.1.5. Limitations10.4.1.6. Tables10.4.2. Change Tracking10.4.2.1. Functions and Views10.5. Data Collection10.5.1. Terminology10.5.2. Architecture and Processing10.5.3. Configuring Data Collection10.5.4. Data Collector Types10.5.5. Data Collection Sets10.5.6. Error Handling10.5.7. Reporting10.5.8. Management Data Warehouse10.6. Monitoring Database Modifications10.6.1. Data Definition Language (DDL) Triggers10.6.1.1. EVENTDATA Function10.7. Summary
11.1. Hardware Optimization11.1.1. CPU Selection11.1.2. Hyperthreading11.1.3. Memory11.1.4. Storage Options11.1.4.1. Write Caching11.1.4.2. Solid-State Drives11.1.5. Network Design11.1.6. Virtualizing SQL Server11.2. Design Considerations11.2.1. Database Recovery Model11.2.2. Designing Efficient Tables11.2.2.1. Normalization11.2.2.2. De-Normalization11.2.2.3. Data Type Selection11.2.2.4. Handling Complex Object Data11.2.3. Declarative Referential Integrity (DRI)11.2.4. Constraints versus Triggers11.2.5. Deciding What to Index11.2.5.1. Primary Key Choice11.2.5.2. Cluster Key Choice11.2.5.3. GUIDS as Cluster Keys11.2.5.4. Other Indexes11.2.6. Indexed Views and Filtered Indexes11.2.7. Minimizing Blocking11.2.8. Hidden Dangers of Time-Outs11.3. Query Optimization11.3.1. Execution Plans11.3.1.1. Plan Caching11.3.1.2. Viewing Execution Plans11.3.1.3. Analyzing Execution Plans11.3.2. Updating Statistics11.3.3. Managing Indexes11.3.3.1. Identifying Missing Indexes11.3.3.1.1. Using Automatic Statistics to Identify Missing Indexes11.3.3.1.2. Using XML ShowPlan to Identify Missing Indexes11.3.3.2. Index De-Fragmentation11.3.3.3. Determining Index Effectiveness11.3.4. Query Optimizer Hints11.3.4.1. Table Hints11.3.4.2. Join Hints11.3.4.3. Query Hints11.3.5. Plan Guides11.3.5.1. Creating Plan Guides11.3.5.2. Creating Plan Guides from the Plan Cache11.3.5.3. Plan Freezing11.3.6. Database Engine Tuning Advisor11.3.6.1. Using the DTA with Profiler11.3.6.2. Correlating Database Activity with Performance Counters11.4. T-SQL Optimization Tips11.4.1. Limiting Result Sets11.4.1.1. TOP N and SET ROWCOUNT11.4.1.2. HAVING versus WHERE11.4.1.3. Functions in the WHERE Clause11.4.2. ANSI-Style Join Syntax11.4.3. Dealing with Null Values11.4.4. Alternatives to Cursors11.4.5. Merge Joins11.4.6. Grouping Sets11.4.7. Distinct Aggregation11.4.8. How Many Records Are in That Table?11.4.9. Temp Tables versus Table Variables11.5. Resource Governor11.5.1. Configuring the Resource Governor11.5.1.1. Configure Resource Pools11.5.1.2. Configure Workload Groups11.5.1.3. Create a Classifier Function11.5.1.4. Assign the Classifier Function and Reconfigure11.5.2. Monitoring the Resource Governor11.5.2.1. Performance Counters11.5.2.2. Events11.5.2.3. System Views11.6. Summary
12.1. Introduction to High Availability12.2. Failover Clustering12.2.1. Windows Clustering — A Quick Primer12.2.2. Clustering Components12.2.3. Active/Passive Clustering12.2.4. Active/Active Clustering12.2.5. Considering Clustering12.3. Log Shipping12.3.1. Preparing for Log Shipping12.3.2. Configuring Log Shipping with SQL Server Management Studio12.3.3. Configuring Log Shipping with Transact-SQL12.3.4. Configuring Failover12.4. Database Mirroring12.4.1. Client Redirection12.4.2. Database Mirroring Modes12.4.2.1. High-Performance Mode12.4.2.2. High-Safety without Automatic Failover Mode12.4.2.3. High-Safety with Automatic Failover Mode12.4.3. Configuring Database Mirroring12.4.3.1. Using SQL Server Management Studio12.4.3.2. Using Transact-SQL12.4.4. Monitoring Database Mirroring12.4.5. Managing Database Mirroring12.4.5.1. Pausing a Mirror Session12.4.5.2. Resuming a Mirror Session12.4.5.3. Manual Failover12.4.5.4. Forcing Service on the Mirror12.4.5.5. Removing a Database Mirror12.5. Summary
13.1. Replication Overview13.2. SQL Server Replication Agents13.2.1. Snapshot Agent13.2.2. Log Reader Agent13.2.3. Distribution Agent13.2.4. Merge Agent13.2.5. Queue Reader Agent13.3. SQL Server Replication Types13.3.1. Distributed Transactions13.3.2. Transactional Replication13.3.2.1. Standard Transactional Publication13.3.2.2. Transactional Publications with Updatable Subscriptions in a Hierarchical Topology13.3.2.3. Transactional Publications in a Peer-to-Peer Topology13.3.3. Snapshot Replication13.3.4. Merge Replication13.3.5. Oracle Replication13.4. SQL Server Replication Models13.4.1. Single Publisher/Multiple Subscribers13.4.2. Multiple Publishers/Single Subscriber13.4.3. Multiple Publishers/Multiple Subscribers13.5. Replication Tools13.5.1. Filtering13.5.1.1. Static Row Filters13.5.1.2. Column Filters13.5.1.3. Parameterized Row Filters13.5.1.4. Join Filters13.5.2. Replicating Partitioned Tables and Indexes13.5.3. New Publication Wizard13.5.4. New Subscription Wizard13.5.5. Replication Monitor13.6. Summary
14.1. Databases and Programming14.2. Is Transact-SQL Going Away?14.3. .NET and the CLR14.3.1. Assemblies14.3.2. Namespaces14.3.3. Classes14.3.4. Methods14.4. SQL Server CLR Objects14.4.1. Enabling SQL CLR14.4.2. Creating a SQL CLR Assembly14.4.3. Adding an Assembly14.4.4. Compatible Data Types14.4.5. User-Defined Functions14.4.5.1. Scalar Functions14.4.5.2. Table-Valued Functions14.4.6. Stored Procedures14.4.7. Triggers14.4.8. User-Defined Types14.4.9. Aggregates14.5. Deployment with Visual Studio14.6. Programming Support14.6.1. Threading14.6.2. Impersonation14.7. Security Options14.7.1. .NET Security14.7.2. Securing SQL CLR14.7.3. SQL Server CLR Permission Sets14.7.3.1. SAFE Permission14.7.3.2. External Access Permission14.7.3.3. UNSAFE Permission14.7.3.4. Enabling External Access and Unsafe Permissions14.8. Summary
15.1. Understanding BI15.2. Performance Management15.3. Business Intelligence Components15.3.1. Data Goes In, Data Comes Out15.3.2. Analyze This!15.3.3. Did You Get the Memo about Cover Pages?15.4. Beyond SQL15.4.1. The BI Side of SharePoint15.4.2. ProClarity and PerformancePoint Server15.5. So Many Tools, So Little Time15.6. Summary
16.1. About SSIS16.1.1. Integration Services16.1.2. Integration Services Object Model16.1.3. Integration Services Run Time16.1.3.1. Integration Services Packages16.1.3.2. Integration Services Tasks16.1.3.3. Integration Services Containers16.1.3.4. Integration Services Event Handlers16.1.4. Integration Services Data Flow16.2. Importing and Exporting Data16.2.1. Using the Import Wizard16.2.2. Using the Export Wizard16.3. Transforming Data with SSIS16.3.1. Understanding the Development Environment16.3.1.1. Toolbox16.3.1.2. Solution Explorer16.3.1.3. Properties16.3.1.4. Connection Managers16.3.2. Package Elements16.3.2.1. Control Flow16.3.2.2. Control Flow Tasks16.3.2.3. Precedence Constraints16.3.2.4. Task Grouping16.3.2.5. Data Flow16.3.2.5.1. Data Sources16.3.2.5.2. Transformations16.3.2.5.3. Data Destinations16.3.2.6. Event Handling16.3.2.7. Package Explorer16.3.3. Creating a Simple Package16.3.3.1. Creating the Connection16.3.3.2. Creating the Data Flow Task16.3.3.3. Defining the Destination16.3.3.4. So That's It, Right? Applying Additional Transforms16.4. Summary
17.1. Understanding OLAP17.1.1. OLAP Terminology17.2. Working with SSAS17.2.1. Creating the Project17.2.2. Defining a Data Source17.2.3. Creating the Data Source View17.2.4. Defining Dimensions17.2.5. Creating the Cube17.2.6. Create Hierarchies17.2.6.1. Create the Products Hierarchy17.2.6.2. Create the Resellers and Geographies Hierarchies17.2.6.3. Reseller Attribute Relationships17.2.6.4. Build the Calendar Hierarchy17.2.6.5. Calendar Attribute Relationships17.2.7. Deploying the Project17.3. Managing SSAS17.3.1. Browsing the Cube17.3.2. SSAS Security17.3.2.1. Creating a Role17.3.2.2. Testing Security17.4. Advanced SSAS Concepts17.4.1. MDX17.4.2. Data Mining17.5. Summary
18.1. SQL Server Reporting Services Overview18.1.1. Components and Tools18.1.1.1. Report Server18.1.1.2. Report Processor18.1.1.3. Scheduling and Delivery Processor18.1.1.4. Extensions18.1.1.5. Reporting Services Configuration Manager18.1.1.6. SQL Server Management Studio18.1.1.7. Business Intelligence Development Studio18.1.1.8. Report Builder18.1.1.9. Report Manager18.2. Installation and Configuration18.2.1. Hardware and Software Requirements18.2.2. Security Considerations18.2.3. Installation Mode18.2.4. Multiple Instances and Versions18.3. Creating Reports18.3.1. Report Designer18.3.2. Report Builder18.3.2.1. Connecting to Data18.3.2.2. Laying out Report Data18.3.2.3. Publishing Reports18.4. Report Delivery18.4.1. Caching18.4.2. Snapshots18.4.3. Subscriptions18.5. Summary
19.1. Service-Oriented Architecture19.2. Service Broker Overview19.3. Service Broker Elements19.3.1. Conversations19.3.1.1. Messages19.3.1.2. Dialog Conversations19.3.1.3. Conversation Groups19.3.1.4. Conversation Priorities19.3.2. Contracts19.3.3. Queues19.3.4. Services19.3.5. Routes19.4. Security Considerations for Service Broker19.4.1. Dialog Security19.4.1.1. Full Security19.4.1.2. Anonymous Security19.4.2. Transport Security19.5. Creating a Sample Application19.5.1. Creating and Preparing the Database19.5.2. Creating the Service Broker Objects19.5.3. Creating Objects for the TicketInputService19.5.4. Creating Objects for the TicketNotifyService19.5.5. Testing the Application19.6. Managing Service Broker with SSMS19.7. Summary

Content preview from Beginning, Microsoft® SQL Server® 2008 Administration

Chapter 5. SQL Server 2008 Databases

The database is the heart of SQL Server 2008, handling everything from storing user information for later retrieval to acting as a temporary storage area for SQL Server operations. Previous chapters discussed the SQL Server installation process and the internal structure of all the files that make up a SQL Server 2008 database. This chapter delves into creating user databases and the various options that can be configured on them.

System Databases

As mentioned in Chapter 1, when SQL Server 2008 is installed, five system databases are created to store system information and support database operations. Four of the system databases (master, model, msdb, and tempdb) are visible during normal database operations, but the fifth (the resource database, as described in Chapter 4) is not. Distribution databases can also be created if the SQL Server instance is configured as a distributor for SQL Server Replication.

User Databases

User databases are those databases that are created by any server login that possesses the appropriate permissions. In past versions of SQL Server, you had the option to install the AdventureWorks2008 sample databases that were briefly described in Chapter 1, but this ability has since been removed from the product. You can download the AdventureWorks2008 sample database and code samples from the "Microsoft SQL Server Community Projects and Samples" located at www.codeplex.com/sqlserversamples.

Database Planning

One of the key responsibilities ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

Microsoft® SQL Server® 2008 Step by Step

Publisher Resources

ISBN: 9780470440919Purchase book

Beginning, Microsoft® SQL Server® 2008 Administration

by Chris Leiter, Dan Wood, Albert Boettger, Michael Cierkowski

Chapter 5. SQL Server 2008 Databases

System Databases

User Databases

Database Planning

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

Microsoft® SQL Server® 2008 Step by Step

Mastering SQL Server® 2008

Professional Microsoft® SQL Server® 2008 Administration

Microsoft® SQL Server® 2008 For Dummies®

Publisher Resources

Chapter 5. SQL Server 2008 Databases

System Databases

User Databases

Database Planning

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

Microsoft® SQL Server® 2008 Step by Step

Mastering SQL Server® 2008

Professional Microsoft® SQL Server® 2008 Administration

Microsoft® SQL Server® 2008 For Dummies®

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.