The purpose of this Blog is to provide detailed information on setting up Always on Availability Group on SQL Servers and use this AOAG enabled Database for XenMobile 10 environment. This includes step by step instructions for AOAG configuration.
For this activity, you would need the following:
- At least 2 Instance of SQL 2012 Enterprise VMs in the same Domain
- Active Directory Environment
- Windows Failover Cluster
- CIFS Share on any windows Machine or a File Server.
- Service Account (Domain User account) which is also used as a SQL Administrator in this lab setup ( with RW access to the CIFS share)
|AOAG||Always on Availability Group|
|WSFC||Windows Server Failover Cluster|
The Hostnames, Usernames and IP Addresses used in the screenshots are for the illustration purposes only. Please use the appropriate Hostnames, Usernames and IP Addresses from your environment.
|AD Windows Server 2012||Virtual Machine (2 No)|
|MSSQL 2012 Enterprise Edition||Virtual Machine (2 to 5 No)|
|WSFC||Windows Server Failover Cluster|
|MSSQL 2012||Virtual Machine (2 No)|
|Same SQL Server collation for all replicas|
Please refer to this article http://msdn.microsoft.com/en-us/library/ff878487.aspx for the detailed prerequisites.
Windows Failover Cluster Creation
Overview of Windows Failover Cluster
Windows Server Failover Clustering provides infrastructure features that support the high-availability and disaster recovery scenarios of hosted server applications such as Microsoft SQL Server and Microsoft Exchange. If a cluster node or service fails, the services that were hosted on that node can be automatically or manually transferred to another available node in a process known as failover.
1. Launch Server Manager on SQL server 1 and click on Add Roles and Features
2. Click Next
3. Select Role-based or feature based installation
4. Here we are selecting the first SQL Server (Local Server) which is going to be a part of failover cluster.
5. Click Next
6. Select .NET framework 3.5 features
7. Click on Failover clustering check box
8. Click on Add features required for Failover clustering.
9. Click Next
10. Click Next
11. Click Next
12. Click on Specify Alternate Source Path to install .NET framework 3.5 from Windows 2k12 DVD ISO.
13. Specify the path as shown below. Here D:\ is the DVD drive loaded with Windows 2012 ISO on the SQL1 Virtual Machine.
14. Click on Install
15. Wait for the .Net Framework 3.5 installation to complete
16. Once the installation is completed, click Close.
17. Repeat the Steps 1 to 16 on SQL Node 2.
18. Goto Start-> Administrative tools
19. Launch the Failover cluster manager as shown below to start creating the Windows Failover Cluster.
20. This is the Failover Cluster Manager Window. Click on Create Cluster.
21. Click Next
22. Browse for the SQL Server 1
23. Enter the SQL server1 name and click check
24. Click OK
25. Repeat steps 21,22,23 to add SQL Server 2 to the cluster.
26. Now we can see both the server listed as shown below
27. Select Yes to run the configuration validation tests.
28. Click Next to get the cluster validation started
29. Select Run All tests and click Next
30. Click Next
31. Once the validation is done, click Finish. Here we can ignore the errors or warnings for the storage tests as we are not using any Shared Storage/SAN for the cluster.
32. Click Finish
33. Now we need to enter the name for this cluster and the virtual IP of the cluster. This is the access point for administering the cluster.
34. Enter the Cluster Name and the IP Address.
35. Click Next
36. Wait for the opearation to Finish
37. Click Finish
38. We can see the cluster status as shown below
SQL Server Installation
1. Launch the SQL Installer Media and click on Installation.
2. Click on New SQL Server Standalone Installation
3. Click OK once all the checks are passed.
4. Enter the SQL Product Key and Click Next
5. Accept the License Terms and Click Next
6. Wait for the test operation to be completed. You can fix the warnings as shown below.
For example: You can disable the windows firewall and re-run the tests.
7. Select SQL Server Feature Installation
8. We need the Database Engine services for the Always on Availability Group (AOAG)
Under Database Engine services Select SQL Server Replication and Full Text and Semantic Extractions for Search. Under Shared Services, select Client Tools connectivity and click Next.
Select Management Tools – Basic and Management Tools – Complete.
9. Enter the Name of the SQL Instance on SQL Server 1. You can choose any name. Here we have chosen SQLCluster1.
10. Click Next
11. Here we need to browse for the SQL Service Account Name (Domain account) for the SQL Server Agent and SQL Server Database Engine.
12. For the purpose of illustration, we have created a domain user account whose name is svcsql1_sql. Here svc represents the service account and sql1 represents the name of the SQL server1 and sql shows the service type is sql.
13. Enter the password for this account.
14. Select Windows Authentication Mode and click on Add current user for SQL Administrator.
Click on Add again and add the service account we used in the previous step as an administrator
15. Click Next
16. Under Data Directories, Please specify the directories as shown below (You can use any drive or path, But you need to use the same paths across all of the SQL Server instances we’re using for AlwaysOn)
Note: You should use the same path on the second SQL server installation.
17. Click Next
18. Click Next
19. Click on Install
20. Click Close once the installation is completed
21. Launch the SQL Server Configuration
22. Click on SQL Server Services. Right click on the SQL Server on the right side and click properties.
23. Check “Enable Always on Availability Groups”
24. Click on OK
25. Right click the SQL Server service and click on Restart
26. Repeat the Steps 1 to 25 for SQL Server 2.
Always On Availability Group Creation
AlwaysOn Availability Groups allows multiple copies of the database be highly available. AOAG allow us to fail over a group of databases as a single entity, unlike database mirroring where you can only do so one database at a time.
1. Create a CIFS Network share with the RW permissions for the SQL Admin user we have created earlier. This share is required to initialize replicas with Backup and Restore.You can use a File Server or a share on SQL Server itself.
2. Launch SQL Server Management Studio. Click on AlwaysOnHigh Availability and right Click on Availability Group and Click on New Availability Wizard. Take the Full Backup of all the Databases for which you are planning to create an Availability group.
3. Click on Next
4. Enter the name of the Availability Group
5. Select the required database for availability group. Here as an example, we have selected few test databases along with the XMS database. You can create separate availability group and have only XMS database as part of it.
6. Click on Add Replica to add Second SQL Server as a Replica.
7. Connect to the Second SQL Server.
8. Select the Checkboxes as shown below. Here SQL1 acts as the current Primary and SQL2 is the secondary and both set to Automatic Failover and synchronous commit happening for both these nodes.
9. Click on Browse and Select the Network Share you created in Step1.
10. Browse for the Share Folder
11. Click on Next
12. Click Next
13. Click Finish
14. We can see the backup and restore of the databases in the availability groups from/to the replicas. Click Close
15. We can see the databases on the SQL2 in the synchronized state.
Manual Failover of Availability Groups.
1. Select the Availability groups which you have created and click on Start Failover wizard. You can test the Availability groups Failover here.
2. Click Next
3. Choose SQL2 as the New Primary Replica.
4. It shows the affected Databases. Click Finish to start the Failover
5. Wizard completed successfully and manual failover to SQL2 is completed
6. Now SQL2 Serve is the primary and we can see the table we created earlier has been replicated to SQL2.
AOAG Listener Configuration
Availability Group Listener is a virtual IP we can use for our database connections. Once the availability group Listener is configured, we can connect to this IP and the port number for example to perform database creation etc.
1. Right Click on the Availability Group Listeners in SQL Server Management Studio
2. Enter the DNS name of the listener, port as 1433 (default SQL port) and Select Network Mode as Static IP.
3. Click on Add
4. Enter the IP Address for the Listener
5. Click OK
6. We can see the Listener Created. We can point XMS server to this IP for database creation.
Configuration of XMS from CLI
1. Open the console of the VM from the hypervisor (Xen/VMware/Microsoft) and enter a new password for Admin account
2. Provide the network configuration details as below
3. If you want to use the default password for data protection, you can type ‘y’ here else type ‘n’ and enter a new password
When you type n:
4. Type y or n to enable or disable FIPS Mode. For this example, we are continuing with FIPS disabled.
If you enable FIPS Mode, then only the remote database is supported.
5. To configure XMS with remote Database, type ‘r’ as shown below.
Enter the AOAG listener IP Addressfor the Server Field and Enter the AOAG Listner Port Number and the Username and Password for the user and type Y to commit the changes.
6. Press Y to enable cluster.
7. Enter the FQDN of your XMS server
8. Press Y to commit changes
9. Modify the ports as required or go with the default ports
10. Enter the instance name. Here default is ZDM, you can modify it as needed
Note: This is used to access your ZDM instances such as helper.jsp or shp
11. Enter the passwords for the certificates.
If you want to enter different passwords for each certificate, type ‘n’ and enter the passwords as below
12. Create the administrator account, with which you want to login to XMS server console.
13. Type ‘n’ (which is a default option) as we are not upgrading it from Previous version.
14. Wait for the configuration to complete.
15. Once the configuration is completed, you will get the login prompt