book

Professional SQL Server™ 2005 Administration

Name: Professional SQL Server™ 2005 Administration
ISBN: 9780470055205

by Brian Knight, Ketan Patel, Wayne Snyder, Jean-Claude Armand, Ross LoForte, Brad McGehee, Steven Wort, Joe Salvatore, Haidong Ji

December 2006

Intermediate to advanced

767 pages

22h 45m

English

Wrox

Read now

Unlock full access

Copyright
About the Authors
Credits
Acknowledgments
Introduction
Who This Book Is ForHow This Book Is StructuredWhat You Need to Use This BookConventionsSource CodeErratap2p.wrox.com
1. SQL Server 2005 Architecture
1.1. Growing Role of a DBA1.1.1. Production DBA1.1.2. Development DBA1.1.3. Business Intelligence DBA1.1.4. Hybrid DBA1.1.5. Industry Trends1.2. SQL Server Architecture1.2.1. Transaction Log and Database Files1.2.2. SQL Native Client1.2.3. System Databases1.2.3.1. The Resource Database1.2.3.2. The Master Database1.2.3.3. Tempdb Database1.2.3.4. Model Database1.2.3.5. msdb Database1.2.4. Schemas1.2.5. Synonyms1.2.6. Dynamic Management Views1.2.7. SQL Server 2005 Data Types1.2.7.1. Character Data Types1.2.7.2. Numeric Data Types1.2.7.3. Binary Data Types1.2.8. XML1.2.8.1. Date Time1.2.8.2. CLR Integration1.3. Editions of SQL Server1.3.1. SQL Express1.3.2. Workgroup and Standard Editions1.3.3. Enterprise, Evaluation, and Developer Editions1.3.4. Operating System1.3.5. Maximum Capacity of SQL Server1.3.6. Database Features by Edition1.3.6.1. Developer Features by Edition1.3.6.2. Business Intelligence Features by Edition1.3.6.3. DBA Features by Edition1.3.7. Licensing1.3.7.1. Modern Processor Issues1.3.7.2. Scaling and High Availability Licensing Issues1.4. Summary
2. SQL Server 2005 Installation Best Practices
2.1. Planning the System2.1.1. Hardware Choices2.1.1.1. Processors2.1.1.2. Disk Configuration2.1.1.2.1. Disk Throughput2.1.1.2.2. Best Practices2.1.1.2.3. Disk Controllers2.1.2. Software and Install Choices2.1.2.1. Collation2.1.2.1.1. SQL Server Collations2.1.2.1.2. Windows Collations2.1.2.1.3. Case Sensitivity2.1.2.2. Sort Order2.1.2.2.1. Collation Best Practices and Warnings2.1.2.3. System Files Location2.1.2.4. Disk Setup2.1.2.5. Security Considerations2.2. Installing SQL Server2.2.1. Side by Side, Upgrade, and New Install2.2.2. Scripted Installation2.2.3. Remote Installation2.2.4. Where is AdventureWorks?2.2.5. Where Are Pubs/Northwind?2.3. Installing Analysis Services2.4. Installing Reporting Services2.5. Burning in the System2.6. Post-Install Configuration2.6.1. Surface Area Configuration2.6.2. SQL Server Configuration Manager2.6.3. SQL Server Management Studio2.6.4. Tempdb2.6.5. Back it up2.7. Uninstalling SQL Server2.7.1. Uninstalling Reporting Services2.7.1.1. Delete the ReportServer Application Pool2.7.1.2. Drop the ReportServer and ReportServerTempDB Databases2.7.1.3. Drop the Remaining Directories2.7.1.4. Drop the File Association2.7.2. Uninstalling Analysis Services2.7.3. Uninstalling SQL Server Engine2.8. Help from Microsoft Product Support Services2.8.1. Installing Client Tools from a Network Share2.8.2. Admin Tools Not Installed by Default on Remote Cluster Nodes2.8.3. Minimum Configuration Warning2.9. Troubleshooting a Failed Install2.10. Summary
3. Upgrading SQL Server 2005 Best Practices
3.1. Why Upgrade to SQL Server 20053.1.1. Risk Mitigation — The Microsoft Contribution3.1.2. Independent Software Vendors (ISV) & SQL Community Contributions3.2. Upgrading to SQL Server 20053.2.1. Upgrade in Place3.2.2. Side-by-Side Upgrade3.2.3. Upgrade in Place versus Side-By-Side Upgrade Considerations3.3. Pre-Upgrade Checks3.3.1. SQL Server Upgrade Advisor3.3.2. Installing the SQL Server 2005 Upgrade Advisor3.3.3. Using the Upgrade Advisor3.3.4. Scripting the Upgrade Advisor3.3.5. Resolving Upgrade Issues3.4. SQL Server Upgrade Assistant3.4.1. Capturing the Environment3.4.2. Setting up the Baseline Server3.4.3. Running the Trace3.4.4. Upgrading to SQL Server 20053.4.5. Final Analysis3.5. Backward Compatibility3.5.1. Unsupported and Discontinued Features3.5.2. SQL Server 2005 Deprecated Database Features3.5.3. Other SQL 2005 impacting Behavior Changes3.6. SQL Server Component Considerations3.6.1. Upgrading Full-Text Catalog to SQL Server 20053.6.2. Upgrading DTS to SQL Server 2005 Integration Services (SSIS)3.6.3. Log Shipping3.6.4. Failover Clustering3.6.5. Upgrading to 64-bit3.7. Post Upgrade Checks3.8. Poor Query Performance After Upgrade3.9. Surface Area Configuration Tool3.9.1. Surface Area Configuration for Services and Connections tool3.9.2. Surface Area Configuration Features tool3.10. Update Usage Counters3.11. Summary
4. Managing and Troubleshooting the Database Engine
4.1. Configuration Tools4.1.1. SQL Server Configuration Manager4.1.2. SQL Server Surface Area Configuration4.1.3. Startup Parameters4.1.4. Startup Stored Procedures4.1.5. Rebuilding the System Databases4.2. Management Studio4.2.1. Reports4.2.1.1. Server Reports4.2.1.2. Database Reports4.2.2. Configuring SQL Server4.2.3. Filtering Objects4.2.4. Error Logs4.2.5. Activity Monitor4.2.6. Monitoring Processes in T-SQL4.2.6.1. sp_who and sp_who24.2.6.2. sys.dm_exec_connections4.2.7. DBCC INPUTBUFFER4.2.7.1. Sys.dm_exec_sql_text4.3. Trace Flags4.4. Getting Help from Support4.4.1. SQLDumper.exe4.4.2. SQLDiag.exe4.5. Summary
5. Automating SQL Server
5.1. Automation Components5.1.1. Jobs5.1.1.1. Job Step Logging5.1.1.2. Job Notifications5.1.2. Schedules5.1.3. Operators5.1.3.1. Scheduling Notifications5.1.3.2. Failsafe Operator5.1.4. Alerts5.1.4.1. Alert Event Types5.1.4.1.1. SQL Server Event5.1.4.1.2. SQL Server Performance Condition5.1.4.2. Alert Responses5.2. SQL Agent Security5.2.1. Service Account5.2.2. Access to SQL Agent5.2.2.1. SQLAgentUserRole5.2.2.2. SQLAgentReaderRole5.2.2.3. SQLAgentOperatorRole5.2.3. Proxy Accounts5.2.3.1. SQL Agent Subsystems5.2.3.2. Proxies5.2.3.2.1. Credentials5.2.3.2.2. Create Proxy5.2.3.2.3. Using Proxies5.3. Configuring SQL Server Agent5.4. Database Mail5.4.1. Architecture5.4.2. Security5.4.3. Configuration5.4.3.1. Database Mail Account5.4.3.2. Setup5.4.4. Archiving5.5. Multiserver Administration5.5.1. Using Token Replacement5.5.2. Event Forwarding5.5.3. Using WMI5.5.4. Multiserver Administration — Using Master and Target Servers5.6. Problem Resolution5.6.1. Job Failure Error 36215.6.2. Database Mail Message Failed5.6.3. Some ISPs block Email from Unknown SMTP Servers5.6.4. SQL Agent Jobs Fail after Installing Service Pack 15.7. Summary

6. Integration Services Administration and Performance Tuning
6.1. Tour of Integration Services6.1.1. Integration Services Uses6.1.2. Four Main Parts of Integration Services6.1.2.1. Integration Services Service6.1.2.2. Integration Services Runtime Engine and Runtime Components6.1.2.3. Integration Services Object Model6.1.2.4. Integration Services Dataflow Engine and Dataflow Components6.1.3. Project Management and Change Control6.2. Administration of the Integration Services Service6.2.1. Overview6.2.2. Configuration6.2.2.1. XML Configuration File6.2.2.2. Setting Service Properties Using the Windows Services Snap-in6.2.2.3. Setting Service Properties Using SQL Server Configuration Manager6.2.2.4. Configuring Windows Firewall for Access6.2.3. Event Logs6.2.4. Monitoring Activity6.3. Administration of Integration Services Packages6.3.1. Overview6.3.2. Creating Packages6.3.2.1. Using the Import and Export Wizard to Create Packages6.3.2.2. Creating and Using Package Templates6.3.3. Management6.3.3.1. Using Management Studio for Package Management6.3.3.2. Using DTUtil Package Management Utility?6.3.3.3. Importing and Exporting Packages6.3.4. Deployment6.3.4.1. Creating a Package Deployment Utility6.3.4.2. Using the Package Installer Wizard6.3.4.3. Manual Package Deployment6.3.4.3.1. Import or Export Packages Deployment6.3.4.3.2. File Save/Move Package Deployment6.3.4.3.3. DTUtil Package Deployment6.3.5. Execution and Scheduling6.3.5.1. Running Packages in Business Intelligence Development Studio6.3.5.2. Running Packages with SQL Server Import and Export Wizard6.3.5.3. Running Packages with DTExec6.3.5.4. Running Packages with DTExecUI6.3.5.5. Scheduling Execution with SQL Server Agent6.4. Applying Security to Integration Services6.4.1. Overview6.4.2. Securing Packages6.4.2.1. Package Protection Levels6.4.2.2. Database Integration Services Roles6.4.3. Saving Packages6.4.4. Running Packages6.4.5. Package Resources6.4.6. Digital Signatures6.5. Summary
7. Analysis Services Administration and Performance Tuning
7.1. Tour of Analysis Services7.1.1. Unified Dimensional Model Components7.1.1.1. Data Source View7.1.1.2. Dimensional Model7.1.1.3. Calculations (Expressions)7.1.1.4. Familiar and Abstracted Model7.1.1.5. Administrative Configuration7.1.2. Analysis Services Architectural Components7.1.2.1. Query Processor7.1.3. XMLA Listener7.1.4. XML for Analysis7.2. Administering Analysis Services Server7.2.1. Server Configuration Settings7.2.1.1. Review and Adjust Server Properties7.2.1.2. Lock Manager Properties7.2.1.3. Log Properties7.2.1.4. Memory Properties7.2.1.5. Network Properties7.2.1.6. OLAP Properties7.2.1.7. Security Properties7.2.2. Required Services7.2.3. Analysis Services Scripting Language7.3. Administering Analysis Services Databases7.3.1. Deploying Analysis Services Databases7.3.1.1. Processing Analysis Services Objects7.3.1.2. Processing Dimensions7.3.1.3. Processing Cubes7.3.1.4. Processing Partitions7.3.1.5. Reprocessing7.3.1.6. Performing Processing7.3.2. Backing Up and Restoring Analysis Services Databases7.3.3. Synchronizing Analysis Services Databases7.4. Analysis Services Performance Monitoring and Tuning7.4.1. Monitoring Analysis Services Events Using SQL Profiler7.4.2. Creating Traces for Replay7.4.3. Using Flight Recorder for After the Fact Analysis7.5. Management of Analysis Services Storage7.5.1. Storage Modes7.5.1.1. Multidimensional OLAP7.5.1.2. Relational OLAP7.5.1.3. Hybrid OLAP7.5.2. Partition Configuration7.5.3. Designing Aggregations7.6. Applying Security to Analysis Services7.6.1. Server and Database Roles7.6.1.1. Server Role7.6.1.2. Database Role7.6.1.3. Database Role Permissions7.7. Summary
8. Administering the Development Features
8.1. The Service Broker8.1.1. Service Broker Architecture8.1.1.1. Meassage Type8.1.1.2. Contracts8.1.1.3. Queue8.1.1.4. Services8.1.2. Service Broker Examples8.1.3. Activation8.1.4. Conversation Groups8.2. Security Considerations for the Service Broker8.2.1. Transport Security8.2.1.1. Windows Authentication8.2.1.2. Certificate-Based Authentication8.2.1.3. Encryption8.2.2. Routing8.2.3. Dialog Security8.3. Administering the Service Broker8.3.1. Installing the Service Broker Application8.3.1.1. Preparing the Databases8.3.1.2. Running the Scripts to Install the Application8.3.2. Setting Permissions on Service Broker Objects8.3.2.1. CONNECT Permission8.3.2.2. SEND Permission8.3.2.3. RECEIVE Permission8.3.2.4. EXECUTE Permission8.3.3. Managing Service Broker Queues8.3.4. Poison Message Handling8.3.5. Moving Service Broker Applications8.3.6. Copying Service Broker Applications8.3.7. Replacing Expired Certificates8.3.8. Troubleshooting Service Broker Applications8.3.8.1. Design and Code Review8.3.8.1.1. Not Handling All Message Types8.3.8.1.2. Not Dealing with Poison Messages8.3.8.1.3. Not Ending the Conversation8.3.8.1.4. Service Broker Commands Outside of an Explicit Transaction8.3.8.2. Using Catalog Views for Service Broker Metadata8.3.8.3. Using Catalog Views for Service Broker Data8.3.8.4. Dynamic Management Views8.3.8.5. Using SQL Profiler to View Service Broker Activities8.3.8.6. No Return Route8.3.8.7. Dropped Messages8.3.8.8. Conversation Endpoint is Not in a Valid State for SEND8.3.8.9. Queue Disabled8.3.8.10. Service Broker Is Disabled in a Database8.3.8.11. Could Not Obtain Information about Windows NT Group/User8.3.8.12. Duplicate Routes8.3.8.13. Activation Stored Procedure Is Not Being Activated8.3.9. Performance8.4. Introduction to CLR Integration8.4.1. SQL Server as .NET Runtime Host8.4.2. Application Domains8.4.3. T-SQL versus CLR8.4.4. Extended Stored Procedure versus CLR8.4.4.1. Enabling CLR Integration8.4.4.2. Creating the CLR Assembly8.4.5. Deploying the Assembly8.4.5.1. Security Notes8.4.5.2. Registering the Assembly8.4.6. ALTER ASSEMBLY8.4.7. DROP ASSEMBLY8.4.8. Cataloging Objects8.4.8.1. Assemblies8.4.8.2. CLR Stored Procedures8.4.8.3. CLR Trigger Metadata8.4.8.4. CLR Scalar Function8.4.8.5. CLR Table Valued Function8.4.8.6. CLR User-Defined Aggregates8.4.8.7. CLR User Defined Types8.4.9. Application Domains8.4.10. Performance Monitoring8.4.10.1. System Monitor8.4.10.2. SQL Profiler8.4.10.3. DMVs8.4.10.3.1. SQL CLR Memory Usage8.4.10.3.2. Loaded Assemblies8.4.10.3.3. CLR Request Status8.4.10.3.4. Time Spent in SQL CLR By a Query8.4.10.3.5. Finding the .NET Framework Version in SQL Server8.5. Summary
9. Securing the Database Engine
9.1. Logins9.1.1. Windows vs. SQL Server Authentication9.1.2. Creating the Login9.1.2.1. Defining Server Roles9.1.2.2. Granting Database Rights9.1.2.3. Defining Access to Objects9.1.2.4. The Status Page9.1.2.5. Logins with T-SQL9.1.2.6. The Security Hierarchy9.1.2.7. The Guest Account9.1.3. BUILTIN\Administrators Login9.1.4. Granting Profiler Access9.2. Credentials9.3. Users9.3.1. Granular Permissions9.3.2. Roles9.3.3. Creating Users with T-SQL9.4. Schemas9.4.1. Schema Example9.4.2. Changing Ownership9.5. Permission Context9.5.1. EXECUTE AS Command9.5.2. Troubleshooting Permission9.6. Encryption9.6.1. Creating the Certificate and Key9.6.2. Encrypting the Data9.6.3. Creating a Helper Function Example9.7. Column-Level Permissions9.8. Summary
10. Change Management
10.1. Creating Projects10.1.1. Creating a Connection10.1.2. Creating a Project Query10.2. DDL Triggers10.2.1. DDL Trigger Syntax10.2.2. Database Triggers10.2.3. Server Triggers10.2.4. Trigger Views10.3. SQLCMD10.3.1. Sqlcmd from the Command Prompt10.3.2. Sqlcmd from Management Studio10.4. Creating Change Scripts10.5. Version Tables10.6. Summary
11. Configuring the Server for Optimal Performance
11.1. What Every DBA Needs to Know about Performance11.1.1. The Performance Tuning Cycle11.1.2. Defining Good Performance11.1.3. Focus on What's Most Important11.2. What the Developer DBA Needs to Know about Performance11.2.1. Users11.2.2. Queries11.2.3. Data11.2.4. Robust Schema11.3. What the Production DBA Needs to Know about Performance11.4. Optimizing the Server11.5. Configuring Server Hardware11.5.1. Windows Server System Reference Architecture11.5.2. Windows Server Catalog11.6. CPU11.6.1. 32-bit X86 Processors11.6.2. 64-bit11.6.3. X64 or IA6411.6.4. Hyperthreading11.6.5. Cache11.6.6. Multi-Core11.6.7. System Architecture11.6.7.1. Symmetric Multi-Processing11.6.7.2. NUMA11.7. Memory11.7.1. Physical Memory11.7.2. Physical Address Space11.7.3. Virtual Memory Manager11.7.4. The Page File11.7.5. Page Faults11.7.5.1. Soft Page Faults11.7.5.2. Hard Page Faults11.7.5.3. Why Page Faults Are Bad11.7.6. Virtual Address Space11.7.7. 32-Bit System Memory Configuration11.7.7.1. Physical Address Extensions11.7.7.2. 3GB11.7.7.3. AWE11.7.7.4. /3GB or AWE?11.7.8. 64-bit Systems11.7.9. Memory Configuration Scenarios11.8. I/O11.8.1. Network11.8.2. Disks11.8.3. Storage Design11.8.3.1. No Silver Bullet in Storage Design11.8.3.2. Use the Vendors' Expertise11.8.3.3. Every System Is Different11.8.3.4. Simple Is Better11.8.3.5. More Disks11.8.3.6. Faster Disks11.8.3.7. Test11.8.3.8. Monitor11.8.4. Designing a Storage System11.8.4.1. Space11.8.4.2. How Many Disks11.8.4.3. Cost11.8.4.4. Desired I/O Characteristics11.8.4.5. RAID11.8.4.5.1. Mirror with Stripe11.8.4.5.2. Striping with Parity11.8.4.5.3. RAID-Level Recommendations11.8.4.6. Isolation11.8.4.6.1. Separate SQL Data from the Log11.8.4.6.2. Group Similar I/O Workloads11.8.4.6.3. Using tempdb11.8.5. Large Storage System Considerations: SAN Systems11.8.5.1. Disk Virtualization11.8.5.2. LUNs11.8.5.2.1. LUN Size11.8.5.2.2. Number of LUNs11.8.6. Server Configuration11.8.6.1. Disk Adapters11.8.6.1.1. Number of Adapters11.8.6.1.2. Placement11.8.6.1.3. Firmware11.8.6.1.4. Drivers11.8.6.1.5. Configuration11.8.6.2. Partitioning11.8.6.3. Mounting Volumes11.8.6.4. Start Sector Alignment11.8.6.5. File Systems—NTFS versus FAT11.8.6.6. NTFS Allocation Unit Size11.8.7. Fragmentation11.9. Summary
12. Optimizing SQL Server 2005
12.1. Application Optimization12.1.1. Defining a Workload12.1.2. System Harmony is the Goal12.2. The Silent Killer: I/O Problems12.2.1. SQL Server I/O Process Model12.2.2. Database File Placement12.2.3. Tempdb considerations12.3. Table and Index Partitioning12.3.1. Why Consider Partitioning?12.3.2. Implementing Partitioning12.3.3. Creating a partition function12.3.4. Creating Filegroups12.3.5. Creating a Partition Scheme12.3.6. Create Tables and Indexes12.3.6.1. Index Alignment12.3.6.2. Storage alignment12.4. Memory Considerations AND Enhancements12.4.1. Tuning SQL Server Memory12.4.2. 64-bit Version of SQL Server 200512.4.3. Configuring SQL 2005 for Dynamic Memory on an X86 platform12.4.4. Memory-Friendly Applications12.5. CPU Considerations12.5.1. Data Locality12.5.2. Cache Coherency12.5.3. Affinity mask12.5.3.1. SQL Server Processor Affinity Mask12.5.3.2. Default SQL Server Work Scheduling12.5.3.3. SQL Server Work Scheduling using Affinity Mask12.5.4. Context switching12.5.4.1. Privileged mode12.5.4.2. User mode12.5.4.3. Priority Boost12.5.4.4. SQL Server Lightweight Pooling12.5.5. Max degree of parallelism (MAXDOP)12.5.6. Affinity I/O mask12.5.7. Max Server Memory12.5.8. Index Creation Memory Option12.5.9. Min Memory per Query12.6. Summary
13. Monitoring Your SQL Server
13.1. Why Should You Monitor?13.1.1. Determining Your Monitoring Goals13.1.2. Choosing the Appropriate Monitoring Tools13.1.3. Evaluating Performance13.2. Performance Monitor13.2.1. Processor Usage13.2.1.1. Object: Processor - Counter: % Processor Time13.2.1.2. Object: System - Counter: % Total Processor Time13.2.1.3. Object: System - Counter: Processor Queue Length13.2.1.4. Object: Processor - Counter: % Privileged Time13.2.1.5. Object: Processor - Counter: % User Time13.2.1.6. Object: Processor - Counter: Interrupts/Sec13.2.1.7. Isolating Processor Activity Created by SQL Server13.2.1.7.1. Object: Process - Counter: % Processor Time13.2.1.7.2. Object: Process - Counter: % User Time13.2.1.8. Resolving Processor Bottlenecks13.2.1.8.1. Analyze Your Application13.2.1.8.2. Server Configuration13.2.2. Disk Activity13.2.2.1. Object: Physical Disk - Counter: % Avg. Disk Sec/Transfer13.2.2.2. Object: Physical Disk - Counter: Avg. Disk Sec/Read13.2.2.3. Object: Physical Disk - Counter: Avg. Disk Sec/Write13.2.2.4. Object: Physical Disk - Counter: % Disk Time13.2.2.5. Object: Physical Disk - Counter: Avg. Disk Queue Length13.2.2.6. Object: Physical Disk - Counter: Current Disk Queue Length13.2.2.7. Object: Physical Disk - Counter: % Disk Read Time13.2.2.8. Object: Physical Disk - Counter: % Disk Write Time13.2.2.9. Object: Physical Disk - Counter: % Idle Time13.2.2.10. Object: Physical Disk - Counter: Avg. Disk Bytes/Transfer13.2.2.11. Object: Physical Disk - Counter: Disk Bytes/Sec13.2.2.12. Isolating Disk Activity Created by SQL Server13.2.2.13. Is Disk Performance the Bottleneck?13.2.2.14. Data Analysis13.2.2.15. Resolving Disk Bottlenecks13.2.2.15.1. Analyze Your Application13.2.2.15.2. Disk Configuration and File Layout13.2.2.15.3. Increase I/O Bandwidth13.2.2.16. Disk Performance Counters13.2.3. Memory Usage13.2.3.1. Object: Memory - Counter: Available Mbytes13.2.3.2. Object: Memory - Counter: Pages/Sec13.2.3.3. Object: Memory - Counter: Committed Bytes13.2.3.4. Object: Memory - Counter: Committed Limit13.2.3.5. Isolating Memory Used by SQL Server13.2.3.5.1. Object: Process - Counter: Working Set13.2.3.5.2. Object: Process - Counter: Private Bytes13.2.3.5.3. Object: Process - Counter: Virtual Bytes13.2.3.5.4. Object: SQL Server: Buffer Manager - Counter: Buffer Cache Hit Ratio13.2.3.5.5. Object: SQL Server: Buffer Manager - Counter: Total Pages13.2.3.5.6. Object: SQL Server: Memory Manager - Counter: Total Server Memory (KB) and Target Server Memory (KB)13.2.3.6. Resolving Memory Bottlenecks13.3. Monitoring Events13.3.1. SQL Trace13.3.1.1. SQL Trace Architecture13.3.1.2. Creating a Server-Side Trace Using T-SQL Stored Procedures13.3.1.2.1. Retrieving the Trace Metadata13.3.1.2.2. Retrieving Data from the Trace File13.3.1.3. SQL Server Profiler13.3.1.3.1. Showplan XML13.3.1.3.2. Correlating a Trace with Windows Performance Log Data13.3.1.3.3. Replaying a Trace13.3.1.4. Performance Considerations When Using Trace13.3.2. Event Notification13.4. Monitoring with Dynamic Management Views and Functions13.4.1. Viewing the Locking Information13.4.2. Viewing Blocking Information13.4.3. Index Usage in a Database13.4.4. View Queries Waiting for Memory Grants13.4.5. Connected User Information13.4.6. Query Plan and Query Text for Currently Running Queries13.4.7. Memory Usage13.5. Summary
14. Performance Tuning T-SQL
14.1. Physical Query Processing14.1.1. Compilation14.1.1.1. Correctness-Related Reasons14.1.1.1.1. Schemas of Objects14.1.1.1.2. SET Options14.1.1.2. Plan Optimality-Related Reasons14.1.2. Tools and Commands for Recompilation Scenarios14.1.2.1. SQL Profiler14.1.2.2. Sys.syscacheobjects Virtual Table14.1.2.3. DBCC FREEPROCCACHE14.1.2.4. DBCC FLUSHPROCINDB (db_id)14.1.3. Parser and Algebrizer14.1.3.1. Name Resolution14.1.3.2. Type Derivation14.1.3.3. Aggregate Binding14.1.3.4. Grouping Binding14.1.4. Optimization14.1.4.1. Optimization Flow14.1.4.2. Simplification14.1.4.3. Exploration14.1.4.4. Implementation14.2. Tuning Process14.2.1. Database I/O Information14.2.2. Working with the Query Plan14.2.2.1. Estimated Execution Plan14.2.2.1.1. SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL14.2.2.1.2. SET SHOWPLAN_XML14.2.2.1.3. Graphical Estimated Showplan14.2.2.2. Actual Execution Plan14.2.2.2.1. SET STATISTICS XML ON|OFF14.2.2.2.2. SET STATISTICS PROFILE ON|OFF14.2.2.2.3. Graphical Actual Execution Plan14.2.3. Join Algorithms14.2.3.1. Nested Loop or Loop Join14.2.3.2. Hash Join14.2.3.3. Merge Join14.2.4. Index Access Methods14.2.4.1. Table Scan14.2.4.1.1. SET STATISTICS IO ON|OFF14.2.4.1.2. Clearing Caches14.2.4.2. Clustered Index Scan (Unordered)14.2.4.3. Covering Nonclustered Index Scan (Unordered)14.2.4.4. Clustered Index Scan (Ordered)14.2.4.5. Covering Nonclustered Index Scan (Ordered)14.2.4.6. Nonclustered Index Seek with Ordered Partial Scan and Lookups14.2.4.7. Clustered Index Seek with Ordered Partial Scan14.2.4.8. Fragmentation14.2.4.9. Statistics14.2.5. Data Modification Query Plan14.2.6. Partitioned Table Query Plan14.2.7. Gathering Query Plans for Analysis with SQL Trace14.3. Summary
15. Indexing Your Database
15.1. What's New for Indexes in SQL Server 200515.1.1. Partition Tables and Indexes15.1.2. Online Index Operations15.1.3. Parallel Index Operations15.1.4. Asynchronous Statistics Update15.1.5. Full Text Indexes15.1.6. Non-Key Columns in Nonclustred Indexes15.1.7. Index Lock Granularity Changes15.1.8. Indexes on XML Columns15.1.9. Dropping and Rebuilding Large Indexes15.1.10. Indexed View Enhancements15.1.11. Version Store15.1.12. Database Tuning Advisor15.2. Sample Database15.3. Partition Tables and Indexes15.3.1. Why Use Partition Tables and indexes?15.3.1.1. Faster and Easier Data Loading15.3.1.2. Faster and Easier Data Deletion or Archival15.3.1.3. Faster Queries15.3.1.4. Sliding Windows15.3.2. Prerequisites for Partitioning15.3.3. Creating Partition Tables15.3.3.1. Creating a Partition Table from an Existing Table15.3.3.2. Adding New Data to the Partition Table15.3.3.3. Deleting Data from the Partition Table15.3.3.4. Partitioning a Production System15.3.3.5. partitioning and dta15.4. Index Maintenance15.4.1. Monitoring Index Fragmentation15.4.2. Cleaning Up Indexes15.5. Database Tuning Advisor15.5.1. Using DTA to Tune Individual Queries15.5.1.1. Indexes for Updates15.5.1.2. Reassessing Inserts after Adding Update Indexes15.5.2. Too Many Indexes?15.5.3. Tuning a Workload15.6. Summary
16. Replication
16.1. Replication Overview16.1.1. Replication types16.1.1.1. Snapshot Replication16.1.1.2. Transactional Replication16.1.1.3. Merge Replication16.1.2. Replication Components16.2. Implementing Replication16.2.1. The Setup16.2.2. Setting up Distribution16.2.3. Implementing Snapshot Replication16.2.3.1. Setting Up Snapshot Publication16.2.3.2. Setting up Subscription to the Snapshot Publication16.2.3.3. Verifying Snapshot Replication16.2.4. Implementing Transactional and Merge Replication16.3. Peer-to-Peer Replication16.4. Replication Monitoring16.5. Summary
17. Database Mirroring
17.1. Overview of Database Mirroring17.1.1. Operating Modes of Database Mirroring17.1.2. Database Mirroring Example17.1.2.1. Preparing the Endpoints17.1.2.2. Preparing the Database for Mirroring17.1.2.3. Initial Synchronization between Principal and Mirror17.1.2.4. Establishing the Mirroring Session17.1.2.5. High-Safety Operating Mode with Automatic Failover17.1.2.5.1. Witness Server17.1.2.5.2. The Quorum17.1.2.6. High-Safety Operating Mode without Automatic Failover17.1.2.7. High-Performance Operating Mode17.1.3. Database Mirroring and SQL Server 2005 Editions17.1.4. Database Mirroring Catalog Views17.1.4.1. sys.database_mirroring17.1.4.2. sys.database_mirroring_witnesses17.1.4.3. sys.database_mirroring_endpoints17.1.5. Database Mirroring Role Change17.1.5.1. Automatic Failover17.1.5.2. Manual Failover17.1.5.3. Forced Service Failover17.1.6. Database Availability Scenarios17.1.6.1. Principal is Lost17.1.6.1.1. Scenario 1: Safety FULL with a Witness17.1.6.1.2. Scenario 2: Safety FULL without a Witness17.1.6.1.3. Scenario 3: SAFETY OFF17.1.6.2. Mirror is Lost17.1.6.3. Witness Is Lost17.1.6.4. Mirror and Witness Are Lost17.2. Monitoring Database Mirroring17.2.1. Monitoring Using System Monitor17.2.1.1. Counters for the Principal17.2.1.2. Counters for Mirror17.2.2. Monitoring Using Database Mirroring Monitor17.2.2.1. Principal Log: Unsent Log17.2.2.2. Principal Log: Oldest Unsent Transaction17.2.2.3. Principal Log: Time to Send Log (Estimated)17.2.2.4. Principal Log: Current Send Rate17.2.2.5. Principal Log: Current Rate of New Transaction17.2.2.6. Mirror Log: Unrestored Log17.2.2.7. Mirror Log: Time to Restore Log17.2.2.8. Mirror Log: Current Restore Rate17.2.2.9. Mirror Committed Overhead17.2.2.10. Time to send and restore all current Log (Estimated)17.2.2.11. Witness Address17.2.2.12. Operating Mode17.2.2.13. Setting Threshold on Counters and Sending Alerts17.2.3. Monitoring using SQL Profiler17.3. Troubleshooting Database Mirroring17.3.1. Troubleshooting Setup Errors17.3.2. Troubleshooting Runtime Errors17.4. Preparing the Mirror server for Failover17.4.1. Hardware, Software, and Server Configuration17.4.2. Database Availability During Planned Downtime17.4.2.1. SAFETY FULL17.4.2.2. SAFETY OFF17.4.3. SQL Job Configuration on the Mirror17.4.4. Database TRUSTWORTHY Bit on the Mirror17.4.5. Client Redirection to the Mirror17.5. Mirroring Multiple Databases17.6. Database Mirroring and Other High-Availability Solutions17.6.1. Database Mirroring and Clustering17.6.2. Database Mirroring and Transactional Replication17.6.3. Database Mirroring and Log Shipping17.7. Mirroring Event Listener Setup17.8. Database Snapshots17.9. Summary
18. Backup and Recovery
18.1. Types of Failure18.1.1. Hardware Failure18.1.2. User Error18.1.3. Application Failure18.1.4. Software Failure18.1.5. Too Much Privilege18.1.6. Local Disasters18.2. Overview of Backup and Restore18.2.1. How Backup Works18.2.2. How Restore Works18.2.3. Recovery Models18.2.4. Choosing a Model18.2.5. Switching Recovery Models18.2.6. Verifying the Backup Images18.2.7. Backup History Tables18.2.8. Permissions required for backup and restore18.2.9. Backup System Databases18.2.9.1. Master18.2.9.2. MSDB18.2.9.3. Tempdb18.2.9.4. Model18.2.10. Full-text Backup18.3. Planning for Recovery18.3.1. Recovery Requirements18.3.2. Data Usage Patterns18.3.3. Maintenance Time Window18.3.4. Other High-Availability Solutions18.4. Developing and Executing a Backup Plan18.4.1. SQL Server 2005 Management Studio18.4.2. Transact-SQL Backup Command18.5. Managing Backups18.6. Backup and Restore Performance18.7. Performing Recovery18.7.1. Restore Process18.7.1.1. Instant File Initialization18.7.1.2. Full Database Restore18.7.1.3. Partial database Restore18.7.1.4. Transaction-Log Restore18.7.1.5. File/Filegroup Restore18.7.1.6. Database Snapshot Restore18.7.1.7. Full-Text Restore18.7.1.8. History Tables Restore18.7.2. SQL Server Management Studio Restore18.7.3. T-SQL Restore Command18.7.4. Restoring System Databases18.8. Archiving Data18.8.1. SQL Server 2005 table partitioning18.8.2. Partition View18.9. Disaster Recovery Planning18.10. Summary
19. SQL Server 2005 Log Shipping
19.1. Log Shipping Deployment Scenarios19.1.1. Log Shipping as a Warm Standby Server19.1.2. Log Shipping as a Disaster Recovery Solution19.1.3. Log Shipping as a Report Database Solution19.2. Log-Shipping Architecture19.2.1. Primary Server19.2.2. Monitor Server19.3. Log Shipping Process19.4. System Requirements19.4.1. Network19.4.2. Identical Capacity Servers19.4.3. Storage19.4.4. Monitor Server19.4.5. Software19.5. Deploying Log Shipping19.5.1. Initial Configuration19.5.2. Deploying with Management Studio19.5.3. Deploying with T-SQL commands19.6. Monitoring and Troubleshooting19.6.1. Monitoring with Management Studio19.6.2. Monitoring with Stored Procedures19.6.3. Troubleshooting Approach19.7. Managing Changing Roles19.7.1. Synchronizing Dependencies19.7.1.1. Login and Database Users19.7.1.2. SQL Agent Jobs19.7.1.3. Other Database Dependencies19.7.2. Switching Roles from the Primary to Secondary Servers19.7.2.1. Planned Failover19.7.2.2. Unplanned Role Change19.7.3. Switching Between Primary and Secondary Roles19.7.4. Redirecting Clients to Connect to the Secondary Server19.7.4.1. Application Coding19.7.4.2. Network Load Balancing19.7.4.3. Domain Name Service (DNS)19.7.4.4. SQL Client Aliasing19.8. Database Backup Plan19.9. Integrating Log Shipping with other High Availability Solutions19.9.1. SQL Server 2005 Data Mirroring19.9.2. Windows Failover Clustering19.9.3. SQL Server 2005 Replication19.10. Removing Log Shipping19.10.1. Removing Log Shipping with Management Studio19.10.2. Removing Log Shipping with T-SQL Commands19.11. Log-Shipping Performance19.12. Upgrading to SQL Server 2005 Log Shipping19.12.1. Minimum Downtime Approach19.12.2. With Downtime Approach19.12.3. Deploy Log Shipping Approach19.12.4. Removing SQL Server 2000 Log-Shipping Tables and Jobs19.13. Summary
20. Clustering SQL Server 2005
20.1. Clustering and Your Organization20.1.1. What Clustering Can Do20.1.2. What Clustering Cannot Do20.1.3. Choosing SQL Server 2005 Clustering for the Right Reasons20.1.4. Alternatives to Clustering20.1.4.1. Warm Backup Server20.1.4.2. Hot Backup Server20.1.4.3. Log Shipping20.1.4.4. Replication20.1.4.5. Database Mirroring20.1.4.6. Third-Party Clustering Solutions20.1.4.7. What to Do20.2. Clustering: The Big Picture20.2.1. How Clustering Works20.2.1.1. Active vs. Passive Nodes20.2.1.2. Shared Disk Array20.2.1.3. The Quorum20.2.1.4. Public and Private Networks20.2.1.5. The Virtual Server20.2.1.6. How a Failover Works20.2.2. Clustering Options?20.2.2.1. Active/Passive vs. Active/Active20.2.2.2. Multinodes Clusters20.2.2.2.1. Purchase the Right Software20.2.2.2.2. Number of Nodes to Use?20.2.2.3. Clustering Multiple Instances of SQL Server on the Same Server20.3. Upgrading SQL Server Clustering20.3.1. Don't Upgrade20.3.2. Upgrading Your SQL Server 2005 Cluster In-Place20.3.3. Rebuilding Your Cluster from Scratch20.3.4. Backout Plan20.3.5. Which Upgrade Option is Best?20.4. Getting Prepared for Clustering20.4.1. Preparing the Infrastructure20.4.2. Preparing the Hardware20.4.2.1. Finding Your Way Through the Hardware Jungle20.4.2.2. The Hardware Compatibility List20.4.2.3. Preparing the Hardware20.5. Clustering Windows Server 200320.5.1. Before Installing Windows 2003 Clustering20.5.2. Installing Windows Server 2003 Clustering20.5.2.1. Installing the First Cluster Node20.5.2.2. Installing the Second Node of Your Cluster20.5.2.3. Verifying the Nodes with Cluster Administrator20.5.3. Configuring Windows Server 2003 for Clustering20.5.4. Test, Test, and Test Again20.5.4.1. Preparing for the Tests20.5.4.2. Move Groups Between Nodes20.5.4.3. Manually Initiate a Failover in Cluster Administrator20.5.4.4. Manually Failover Nodes by Turning Them Off20.5.4.5. Manually Failover Nodes by Breaking the Public Network Connections20.5.4.6. Manually Failover Nodes by Breaking the Shared Array Connection20.6. Configuring the Microsoft Distributed Transaction Coordinator20.7. Clustering SQL Server 200520.7.1. Clustering SQL Server20.7.2. Clustering Analysis Services20.7.3. Installing the Service Pack and Hot Fixes20.7.4. Checking the SQL Server 2005 Installation From Cluster Administrator20.7.5. Installing Clustering on Multiple Nodes20.7.6. Test, Test, and Test Again20.7.6.1. Preparing for the Testing20.7.6.2. Move Groups Between Nodes20.7.6.3. Manually Initiate a Failover in Cluster Administrator20.7.6.4. Manually Fail Over Nodes by Turning Them Off20.7.6.5. Manually Fail Over Nodes by Breaking the Public Network Connections20.7.6.6. Manually Fail Over Nodes by Breaking the Shared Array Connection20.8. Maintaining the Cluster20.8.1. Learn to Love Cluster Administrator20.8.2. Doing the Routine20.8.2.1. Checking the Cluster20.8.2.2. Reviewing Logs20.8.3. Dealing with Cluster Failovers20.8.3.1. Removing the Failed Node20.8.3.2. Re-Adding the New Node20.8.3.3. What Happens if My Disk Array Dies?20.8.4. Installing Patches and Service Packs20.8.4.1. Operating System Patches and Service Packs20.8.4.2. SQL Server 2005 Service Packs and Hot Fixes20.8.5. Test, Test, and Test Regularly20.9. Troubleshooting Cluster Problems20.9.1. How to Approach Clustering Troubleshooting20.9.2. Do It Right the First Time20.9.3. Gathering Information20.9.4. Resolving Problems20.9.5. Working with Microsoft20.10. Summary
A. Raymond James Lab Report
A.1. Summary

Content preview from Professional SQL Server™ 2005 Administration

Chapter 9. Securing the Database Engine

In this chapter, you learn how to secure your data and the core database engine. As you can imagine, security is a huge topic and can easily turn into an entire book by itself. This chapter covers core topics such as setting up your logins and users for the minimal data access to do their job. This is called the principle of least access. Also, we cover how to do encryption inside T-SQL, which is now part of SQL Server 2005.

Logins

Logins are the essential element for any person who would like to connect to your SQL Server instance. There is a key differentiating factor when a DBA uses the word login versus the word user. A login grants you instance-level rights to an instance, and a user gives you database-level rights to a database. It's similar to the login giving you the key to the front door with no access to any room, unless you have the user key. Certain types of rights at server-level roles will grant you rights to do anything you'd like to do, almost like a skeleton key in a building. We'll cover those server roles in a moment.

Windows vs. SQL Server Authentication

There are two types of logins you can enable in SQL Server: Windows and SQL Server authentication. Windows authentication is the most secure solution for most environments, if you have the luxury of using this type of authentication. With Windows authentication, you would grant the rights to the database to the user's Windows login. A better solution would be to create a group ...

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.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Professional Microsoft® SQL Server® 2008 Administration

Publisher Resources

ISBN: 9780470055205Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design