Contained a gs
Description
How Has This Been Tested?
Types of changes
- [ ] Bug fix (non-breaking change which fixes an issue) - Fixes #
- [X] New feature (non-breaking change which adds functionality)
Checklist:
- [ X] I have read/followed the CONTRIBUTING document.
- [ X] I have read/followed the PR Quick Start Guide
- [ X] I have added tests to cover my changes or they are N/A.
- [X] I have added a changelog fragment describing the changes.
- [X] New module options/parameters include a
version_addedproperty.
WIP. needs the latest dbatools.
Docs Build 📝
Thank you for contribution!✨
The docs for this PR have been published here: https://lowlydba.github.io/lowlydba.sqlserver/pr/249
You can compare to the docs for the main branch here:
https://lowlydba.github.io/lowlydba.sqlserver/branch/main
The docsite for this PR is also available for download as an artifact from this run: https://github.com/lowlydba/lowlydba.sqlserver/actions/runs/9518565030
File changes:
Click to see the diff comparison.
NOTE: only file modifications are shown here. New and deleted files are excluded. See the file list and check the published docs to see those files.
diff --git a/home/runner/work/lowlydba.sqlserver/lowlydba.sqlserver/docsbuild/base/collections/lowlydba/sqlserver/availability_group_module.html b/home/runner/work/lowlydba.sqlserver/lowlydba.sqlserver/docsbuild/head/collections/lowlydba/sqlserver/availability_group_module.html
index 0fe25ed..59fd612 100644
--- a/home/runner/work/lowlydba.sqlserver/lowlydba.sqlserver/docsbuild/base/collections/lowlydba/sqlserver/availability_group_module.html
+++ b/home/runner/work/lowlydba.sqlserver/lowlydba.sqlserver/docsbuild/head/collections/lowlydba/sqlserver/availability_group_module.html
@@ -256,6 +256,18 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
</div></td>
</tr>
<tr class="row-odd"><td><div class="ansible-option-cell">
+<div class="ansibleOptionAnchor" id="parameter-contained_availability_group"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-contained-availability-group"><strong>contained_availability_group</strong></p>
+<a class="ansibleOptionLink" href="#parameter-contained_availability_group" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">boolean</span></p>
+</div></td>
+<td><div class="ansible-option-cell"><p>Indicates whether the availability group is Contained. Requires DBATools >= 2.1.15</p>
+<p class="ansible-option-line"><strong class="ansible-option-choices">Choices:</strong></p>
+<ul class="simple">
+<li><p><code class="ansible-option-choices-entry docutils literal notranslate"><span class="pre">false</span></code></p></li>
+<li><p><code class="ansible-option-choices-entry docutils literal notranslate"><span class="pre">true</span></code></p></li>
+</ul>
+</div></td>
+</tr>
+<tr class="row-even"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-database"></div>
<div class="ansibleOptionAnchor" id="parameter-database_name"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-database-name"><span id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-database"></span><strong>database</strong></p>
<a class="ansibleOptionLink" href="#parameter-database" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-aliases">aliases: database_name</span></p>
@@ -264,7 +276,7 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
<td><div class="ansible-option-cell"><p>Name of the database to create the Availability Group for.</p>
</div></td>
</tr>
-<tr class="row-even"><td><div class="ansible-option-cell">
+<tr class="row-odd"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-database_health_trigger"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-database-health-trigger"><strong>database_health_trigger</strong></p>
<a class="ansibleOptionLink" href="#parameter-database_health_trigger" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">boolean</span></p>
</div></td>
@@ -276,7 +288,7 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
</ul>
</div></td>
</tr>
-<tr class="row-odd"><td><div class="ansible-option-cell">
+<tr class="row-even"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-dtc_support_enabled"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-dtc-support-enabled"><strong>dtc_support_enabled</strong></p>
<a class="ansibleOptionLink" href="#parameter-dtc_support_enabled" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">boolean</span></p>
</div></td>
@@ -288,7 +300,7 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
</ul>
</div></td>
</tr>
-<tr class="row-even"><td><div class="ansible-option-cell">
+<tr class="row-odd"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-failover_mode"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-failover-mode"><strong>failover_mode</strong></p>
<a class="ansibleOptionLink" href="#parameter-failover_mode" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span></p>
</div></td>
@@ -300,7 +312,7 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
</ul>
</div></td>
</tr>
-<tr class="row-odd"><td><div class="ansible-option-cell">
+<tr class="row-even"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-failure_condition_level"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-failure-condition-level"><strong>failure_condition_level</strong></p>
<a class="ansibleOptionLink" href="#parameter-failure_condition_level" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span></p>
</div></td>
@@ -315,7 +327,7 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
</ul>
</div></td>
</tr>
-<tr class="row-even"><td><div class="ansible-option-cell">
+<tr class="row-odd"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-force"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-force"><strong>force</strong></p>
<a class="ansibleOptionLink" href="#parameter-force" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">boolean</span></p>
</div></td>
@@ -327,7 +339,7 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
</ul>
</div></td>
</tr>
-<tr class="row-odd"><td><div class="ansible-option-cell">
+<tr class="row-even"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-healthcheck_timeout"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-healthcheck-timeout"><strong>healthcheck_timeout</strong></p>
<a class="ansibleOptionLink" href="#parameter-healthcheck_timeout" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">integer</span></p>
</div></td>
@@ -335,7 +347,7 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
<p>Changes that are made to the timeout settings are effective immediately and do not require a restart of the SQL Server resource.</p>
</div></td>
</tr>
-<tr class="row-even"><td><div class="ansible-option-cell">
+<tr class="row-odd"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-is_distributed_ag"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-is-distributed-ag"><strong>is_distributed_ag</strong></p>
<a class="ansibleOptionLink" href="#parameter-is_distributed_ag" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">boolean</span></p>
</div></td>
@@ -347,7 +359,7 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
</ul>
</div></td>
</tr>
-<tr class="row-odd"><td><div class="ansible-option-cell">
+<tr class="row-even"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-seeding_mode"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-seeding-mode"><strong>seeding_mode</strong></p>
<a class="ansibleOptionLink" href="#parameter-seeding_mode" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span></p>
</div></td>
@@ -359,56 +371,56 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
</ul>
</div></td>
</tr>
-<tr class="row-even"><td><div class="ansible-option-cell">
+<tr class="row-odd"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-shared_path"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-shared-path"><strong>shared_path</strong></p>
<a class="ansibleOptionLink" href="#parameter-shared_path" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span></p>
</div></td>
<td><div class="ansible-option-cell"><p>The network share where the backups will be backed up and restored from.</p>
</div></td>
</tr>
-<tr class="row-odd"><td><div class="ansible-option-cell">
+<tr class="row-even"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-sql_instance"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-sql-instance"><strong>sql_instance</strong></p>
<a class="ansibleOptionLink" href="#parameter-sql_instance" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span> / <span class="ansible-option-required">required</span></p>
</div></td>
<td><div class="ansible-option-cell"><p>The SQL Server instance to modify.</p>
</div></td>
</tr>
-<tr class="row-even"><td><div class="ansible-option-cell">
+<tr class="row-odd"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-sql_instance_secondary"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-sql-instance-secondary"><strong>sql_instance_secondary</strong></p>
<a class="ansibleOptionLink" href="#parameter-sql_instance_secondary" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span></p>
</div></td>
<td><div class="ansible-option-cell"><p>The secondary SQL Server instance for the new Availability Group.</p>
</div></td>
</tr>
-<tr class="row-odd"><td><div class="ansible-option-cell">
+<tr class="row-even"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-sql_password"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-sql-password"><strong>sql_password</strong></p>
<a class="ansibleOptionLink" href="#parameter-sql_password" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span></p>
</div></td>
<td><div class="ansible-option-cell"><p>Password for SQL Authentication.</p>
</div></td>
</tr>
-<tr class="row-even"><td><div class="ansible-option-cell">
+<tr class="row-odd"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-sql_password_secondary"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-sql-password-secondary"><strong>sql_password_secondary</strong></p>
<a class="ansibleOptionLink" href="#parameter-sql_password_secondary" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span></p>
</div></td>
<td><div class="ansible-option-cell"><p>Password for SQL Authentication for the secondary replica.</p>
</div></td>
</tr>
-<tr class="row-odd"><td><div class="ansible-option-cell">
+<tr class="row-even"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-sql_username"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-sql-username"><strong>sql_username</strong></p>
<a class="ansibleOptionLink" href="#parameter-sql_username" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span></p>
</div></td>
<td><div class="ansible-option-cell"><p>Username for SQL Authentication.</p>
</div></td>
</tr>
-<tr class="row-even"><td><div class="ansible-option-cell">
+<tr class="row-odd"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-sql_username_secondary"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-sql-username-secondary"><strong>sql_username_secondary</strong></p>
<a class="ansibleOptionLink" href="#parameter-sql_username_secondary" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span></p>
</div></td>
<td><div class="ansible-option-cell"><p>Username for SQL Authentication for the secondary replica.</p>
</div></td>
</tr>
-<tr class="row-odd"><td><div class="ansible-option-cell">
+<tr class="row-even"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-state"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-state"><strong>state</strong></p>
<a class="ansibleOptionLink" href="#parameter-state" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">string</span></p>
</div></td>
@@ -420,7 +432,7 @@ see <a class="reference internal" href="#ansible-collections-lowlydba-sqlserver-
</ul>
</div></td>
</tr>
-<tr class="row-even"><td><div class="ansible-option-cell">
+<tr class="row-odd"><td><div class="ansible-option-cell">
<div class="ansibleOptionAnchor" id="parameter-use_last_backup"></div><p class="ansible-option-title" id="ansible-collections-lowlydba-sqlserver-availability-group-module-parameter-use-last-backup"><strong>use_last_backup</strong></p>
<a class="ansibleOptionLink" href="#parameter-use_last_backup" title="Permalink to this option"></a><p class="ansible-option-type-line"><span class="ansible-option-type">boolean</span></p>
</div></td>
Codecov Report
:white_check_mark: All modified and coverable lines are covered by tests.
:white_check_mark: Project coverage is 85.37%. Comparing base (d3d84e6) to head (5d3289c).
:warning: Report is 8 commits behind head on main.
Additional details and impacted files
@@ Coverage Diff @@
## main #249 +/- ##
==========================================
- Coverage 88.83% 85.37% -3.47%
==========================================
Files 70 107 +37
Lines 2598 2954 +356
Branches 39 78 +39
==========================================
+ Hits 2308 2522 +214
- Misses 275 402 +127
- Partials 15 30 +15
| Flag | Coverage Δ | |
|---|---|---|
| integration | 40.83% <ø> (?) |
|
| sanity | 100.00% <ø> (?) |
Flags with carried forward coverage won't be shown. Click here to find out more.
:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.
:rocket: New features to boost your workflow:
- :snowflake: Test Analytics: Detect flaky tests, report on failures, and find test suite problems.
Alright @lowlydba I think I'm ready for you to have a look at the PR
I just updated the repo permissions so you should be able to trigger the CI tests with your own commits now as well.
Hi! Just in case you missed it, what kind of tests did you mean?
Hi! Just in case you missed it, what kind of tests did you mean?
Right below the comment thread here, you'll see a series of workflows that run integration and unit tests for the collection. Right now the "CI / Sanity" test is failing for some minor issues - all of the workflows will need to pass to make this mergeable.
I still don't really understand how to add tests that cover contained AGs, I edited tests/integration/ and added an option to change contained_availability_group, but where can I create a test where it is set to true?
I still don't really understand how to add tests that cover contained AGs, I edited tests/integration/ and added an option to change contained_availability_group, but where can I create a test where it is set to true?
Because what I would really like to do is to run the win_availability_group test with contained_availability_group: true and I was wondering if that's possible without creating a new test.
I still don't really understand how to add tests that cover contained AGs, I edited tests/integration/ and added an option to change contained_availability_group, but where can I create a test where it is set to true?
Because what I would really like to do is to run the win_availability_group test with
contained_availability_group: trueand I was wondering if that's possible without creating a new test.
Yeah, the issue is right now its always set to false, so it won't get tested. You'd want to add a new test like:
- name: Create contained availability group
lowlydba.sqlserver.availability_group:
ag_name: "contained_AG"
contained_availability_group: true
register: result
- assert:
that:
- result.data.ComputerName != None
- result.data.InstanceName != None
- result.data.SqlInstance != None
- result.data.AvailabilityGroup == "contained_AG"
- result.data.ClusterType == "{{ cluster_type }}"
- result.data.DtcSupportEnabled is false
- result.data.AvailabilityReplicas != None
- result is changed
The when conditionals now won't get use as written, so those can probably be removed.
Seems like adding a test form contained AGs didn't help the codecov test? Would really like your input on that @lowlydba. Thank you!
Also, I noticed I don't handle the case where an availability group that is not contained exists but the module specifies it should be contained. It is currently not possible to have an availability group be converted to a contained availability group, should I just error out in this case? and if force is specified only then drop it and recreate?
Also, I noticed I don't handle the case where an availability group that is not contained exists but the module specifies it should be contained. It is currently not possible to have an availability group be converted to a contained availability group, should I just error out in this case? and if force is specified only then drop it and recreate?
It should probably error out in both cases, using force to drop and re-create feels a bit too risky as behavior in general.
Seems like adding a test form contained AGs didn't help the codecov test? Would really like your input on that @lowlydba. Thank you!
The codecov tests appear to be passing (they just look for code coverage) but the actual tests are failing.
I am not sure why the original availability group test is failing now though:
TASK [win_availability_group : Create availability group] ********************** ... The full traceback is: Database "ag-test-db" might contain bulk logged changes that have not been backed up. Take a log backup on the principal database or primary database. Then restore this backup either on the mirror database to enable database mirroring or on every secondary database to enable you to join it to the availability group.
I can't seem to figure out why the test is failing? doesn't seem to have anything to do with my changes either. I also have a hard time replicating this bug on my local setup.
I can't seem to figure out why the test is failing? doesn't seem to have anything to do with my changes either. I also have a hard time replicating this bug on my local setup.
It may be helpful to try having the new "Create Contained availability group" test use all its own variables (i.e. not the same database as the AG test above it) to rule out any issues from re-using parts of the tests earlier in the file. This will help narrow down what the issue is. I think using a separate database may resolve the backup warnings you're seeing.
For the dlevel tests, I am pushing a fix for those via https://github.com/lowlydba/lowlydba.sqlserver/pull/257/files
I can't seem to figure out why the test is failing? doesn't seem to have anything to do with my changes either. I also have a hard time replicating this bug on my local setup.
It may be helpful to try having the new "Create Contained availability group" test use all its own variables (i.e. not the same database as the AG test above it) to rule out any issues from re-using parts of the tests earlier in the file. This will help narrow down what the issue is. I think using a separate database may resolve the backup warnings you're seeing.
For the dlevel tests, I am pushing a fix for those via https://github.com/lowlydba/lowlydba.sqlserver/pull/257/files
Sounds good. How would you like me to implement this syntactically in the same file as the regular tests?
Sounds good. How would you like me to implement this syntactically in the same file as the regular tests?
I think just using the inputs needed inline for each assertion is fine, no need to declare a ton of them at the top like the current ones.
Sounds good. How would you like me to implement this syntactically in the same file as the regular tests?
I think just using the inputs needed inline for each assertion is fine, no need to declare a ton of them at the top like the current ones.
This should do it. Sorry for the delay, I have been busy.
@lowlydba Alright, I think I'm ready
The Windows test issues have been resolved upstream, please make sure the tests are passing now.
lowlydba.sqlserver.login seems to require FQDN, I can't seem to figure out the active directory domain name in the test environment.
lowlydba.sqlserver.loginseems to require FQDN, I can't seem to figure out the active directory domain name in the test environment.
The basic credentials used in CI for the tests are stored in tests\integration\integration_config.sample.yml:
https://github.com/lowlydba/lowlydba.sqlserver/blob/20a0a5f459bb1fff509aa857c3ff022fcf3ea176/tests/integration/integration_config.sample.yml#L2-L4
You shouldn't need to change those though, just reference them in the test file by name. Example:
https://github.com/lowlydba/lowlydba.sqlserver/blob/20a0a5f459bb1fff509aa857c3ff022fcf3ea176/tests/integration/targets/login/tasks/main.yml#L13-L17
lowlydba.sqlserver.loginseems to require FQDN, I can't seem to figure out the active directory domain name in the test environment.The basic credentials used in CI for the tests are stored in
tests\integration\integration_config.sample.yml:https://github.com/lowlydba/lowlydba.sqlserver/blob/20a0a5f459bb1fff509aa857c3ff022fcf3ea176/tests/integration/integration_config.sample.yml#L2-L4
You shouldn't need to change those though, just reference them in the test file by name. Example:
https://github.com/lowlydba/lowlydba.sqlserver/blob/20a0a5f459bb1fff509aa857c3ff022fcf3ea176/tests/integration/targets/login/tasks/main.yml#L13-L17
I asked about the domain name, not the credentials
@lowlydba I am completely baffled as to why this does not work. The error seems to be Configuring login failed: The network path was not found When pointing login to the listener, as should be done with contained availability groups, but I am unable to recreate this in my own setup. It works on mine.
What active directory environment do the tests run it? Could it be a DC replication lag issue?
What active directory environment do the tests run it? Could it be a DC replication lag issue?
You can check the GitHub test workflow for info, but there isn't anything beyond a local AD since we're using a single test runner.
I still don't have a ton of time to dedicate to this project right now, so I apologize that I can't be of more help at this time.
Hi @lowlydba was wondering if you take a look at this I'm unable to replicate this issue locally
Hi @lowlydba was wondering if you take a look at this I'm unable to replicate this issue locally
I don't have anything setup locally right now for AG testing, so I am not sure honestly. I've also never personally used contained AG before (and don't use any of this collection at my current job).
I can take a look when I find some free time, but no promises.
@DorBreger In an effort to continue to be able to support this collection for users, I've introduced GitHub Sponsors for this project (see announcement). This feature request would be a "small feature" in that context - if you have any further questions please reply in that discussion thread and we can discuss.
Thanks!