Home > Sample chapters > Programming > SQL Server

Administering Microsoft SQL Server 2012 Databases: Clustering and AlwaysOn

Lesson 2: AlwaysOn Availability Groups

This lesson covers AlwaysOn Availability Groups, a high-availability feature that is new in SQL Server 2012. In this lesson, you learn about the infrastructure requirements for implementing AlwaysOn and what steps to take to enable AlwaysOn functionality.

What Are AlwaysOn Availability Groups?

httpatomoreillycomsourcemspimages1685956.jpg AlwaysOn Availability Groups are an alternative to database mirroring. An availability group is a collection of user databases, termed availability databases, that can fail over together. Unlike mirroring that is limited to a principal and a mirror database, availability groups support a set of read-write primary databases and up to four sets of secondary databases. Availability groups also enable you to configure one or more sets of secondary databases so that they are accessible for read-only operations.

Failover occurs on a per-replica basis, and all databases in the replica fail over. Database failover is not caused by issues related to individual databases, such as database file or transaction log corruption, but by factors at the instance level, as is the case with normal failover clusters. Availability groups support automatic failover.

Although you must deploy AlwaysOn Availability Groups on an instance that resides on a failover cluster, you usually do not deploy availability groups on a failover cluster instance. Put another way, even though you deploy AlwaysOn with a cluster, you install availability groups on an instance that was deployed by using the typical method outlined in Chapter 1 rather than by using the advanced cluster preparation and advanced cluster completion processes outlined in Lesson 1: Clustering SQL Server 2012, of this chapter. You can deploy AlwaysOn Availability Groups on a Windows Server failover cluster that does not include a shared storage resource.

Meeting Availability Group Prerequisites

For production environments, only SQL Server 2012 Enterprise edition supports AlwaysOn Availability Groups. When planning the deployment of AlwaysOn Availability Groups, the host server must meet the following conditions:

  • Host servers cannot be domain controllers.

  • Each host server must be a participant node in a Windows Server failover cluster. Failover clustering is supported only on Windows Server 2008 Enterprise and Datacenter editions and Windows Server 2008 R2.

  • You must ensure that appropriate hotfixes are applied to the host server operating system.

Although not a requirement, best practice is to ensure all host systems that participate in an availability group can handle identical workloads and to provide host systems with separate network adapters dedicated for availability group traffic. You should also configure a Time To Live (TTL) of 60 seconds on the zone that hosts the DNS records related to the availability group.

If you must support Kerberos authentication with availability groups, you must perform the following extra steps:

  • The SQL Server service on each participating instance must use the same domain account.

  • You must manually register a Service Principal Name (SPN) for the virtual network name (VNN) of the availability group listener with the domain account used as each instance’s SQL Server service account.

These steps are unnecessary if you are using the default NTLM authentication option.

Configuring Availability Modes

AlwaysOn Availability Groups support similar modes to database mirroring. The type of availability mode that is appropriate depends on data loss and transaction latency requirements. You configure availability modes on a per-availability replica basis. AlwaysOn Availability Groups support the following availability modes:

  • Asynchronous-commit mode This mode is suitable when you must place availability replicas at geographically dispersed locations. When you configure all secondary replicas to use asynchronous-commit mode, the primary will not wait for secondaries to harden the log (write log records to disk) and will run with minimum transaction latency. If you configure the primary to use asynchronous-commit mode, the transactions for all replicas will be committed asynchronously independently of which mode you’ve configured on each secondary replica.

  • Synchronous-commit mode This mode increases transaction latency but minimizes the chance of data loss in the event of automatic failover. When you use this mode, each transaction is applied to the secondary replica before being written to the local log file. The primary verifies that the transaction has been applied to the secondary before entering a SYNCHRONIZED state.

You can configure the availability mode on the Availability Group Properties page, as shown in Figure 8-15. You can also use the ALTER AVAILABILITY GROUP Transact-SQL statement with the AVAILABILITY_MODE option to change the availability mode. For example, to change the availability mode of the SQL-C\AlwaysOn replica to synchronous commit for the AG-Alpha availability group, execute the statement:

ALTER AVAILABILITY GROUP AG-ALPHA MODIFY REPLICA ON 'SQL-C\AlwaysOn' WITH
( AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
Figure 8-15

Figure 8-15. Synchronous-commit availability mode

Selecting Failover Modes

Availability groups fail over at the availability-replica level. Failover involves another instance becoming the primary replica, with the original primary replica being demoted to become a secondary replica. AlwaysOn Availability Groups support three forms of failover:

  • Automatic failover This form of failover occurs without administrator intervention. No data loss occurs during automatic failover. Automatic failover is supported only if the current primary and at least one secondary replica are configured with a failover mode set to AUTOMATIC, and at least one of the secondary replicas set to AUTOMATIC is also synchronized. Automatic failover can occur only if the primary and replica are in synchronous-commit mode, as shown in Figure 8-16.

    Figure 8-16

    Figure 8-16. Automatic failover with synchronous commit

  • Planned manual failover This form of failover is triggered by an administrator. No data loss occurs during planned manual failover. You perform this type of failover when you must perform a type of maintenance on a host instance that requires the instance or the host server to be taken offline or restarted. Planned manual failover can occur only if at least one of the secondary replicas is in a SYNCHRONIZED state. You can perform planned manual failover only if the primary and replica instances are in synchronous-commit mode.

  • Forced manual failover This form of failover involves the possibility of data loss. Use forced manual failover when no secondary replica is in the SYNCHRONIZED state or when the primary replica is unavailable. This type of failover is the only type supported if asynchronous-commit mode is used on the primary, or if the only available replica uses asynchronous-commit mode.

To perform manual failover by using SQL Server Management Studio, perform the following steps:

  1. Connect to the server instance that hosts the secondary replica of the availability group that you will make the primary replica.

  2. Right-click the availability group and click Failover. This starts the Fail Over Availability Group Wizard.

  3. On the Select New Primary Replica page, shown in Figure 8-17, select the instance on which to perform failover and then click Next.

Figure 8-17

Figure 8-17. Manual failover

You can use the ALTER AVAILABILTY GROUP statement with the FAILOVER option on the replica instance that you will make the primary instance. For example, to perform manual failover of the AG-Alpha availability group, execute the following statement:

ALTER AVAILABILITY GROUP AG-Alpha FAILOVER;

You can use the Switch-SqlAvailabilityGroup PowerShell cmdlet to perform manual failover. For example, to perform manual failover of availability group AG-Alpha to the SQL-D\AlwaysOn instance, execute the command:

Switch-SqlAvailabilityGroup -Path SQLSERVER:\SQL\SQL-D\AlwaysOn\AvailabilityGroups\AG-
Alpha

To perform forced failover by using SQL Server Management Studio, perform the following steps:

  1. Connect to the server instance that hosts the secondary replica of the availability group you will make the primary replica.

  2. Right-click the availability group and click Failover. This starts the Fail Over Availability Group Wizard.

  3. On the Select New Primary Replica page, select the instance on which to perform failover.

  4. On the Confirm Potential Data Loss page, shown in Figure 8-18, select Click Here To Confirm Failover With Potential Data Loss and click Next.

Figure 8-18

Figure 8-18. Confirming potential data loss

You can use the ALTER AVAILABILTY GROUP statement with the FORCE_FAILOVER_ALLOW_DATA_LOSS option on the replica instance that you will make the primary instance to force failover. For example, to force failover of the AG-Alpha availability group, execute the following statement:

ALTER AVAILABILITY GROUP AG-Alpha FORCE_FAILOVER_ALLOW_DATA_LOSS;

You can use the Switch-SQLAvailabilityGroup PowerShell cmdlet with the AllowDataLoss option to force failover. For example, to force failover of availability group AG-Alpha to the SQL-D\AlwaysOn instance, execute the command:

Switch-SqlAvailabilityGroup -Path SQLSERVER:\SQL\SQL-D\AlwaysOn\AvailabilityGroups\AG-
Alpha -AllowDataLoss

You can also use the Force option with the preceding PowerShell command if you do not want to be prompted, such as when using the command in a script.

Configuring Readable Secondary Replicas

Readable secondary replicas can service read-only requests for database access, which enables you to offload read-only workloads from the primary replica. You can configure a secondary replica to be readable from the Availability Group Properties dialog box, as shown in Figure 8-19. There are three options when configuring a readable secondary: No, Yes, and Read-intent only. The difference between Yes and Read-intent is that when you configure Read-intent, only read-only connections are allowed to the secondary databases on the secondary replica. When you configure Yes, all connections are allowed to secondary databases on the secondary replica but only for read access.

Figure 8-19

Figure 8-19. Readable secondary replicas

You can configure readable secondary properties for a replica by using the ALTER AVAILABILITY GROUP Transact-SQL statement with the SECONDARY_ROLE option.

Deploying AlwaysOn Availability Groups

Even when you have the requisite instances deployed on a Windows Server failover cluster, deploying AlwaysOn Availability Groups involves performing several tasks in order. These tasks are as follows:

  • Creating a mirroring endpoint

  • Enabling AlwaysOn

  • Creating an availability group

  • Creating an availability group listener

  • Adding a secondary replica

Creating an AlwaysOn Endpoint

httpatomoreillycomsourcemspimages1685956.jpg Unless you are using a domain-based account for each SQL Server service, you must create a mirroring endpoint prior to creating an AlwaysOn Availability Group. If you are using a domain-based account for all SQL Server services that will participate in the availability group, the Database Engine can create the appropriate mirroring endpoint automatically as part of the availability group creation process.

Prior to creating the endpoint, check whether there is an existing endpoint on the instance because you can have only one mirroring endpoint on an instance. You can check whether there are any mirroring endpoints on an instance by querying the sys.database_mirroring_endpoints catalog view.

You can create an endpoint from the SQL Server PowerShell module by using the New-SqlHadrEndpoint cmdlet. For example, to create an endpoint named AlwaysOnEndpoint that uses TCP port 7028 on instance SQL-A\ALTERNATE, issue the command:

$endpoint = New-SqlHadrEndpoint AlwaysOnEndpoint -Port 7028 -Path SQLSERVER:\SQL\SQL-AALTERNATE

After an endpoint has been created, you must start that endpoint. You can do so by using the Set-SqlHadrEndpoint cmdlet. For example, to start the endpoint created in the previous example, issue the command:

Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"

Enabling AlwaysOn Availability Groups

Before you can create an AlwaysOn Availability Group, you must enable the AlwaysOn Availability Groups functionality at the instance level. To enable AlwaysOn Availability Groups on an instance, perform the following steps:

  1. In SQL Server Configuration Manager, navigate to the SQL Server Services node.

  2. Right-click the SQL Server service related to the instance on which you want to enable AlwaysOn Availability Groups.

  3. On the AlwaysOn High Availability tab, select Enable AlwaysOn Availability Groups, as shown in Figure 8-20.

    This tab should also display the name of the failover cluster to which the node belongs.

    Figure 8-20

    Figure 8-20. Enabling AlwaysOn

  4. You must now restart the SQL Server service before AlwaysOn is enabled. When enabling AlwaysOn Availability Groups, you should enable only one instance at a time. You should then wait until the SQL Server service has restarted before enabling AlwaysOn on other instances that will participate in the availability group.

You can also enable AlwaysOn by using SQL Server PowerShell with the Enable-SQLAlwaysOn cmdlet. For example, to enable AlwaysOn on the ALTERNATE instance on server SQL-B, issue the following command:

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\SQL-B\ALTERNATE

If you choose to disable AlwaysOn on a Database Engine instance, either by using the Disable-SqlAlwaysOn PowerShell cmdlet or by using SQL Server Configuration Manager, you must restart the associated SQL Server service.

Creating an Availability Group

After AlwaysOn is enabled at the Database Engine instance level, you can create availability groups. To create an availability group by using SQL Server Management Studio, perform the following steps:

  1. In SQL Server Management Studio, on the instance that hosts the primary replica, expand the AlwaysOn High Availability node.

  2. Right-click Availability Groups and click New Availability Group Wizard.

  3. On the Specify Availability Group Name page, provide a name for the availability group.

  4. On the Select User Databases For The Availability Group page, shown in Figure 8-21, select the databases you will add to the availability group. You cannot create an availability group by using this wizard unless you can add at least one database. This page also informs you of whether the database meets the availability group’s prerequisites or must be backed up before it can be added.

    Figure 8-21

    Figure 8-21. Adding a database to an availability group

  5. On the Specify Replicas page, shown in Figure 8-22, click Add Replica. In the Connect To Server dialog box, specify the credentials you use to connect. Add the instances that will function as replicas. You can also use this page of the wizard to configure an availability group listener.

    Figure 8-22

    Figure 8-22. Specifying replicas

  6. On the Select Initial Data Synchronization page, specify the location of a network share that allows read/write access to the SQL Server service account of all replicas.

  7. On the Validation page, verify that all processes except Checking The Listener Configuration are competed successfully. You learn how to create an availability group listener in the Creating or Adding an Availability Group Listener section in this chapter.

  8. Review the summary and complete the wizard.

You cannot use the New Availability Group Wizard or Add Database To Availability Group Wizard to add a database to an availability group if that database is encrypted or contains a Database Encryption Key. You also cannot use the New Availability Group Wizard to add replicas that use different paths for database and log files. You must add these replicas manually. You learn how to perform this task in the Adding Secondary Replicas section in this chapter.

You can use the CREATE AVAILABILITY GROUP Transact-SQL statement to create an availability group. For example, to create an availability group with the following properties:

  • Name: AG-BETA

  • Database: Saturn

  • Replica instances: SQL1.contoso.com\newinstance, SQL2.contoso.com\newinstance

  • Endpoint TCP port: 7030

  • Failover mode: Manual

  • Availability mode: Asynchronous

execute the following Transact-SQL code:

CREATE AVAILABILITY GROUP AG-BETA        FOR
                 DATABASE Saturn
        REPLICA ON
        'SQL1\newinstance' WITH
                 (
                 ENDPOINT_URL = 'TCP://sql1.contoso.com:7030',
                 AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                 FAILOVER_MODE = MANUAL
        ),
        'SQL2\newinstance' WITH
                  (
                 ENDPOINT_URL = 'TCP://sql2.contoso.com:7030',
                 AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                 FAILOVER_MODE = MANUAL
        );
GO

Creating or Adding an Availability Group Listener

httpatomoreillycomsourcemspimages1685956.jpg An availability group listener is a network connectivity endpoint for an availability group. Clients connect to the listener, which in turn connects them to the availability group’s primary instance. You can create one listener per availability group by using SQL Server Management Studio. If you need more than one listener for an availability group, it is possible to create additional listeners by using Windows PowerShell or the Failover Cluster Manager console.

To create an availability group listener, you must be connected to the Database Engine instance that hosts the primary replica. To create an availability group listener for an existing availability group by using SQL Server Management Studio, perform the following steps:

  1. In SQL Server Management Studio, navigate to the AlwaysOn High Availability node and then expand the Availability Groups node. Right-click the availability group for which you will create a listener and click Add Listener.

  2. On the New Availability Group Listener page, shown in Figure 8-23, specify a Listener DNS Name and a TCP Port. In the Network Mode box, select either DHCP or Static IP. If using a static IP, specify the static IP address.

Figure 8-23

Figure 8-23. Availability group listener

You can add a listener to an existing availability group by using the ALTER AVAILABILITY GROUP Transact-SQL statement. For example, to add a listener named Beta-Listener to the AG-Alpha availability group that uses IP address 10.0.0.222, subnet mask 255.0.0.0, and port 7028, execute the statement:

ALTER AVAILABILITY GROUP [AG-Alpha]
      ADD LISTENER 'Beta-Listener' (with IP (('10.0.0.222','255.0.0.0')), PORT=7028);
GO

You can create an availability group listener by using the New-SqlAvailabilityGroupListener cmdlet. For example, to create a new availability group listener on instance SQL-C\AlwaysOn named Gamma-Listener to the AG-Gamma availability group that uses IP address 10.0.0.224, subnet mask 255.0.0.0, and port 7030, use the command:

New-SqlAvailabilityGroupListener -Name Gamma-Listener -StaticIP '10.0.0.224/255.0.0.0'
-Port 7030 -Path SQLSERVER:\SQL\SQL-C\ALWAYSON\AvailabilityGroups\AG-Gamma

Adding Secondary Replicas

You can add secondary replicas to an existing availability group under the following conditions:

  • The availability group has fewer than four secondary replicas.

  • The primary replica of the availability group is online.

  • You are connected to the Database Engine instance that will host the secondary replica.

  • The Database Engine instance that will host the secondary replica can connect to the mirroring endpoint on the primary replica.

  • You have enabled AlwaysOn Availability Groups on the Database Engine instance that will host the secondary replica.

To join a secondary replica to an availability group by using SQL Server Management Studio, perform the following steps:

  1. On the Database Engine instance that hosts the secondary replica, right-click the secondary replica under the AlwaysOn High Availability\Availability Groups node and click Join To Availability Group.

  2. In the Join Replica To Availability Group dialog box, verify the details and click OK.

You can use the ALTER AVAILABILITY GROUP Transact-SQL statement to join a secondary replica to an availability group. For example, to join the AG-Delta availability group, execute the following Transact-SQL statement on the Database Engine instance that hosts the secondary replica:

ALTER AVAILABILITY GROUP AG-Delta JOIN;

You can also use the Join-SqlAvailabilityGroup cmdlet to join a secondary replica to an availability group. For example, to join the SQL-E\AlwaysOn instance to the AG-Delta availability group, execute the command:

Join-SqlAvailabilityGroup -Path SERVER:\SQL\SQL-E\AlwaysOn -Name 'AG-Delta'

Using Availability Groups on Failover Cluster Instances

Although you must deploy availability groups on a host server that is a member of a Windows Server failover cluster, the instance on which you deploy availability groups is not usually a failover cluster instance. You can use availability groups with SQL Server failover cluster instances, but you cannot use all availability group functionality. The following restrictions apply in this scenario:

  • Only one failover cluster instance partner can host a replica. A failover partner cannot host a secondary replica for the same availability group.

  • Failover cluster instances support only manual failover. You cannot configure AlwaysOn automatic failover to a replica on a failover cluster instance.

  • Failover cluster instances do not support initial data synchronization by using the New Availability Group Wizard, Add Database To Availability Group Wizard, or Add Replica To Availability Group Wizard.

If you are using a failover cluster instance with AlwaysOn Availability Groups, you must prepare the secondary database on the instance by using a different method, such as backup and restore, and then join that secondary database to the availability group.

Practice Deploying AlwaysOn Availability Groups

Practice Deploying AlwaysOn Availability Groups

In this practice, you deploy AlwaysOn Availability Groups.

EXERCISE 1 Prepare for AlwaysOn Availability Groups

In this exercise, you prepare the servers for the deployment of AlwaysOn Availability Groups. To complete this exercise, perform the following steps:

  1. On the domain controller, edit the SQL-POLICY GPO.

    1. Add an Isolation connection security rule that requires authentication for inbound connections and requests authentication for outbound connections by using Computer (Kerberos V5) authentication for all profiles.

    2. Create an Inbound Port–based rule that allows TCP traffic on all local ports if the connection is secure and comes from computers DC, SQL-A, SQL-B, SQL-C, SQL-D, and SQL-CORE, as shown in Figure 8-24. Enable this rule in all profiles.

      Figure 8-24

      Figure 8-24. Isolation rule

    3. Create an Inbound Port–based rule that allows UDP traffic on all local ports if the connection is secure and comes from computers DC, SQL-A, SQL-B, SQL-C, SQL-D, and SQL-CORE. Enable this rule in all profiles.

  2. Log on to servers SQL-C and SQL-D with the Kim_Akers user account.

  3. Run gpupdate /force from an elevated command prompt to apply the new firewall rule to these computers.

  4. Create a new shared folder named Share on SQL-C in the C:\Share directory. Configure the share so that the Contoso\SQL-CLUSTER user has read\write access.

  5. Install a new Database Engine instance named AlwaysOn on SQL-C and SQL-D. Configure Contoso\SQL-Cluster as the SQL Server service account and configure Kim_Akers as the SQL Administrator on these instances by running the following command from an elevated command prompt on each server:

    setup.exe /qs /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=AlwaysOn /
    SQLSVCACCOUNT="CONTOSO\SQL-CLUSTER" /SQLSVCPASSWORD="Pa$$w0rd" /
    SQLSYSADMINACCOUNTS="Contoso\Kim_Akers" /AGTSVCACCOUNT="CONTOSO\SQL-CLUSTER" /
    AGTSVCPASSWORD="Pa$$w0rd" /IACCEPTSQLSERVERLICENSETERMS
  6. On server SQL-C, use SQL Server Management Studio to connect to the SQL-C\AlwaysOn instance. Right-click the SQL-C\ALWAYSON node and click Start PowerShell. Create a mirroring endpoint on this instance by using the following commands:

    $endpoint = New-SqlHadrEndpoint AlwaysOnEndpoint -Port 7026 -Path   SQLSERVER:\SQL\SQL-C\ALWAYSON
    Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"
  7. On server SQL-D, use SQL Server Management Studio to connect to the SQL-D\AlwaysOn instance. Right-click the SQL-C\ALWAYSON node and select Start PowerShell. Create a mirroring endpoint on this instance by using the following commands:

    $endpoint = New-SqlHadrEndpoint AlwaysOnEndpoint -Port 7026 -Path   SQLSERVER:\SQL\SQL-D\ALWAYSON
    Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"
  8. Use SQL Server Configuration Manager to enable AlwaysOn Availability Groups on the SQL Server (ALWAYSON) service on SQL-C and SQL-D.

  9. Use SQL Server Configuration Manager to restart the SQL Server (ALWAYSON) service on SQL-C and SQL-D.

EXERCISE 2 Create a Database and Add It to a New Availability Group

In this exercise, you create a database and add it to a newly created availability group. To complete this exercise, perform the following steps:

  1. On the SQL-C\ALWAYSON instance, create a new database named Jupiter by using the default settings.

  2. Perform a full backup of database Jupiter.

  3. Use the New Availability Group Wizard to create a new availability group named Availability Group Alpha.

  4. Add the Jupiter database to the new availability group.

  5. Ensure that SQL-C\ALWAYSON and SQL-D\ALWAYSON are configured as replicas.

  6. Choose Full as the data synchronization preference and use the \\SQL-C\Share share as the accessible network location.

EXERCISE 3 Create an Availability Group Listener

In this exercise, you create an availability group listener for the availability group you configured in the previous exercise. To complete this exercise, perform the following steps:

  1. Use SQL Server Management Studio to create an availability group listener for the Availability Group Alpha availability group that uses the following properties:

    • Listener DNS Name: Alpha-Listener

    • Port: 7028

    • Static IP: 10.0.0.222

EXERCISE 4 Configure Availability and Failover Mode

In this exercise, you configure availability modes and failover modes and then perform manual failover. To complete this exercise, perform the following steps:

  1. Configure Availability Group Alpha so that:

    • Both instances use the synchronous-commit availability mode.

    • SQL-C\AlwaysOn uses the automatic failover mode.

    • SQL-D\AlwaysOn is a Readable Secondary.

  2. Perform manual failover from the primary to the replica instance.

Lesson Summary

  • The AlwaysOn Availability Groups feature is an alternative to database mirroring.

  • Availability groups are supported in production on SQL Server 2012 Enterprise edition only.

  • An AlwaysOn availability group can have one primary and four secondary replicas.

  • You must create mirroring endpoints either before or during the availability group creation process.

  • An availability group replica can contain multiple databases.

  • You can configure secondary replicas to be available to read-only queries.

  • Failover occurs on a per-replica basis.

  • Synchronous-commit mode involves higher transaction latency but allows manual and automatic failover.

  • Asynchronous-commit mode minimizes transaction latency, is suitable for geographically dispersed clusters, but only supports forced failover.

  • You can perform availability group failover by using SQL Server Management Studio, the ALTER AVAILABILITY GROUP Transact-SQL statement, or the Switch-SqlAvailabilityGroup PowerShell cmdlet.

  • You can have only one listener per availability group.

Lesson Review

Answer the following questions to test your knowledge of the information in this lesson. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the Answers section at the end of this chapter.

  1. Which tool can you use to enable AlwaysOn Availability Groups on a SQL Server 2012 instance?

    1. SQL Server Management Studio

    2. SQL Server Installation Center

    3. SQL Server Configuration Manager

    4. SQL Server Data Tools

  2. Which Windows PowerShell cmdlet can you use to perform manual availability group failover?

    1. Switch-SqlAvailabilityGroup

    2. New-SqlHadrEndpoint

    3. New-SqlAvailabilityGroupListener

    4. Enable-SqlAlwaysOn

  3. Which Windows PowerShell cmdlet can you use to create a mirroring endpoint on an instance when preparing it for the deployment of AlwaysOn Availability Groups?

    1. New-SqlAvailabilityGroupListener

    2. Switch-SqlAvailabilityGroup

    3. Enable-SqlAlwaysOn

    4. New-SqlHadrEndpoint

  4. You will configure an AlwaysOn Availability Group to support automatic failover from the primary replica to any available secondary replica. Which of the following availability modes should you configure for the replicas in this availability group? (Each correct answer forms part of a complete solution. Choose all that apply.)

    1. Configure the primary replica to use the asynchronous-commit availability mode.

    2. Configure the primary replica to use the synchronous-commit availability mode.

    3. Configure the secondary replica to use the asynchronous-commit availability mode.

    4. Configure the secondary replica to use the synchronous-commit availability mode.