Using SQL Server Always On Availability Groups with vCloud Director
Article ID: 344219
Updated On:
Products
VMware Cloud Director
Issue/Introduction
A vCloud Director server group can use Microsoft SQL Server with Always On Availability Groups as the vCloud Director database. Multi-subnet failover clusters are not supported.
Environment
VMware Cloud Director for Service Provider 9.x
VMware Cloud Director for Service Provider 9.0.x
VMware Cloud Director for Service Provider 8.20.x
VMware Cloud Director for Service Provider 9.0.x
VMware Cloud Director for Service Provider 8.20.x
Resolution
When using Microsoft SQL Server with Always On Availability Groups, database failover can incur downtime for user and system operations.
Perform these steps to create a successful VCD setup with AAG support.
Note: 2 nodes are used here
11. VCD service should be up and running
For more information, see the Microsoft TechNet article Overview of Always On Availability Groups (SQL Server)
Perform these steps to create a successful VCD setup with AAG support.
Note: 2 nodes are used here
- Deploy 3 nodes – 1 for Domain Controller, 2 for each of the DB nodes – primary and secondary.
- Setup Domain Controller.
- Setup Windows Cluster using the 2 database nodes.
- Install SQL Server in standalone mode on both the DB nodes.
- Create VCD DB on primary node as in Configure a Microsoft SQL Server Database (from steps 3-6)
- Create AAG cluster using the above 2 nodes
- In SQL Server, each user in DB should be mapped to login. After the AAG cluster is created(step 6), the secondary node will not have the login for user vCloud (created on primary node as per step 5). To avoid issues during failover after VCD is installed, create this login for vcloud user on this secondary node.
Run below commands on the secondary node:
Note: Do not create this login from UI as it would cause mismatch of SIDs later
a. Run this command to find the SID:
use vcloud
go
select name,sid from sys.sysusers where name ='vcloud'
go
b. Copy the SID from the above command and use it for the login creation:
create login vcloud
a. Run this command to find the SID:
use vcloud
go
select name,sid from sys.sysusers where name ='vcloud'
go
b. Copy the SID from the above command and use it for the login creation:
create login vcloud
with password = '***same_as_that_used_on_primary_node***', sid=***sid_from_above_select_query***,check_policy=off;
c. Run the following query to ensure that the vcloud user login now exists:
use master
go
select name,sid from sys.sql_logins where name = 'vcloud'
go
c. Run the following query to ensure that the vcloud user login now exists:
use master
go
select name,sid from sys.sql_logins where name = 'vcloud'
go
8. Test failover manually
9. Install VCD bits (run the *.bin file)
10. Run configure (either at the end of the *.bin or by manually invoking /opt/vmware/vcloud-director/bin/configure )9. Install VCD bits (run the *.bin file)
11. VCD service should be up and running
For more information, see the Microsoft TechNet article Overview of Always On Availability Groups (SQL Server)
Feedback
Yes
No
Powered by
