SQL SERVER 2012 AlwaysOn操作手册.pdf

SQL Server Denali AlwaysOn Hands on Lab VERSION version 1 0 Lab Manual Beta Version 1 0 Author Ted Malone v temalo SQL Server Denali AlwaysOn 2 2011 Microsoft Corporation All rights reserved Table of Contents Lab Environment 3 Lab Overview 4 About This Lab 5 Exercise 1 Configuring a Windows Failover Cluster 6 Exercise 2 Configuring an Availability Group 13 Exercise 3 Enabling Read Access to a Secondary Replica 21 Exercise 4 Configuring an Application to Use a ALWAYSON Virtual Network Name 25 Lab Environment During this lab you will work in a simulated environment with the following computers or virtual machines Computers and virtual machines used in this lab This lab uses three virtual machines with the following details Setting Value Computer Name SQLLABS DC Function Domain Controller Operating System Windows Server 2008 R2 Computer Name DENALI SERVER1 Function Microsoft SQL Server Primary Replica Operating System Windows Server 2008 R2 SQL Server Instances INSTANCE1 Computer Name DENALI SERVER2 Function SQL Server Secondary Replica Operating System Windows Server 2008 R2 SQL Server Instances INSTANCE1 Logon credentials Log on to the Hands on Lab environment using the following credentials unless specified differently in the lab manual User Name Password LabUser pass word1 Administrator pass word1 If you are required to log on use the following key combinations Virtualization Strategy Logon Key Combination Virtual Server Right Arrow Alt Del Hyper V Ctrl Alt End SQL Server Denali AlwaysOn 4 2011 Microsoft Corporation All rights reserved Lab Overview Abstract In this lab you will learn how to configure the new high availability and disaster recovery solution SQL Server AlwaysOn AlwaysOn Availability Group is a new feature that provides database availability in SQL Server Denali In this lab you will configure an Availability Group for providing high availability for an application with multiple databases and learn how to configure and use the a virtual name to enable the application to seamlessly failover Finally you will also learn to monitor the ALWAYSON configuration using the new distributed management views DMV s Learning Objectives After completing the exercises in this lab you will be able to Configure a Windows Failover Cluster Explain the functionality of the new AlwaysOn Availability Group feature in SQL Server Denali Configure an Availability Group using the Availability Group Wizard Configure an Availability Group using T SQL commands Enable a secondary replica in an Availability Group configuration for read operations Monitor an Availability Group using DMVs Configure and use an Availability Group virtual name Estimated lab time 120 minutes The estimated time to complete this lab depends on the virtual machine infrastructure Recommended hardware This lab has been tested on a dual quad core CPU with 8 GB RAM of which 6 GB is assigned to the virtual machines used in this lab The estimated lab time is based on completing every portion of the lab including optional exercises About This Lab Scope This Hands on Lab explores the new Alwayson high availability solution available in SQL Server Denali The lab consists of four exercises which will allow you to configure and monitor the ALWAYSON features in a variety of ways This lab uses a virtual machine infrastructure which has been configured to support Windows Clustering It is important to understand that the concepts presented in the lab are designed for learning purposes and do not necessarily reflect Windows Clustering best practices SQL Server Denali AlwaysOn 6 2011 Microsoft Corporation All rights reserved Exercise 1 Configuring a Windows Failover Cluster Scenario One of the pre requisites for enabling an Availability Group is that the machines hosting the SQL Server instances that needs to be part of the group should be nodes in the same Windows Server Failover Cluster WSFC In this exercise you will learn how to create a windows cluster as a pre requisite for enabling an availability group In Windows Server 2008 R2 Windows Clustering is very simple to configure In this exercise you will take two individual Windows Server 2008 R2 virtual machines which already have SQL Server Denali installed and convert them into a Windows Failover Cluster The cluster will use local storage for each node and will not have any shared storage The resulting Windows Failover Cluster will have the following network configuration By the end of this exercise you will be able to Describe the components necessary to create a Windows Failover Cluster node Configure a two node Windows Failover Cluster Install the Windows Clustering service Task Detailed Steps Install the Failover Clustering feature on the DENALI SERVER1 virtual machine 1 Log on to the DENALI SERVER1 virtual machine 2 Start Server Manager by clicking the Server Manager icon in the task bar 3 In the Server Manager console in the left pane right click Features and then click Add Features 4 On the Select Features page select Failover Clustering 5 On the Select Features page click Next 6 On the Confirm Installation Selections page click Install 7 When the installation has finished on the Installation Results page click Close 8 Leave the Server Manager console open Install the Failover Clustering feature on the DENALI SERVER2 virtual machine 9 Switch to the DENALI SERVER2 virtual machine 10 In the Server Manager console in the left pane right click Features and then click Add Features 11 On the Select Features page select Failover Clustering 12 On the Select Features page click Next 13 On the Confirm Installation Selections page click Install 14 When the installation has finished on the Installation Results page click Close 15 Leave the Server Manager console open Validate the cluster configuration on the DENALI SERVER1 virtual machine 16 Switch to the DENALI SERVER1 virtual machine 17 In the Server Manager console expand the Features node and then select Failover Cluster Manager The steps in this exercise are performed on the SQLLABS DC DENALI SERVER1 and DENALI SERVER2 virtual machines using the INSTANCE1 SQL Server Denali instance LOGON INSTRUCTIONS When the LOGON screen displays log on using the following credentials User name Administrator Password pass word1 SQL Server Denali AlwaysOn 8 2011 Microsoft Corporation All rights reserved 18 In the Management section click Validate a Configuration 19 On the Before You Begin page read the information and then click Next 20 On the Select Servers or a Cluster page add denali and denali 21 On the Select Servers or a Cluster page click Next 22 On the Testing Options page select Run all tests recommended and then click Next 23 On the Confirmation page click Next This test will take several minutes to complete Do not attempt to run this validation wizard on DENALI SERVER2 while it is running on DENALI SERVER1 because they will conflict with each other 24 In the Summary dialog box view the report note the IP warnings and then click Finish The IP warnings occur because of the environment used for this lab there are no default gateways configured for the secondary network adapters 25 Leave the Server Manager console open Validate the cluster configuration on the DENALI SERVER2 virtual machine 26 Switch to the DENALI SERVER2 virtual machine 27 In the Server Manager console expand the Features node and then select Failover Cluster Manager 28 In the Management section click Validate a Configuration 29 On the Before You Begin page read the information and then click Next 30 On the Select Servers or a Cluster page add denali SQL Server Denali AlwaysOn 10 2011 Microsoft Corporation All rights reserved and denali 31 On the Select Servers or a Cluster page click Next 32 On the Testing Options page select Run all tests recommended and then click Next 33 On the Confirmation page click Next This test will take several minutes to complete 34 In the Summary dialog box view the report note the IP warnings and then click Finish The IP warnings occur because of the environment used for this lab there are no default gateways configured for the secondary network adapters 35 Log off the DENALI SERVER2 virtual machine Create the Cluster on the DENALI SERVER1 virtual machine 36 Switch to the DENALI SERVER1 virtual machine 37 In Server Manager Failover Cluster Manager in the Management section click Create a Cluster 38 Read the information in the Before you begin page and then click Next 39 In the Select Servers page add denali and denali 40 On the Select Servers page click Next 41 In the Access Point for Administering the Cluster page in the Cluster Name box type DenaliCluster 42 Clear the check box next to the 192 168 3 0 24 network 43 In the Address box next to the 192 168 1 0 24 network type 192 168 1 201 44 In the Address box next to the 192 168 2 0 24 network type 192 168 2 201 The 192 168 3 0 network in the lab environment is reserved for iSCSI communications When designing a Windows Cluster it is very important to isolate disk network traffic from cluster network traffic This is why you clear the network as part of the configuration In the lab cluster environment communication between cluster nodes can take place on the 192 168 2 0 network while clients can communicate with the cluster on the 192 168 1 0 network SQL Server Denali AlwaysOn 12 2011 Microsoft Corporation All rights reserved 45 On the Access Point for Administering the Cluster page click Next 46 On the Confirmation page click Next This will take several minutes to complete 47 On the Summary page review the report and then click Finish Note that the cluster has been configured with two nodes 48 Log off the DENALI SERVER1 virtual machine Review In this exercise you reviewed the iSCSI target configuration on the SQLLABS DC virtual machine and examined the virtual disk configuration You connected each node of the cluster to the iSCSI disk on the SQLLABS DC machine You then verified the cluster configuration on each virtual machine and installed the Failover Clustering feature on each of the virtual machines Finally you created a Windows Failover Cluster with two nodes Exercise 2 Configuring an Availability Group Scenario AlwaysOn Availability Groups introduced in SQL Server Denali provide high availability for your application databases Availability Group allows you to failover a group of databases together and allows configuring multiple instances as replicas to which you can failover thereby increasing redundancy and availability The availability group involves a set of SQL Server instances known as availability replicas Each availability replica possesses a local copy of each of the databases in the availability group Only one of these replicas act as the primary replica at any point in time and and maintains the primary copy of each database The primary replica makes these databases known as primary databases available to users for read write access For each primary database one or more availability replicas known as a secondary replicas maintain a copy of the database and the database on a secondary replica is referred to as a secondary database By the end of this exercise you will understand How to enable the SQL ALWAYSON service How to configure an AlwaysOn Availability Group using the wizard The steps in this exercise are performed on both the DENALI SERVER1 and DENALI SERVER2 virtual machines using the INSTANCE1 SQL Server Denali instance Task Detailed Steps View existing application database configuration using SQL Server Management Studio 1 Log on to the DENALI SERVER1 virtual machine 2 Click Start SQL Server Management Studio 3 Connect to the DENALI SERVER1 Instance1 instance using Windows Authentication 4 In the Object Explorer pane expand the Databases node 5 Note the three AdventureWorks databases SQL Server Denali AlwaysOn 14 2011 Microsoft Corporation All rights reserved Enable the SQL ALWAYSON service on DENALI SERVER1 INSTANCE1 6 Click Start SQL Server Configuration Manager 7 When prompted select Yes in the User Account Control dialog box 8 In the left pane select SQL Server Services 9 Right click SQL Server Instance 1 and then select Properties 10 In the SQL Server INSTANCE1 Properties on the AlwaysOn High Availability tab select the Enable AlwaysOn Availability Groups check box and then click OK 11 Click OK on the Warning message These steps will enable the SQL HADR service but it will not take effect until the INSTANCE1 service is restarted Restart the SQL Server INSTANCE1 instance 12 In SQL Server Configuration Manager right click SQL Server INSTANCE1 and then click Restart Enable the SQL HADR service on DENALI SERVER2 INSTANCE1 13 Switch to the DENALI SERVER2 virtual machine and log on 14 Click Start SQL Server Configuration Manager 15 When prompted select Yes in the User Account Control dialog box 16 In the left pane select SQL Server Services 17 Right click SQL Server Instance 1 and then select Properties 18 In the SQL Server INSTANCE1 Properties dialog box on the AlwaysOn High Availability tab select the Enable AlwaysOn Availability Groups check box and then click OK 19 Click OK on the Warning message 20 In SQL Server Configuration Manager right click SQL Server INSTANCE1 and then click Restart Create a new Availability Group using the wizard 21 Switch to the DENALI SERVER1 virtual machine 22 In SQL Server Management Studio in the Object Explore pane expand the Management node 23 Right click the Availability Groups node and then select New Availability Group SQL Server Denali AlwaysOn 16 2011 Microsoft Corporation All rights reserved 24 On the Introduction page click Next 25 On the Specify Availability Group Name page in the Name box type AdvWorksApp and click Next 26 On the Select Databases page select the two AdventureWorks databases in the list 27 On the Select Databases page click Next To be eligible to be included in an HADR Availability Group databases must meet the following prerequisites Be a user database Be a read write database Be a multi user database Not use AUTO CLOSE Use the full recovery mode Possess a full database backup Reside on the SQL Server instance where you are creating the availability group and be accessible Not belong to another availability group Not be configured for database mirroring 28 On the Specify Replicas page click Add In order to function as a replica host the instance of SQL Server must be enabled for SQL HADR 29 Connect to the DENALI SERVER2 INSTANCE1 instance and then change Connection Mode in Secondary Role to Allow all connections 30 On the Specify Replicas page click Next SQL Server Denali AlwaysOn 18 2011 Microsoft Corporation All rights reserved 31 On the Specify Availability Group Listener page select skip and then click Next 32 On the Select Initial Data Synchronization page change the backup location to DENALI SERVER1 c then click Next 33 On the Validation page click Next 34 On the Summary page click Finish and then click Close Review the Availability Group 35 If necessary restore Microsoft SQL Server Management Studio 36 In the Object Explorer pane expand Availability Groups expand the AdvWorksApp availability group and then note the availability group SQL Server Denali AlwaysOn 20 2011 Microsoft Corporation All rights reserved Review In this exercise you enabled the SQL HADR service on both DENALI SERVER1 and DENALI SERVER2 for the INSTANCE1 SQL Server instance By creating an Availability Group and assigning the three AdventureWorks databases to the group you enabled high availability for all three databases In the next exercise you will configure the secondary replica to allow read operations and configure an application connection string to specify a failover partner Configuration configuration 37 Close all open windows and log off the DENALI SERVER1 virtual machine 38 Switch to the DENALI SERVER2 virtual machine 39 From the Start menu open Microsoft SQL Server Management Studio 40 Connect to DENALI SERVER2 INSTANCE1 using Windows Authentication 41 In the Object Explorer pane expand the Management Availability Groups nodes expand the AdvWorksApp availability group and then note the configuration 42 Log off the DENALI SERVER2 virtual machine Exercise 3 Enabling Read Access to a Secondary Replica Scenario Business executives often complain about the cost of the hardware required to maintain a High Availability and Disaster Recovery solution AlwaysOn Availability Groups introduces Active Secondary which provides a mechanism to allow administrators to configure secondary replicas for read access This helps to alleviate performance concerns and allows the secondary replica to become a functional part of an application infrastructure By the end of this exercise you will be able to Configure a secondary replica for read access Monitor Data Movement between members of an Availability Group Suspend Data Movement between members of an Availability Group Resume Data Movement between members of an Availability Group Task Detailed Steps Test the secondary replica read capability 1 Connect to DENALI SERVER1 virtual machine and log on 2 Open a new query window and then run the following T SQL command SELECT LastName FROM Person Person WHERE BusinessEntityID 1 3 Note the returned value and then run the following T SQL command UPDATE Person Person SET LastName Smith WHERE BusinessEntityID 1 4 Switch to the DENALI SERVER2 virtual machine and log on 5 Click START SQL Server Management Studio 6 Connect to the DENALI SERVER2 INSTANCE1 instance using Windows Authentication 7 Open a new query window and then run the following T SQL command SELECT LastName FROM Person Person WHERE BusinessEntityID 1 Note that the LastName has changed to Smith 8 Switch to the DENALI SERVER1 virtual machine The steps in this exercise are performed on both the DENALI SERVER1 and DENALI SERVER2 virtual machines using the INSTANCE1 SQL Server Denali instance SQL Server Denali AlwaysOn 22 2011 Microsoft Corporation All rights reserved The