sequence of SQL server DSC for ALwaysOn AG

[amitchettri]

Want to know correct steps for successfully enabling Always On Availability Group

I have configure the AlwaysOn using GUI and following some video. Now i want to learn doing the same using SqlServerDsc.

kindly help me to know which are the required DSC Resource to be used and in what sequence to use it.

[johlju]

Please see here https://github.com/PowerShell/SqlServerDsc/tree/dev/Examples#setting-up-a-sql-server-alwayson-availability-groups. Let us know if you get stuck.

You can also jump on the slack channel to discuss this with others; https://dsccommunity.org/community/contact/

[amitchettri]

Hello @johlju,

In thread #910 I have found a sequence of steps given by you to achieve the SQL AlwaysON AG using xSqlServerAlwaysOn ( OBSOLETE module ) #910 (comment)

Can you kindly help with the sequence in similar manner using new latest DSC https://github.com/PowerShell/SqlServerDsc

[johlju]

The sequence is still correct, it's just the resources that have been renamed or new resources have been added. That sequence was remade into the guide in the link above, the guide I linked to in my previous comment. It is not possible to have an exact sequence here since it might be outdated pretty quick since it is dependent on three different modules. Any change in one of the other modules would make the sequence invalid. So the guide is pointing to examples in the other modules that is kept updated by those contributors. The modules are ActiveDirectoryDsc, xFailOverCluster and SqlServerDsc.

There are potential for someone in the community to blog about this; if so - if you like - please send a blog post to https://dsccommunity.org.

[amitchettri]

Hello @johlju,

I have test env as 1 DC, 2 SQL nodes with SQL 2016 install. Now i am using ansible with win_dsc to call each of the respective dsc resource to achieve the always on.

as of now using the playbook i am able to do the below

1. Set SQL Server AlwaysOn Service --> calling SqlAlwaysOnService resource with win_dsc module
2. Create Princpal NT SERVICE\ClusSvc --> calling SqlServerLogin resource with win_dsc module
3. Sets SQL Server AlwaysOn Permission for Princpal NT SERVICE\ClusSvc --> calling SqlServerPermission resource with win_dsc module
4. Sets SQL Server AlwaysOn Endpoint --> calling SqlServerEndpoint resource with win_dsc module
5. Sets SQL Server AlwaysOn Endpoint State --> calling SqlServerEndpointState resource with win_dsc module
6. Sets SQL Server AlwaysOn Endpoint Permission --> calling SqlServerEndpointPermission resource with win_dsc module
7. Create AG --> calling SqlAG resource with win_dsc module

after all this when i am trying to ensure an availability group replica is present using SqlAGReplica I am getting the error as below

fatal: [WINSER1.corplab.com]: FAILED! => {"changed": false, "module_version": "12.4.0.0", "msg": "Failed to invoke DSC Test method: PowerShell DSC resource MSFT_SqlAGReplica failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Failed to connect to SQL instance 'WINSER2.corplab.com'. ", "reboot_required": false}

Ansible CODE

• name: SQL wait for a AlwaysON AG group to be created
  win_dsc:
  resource_name: SqlAGReplica
  Ensure: Present
  Name: WINSER2\MSSQLSERVER
  AvailabilityGroupName: AG_TEST_AMIT
  ServerName: WINSER2.corplab.com
  InstanceName: MSSQLSERVER
  PrimaryReplicaServerName: WINSER1.corplab.com
  PrimaryReplicaInstanceName: MSSQLSERVER
  AvailabilityMode: SynchronousCommit
  BackupPriority: 50
  ConnectionModeInPrimaryRole: AllowAllConnections
  ConnectionModeInSecondaryRole: AllowAllConnections
  FailoverMode: Automatic
  ProcessOnlyOnActiveNode: true

NOTE:
1. I have checked #1220 (comment) but still getting the same error.
2. for more clarity currently ansible play is running at the WINSER1.corplab.com where primary replica is present but i have already enable AlwaysON, added ClusSvc, Added Endpoint and its permission at WINSER2.corplab.com server already

please help to understand where i am going wrong.

[amitchettri]

Hi @johlju , I got the issue fixed.

This was due the fact that I was not able to connect from SQL_server1 to SQL_server2 from GUI itself because the SQL Browser was not running and TCP IPALL port was not set to 1433