In this demonstration, you will see how to use an AlwaysOn availability group. Verify Cluster and AlwaysOn Configuration
1. Ensure that the MIA-DC1, MIA-CLUST1, MIA-CLUST2, and MIA-CLUST3 virtual machines are running. 2. When all virtual machines are running, log into MIA-CLUST1 as ADVENTUREWORKS\Student with
the password Pa$$w0rd.
3. In the D:\Demofiles folder, right-click Setup.cmd and click Run as administrator. When prompted, click Yes to run the script.
4. Click Start, click Administrative Tools, and click Failover Cluster Manager.
5. In the tree pane on the left, expand MIA-CLUSTER.AdventureWorks.msft and click Nodes. Then verify that the following cluster nodes are listed with a status value of Up.
MIA-CLUST1
MIA-CLUST2
MIA-CLUST3
6. Close Failover Cluster Manager.
7. Click Start, point to All Programs, click Microsoft SQL Server 2012, click Configuration Tools, and click SQL Server Configuration Manager. When prompted, click Yes to allow the program to make changes to the computer.
8. In the tree pane on the left, select SQL Server Services, and in the pane on the right, double-click SQL Server (MSSQLSERVER). Then on the AlwaysOn High Availability tab, verify that AlwaysOn Availability Groups are enabled for the MIA-CLUSTER failover cluster. This setting has been applied to all of the servers in the cluster.
9. Click Cancel to close the SQL Server (MSSQLSERVER) Properties dialog box, and close SQL Server Configuration Manager.
Perform a Full Database Backup
1. Click Start, point to All Programs, click Microsoft SQL Server 2012, and click SQL Server
Management Studio. When prompted, use Windows authentication to connect to the MIA-CLUST1 instance of the database engine.
2. In Object Explorer, expand Databases and note the Sales database. 3. Right-click the Sales database, point to Tasks, and click Back Up.
4. In the Back Up Database – Sales dialog box, in the Destination list, select the existing backup file path and click Remove. Then click Add, and in the Select Backup Destination dialog box, enter the path D:\Demofiles\Sales.bak and click OK.
5. In the Back Up Database – Sales dialog box, ensure that the Full backup type is selected, and then click OK. When the backup has completed successfully, click OK.
Create an AlwaysOn Availability Group
1. In SQL Server Management Studio, in Object Explorer, expand AlwaysOn High Availability. Then right-click Availability Groups and click New Availability Group Wizard.
2. Read the Introduction page, and then click Next.
3. On the Specify Availability Group Name page, enter the name MIA-SQL-AG. Then click Next. 4. On the Select Databases page, select the Sales database, and then click Next.
5. On the Specify Replicas page, on the Replicas tab, click Add Replica. Then in the Connect to Server dialog box, connect to MIA-CLUST2 by using Windows authentication.
6. Repeat the previous step to add MIA-CLUST3 as a replica.
7. On the Replicas tab, select the Automatic Failover checkbox for MIA-CLUST1 and MIA-CLUST2. This automatically selects the Synchronous Commit checkbox for these replicas.
8. On the Replicas tab, in the Readable Secondary list for MIA-CLUST2, select Read-Intent Only. 9. On the Replicas tab, in the Readable Secondary list for MIA-CLUST3, select Yes.
10. Review the default settings on the Endpoints and Backup Preferences tabs, and then on the Listener tab, select Create an availability group listener and specify the following settings:
Listener DNS Name: MIA-SQL-CLUST
Port: 1433
Network Mode: Static IP
Click Add, and in the Add IP Address dialog box, enter the IPv4 address 10.10.0.40 and click OK.
11. On the Specify Replicas page, click Next.
12. On the Select Initial Data Synchronization page, ensure that Full is selected and specify the shared network location \\MIA-CLUST1\DataShare. Then click Next.
15. On the Results page, review the warning that is generated (which indicates that a cluster quorum of 3 is not recommended because in the event of a primary node failure, the remaining nodes may not produce a majority vote for new primary node). Then click Close.
View Availability Group Configuration
1. In SQL Server Management Studio, in Object Explorer, under Availability Groups, expand MIA-SQL- AG (Primary) and all of its subfolders.
2. Right-click MIA-SQL-AG (Primary) and click Show Dashboard.
3. View the status information for the availability group, and then minimize SQL Server Management Studio – you will use it again later in the demonstration.
Connect to an AlwaysOn Availability Group
1. Click Start, click All Programs, click Accessories, and click Command Prompt.
2. Enter the following command to open a SQLCMD session and connect to the MIA-SQL-CLUST availability group listener:
sqlcmd –E –S MIA-SQL-CLUST
3. Enter the following commands to verify that the SQLCMD session is connected to the primary replica (MIA-CLUST1).
SELECT @@ServerName GO
4. Enter the following commands to retrieve rows from the Orders table in the Sales database. SELECT * FROM Sales.dbo.Orders
GO
5. View the results.
6. Enter the following command to exit the SQLCMD session. Exit
7. Keep the command prompt open for the next task. Use a Readable Secondary Replica
1. Enter the following command to open a SQLCMD session and connect to the MIA- CLUST3 replica: sqlcmd –E –S MIA-CLUST3
2. Enter the following commands to retrieve rows from the Orders table in the sales database. SELECT * FROM Sales.dbo.Orders
GO
3. View the results.
4. Enter the following command to exit the SQLCMD session. Exit
Use a Readable Secondary Replica with a Read-Intent Connection
1. Enter the following command to open a SQLCMD session and connect to the MIA- CLUST2 replica: sqlcmd –E –S MIA-CLUST2
2. Enter the following commands to retrieve rows from the Orders table in the sales database. SELECT * FROM Sales.dbo.Orders
GO
3. View the error that is returned.
4. Enter the following command to exit the SQLCMD session. Exit
5. Enter the following command to open a SQLCMD session and connect to the MIA- CLUST2 replica with a read-intent connection:
sqlcmd –E –S MIA-CLUST2 –K ReadOnly
6. Enter the following commands to retrieve rows from the Orders table in the sales database. SELECT * FROM Sales.dbo.Orders
GO
7. View the results.
8. Enter the following command to exit the SQLCMD session. Exit
9. Minimize the command prompt window. You will use it again later in the demonstration. Perform a Manual Failover
1. Maximize SQL Server Management Studio and view the dashboard for the MIA-SQL-AG availability group.
2. At the top right of the dashboard, click Start Failover Wizard. 3. Read the introduction page, and click Next.
4. On the Select New Primary Replica page, select MIA-CLUST2. Then click Next.
5. On the Connect to Replica page, click Connect and in the Connect to Server dialog box, connect to MIA-CLUST2 by using Windows authentication. Then click Next.
6. On the Summary page, click Finish. 7. On the Results page, click Close.
8. In the dashboard, wait a few minutes for the page to refresh, and then note that the primary instance is now MIA-CLUST2 and that MIA-CLUST1 is a secondary replica.
9. In the command prompt window, enter the following command to open a SQLCMD session and connect to the MIA-SQL-CLUST availability group listener:
10. Enter the following commands to verify that the SQLCMD session is connected to the new primary replica (MIA-CLUST2).
SELECT @@ServerName GO
11. Enter the following command to exit the SQLCMD session. Exit
12. Keep the command prompt open for the next task. Observe Automatic Failover
1. In the command prompt window, enter the following command to stop the SQL Server service on MIA-CLUST2.
sc \\MIA-CLUST2 stop mssqlserver
2. Enter the following command to open a SQLCMD session and connect to the MIA-SQL-CLUST availability group listener:
sqlcmd –E –S MIA-SQL-CLUST
3. Enter the following commands to verify that automatic failover has resulted in MIA-CLUST1 resuming the primary replica role.
SELECT @@ServerName GO
4. Enter the following command to exit the SQLCMD session. Exit
5. Close the command prompt and SQL Server Management Studio. Then shut down the MIA-CLUST1, MIA-CLUST2, and MIA-CLUST3 virtual machines.
Module Review
Module Summary
In this module, you have learned about the new and enhanced features for database development and management in SQL Server 2012.