xSQLServerAlwaysOnAvailabilityGroupDatabaseMembership fails to create backup

[4c74356b41]

Details of the scenario you tried and the problem that is occurring:
I'm trying to create a sql server alwaysOn group with a database. The database gets added to alwaysOn group and after that it fails when trying to do restore (i'm using a local path to be sure the backup gets created properly, but it doesn't). I can switch to network path as soon as backup starts working.
The DSC configuration that is using the resource (as detailed as possible):
full configuration here. I'm pasting a failing snippet. The path c:\setup exists.

        xSQLServerAlwaysOnAvailabilityGroupDatabaseMembership DatabaseToAlwaysOn {
            AvailabilityGroupName = "${deploymentPrefix}-sql-ag"
            BackupPath            = "C:\setup"
            DatabaseName          = "ContosoClinic"
            SQLServer             = $env:COMPUTERNAME
            SQLInstanceName       = "MSSQLSERVER"
            
            DependsOn             = @("[xDatabase]DeployBacPac", "[xSQLServerAlwaysOnAvailabilityGroup]AvailabilityGroup" )
            Ensure                = "Present"
            PsDscRunAsCredential  = $DomainCreds
        }

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Microsoft Windows NT 6.3 (14393), SQL - 13.0.4435.0 (2016 SP1 latest on Azure)

Name                           Value
----                           -----
PSVersion                      5.1.14393.1532
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.14393.1532
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:
SqlServer 21.0.17178
Version of the DSC module you're using, or 'dev' if you're using current dev branch:
ModuleVersion = '8.1.0.0'

Actual error:

VERBOSE: [2017-09-15 08:58:58Z] [ERROR] PowerShell DSC resource 
MSFT_xSQLServerAlwaysOnAvailabilityGroupDatabaseMembership  failed to execute 
Set-TargetResource functionality with error message: The operation on the 
database 'ContosoClinic' failed with the following errors: System.Exception: 
System.InvalidOperationException: Executing non-query failed on database 
'master'. ---> System.Management.Automation.MethodInvocationException: 
Exception calling "ExecuteNonQuery" with "1" argument(s): "ExecuteNonQuery 
failed for Database 'master'. " ---> 
Microsoft.SqlServer.Management.Smo.FailedOperationException: ExecuteNonQuery 
failed for Database 'master'.  ---> 
Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception 
occurred while executing a Transact-SQL statement or batch. ---> 
System.Data.SqlClient.SqlException: Cannot open backup device 
'C:\setup\ContosoClinic_Full_20170915085856.bak'. Operating system error 3(The 
system cannot find the path specified.).

RESTORE DATABASE is terminating abnormally.

   at 
Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSql
Action action, Object execObject, DataSet fillDataSet, Boolean catchException)

   at 
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String 
sqlCommand, ExecutionTypes executionType, Boolean retry)

   --- End of inner exception stack trace ---

   at 
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String 
sqlCommand, ExecutionTypes executionType, Boolean retry)

   at 
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCo
llection sqlCommands, ExecutionTypes executionType, Boolean retry)

   at 
Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringColle
ction queries, ExecutionTypes executionType)

   at 
Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(StringCollection 
sqlCommands, ExecutionTypes executionType)

   --- End of inner exception stack trace ---

   at 
Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(StringCollection 
sqlCommands, ExecutionTypes executionType)

   at CallSite.Target(Closure , CallSite , Object , String )

   --- End of inner exception stack trace ---

   at 
System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(Functio
nContext funcContext, Exception exception)

   at 
System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(Interprete
dFrame frame)

   at 
System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(In
terpretedFrame frame)

   at 
System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(In
terpretedFrame frame)

   --- End of inner exception stack trace ---. 

[johlju]

Doesn't the restore need a UNC path to work? The other node which should restore would not know where to find it otherwise.
If you can test your config thru the GUI to make sure this do actually work (using local path)

@randomnote1 Can you be of any assistance here?

[randomnote1]

Hi @4c74356b41, thanks for the question!

Yes, the BackupPath must be accessible to all nodes that will contain a replica, so a UNC path would the correct way to do this.

It is also important to ensure the SQL Server Service Account has permission (read/write/delete) to the UNC path (NTFS and Share) to ensure it can create the backup, restore the backup, and delete the files when complete.

Question for you: Is there something in the resource and/or documentation that could make this clearer?

[4c74356b41]

like i said, it doesnt create a backup, so it doesnt matter if it is a UNC path or not. there is nothing to restore. @randomnote1 @johlju

[randomnote1]

Did you verify the SQL Server service has permissions to that directory? That's the one that is normally a gotcha for me.

[4c74356b41]

yes, it was throwing another error when it had no permissions, so right now it breaks on restore step, when it had no permissions it was breaking on the backup step (the step previous to the restore, if you look at the code) @randomnote1

[randomnote1]

@4c74356b41, would you mind posting the error received during the restore when using a network path?

[4c74356b41]

well, i didn't try that, does it make sense, since no backup is being created? @randomnote1

[johlju]

@4c74356b41 I'm sorry, I don't follow you. In comment above you said

it was throwing another error when it had no permissions, so right now it breaks on restore step, when it had no permissions it was breaking on the backup step

It sounds like you was able to take backup, so that the backup of the database is present on the disk, and it's now failing on the restore step.
Do we misunderstand you?

[4c74356b41]

yeah, sorry, it was failing to create a backup because it had no permission, when I gave it permission its is now failing on restore step and if you look at the error it says file not found, I've checked all the nodes (since the path is local), none of the nodes had the backup file on them. Looking at the code it doesnt look like the backup gets cleaned up, so... unless I'm missing something it doesnt get created at all @johlju @randomnote1

[randomnote1]

That's correct, the restore will fail if it is looking for it on a local directory. There is no logic in the resource to copy the backups around.

The backup must be targeted to a UNC path. All of the SQL Servers must have permissions to the UNC path.

The backup files do get cleaned up after all restore attempts, successful or otherwise.
Remove-Item -Path $databaseFullBackupFile, $databaseLogBackupFile -Force -ErrorAction Continue Line 510

[4c74356b41]

oh, my bad, let me try the unc path. @randomnote1

[4c74356b41]

ok, definitely got further this time:

The process cannot access the file '\\dev-sql-0\setup\ContosoClinic_Full_20170921044237.bak' because it is being used
by another process.
    + CategoryInfo          : WriteError: (\\dev-sql-0\set...70921044237.bak:) [], CimException
    + FullyQualifiedErrorId : RemoveFileSystemItemIOError,Microsoft.PowerShell.Commands.RemoveItemCommand
    + PSComputerName        : localhost

The process cannot access the file '\\dev-sql-0\setup\ContosoClinic_Log_20170921044237.trn' because it is being used
by another process.
    + CategoryInfo          : WriteError: (\\dev-sql-0\set...70921044237.trn:) [], CimException
    + FullyQualifiedErrorId : RemoveFileSystemItemIOError,Microsoft.PowerShell.Commands.RemoveItemCommand
    + PSComputerName        : localhos

Any ideas? Only got 2 nodes. @randomnote1 @johlju

[randomnote1]

Did the database get added to the availability group?

I wonder if the file was in the process being scanned or something like that. It's difficult to say why this might be the case without analyzing the file using something like process explorer.

[4c74356b41]

So, this was a combination of me misreading the code (I missed the part where backup gets deleted) and trying to use a local path (to see if a backup will get created). I'm not sure why it errored out with file is locked (that happened only once).