Walters / Fritchey / Taglienti Beginning SQL Server 2008 Administration

1. ed
ISBN: 978-1-4302-2414-3
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 400 Seiten

Beginning SQL Server 2008 Administration is essential for anyone wishing to learn about implementing and managing SQL Server 2008 database. From college students, to experienced database administrators from other platforms, to those already familiar with SQL Server and wanting to fill in some gaps of knowledge, this book will bring all readers up to speed on the enterprise platform Microsoft SQL Server 2008. Clearly describes relational database concepts Explains the SQL Server database engine and supporting tools Shows various database maintenance scenarios

Rob Walters is a data platform technology specialist for Microsoft. His extensive experience with Microsoft SQL Server began over 10 years ago when he worked as a consultant for Microsoft Consulting Services in Denver, Colorado. Shortly after the dot-com bubble burst, Walters returned to Microsoft s headquarters and worked as a program manager in the SQL Server product unit. There, he owned various features within SQL Server including SQL Server Agent, various management features, and the security for the database engine. Walters has co-authored three patents for technologies used within SQL Server. Walters has also co-authored Programming Microsoft SQL Server 2005 by Microsoft Press, and Pro SQL Server 2005, Accelerated SQL Server 2008, and Beginning SQL Server 2008 Database Administration by Apress. He holds a bachelor's of science degree in electrical engineering from Michigan State University and a master's degree in business administration from Seattle University. When not at work, he is very active in his community as an on-call firefighter.

Autoren/Hrsg.

Walters, Robert

Fritchey, Grant

Taglienti, Carmen

Inhaltsverzeichnis

1;Contents at a Glance;4
2;Table of Contents;5
3;About the Authors;15
4;About the Technical Reviewer;17
5;Acknowledgments;18
6;Introduction;19
6.1;Who This Book Is For;20
6.2;How This Book Is Structured;20
6.3;Getting the Most from This Book;22
6.4;Errata;22
6.5;Contacting the Authors;22
7;Chapter 1: The Database Administration Profession;23
7.1;Why Do We Need a DBA? Access Runs Great!;23
7.2;What Exactly Does a DBA Do Anyway?;24
7.2.1;Database Maintenance;24
7.2.2;Security and Regulatory Compliance;25
7.2.3;Disaster Recovery;25
7.2.4;Design and Performance Improvement;25
7.2.5;Documentation;26
7.3;Salary Information;26
7.4;Words from Real-World DBAs;26
7.4.1;Grant Fritchey, Principal DBA;26
7.4.2;Roman Rehak, Principal Database Architect;28
7.5;Summary;30
8;Chapter 2: Planning and Installing SQL Server 2008;31
8.1;Understanding the Editions;31
8.1.1;Demystifying 32-Bit and 64-Bit Architectures;32
8.1.2;Server Editions;32
8.1.2.1;Enterprise Edition (x86, x64, and IA64);33
8.1.2.2;Standard Edition (x86 and x64);33
8.1.2.3;Specialized Editions;33
8.1.2.4;Developer Edition (x86, x64, and IA64);33
8.1.2.5;Workgroup (x86 and x64);33
8.1.2.6;Web (x86 and x64);33
8.1.2.7;Express (x86 and x64);34
8.1.2.8;Compact (x86 Only);34
8.1.2.9;Evaluation (x86, x64, and IA64);34
8.1.3;SQL Server Terminology;34
8.2;Planning Your Installation;34
8.2.1;Assessing the Environment;35
8.2.2;Choosing the Right Edition;37
8.2.3;Validating Hardware and Software Requirements;38
8.3;Installing SQL Server Evaluation Edition;38
8.3.1;Step 1: Downloading SQL Server Evaluation Edition;38
8.3.2;Step 2: Understanding the SQL Server Installation Center;39
8.3.2.1;Planning Tab;39
8.3.2.2;Installation Tab;42
8.3.2.3;Maintenance Tab;43
8.3.2.4;Tools Tab;44
8.3.2.5;Resources Tab;45
8.3.2.6;Advanced Tab;46
8.3.2.7;Options Tab;47
8.3.3;Step 3: Installing an Instance of SQL Server;48
8.3.3.1;Feature Selection Page;48
8.3.3.2;Instance Configuration Page;50
8.3.3.3;Disk Space Requirements Page;51
8.3.3.4;Server Configuration Page;51
8.3.3.5;Database Engine Configuration Page;52
8.3.3.6;Error and Usage Reporting Page;55
8.3.3.7;Installation Rules Page;55
8.3.3.8;Ready to Install Page;55
8.4;Upgrading SQL Server;57
8.5;A Word from the SQL Server Setup Team;60
8.6;Summary;61
9;Chapter 3: What’s in the Toolbox?;63
9.1;SQL Server Management Studio;63
9.1.1;Connecting to SQL Server;63
9.1.2;Issuing Queries Using SSMS;66
9.1.2.1;Object Explorer;67
9.1.2.2;Writing the Query;70
9.1.2.3;Document Windows;72
9.1.2.4;Results Pane;73
9.1.3;Managing Multiple Servers;73
9.1.3.1;Registered Servers;73
9.1.3.2;Queries Against Multiple Servers;75
9.1.3.3;Policy-Based Management;75
9.1.4;Monitoring Server Activity;75
9.1.4.1;Activity Monitor;76
9.1.4.2;Performance Data Collector;77
9.1.4.3;Reports;80
9.2;Other Tools from the Start Menu;81
9.2.1;Analysis Services Folder;82
9.2.2;Configuration Tools Folder;83
9.2.2.1;SQL Server Installation Center;83
9.2.2.2;SQL Server Error and Usage Reporting;83
9.2.2.3;Reporting Services Configuration Manager;85
9.2.2.4;SQL Server Configuration Manager;86
9.2.3;Documentation and Tutorials Folder;88
9.2.3.1;Microsoft SQL Server Samples Overview;88
9.2.3.2;SQL Server Books Online;89
9.2.3.3;SQL Server Tutorials;89
9.2.4;Integration Services Folder;89
9.2.4.1;Data Profiler Viewer;89
9.2.4.2;Execute Package Utility;89
9.2.5;Performance Tools Folder;90
9.2.5.1;Database Engine Tuning Advisor;90
9.2.5.2;SQL Server Profiler;90
9.2.5.3;Import and Export Data Wizard;90
9.2.5.4;Business Intelligence Development Studio (BIDS);90
9.3;Command-Line Tools;91
9.3.1;SQLCMD;91
9.3.1.1;Connecting to SQL Server;91
9.3.1.2;Passing Variables;92
9.3.2;PowerShell Provider for SQL Server;93
9.4;Summary;94
10;Chapter 4: Creating Tables and Other Objects;96
10.1;Navigating the Object Explorer Tree;96
10.2;Scripting the Actions of an SSMS Dialog Box;97
10.3;Transact-SQL (T-SQL) Primer;100
10.3.1;Data Definition Language (DDL);101
10.3.2;Data Manipulation Language (DML);101
10.3.3;Data Control Language (DCL);102
10.4;Creating Tables;103
10.4.1;Creating Tables from the Table Designer;103
10.4.2;Issuing the CREATE TABLE Statement;104
10.5;Altering Tables;106
10.6;Adding Constraints;106
10.6.1;NULL Constraints;106
10.6.2;CHECK Constraints;107
10.6.3;Primary Key and Unique Constraints;107
10.6.4;Foreign Key Constraints;108
10.7;Dropping Tables;109
10.8;Creating Indexes;111
10.9;Summary;115
11;Chapter 5: Transact-SQL;117
11.1;The VetClinic Sample Database Revisited;117
11.2;Data Types;118
11.2.1;Unicode vs. ANSI;119
11.2.2;Living with NULL;120
11.3;Data Manipulation Language;120
11.3.1;SELECT;120
11.3.2;INSERT;123
11.3.3;UPDATE;123
11.3.4;DELETE;124
11.4;Transactions;124
11.4.1;Execution;124
11.4.2;Transaction Isolation;125
11.4.3;Deadlocks;126
11.5;Stored Procedures;128
11.5.1;Creating Stored Procedures Using Templates;129
11.5.2;Modifying Stored Procedures;132
11.5.3;System Stored Procedures;132
11.6;Functions;133
11.6.1;Creating a Function;133
11.6.2;Invoking a Function;133
11.6.2.1;System-Defined Functions;134
11.7;Triggers;134
11.8;Summary;135
12;Chapter 6: SQL Server Internals;137
12.1;Databases;137
12.1.1;Master Database;137
12.1.2;Tempdb Database;137
12.1.3;Model Database;138
12.1.4;MSDB Database;138
12.1.5;Resource Database;139
12.2;Repairing Corrupt System Databases;139
12.3;Writing Data to Disk;139
12.4;SQL Server Services;140
12.5;Single-User Mode;142
12.5.1;Placing an Already-Started Database into Single-User Mode;142
12.5.2;Starting SQL Server in Single-User Mode;143
12.6;Summary;146
13;Chapter 7: Storage Management Strategies;147
13.1;Storage Systems;147
13.2;Storage System Interfaces;149
13.2.1;IDE ATA;149
13.2.2;SATA;149
13.2.3;SCSI;149
13.2.4;Serial Attached SCSI;149
13.2.5;Fibre Channel Direct Attached Storage;150
13.2.6;Fibre Channel Storage Area Network;150
13.2.7;iSCSI;150
13.2.8;InfiniBand;150
13.3;Storage System Types;150
13.3.1;Direct Attached Storage;150
13.3.2;Storage Area Network;151
13.3.2.1;Logical Unit Numbers (LUNs);153
13.3.3;Network Attached Storage;153
13.3.4;Figure 7-3. NAS architecture;154
13.4;Disk Configuration: RAID;155
13.4.1;RAID 0 (Striping);155
13.4.2;RAID 1 (Mirroring and Duplexing);155
13.4.3;RAID 10 (Stripe of Mirrors);156
13.4.4;RAID 5 (Striping with Parity);157
13.5;Hardware and Software RAID;159
13.6;Selecting a Storage System for SQL Server 2008;159
13.6.1;I/O Performance;159
13.6.2;Redundancy Is Critical;161
13.7;Configuring Your Database;165
13.7.1;Data Compression;167
13.7.2;Disk Volume Alignment;169
13.8;Summary;169
14;Chapter 8: Database Backup Strategies;170
14.1;Defining the Types of Backups;170
14.1.1;Full Backups;171
14.1.1.1;Full Backup Using SQL Server Management Studio;171
14.1.1.2;Full Backup Using T-SQL;176
14.1.2;Differential Backups;177
14.1.3;File and Filegroup Backups;178
14.2;Backing Up the Logs;180
14.2.1;Initiating Fully Recovery Mode;181
14.2.2;Log Backup Using Management Studio;182
14.2.3;Log Backup Using T-SQL;183
14.2.4;Backing Up a Copy of the Database;183
14.3;Automating the Backup Process;183
14.4;Encrypting Backup Files for Security;195
14.5;Compressing Backups to Save Space;196
14.6;Designing a Backup Plan;199
14.7;Summary;201
15;Chapter 9: Database Restore Strategies;202
15.1;Restoring a Database from the Management Studio GUI;202
15.2;Specifying Restore Options from the GUI;210
15.3;Restoring a Database Using T-SQL;212
15.3.1;Executing a Simple Restore;212
15.3.2;Creating a Copy of a Database;212
15.3.3;Retrieving Logical and Physical File Names;214
15.3.4;Displaying General Information About a Backup;216
15.3.5;Cleaning Up;217
15.4;Restoring Differential Backups;217
15.5;Restoring Log Backups;219
15.6;Restoring File Backups;223
15.7;Testing Backups;225
15.8;Developing a Full Disaster Recovery Plan;226
15.8.1;Gathering Information About the Business;226
15.8.2;Establishing the Disaster Plan;227
15.8.2.1;Scenario 1: Large Commercial Insurance Company;227
15.8.2.2;Scenario 2: Small Online Retail Business;228
15.8.3;Testing the Disaster Plan;228
15.9;Summary;229
16;Chapter 10: Common Database Maintenance Tasks;230
16.1;Backing Up and Restoring;230
16.2;Checking the Database Integrity;231
16.3;Shrinking the Database;234
16.4;Reorganizing and Rebuilding Indexes;237
16.4.1;Detecting Undue Fragmentation;237
16.4.2;Rebuilding an Index;238
16.4.3;Reorganizing an Index;239
16.5;Getting Updated Statistics;239
16.5.1;Checking the Status of Automatic Statistics Gathering;240
16.5.2;Manually Updating Statistics;240
16.6;Monitoring Logs;241
16.7;Creating SQL Server Agent Jobs;244
16.7.1;Connecting to SQL Server;245
16.7.2;Creating an Agent Job;246
16.8;Defining Alerts;253
16.9;Summary;257
17;Chapter 11: Automation Through SQLCMD and PowerShell;258
17.1;Scripting in SQLCMD;258
17.2;Executing Commands Interactively;258
17.3;Executing Script in Batch Mode;259
17.4;Testing SQLCMD Scripts;261
17.5;Using SQLCMD to Back Up a Database;263
17.6;Generating Scripts from Management Studio;264
17.7;Scripting in PowerShell;266
17.7.1;Configuring PowerShell for Use With SQL Server;267
17.7.2;Understanding the Components;269
17.7.3;Starting PowerShell in Interactive Mode;270
17.7.4;Specifying an Execution Policy;272
17.7.5;Executing a PowerShell Script Interactively;272
17.7.6;Running PowerShell Scripts in Batch Mode;276
17.7.7;Running PowerShell from SQL Server Agent;277
17.7.8;Running PowerShell Scripts from Maintenance Plans;278
17.7.9;Using the PowerShell Integrated Scripting Environment;279
17.8;Summary;281
18;Chapter 12: Database Maintenance Plans;282
18.1;Understanding the Fundamentals;284
18.2;Creating a Maintenance Plan;286
18.3;Starting the Maintenance Plan Wizard;286
18.3.1;Specifying Plan Properties;287
18.3.2;Specifying Job Schedule Properties;288
18.3.3;Selecting Maintenance Tasks;289
18.3.4;Selecting Maintenance Task Order;290
18.4;Configuring Individual Tasks;291
18.4.1;Check Database Integrity;291
18.4.2;Shrink Database;293
18.4.3;Reorganize Index;294
18.4.4;Rebuild Index;295
18.4.5;Update Statistics;297
18.4.6;History Cleanup;298
18.4.7;Back Up Database (Full);298
18.4.8;Maintenance Cleanup;300
18.4.9;Select Report Options;301
18.5;Completing the Maintenance Plan Wizard;301
18.6;Modifying an Existing Maintenance Plan;303
18.7;Summary;304
19;Chapter 13: Performance Tuning and Optimization;306
19.1;Measuring SQL Server Performance;306
19.1.1;Performance Counters;307
19.1.2;Performance Monitor;308
19.1.3;Dynamic Management Views;312
19.1.4;Data Collector;314
19.1.5;Setting Up the Data Collector;315
19.1.6;Viewing the Data Collector Data;319
19.1.6.1;Server Activity History;319
19.1.6.2;Disk Usage Summary;322
19.1.6.3;Query Statistics History;324
19.2;Tuning Queries;327
19.2.1;Understanding Execution Plans;327
19.2.2;Gathering Query Information with Profiler;334
19.2.2.1;Working with Trace Data;337
19.2.2.2;Using Trace to Capture Execution Plans;338
19.2.3;Using the Database Engine Tuning Advisor;339
19.3;Managing Resources;342
19.4;Limiting Resource Use;342
19.4.1;Leveraging Data Compression;344
19.5;Summary;345
20;Chapter 14: SQL Server Security;346
20.1;Terminology;346
20.1.1;Authentication;346
20.1.2;Authentication Mode;347
20.1.3;Authorization;347
20.1.4;Server Instance vs. the Database;347
20.2;SQL Server Instance Security;349
20.2.1;Creating a SQL Server Login;349
20.2.2;Server Roles;351
20.2.3;Server Permissions;352
20.2.4;Endpoints;354
20.3;Database Security;356
20.3.1;Database Users;356
20.3.2;Schemas;356
20.3.2.1;An Example of the “Wrong” Way;356
20.3.2.2;The “Right” Way;357
20.3.2.3;Four-Part Naming Convention;358
20.3.2.4;Default Schema;358
20.3.2.5;Reassigning Schema Ownership;359
20.3.3;Fixed Database Roles;359
20.3.3.1;Database Permissions;360
20.3.4;Flexible Database Roles;362
20.3.5;Security Functions;363
20.3.5.1;fn_my_permissions() Function;363
20.3.5.2;HAS_PERMS_BY_NAME Function;364
20.4;Summary;364
21;Chapter 15: Auditing, Encryption, and Compliance;365
21.1;Auditing in SQL Server 2008;366
21.1.1;Auditing Objects;367
21.1.2;Server Audit Object;368
21.1.3;Server Audit Specification Object;370
21.1.4;Database Audit Specification Object;374
21.2;Encryption;376
21.2.1;Encryption Primer;376
21.2.2;Password-Based Encryption;377
21.2.3;Certificate-Based Encryption;380
21.2.4;Transparent Data Encryption;381
21.3;Validating Server Configuration;382
21.3.1;The Need for a Policy;383
21.3.2;Create Policy on a Local Server;383
21.3.3;Evaluate the Policy;386
21.3.4;Using the Central Management Server;388
21.4;Summary;389
22;Chapter 16: SQL Server in the Enterprise;390
22.1;Systems Center Operations Manager 2007;390
22.2;Installing the SQL Server SCOM Management Pack;392
22.3;Getting Familiar with the SQL Server Management Pack;395
22.4;Finding and Resolving an Issue;398
22.5;Using System Center Data Protection Manager;402
22.5.1;Setting Up a Protection Group;404
22.5.2;Executing DPM Reports;407
22.5.3;Recovering a Databases;409
22.6;Summary;411
23;Chapter 17: Where to Go Next?;412
23.1;Visit SQL Server Books Online;412
23.2;Keep Up with Public SQL Server Websites;412
23.3;Attend a Conference;413
23.4;Find a Local Users Group;413
23.5;Create a Lab System and Practice;413
23.6;Get Certified!;416
24;Index;417