SqlAgDatabase: Failing when AG is Automatic Seeding

[Jens-Sjolund]

Details of the scenario you tried and the problem that is occurring

When creating a new database and subsequently adding it to an Availability Group (using SqlAgDatabase) there are errors. This occurr when seeding is automatic, bu I have not tried with manual.

Verbose logs showing the problem

PowerShell DSC resource MSFT_SqlAGDatabase failed to execute Set-TargetResource functionality with error mess
age: The operation on the database 'TEST02_2019DSC' failed with the following errors: System.Exception: System
.InvalidOperationException: Executing non-query failed on database 'master'. (SQLCOMMON0046) ---> System.Manag
ement.Automation.MethodInvocationException: Exception calling "ExecuteNonQuery" with "1" argument(s): "Execute
NonQuery failed for Database 'master'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Exe
cuteNonQuery failed for Database 'master'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureExcepti
on: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlExc
eption: The file 'K:\MSSQL\TEST02_2019DSC.mdf' cannot be overwritten. It is being used by database 'TEST02_20
19DSC'.
File 'TEST02_2019DSC' cannot be restored to 'K:\MSSQL\TEST02_2019DSC.mdf'. Use WITH MOVE to identify a valid l
ocation for the file.
The file 'L:\MSSQL\TEST02_2019DSC_log.ldf' cannot be overwritten. It is being used by database 'TEST02_2019DS
C'.
File 'TEST02_2019DSC_log' cannot be restored to 'L:\MSSQL\TEST02_2019DSC_log.ldf'. Use WITH MOVE to identify a
valid location for the file.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally.

Suggested solution to the issue

Determine whether SEEDING_MODE = AUTOMATIC or MANUAL. Rows 482-567 may not be executed if seeding is automatic.

The DSC configuration that is used to reproduce the issue (as detailed as possible)

    SqlAgDatabase 'AddAGDatabaseMembership'
    {
        ServerName              = $Node.NodeName
        InstanceName            = $ConfigurationData.DataBases.Instance
        DatabaseName            = $ConfigurationData.DataBases.Name
        AvailabilityGroupName   = $ConfigurationData.DataBases.AGName
        BackupPath              = $ConfigurationData.DataBases.BackupPath
        Ensure                  = 'Present'
        ProcessOnlyOnActiveNode = $true
        PsDscRunAsCredential = $SqlAdministratorCredential
    }

insert configuration here

instance of MSFT_SqlAGDatabase as $MSFT_SqlAGDatabase1ref
{
ResourceID = "[SqlAGDatabase]AddAGDatabaseMembership";
PsDscRunAsCredential = $MSFT_Credential10ref;
ServerName = "TS17";
InstanceName = "MSSQLSERVER";
BackupPath = "\\TS07\MSSQL_Backup";
SourceInfo = "D:\DSC\RFS591\SQLDB\CreateDatabase2.3.ps1::118::9::SqlAgDatabase";
DatabaseName = {
"TEST02_2019DSC"
};
AvailabilityGroupName = "TCLUPOC01AG01";
ModuleName = "SqlServerDsc";
ProcessOnlyOnActiveNode = True;
ModuleVersion = "13.4.0";
Ensure = "Present";
ConfigurationName = "CreateSQLDBTemplateConfig";

};

SQL Server edition and version the target node is running

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
+
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

Name Version Path

---

SQLPS 15.0 C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

The operating system the target node is running

OsName : Microsoft Windows Server 2016 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture : 64-bit
WindowsBuildLabEx : 14393.3564.amd64fre.rs1_release.200303-1942
OsLanguage : en-US
OsMuiLanguages : {en-US}
+
OsName : Microsoft Windows Server 2019 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture : 64-bit
WindowsVersion : 1809
WindowsBuildLabEx : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage : en-US
OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

Name Value

---

PSVersion 5.1.17763.1007
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.1007
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Version of the DSC module that was used

Name Version Path

---

SqlServerDsc 13.4.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.4.0\SqlServerDsc.psd1

[Fiander]

Working on this one.

[Jens-Sjolund]

@Fiander - Great work done! Just tried it and discovered the obligatory typo...
Look at row 450, the variable name 'needsBackup' is wrong. When I changed it the needed backup was made.

[Fiander]

oops.

i think because on my test system all new databases are backuped to nul with a triger at create time i missed that one.
thank you, will fix.