Microsoft SQL Server 2005 Unleashed

Microsoft® SQL Server 2005 Unleashed......Page 1
Table of Contents......Page 6
Introduction......Page 44
Who This Book Is For......Page 45
What This Book Covers......Page 46
Conventions Used in This Book......Page 48
Good Luck!......Page 50
Part I: Welcome to Microsoft SQL Server......Page 52
SQL Server Components and Features......Page 54
SQL Server 2005 Editions......Page 68
SQL Server Licensing Models......Page 72
Summary......Page 76
New SQL Server 2005 Features......Page 78
SQL Server 2005 Enhancements......Page 88
Summary......Page 97
Part II: SQL Server Tools and Utilities......Page 98
What’s New in SSMS......Page 100
The Integrated Environment......Page 101
Administration Tools......Page 107
Development Tools......Page 114
Summary......Page 130
4 SQL Server Command-Line Utilities......Page 132
What’s New in SQL Server Command-Line Utilities......Page 133
The sqlcmd Command-Line Utility......Page 134
The dta Command-Line Utility......Page 138
The tablediff Command-Line Utility......Page 141
The sac Command-Line Utility......Page 144
The bcp Command-Line Utility......Page 147
The sqldiag Command-Line Utility......Page 148
The sqlservr Command-Line Utility......Page 150
Removed or Deprecated Utilities in SQL Server 2005......Page 151
Summary......Page 152
What’s New with SQL Server Profiler......Page 154
SQL Server Profiler Architecture......Page 155
Creating Traces......Page 156
Saving and Exporting Traces......Page 166
Replaying Trace Data......Page 172
Defining Server-Side Traces......Page 174
Profiler Usage Scenarios......Page 187
Summary......Page 195
Part III: SQL Server Administration......Page 196
What’s New in SQL Server System and Database Administration......Page 198
System Administrator Responsibilities......Page 199
System Databases......Page 200
System Tables......Page 203
System Views......Page 204
System Stored Procedures......Page 213
Summary......Page 215
Installation Requirements......Page 216
Installation Walkthrough......Page 222
Unattended Installation......Page 234
Installing SP1......Page 236
Summary......Page 238
What’s New in Upgrading SQL Server......Page 240
Using the SQL Server Upgrade Advisor (UA)......Page 241
Destination: SQL Server 2005......Page 246
Unattended Upgrades......Page 262
Summary......Page 263
What’s New in Client Installation and Configuration......Page 264
Client/Server Networking Considerations......Page 265
Client Installation......Page 271
Client Configuration......Page 274
Client Data Access Technologies......Page 280
Summary......Page 289
What’s New in Security and User Administration......Page 290
An Overview of SQL Server Security......Page 291
Authentication Methods......Page 292
Managing Principals......Page 294
Managing Securables......Page 308
Managing Permissions......Page 309
Managing SQL Server Logins......Page 311
Managing SQL Server Users......Page 316
Managing Database Roles......Page 319
Managing SQL Server Permissions......Page 320
The Execution Context......Page 329
Summary......Page 332
What’s New in Database Backup and Restore......Page 334
Developing a Backup and Restore Plan......Page 335
Types of Backups......Page 337
Recovery Models......Page 339
Backup Devices......Page 343
Backing Up a Database......Page 345
Backing Up the Transaction Log......Page 350
Backup Scenarios......Page 353
Restoring Databases and Transaction Logs......Page 360
Restore Scenarios......Page 369
Additional Backup Considerations......Page 378
Summary......Page 381
Setting Up Database Mail......Page 382
Sending and Receiving with Database Mail......Page 390
Using SQL Server Agent Mail......Page 397
Related Views and Procedures......Page 400
Summary......Page 403
What’s New in Scheduling and Notification......Page 404
Configuring the SQL Server Agent......Page 405
Viewing the SQL Server Agent Error Log......Page 411
Managing Operators......Page 413
Managing Jobs......Page 416
Managing Alerts......Page 424
Scripting Jobs and Alerts......Page 430
Multiserver Job Management......Page 431
Event Forwarding......Page 433
Summary......Page 434
14 SQL Server High Availability......Page 436
What’s New in High Availability......Page 437
What Is High Availability?......Page 438
The Fundamentals of HA......Page 439
Building Solutions with One or More HA Options......Page 443
Other HA Techniques That Yield Great Results......Page 451
High Availability from the Windows Server Family Side......Page 453
Summary......Page 455
15 Replication......Page 458
What’s New in Data Replication......Page 459
What Is Replication?......Page 460
The Publisher, Distributor, and Subscriber Metaphor......Page 461
Replication Scenarios......Page 468
Subscriptions......Page 476
Replication Agents......Page 479
Planning for SQL Server Data Replication......Page 486
SQL Server Replication Types......Page 487
Basing the Replication Design on User Requirements......Page 490
Setting Up Replication......Page 493
Scripting Replication......Page 513
Monitoring Replication......Page 514
Summary......Page 523
What’s New in Database Mirroring......Page 524
What Is Database Mirroring?......Page 525
Roles of the Database Mirroring Configuration......Page 528
Setting Up and Configuring Database Mirroring......Page 529
Testing Failover from the Principal to the Mirror......Page 550
Client Setup and Configuration for Database Mirroring......Page 552
Using Replication and Database Mirroring Together......Page 554
Using Database Snapshots from a Mirror for Reporting......Page 555
Summary......Page 557
17 SQL Server Clustering......Page 558
How Microsoft SQL Server Clustering Works......Page 559
Installing SQL Server Clustering......Page 567
Summary......Page 586
Part IV: Database Administration......Page 588
18 Creating and Managing Databases......Page 590
Data Storage in SQL Server......Page 591
Database Files......Page 592
Creating Databases......Page 598
Setting Database Options......Page 603
Managing Databases......Page 609
Summary......Page 620
What’s New in SQL Server 2005......Page 622
Creating Tables......Page 623
Defining Columns......Page 627
Defining Table Location......Page 637
Defining Table Constraints......Page 639
Modifying Tables......Page 641
Dropping Tables......Page 648
Partitioned Tables......Page 650
Summary......Page 665
What’s New in Creating and Managing Indexes......Page 666
Types of Indexes......Page 667
Creating Indexes......Page 670
Managing Indexes......Page 676
Online Indexing Operations......Page 680
Indexes on Views......Page 682
Summary......Page 683
What’s New in Data Integrity......Page 684
Enforcing Data Integrity......Page 685
Using Constraints......Page 686
Rules......Page 702
Defaults......Page 704
Summary......Page 708
Definition of Views......Page 710
Using Views......Page 712
Creating Views......Page 717
Managing Views......Page 724
Data Modifications and Views......Page 726
Partitioned Views......Page 727
Indexed Views......Page 733
Summary......Page 740
What’s New in Creating and Managing Stored Procedures......Page 742
Advantages of Stored Procedures......Page 743
Creating Stored Procedures......Page 744
Executing Stored Procedures......Page 753
Deferred Name Resolution......Page 758
Viewing Stored Procedures......Page 762
Modifying Stored Procedures......Page 765
Using Input Parameters......Page 767
Using Output Parameters......Page 774
Returning Procedure Status......Page 775
Using Cursors in Stored Procedures......Page 776
Nested Stored Procedures......Page 786
Using Temporary Tables in Stored Procedures......Page 792
Using Remote Stored Procedures......Page 798
Debugging Stored Procedures Using Microsoft Visual Studio .NET......Page 799
Using System Stored Procedures......Page 803
Stored Procedure Performance......Page 805
Using Dynamic SQL in Stored Procedures......Page 815
Startup Procedures......Page 821
T-SQL Stored Procedure Coding Guidelines......Page 824
Creating and Using CLR Stored Procedures......Page 834
Using Extended Stored Procedures......Page 836
Summary......Page 841
What’s New in SQL Server 2005......Page 842
Why Use User-Defined Functions?......Page 843
Types of User-Defined Functions......Page 845
Creating and Managing User-Defined Functions......Page 850
Systemwide Table-Valued Functions......Page 868
Rewriting Stored Procedures as Functions......Page 869
Creating and Using CLR Functions......Page 870
Summary......Page 874
25 Creating and Managing Triggers......Page 876
Using DML Triggers......Page 877
Using DDL Triggers......Page 902
Using CLR Triggers......Page 909
Using Nested Triggers......Page 912
Using Recursive Triggers......Page 913
Summary......Page 914
What’s New in Transaction Management......Page 916
How SQL Server Manages Transactions......Page 917
Defining Transactions......Page 918
Transaction Logging and the Recovery Process......Page 928
Transactions and Batches......Page 940
Transactions and Stored Procedures......Page 942
Transactions and Triggers......Page 947
Transactions and Locking......Page 954
Coding Effective Transactions......Page 955
Long-Running Transactions......Page 956
Bound Connections......Page 958
Summary......Page 961
27 Database Snapshots......Page 962
What Are Database Snapshots?......Page 963
Limitations and Restrictions of Database Snapshots......Page 968
Copy-on-Write Technology......Page 969
When to Use Database Snapshots......Page 970
Setup and Breakdown of a Database Snapshot......Page 975
Reverting to a Database Snapshot for Recovery......Page 981
Setting Up Snapshots Against a Database Mirror......Page 983
Database Snapshots Maintenance and Security Considerations......Page 985
Summary......Page 986
What’s New in Database Maintenance......Page 988
The Maintenance Plan Wizard......Page 989
Managing Maintenance Plans Without the Wizard......Page 1002
Executing a Maintenance Plan......Page 1007
Summary......Page 1008
Part V: SQL Server Performance and Optimization......Page 1010
29 Indexes and Performance......Page 1012
Understanding Index Structures......Page 1013
Index Utilization......Page 1018
Index Selection......Page 1021
Evaluating Index Usefulness......Page 1022
Index Statistics......Page 1025
SQL Server Index Maintenance......Page 1041
Index Design Guidelines......Page 1056
Indexed Views......Page 1064
Indexes on Computed Columns......Page 1065
Choosing Indexes: Query Versus Update Performance......Page 1067
Summary......Page 1069
30 Understanding Query Optimization......Page 1070
What’s New in Query Optimization......Page 1071
Query Compilation and Optimization......Page 1073
Query Analysis......Page 1075
Row Estimation and Index Selection......Page 1077
Join Selection......Page 1102
Execution Plan Selection......Page 1113
Query Plan Caching......Page 1115
Other Query Processing Strategies......Page 1126
Parallel Query Processing......Page 1129
Common Query Optimization Problems......Page 1133
Managing the Optimizer......Page 1137
Summary......Page 1156
31 Query Analysis......Page 1158
What’s New in Query Analysis......Page 1159
Query Analysis in SSMS......Page 1160
SSMS Client Statistics......Page 1175
Using the SET SHOWPLAN Options......Page 1176
Using sys.dm_exec_query_plan......Page 1180
Query Statistics......Page 1182
Query Analysis with SQL Server Profiler......Page 1190
Summary......Page 1192
What’s New in Locking and Performance......Page 1194
The Need for Locking......Page 1195
Transaction Isolation Levels in SQL Server......Page 1196
Monitoring Lock Activity in SQL Server......Page 1203
SQL Server Lock Types......Page 1214
SQL Server Lock Granularity......Page 1219
Lock Compatibility......Page 1230
Locking Contention and Deadlocks......Page 1231
Table Hints for Locking......Page 1246
Optimistic Locking......Page 1250
Summary......Page 1255
What’s New in Database Design and Performance......Page 1256
Basic Tenets of Designing for Performance......Page 1257
Logical Database Design Issues......Page 1258
Denormalizing a Database......Page 1261
Database Filegroups and Performance......Page 1268
RAID Technology......Page 1270
Summary......Page 1275
34 Monitoring SQL Server Performance......Page 1276
What’s New in Monitoring SQL Server Performance......Page 1277
A Performance Monitoring Approach......Page 1278
Performance Monitor......Page 1279
Windows Performance Counters......Page 1282
SQL Server Performance Counters......Page 1300
Using DBCC to Examine Performance......Page 1304
The Top 100 Worst-Performing Queries......Page 1308
Other SQL Server Performance Considerations......Page 1312
Summary......Page 1313
Part VI: SQL Server Application Development......Page 1314
35 What’s New for Transact-SQL in SQL Server 2005......Page 1316
The max Specifier......Page 1317
TOP Enhancements......Page 1319
The OUTPUT Clause......Page 1323
Common Table Expressions......Page 1327
Ranking Functions......Page 1338
PIVOT and UNPIVOT......Page 1348
The APPLY Operator......Page 1352
TRY...CATCH Logic for Error Handling......Page 1355
The TABLESAMPLE Clause......Page 1357
Summary......Page 1361
Working with ADO.NET 2.0 and SQL Server......Page 1362
Developing Custom Managed Database Objects......Page 1374
Summary......Page 1418
Understanding XML......Page 1420
Relational Data as XML: The FOR XML Modes......Page 1421
XML as Relational Data: Using OPENXML......Page 1442
Using the New xml Data Type......Page 1445
Indexing and Full-Text Indexing of xml Columns......Page 1473
Summary......Page 1480
Web Services History and Overview......Page 1482
Building Web Services......Page 1485
Examples: A C# Client Application......Page 1496
Using Catalog Views and System Stored Procedures......Page 1509
Controlling Access Permissions......Page 1511
Summary......Page 1512
Part VII: SQL Server Business Intelligence Features......Page 1514
What’s New in SSAS......Page 1516
Understanding SSAS and OLAP......Page 1517
Understanding the SSAS Environment Wizards......Page 1519
An Analytics Design Methodology......Page 1525
An OLAP Requirements Example: CompSales International......Page 1528
Summary......Page 1580
40 SQL Server Integration Services......Page 1582
SSIS Basics......Page 1583
SSIS Architecture and Concepts......Page 1588
SSIS Tools and Utilities......Page 1592
A Data Transformation Requirement......Page 1598
Running the SSIS Wizard......Page 1599
The SSIS Designer......Page 1609
The Package Execution Utility......Page 1617
Using bcp......Page 1629
Logged and Non-Logged Operations......Page 1644
Summary......Page 1648
41 SQL Server 2005 Reporting Services......Page 1650
What’s New in Reporting Services 2005......Page 1653
Installing and Configuring Reporting Services......Page 1654
Designing Reports......Page 1661
Management and Security......Page 1688
Performance and Monitoring Tools......Page 1695
Building Applications for SQL Server Reporting Services 2005 Using the Report Viewer Controls......Page 1696
Summary......Page 1702
Part VIII: Bonus Chapters on the CD......Page 1704
42 Managing Linked and Remote Servers......Page 1706
Managing Remote Servers......Page 1707
Linked Servers......Page 1714
Adding, Dropping, and Configuring Linked Servers......Page 1716
Mapping Local Logins to Logins on Linked Servers......Page 1726
Obtaining General Information About Linked Servers......Page 1730
Setting Up Linked Servers Through SQL Server Management Studio......Page 1732
Summary......Page 1735
43 Configuring, Tuning, and Optimizing SQL Server Options......Page 1736
SQL Server Instance Architecture......Page 1737
Configuration Options......Page 1738
Setting Configuration Options with SSMS......Page 1745
Configuration Options and Performance......Page 1746
Database Engine Tuning Advisor......Page 1774
Summary......Page 1785
What’s New for Administering Very Large SQL Server Databases......Page 1786
Do I Have a VLDB?......Page 1787
VLDB Maintenance Issues......Page 1788
VLDB Database Design Considerations......Page 1804
Summary......Page 1813
45 SQL Server Disaster Recovery Planning......Page 1814
How to Approach Disaster Recovery......Page 1815
Microsoft SQL Server Options for Disaster Recovery......Page 1823
The Overall Disaster Recovery Process......Page 1827
Third-Party Disaster Recovery Alternatives......Page 1834
Summary......Page 1835
46 Transact-SQL Programming Guidelines, Tips, and Tricks......Page 1836
General T-SQL Coding Recommendations......Page 1837
General T-SQL Performance Recommendations......Page 1850
T-SQL Tips and Tricks......Page 1860
Summary......Page 1883
What’s New in SQL Server Notification Services......Page 1884
Requirements and Editions of SSNS......Page 1885
Making the Business Case for Using SSNS......Page 1886
Understanding the SSNS Platform Architecture......Page 1887
Building an Effective SSNS Application......Page 1890
Compiling and Running the Sample Application......Page 1909
Summary......Page 1917
Understanding Distributed Messaging......Page 1918
Designing an Example System......Page 1923
Understanding Service Broker Constructs......Page 1924
Service Broker Routing and Security......Page 1944
Related System Catalogs......Page 1952
Summary......Page 1954
49 SQL Server Full-Text Search......Page 1956
How SQL Server FTS Works......Page 1957
Setting Up a Full-Text Index......Page 1959
Full-Text Searches......Page 1976
Full-Text Search Performance......Page 1981
Summary......Page 1982
A......Page 1984
B......Page 1986
C......Page 1987
D......Page 1990
E......Page 1995
F......Page 1996
H......Page 1998
I......Page 1999
K......Page 2001
L......Page 2002
M......Page 2003
N......Page 2006
O......Page 2007
P......Page 2008
Q......Page 2011
R......Page 2012
S......Page 2015
T......Page 2022
U......Page 2025
V......Page 2026
W......Page 2027
X-Y-Z......Page 2028