Microsoft SQL Server 2008 R2 深度解析

需积分: 13 51 浏览量更新于2024-07-28 收藏 47.93MB PDF 举报

"Microsoft SQL Server 2008 R2 Unleashed" 本书《Microsoft SQL Server 2008 R2 Unleashed》是关于微软的数据库管理系统——SQL Server 2008 R2的权威指南。作者团队包括Ray Rankins、Paul Bertucci、Chris Gallelli和Alex T. Silverstein，他们在IT领域，尤其是SQL Server方面具有丰富的经验和深入的理解。 SQL Server 2008 R2是微软推出的一个重要版本，它在2008年基础版的基础上进行了多项改进和增强，旨在提供更高效、安全和可扩展的数据管理解决方案。这本书详细涵盖了SQL Server 2008 R2的各种特性，包括但不限于： 1. **数据库设计与管理**：书中详细讲解了如何设计和创建高效、可靠的数据库架构，包括表、索引、存储过程和触发器的使用，以及数据类型选择和关系设计的最佳实践。 2. **性能优化**：探讨了监控和调整SQL Server性能的策略，如查询优化、索引调优、资源管理器（Resource Governor）的使用，以及性能计数器和动态管理视图（DMV）的分析。 3. **安全性**：详细介绍了SQL Server的安全模型，包括用户权限管理、角色和登录、加密技术、审计和防火墙配置，以确保数据安全。 4. **备份与恢复**：深入讨论了备份策略，包括完整备份、差异备份、日志备份以及灾难恢复计划的制定，以保护企业数据免受损失。 5. **高可用性**：涵盖了SQL Server的高可用性解决方案，如镜像、故障转移群集、AlwaysOn可用性组等，以确保服务的连续性。 6. **数据仓库与商业智能**：讨论了SQL Server 2008 R2中的集成服务（SSIS）、分析服务（SSAS）和报告服务（SSRS），以及如何构建数据仓库和实施BI解决方案。 7. **开发与集成**：讲解了如何使用Transact-SQL进行编程，以及与其他开发平台（如.NET和Java）的集成。 8. **PowerShell支持**：介绍如何使用PowerShell进行SQL Server的自动化管理，提高工作效率。 9. **云服务集成**：SQL Server 2008 R2开始引入对云服务的支持，书中可能涉及Azure集成，以及数据同步和迁移至云端的方案。 10. **案例研究**：书中可能包含实际案例，帮助读者理解如何在实际环境中应用所学知识解决问题。《Microsoft SQL Server 2008 R2 Unleashed》是一本全面而深入的技术参考书，适合SQL Server管理员、开发人员和数据库架构师阅读，以提升他们在SQL Server 2008 R2平台上的技能和知识。

ptg

Step-By-Step Examples . ..............................................................................509

General Tasks .....................................................................................509

Scheduling Scripts..............................................................................510

Common OS-Related Tasks................................................................512

SQL Server–Specific Tasks ..................................................................514

Using the Provider.............................................................................515

Creating a Database Table .................................................................515

Performing a Database Backup..........................................................516

Checking Server Settings ...................................................................518

Checking the Database Usage ...........................................................519

Getting Table Properties ....................................................................520

Cmdlet Example: Invoke-SqlCmd.....................................................520

Cmdlet Example: Invoke-PolicyEvaluation ......................................521

Joining Columns................................................................................521

Retrieving an Entry............................................................................522

Summary .....................................................................................................522

18 SQL Server High Availability

523

What’s New in High Availability................................................................524

What Is High Availability?. ........................................................................525

The Fundamentals of HA. ..........................................................................526

Hardware Factors ...............................................................................527

Backup Considerations ......................................................................527

Operating System Upgrades ..............................................................527

Vendor Agreements Followed............................................................528

Training Kept Up to Date ..................................................................528

Quality Assurance Done Well............................................................528

Standards/Procedures Followed.........................................................528

Server Instance Isolation ...................................................................528

Building Solutions with One or More HA Options. ..................................530

Microsoft Cluster Services (MSCS) ....................................................530

SQL Clustering. .................................................................................531

Data Replication . ..............................................................................534

Log Shipping. ....................................................................................535

Database Mirroring . ..........................................................................537

Combining Failover with Scale-Out Options....................................538

Other HA Techniques That Yield Great Results .........................................538

High Availability from the Windows Server Family Side ..........................540

Microsoft Virtual Server 2005............................................................541

Virtual Server 2005 and Disaster Recovery .......................................542

Summary .....................................................................................................542

Contents

Download from www.wowebook.com

ptg

19 Replication 545

What’s New in Data Replication ................................................................546

What Is Replication?. .................................................................................547

The Publisher, Distributor, and Subscriber Magazine Metaphor ...............549

Publications and Articles ...................................................................550

Filtering Articles. ...............................................................................550

Replication Scenarios . ................................................................................555

The Central Publisher Replication Model .........................................555

The Central Publisher with Remote Distributor Replication Model. ....557

The Publishing Subscriber Replication Model ..................................558

The Central Subscriber Replication Model .......................................559

The Multiple Publishers with Multiple

Subscribers Replication Model ........................................................559

The Updating Subscribers Replication Model ...................................560

The Peer-to-Peer Replication Model ..................................................561

Subscriptions . .............................................................................................562

Anonymous Subscriptions (Pull Subscriptions) ................................563

The Distribution Database. ...............................................................564

Replication Agents . ....................................................................................565

The Snapshot Agent ..........................................................................566

The Log Reader Agent. ......................................................................569

The Distribution Agent. ....................................................................569

The Merge Agent . .............................................................................570

Other Specialized Agents . .................................................................571

Planning for SQL Server Data Replication . ...............................................572

Autonomy, Timing, and Latency of Data .........................................572

Methods of Data Distribution . .........................................................573

SQL Server Replication Types . ...................................................................574

Snapshot Replication.........................................................................574

Transactional Replication ..................................................................574

Merge Replication. ............................................................................575

Basing the Replication Design on User Requirements. .............................577

Data Characteristics...........................................................................578

Setting Up Replication . ..............................................................................579

Creating a Distributor and Enabling Publishing ..............................580

Creating a Publication. .....................................................................584

Horizontal and Vertical Filtering. .....................................................592

Creating Subscriptions. .....................................................................594

Scripting Replication...................................................................................600

Monitoring Replication ..............................................................................603

Replication Monitoring SQL Statements...........................................603

Monitoring Replication within SQL Server Management Studio.....606

xvi

Download from www.wowebook.com

ptg

Troubleshooting Replication Failures ................................................608

New and Improved Peer-to-Peer Replication ....................................609

The Performance Monitor . ...............................................................610

Replication in Heterogeneous Environments ...................................611

Backup and Recovery in a Replication Configuration......................612

Some Thoughts on Performance .......................................................613

Log Shipping. ....................................................................................614

Data Replication and Database Mirroring for

Fault Tolerance and High Availability . ..........................................614

Summary . ...................................................................................................615

20 Database Mirroring

617

What’s New in Database Mirroring . ..........................................................617

What Is Database Mirroring?. ....................................................................618

Copy-on-Write Technology ...............................................................620

When to Use Database Mirroring .....................................................621

Roles of the Database Mirroring Configuration. .......................................621

Playing Roles and Switching Roles....................................................622

Database Mirroring Operating Modes. .............................................622

Setting Up and Configuring Database Mirroring. .....................................623

Getting Ready to Mirror a Database..................................................624

Creating the Endpoints . ...................................................................627

Granting Permissions . ......................................................................629

Creating the Database on the Mirror Server .....................................630

Identifying the Other Endpoints for Database Mirroring ................632

Configuring Database Mirroring by Using the Wizard ....................633

Monitoring a Mirrored Database Environment ................................639

Removing Mirroring . ........................................................................643

Testing Failover from the Principal to the Mirror . ....................................645

Client Setup and Configuration for Database Mirroring...........................647

Migrate to Database Mirroring 2008 as Fast as You Can ...........................649

Using Replication and Database Mirroring Together.................................651

Using Database Snapshots from a Mirror for Reporting............................652

Summary . ...................................................................................................654

21 SQL Server Clustering

655

What’s New in SQL Server Clustering . ......................................................656

How Microsoft SQL Server Clustering Works.............................................656

Understanding MSCS.........................................................................658

Extending MSCS with NLB................................................................662

How MSCS Sets the Stage for SQL Server Clustering........................663

Contents

xvii

Download from www.wowebook.com

ptg

Installing SQL Server Clustering. ...............................................................665

Configuring SQL Server Database Disks............................................666

Installing Network Interfaces . ..........................................................668

Installing MSCS . ...............................................................................668

Installing SQL Server . .......................................................................668

Failure of a Node . .............................................................................679

The Connection Test Program for a SQL Server Cluster...................681

Potential Problems to Watch Out for with SQL Server Clustering .....684

Summary .....................................................................................................685

22 Administering Policy-Based Management

687

Introduction to Policy-Based Management................................................687

Policy-Based Management Concepts..........................................................689

Facets..................................................................................................689

Conditions .........................................................................................693

Policies ...............................................................................................693

Categories...........................................................................................693

Targets ................................................................................................693

Execution Modes ...............................................................................694

Central Management Servers ............................................................695

Implementing Policy-Based Management. ................................................697

Creating a Condition Based on a Facet.............................................697

Creating a Policy. ..............................................................................699

Creating a Category. .........................................................................701

Evaluating Policies. ...........................................................................702

Importing and Exporting Policies . ...................................................703

Sample Templates and Real-World Examples. ...........................................704

Sample Policy Templates ...................................................................704

Evaluating Recovery Models..............................................................705

Implementing Surface Area Configuration Checks ..........................705

SQL Server Health Checks .................................................................705

Ensuring Object Naming Conventions.............................................706

Checking Best Practices Compliance ................................................706

Policy-Based Management Best Practices ...................................................706

Summary . ...................................................................................................707

Part IV Database Administration

23 Creating and Managing Databases

709

What’s New in Creating and Managing Databases....................................710

Data Storage in SQL Server . .......................................................................710

xviii

Download from www.wowebook.com

ptg

Database Files. ............................................................................................711

Primary Files ......................................................................................712

Secondary Files ..................................................................................712

Using Filegroups ................................................................................713

Using Partitions .................................................................................716

Transaction Log Files .........................................................................716

Creating Databases. ....................................................................................717

Using SSMS to Create a Database......................................................718

Using T-SQL to Create Databases . ....................................................721

Setting Database Options. ..........................................................................722

The Database Options .......................................................................723

Using T-SQL to Set Database Options ...............................................725

Retrieving Option Information .........................................................726

Managing Databases . .................................................................................729

Managing File Growth.......................................................................729

Expanding Databases.........................................................................730

Shrinking Databases ..........................................................................731

Moving Databases. ............................................................................736

Restoring a Database Backup to a New Location..............................736

Using ALTER DATABASE....................................................................736

Detaching and Attaching Databases .................................................737

Summary .....................................................................................................740

24 Creating and Managing Tables

741

What’s New in SQL Server 2008.................................................................741

Creating Tables. ..........................................................................................742

Using Object Explorer to Create Tables ............................................742

Using Database Diagrams to Create Tables. .....................................743

Using T-SQL to Create Tables . ..........................................................744

Defining Columns ......................................................................................747

Data Types..........................................................................................747

Column Properties. ...........................................................................755

Defining Table Location .............................................................................761

Defining Table Constraints.........................................................................763

Modifying Tables. .......................................................................................765

Using T-SQL to Modify Tables...........................................................766

Using Object Explorer and the Table Designer to Modify Tables.....769

Using Database Diagrams to Modify Tables......................................772

Dropping Tables ..........................................................................................773

Using Partitioned Tables .............................................................................774

Creating a Partition Function ...........................................................776

Creating a Partition Scheme..............................................................778

Creating a Partitioned Table. ............................................................779

Contents

xix

Download from www.wowebook.com

剩余2497页未读，继续阅读

susuansun

粉丝: 0
资源: 10

Microsoft SQL Server 2008 R2 深度解析

SQL Server 2008 R2 UNLEASHED

SQL Server 2008 R2 教程 Unleashed.pdf

SQLServer2008_R2_Datasheet.pdf

Sams.Microsoft.SQL.Server.2008.R2.Unleashed.part2

Sams.Microsoft.SQL.Server.2008.R2.Unleashed.part1

[Microsoft.SQL.Server.2008.R2.Unleashed].(Ray)影印版.rar

[Microsoft.SQL.Server.2008.R2.Unleashed].(Ray)影印版.part2.rar

Sams.Microsoft.SQL.Server.2008.Reporting.Services.Unleashed.Jul.2009.rar

Sams.Microsoft.Windows.Server.2003.Unleashed.R2.Edition.May.2006.chm

Sams.Windows.Server.2008.R2.Unleashed.Jan.2010.rar

最新资源