Steps Required: SQL AlwaysOn Availability Group High Availability

[mohamednazar]

**Want to know correct steps for successfully enabling Always on High Availability with WFSC **

I am using chef recipe with DSC_Resource for setting up SQL always on. I am not sure how we can roles like PrimaryReplica and SecondaryReplica... as per the examples in Github those are created in configurationdata. How can I create the same via DSC_Resource in chef recipe?

`$ConfigurationData = @{
AllNodes = @(
@{
NodeName = '*'
SQLInstanceName = 'MSSQLSERVER'
},

    @{
        NodeName = 'SP23-VM-SQL1'
        Role     = 'PrimaryReplica'
    }
)

}`

How can I done via DSC_Resource?

Also what is the order script execution we need to done in Primary and Secondary server? Currently I am following below. Correct me if I am wrong.

	PrimaryReplica
	a. Install Cluster Feature
	b. Create Cluster
	c. Enable Quoram config in Cluster
	d. Enable Always On in SQL Configuration
	e. Enable Quorum sharing folder FULL access for primary and secondary node
	f. Create Always on Database
	g. Take full backup of always on and share it to secondary node
	h. Create Availability Group
	i. Create Availability Group Primary Replica
	j. Create Database Groupmembership
	k. Create Listener
	
	SecondaryReplica		
	a. Install Cluster Feature
	b. WaitforCluster
	d. Enable Always On in SQL Configuration
	e. Enable Quorum sharing folder FULL access for primary and secondary node
	f. Create Always on Database
	g. Restore database from backup
	h. WaitForAvailabilityGroup
	i. Create Availability Group Secondary Replica
	j. Create Database Groupmembership
	k. Create Listener

The DSC configuration that is using the resource (as detailed as possible):

Latest versions

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:

Windows 2012 R2 Standard edition

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:

9.0.0.0

Version of the DSC module you're using, or 'dev' if you're using current dev branch:

dev

[johlju]

I can't help you with chef since I'm not familiar with Chef. Also, we do not have a full step-by-step examples as of yet. It's being worked on in issue #462.

Please see the following example. It uses the same configuration for both the Primary and Secondary replica in the beginning of the configuration. Then, depending which replica, it uses xSQLServerAlwaysOnAvailabilityGroup and xSQLServerAlwaysOnAvailabilityGroupReplica.

https://github.com/PowerShell/xSQLServer/blob/dev/Examples/Resources/xSQLServerAlwaysOnAvailabilityGroupReplica/1-CreateAvailabilityGroupReplica.ps1

For the secondary replica you probably should add to wait for the availability replica to be created on the primary replica.

https://github.com/PowerShell/xSQLServer/blob/dev/Examples/Resources/xWaitForAvailabilityGroup/2-WaitForMultipleClusterGroups.ps1

After that you should create a listener using xSQLServerAvailabilityGroupListener resource.

https://github.com/PowerShell/xSQLServer/blob/dev/Examples/Resources/xSQLServerAvailabilityGroupListener/1-AddAvailabilityGroupListenerWithSameNameAsVCO.ps1

So the steps should be like this for both the Primary replica and Secondary replicas. Anyone correct me if I'm wrong.
See the examples for each resource to see how they should be used, as well as the resource and parameter descriptions in the README.md.

1. Install Cluster Feature; 'Failover-clustering', 'RSAT-Clustering-Mgmt', 'RSAT-Clustering-PowerShell','RSAT-Clustering-CmdInterface'
2. (Secondary replicas only) Wait for cluster using xWaitForCluster.
3. Create Cluster or join cluster using xCluster. Note: same for all replicas regardless of type. Primary will create and secondaries will join, since secondaries are waiting for cluster to be created in the previous step.
4. (Optional) (Primary replica only) Set quorum using xClusterQuorum.
5. Enable AlwaysOn service using xSQLServerAlwaysOnService.
6. Add the the 'NT SERVICE\ClusSvc' account using xSQLServerLogin (see AlwaysOn-example above).
7. Grant the correct permissions for 'NT SERVICE\ClusSvc' using xSQLServerPermission (see AlwaysOn-example above).
8. Create a new default mirror endopoint using xSQLServerEndpoint (see AlwaysOn-example above).
9. (Optional) Make sure the endpoint is started using xSQLServerEndpointState.
10. Make sure the SQL Server service account has connect permission to the default mirror endpoint using xSQLServerEndpointPermission. Note: If the replicas using different accounts, then all must have connect permission.
11. Create Availability Group using xSQLServerAlwaysOnAvailabilityGroup (see AlwaysOn-example above).
12. (Secondary replicas only) Wait for the availability group xWaitForAvailabilityGroup.
13. (Secondary replicas only) Create Availability Group replica using xSQLServerAlwaysOnAvailabilityGroupReplica.
14. (Primary replica only) Add a availability group listener using xSQLServerAvailabilityGroupListener.
15. (Primary replica only) Create a user database.
16. Add the user database to the availability group using xSQLServerAlwaysOnAvailabilityGroupDatabaseMembership. This will backup the database on the primary, restore on secondaries and start synchronization. Note: Use only on primary replica, unless the ProcessOnlyOnActiveNode is used.

[johlju]

On top of that you need to make sure the account used to run cluster configuration has the right permission to create CNO in Active Directory (unless pre-staging). And that the CNO has the correct permission in Active Directory to create the VCO's.

[johlju]

@mohamednazar Did you get an answer to your question?

[johlju]

Closing this due to lack of activity. Please reopen if there are any unanswered questions.

[mohamednazar]

Yes its answered my question. Thanks

[tolstyiii]

@johlju Hello, I used this instructions for building Always on group via DSC, but stopped at the point creating Availability Group on the primary replica. I try to create it in 2 Azure VMs. Section of adding AG is:

SqlAG AddAG
{
Ensure = 'Present'
Name = $Node.AGName
InstanceName = $Node.SQLInstanceName
ServerName = $Node.NodeName
ProcessOnlyOnActiveNode = $true

        AutomatedBackupPreference     = 'Secondary'
        AvailabilityMode              = 'SynchronousCommit'
        BackupPriority                = 50
        ConnectionModeInPrimaryRole   = 'AllowAllConnections'
        ConnectionModeInSecondaryRole = 'AllowNoConnections'
        FailoverMode                  = 'Automatic'
        HealthCheckTimeout            = 15000

        # sql server 2016 or later only
        BasicAvailabilityGroup        = $true
        DatabaseHealthTrigger         = $True
        DtcSupportEnabled             = $True

        DependsOn                     = @('[SqlServerEndpointState]StartEndpoint', '[SqlServerPermission]AddNTServiceClusSvcPermissions')
        PsDscRunAsCredential          = $Creds
    }

And in the logs on the target machine there is an error:
"error", "message": "PowerShell DSC resource MSFT_SqlAG failed to execute Set-TargetResource functionality with error message: Creating the availability group 'qaag'. InnerException: Cannot bind parameter 'InputObject'. Cannot convert the "[qavmsql1]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server". "},

Could you help with adding AG? I can't find errors in config. Failover Cluster is presented, Always on service is accepted, also created Database mirroring endpoints on both replicas.

[mohamednazar]

IS this 2016 server? and installed SSMS already? if so please remove SSMS and try again.

[tolstyiii]

@mohamednazar yes, it's image Win2012R2+SQL2016SP1. SSMS is installed by default. Thanks, I try now.

[tolstyiii]

@mohamednazar great - thank you, AG created after removing SSMS. But it is not convenient, is there no any possibilities to create ag without removing ssms? it's rather long time to remove and install back for preparing environment...

[mohamednazar]

#1151 please check

[tolstyiii]

@mohamednazar ok, thank you. Will live with SSMS on the separate server, and add removing SSMS in the DSC.)

[tolstyiii]

@mohamednazar could you help with second replica, please? Second Replica qavmsql2 is in state:
System.InvalidOperationException: Failed to connect to SQL instance 'qavmsql1'.
Firewalls is off, ping succeded, wsfc content both servers. But wsfc I can't create via xCluster - second node also didn't add to cluster. I create it with manual script resource on the first node, with adding second node. Resource for second replica is:
SqlAGReplica AddReplica
{
DependsOn = '[WaitForAll]WaitForAG'
Ensure = 'Present'
Name = $Node.NodeName
AvailabilityGroupName = $Node.AGName
ServerName = $Node.NodeName
InstanceName = $Node.SQLInstanceName
PrimaryReplicaServerName = $AllNodes.Where{$.Role -eq 'SQLServer'}.Where{$.FirstNode -eq $true}.NodeName
PrimaryReplicaInstanceName = $Node.SQLInstanceName
ProcessOnlyOnActiveNode = $true
}

[johlju]

Please see issue #1220 for SqlAGReplica.

[tolstyiii]

Is there some convenient ways to uninstall SSMS from SQLServerDSC or via script?

[johlju]

No, not yet. There is a resource proposal here #125

[tolstyiii]

Ok, thank you.

[tolstyiii]

Hi guys. With your help I created DSC for MS SQL Always on group, and succesfully deploy it on win 2016 with MSSQL 2016SP1, but on win 2017 and MS SQL 2017 appeared error adding secondary node to the Availability group. It behavior similar if via DSC or manually on the server create Always on Group. Have you such problems, or may be have some points for checking? Text error:

But with the same configuration at MSSQL 2016 all work fine. WSFC work fine, and I check any status from both nodes. DSC blocks, Primary Node:

SqlAG AddAG
{
Ensure = 'Present'
Name = $Node.AGName
InstanceName = $Node.SQLInstanceName
ServerName = $Node.NodeName
DependsOn = @('[SqlServerEndpointState]StartEndpoint', '[SqlServerPermission]AddNTServiceClusSvcPermissions')
PsDscRunAsCredential = $Creds
}

Secondary Node:

SqlAGReplica AddReplica
{
DependsOn = '[SqlServerEndpointState]StartEndpoint'
Ensure = 'Present'
Name = $Node.NodeName
AvailabilityGroupName = $Node.AGName
ServerName = $Node.NodeName
InstanceName = $Node.SQLInstanceName
PrimaryReplicaServerName = $AllNodes.Where{$.Role -eq 'SQLServer'}.Where{$.FirstNode -eq $true}.NodeName
PrimaryReplicaInstanceName = $Node.SQLInstanceName
ProcessOnlyOnActiveNode = $true
PsDscRunAsCredential = $Creds
}

[johlju]

@tolstyiii just double checking, is each instance on each node a normal SQL Server installation (Action = Install), not a cluster install Action = InstallFailoverCluster? I'm having a hard time trying to understand what the error message means. It feels like it thinks that there is an FCI? I haven't looked at that for some time so I can't really help with out testing in a lab myself.

[tolstyiii]

@johlju thank for your reply. As I understood, in 2017 MS SQL via current SQLServerDSC the second node is added to the AG, but it stayed in error state. When we removes it from AG, manually restart Always on service and SQLServer instance, and manually add second node to the AG via AG Wizard, all work fine. So I concluded it some advanced setting was added/removed from default config. If will updated SQLServerDSC module with new/deprecated feature in future - it will really great point.