Issues with SQL Server Geodatabase setup

[rupertbrowning]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request

Module Version

• 3.0.2

Affected Resource(s)

• SQLServer

Configuration Files

# Copy-paste your DSC JSON configurations here - for large configs,
# please use a service like Dropbox and share a link to the ZIP file.
{
    "AllNodes": [
        {
            "NodeName": "sw5-depg-2SA",
            "DataStoreTypes": [
                "Relational"
            ],
            "Role": [
                "Server",
                "Portal",
                "DataStore",
                "ServerWebAdaptor",
                "PortalWebAdaptor",
                "SQLServerClient",
                "SQLServer"
            ],
            "SQLServerInstallerPath": "C:\\Software\\SQLServer2019\\setup.exe"
        }
    ],

Expected Behavior

Should have installed an all in one enterprise deployment including SQL Server Geodatabase.

Actual Behavior

The deployment failed with errors:

The request to add or remove features on the specified server failed.
Installation of one or more roles, role services, or features failed. Error: 0x800f0954

The PowerShell DSC resource '[WindowsFeature]NET' with SourceInfo 'C:\Program Files\WindowsPowerShell\Modules\ArcGIS\3.0.2\Configurations-OnPrem\ArcGISInstall.ps1::255::21::WindowsFeature' threw one or more non-terminating errors while running the Set-TargetResource functionality. These errors are logged to the ETW channel called Microsoft-Windows-DSC/Operational. Refer to this channel for more details.
'/q' is not recognized as an internal or external command,
operable program or batch file.

The PowerShell DSC resource '[Script]SQLServerInstall' with SourceInfo 'C:\Program Files\WindowsPowerShell\Modules\ArcGIS\3.0.2\Configurations-OnPrem\ArcGISInstall.ps1::263::21::Script' threw one or more non-terminating errors while running the Set-TargetResource functionality. These errors are logged to the ETW channel called Microsoft-Windows-DSC/Operational. Refer to this channel for more details.
The SendConfigurationApply function did not succeed.

EDIT Error message 1 has been resolved by installing .net 3.5. In my case the .Net framework failed to install by downloading from the internet. I had to force it to install by providing a local source.

Error message 2 has been resolved by pre-installing SQL Server. I found that the issue was it was expecting to have a file to extract for the SQL Server source. I don't have that, I have a virtual DVD downloaded from Microsoft.

This allowed me to progress but I then hit further issues with the credentials being passed in to the Database creation. There is a mismatch between what the scripts are expect and the sample configuration.

Steps to Reproduce

This is an all in one environment including SQL Server 2019 Geodatabase. This is intended for development/test only so is probably not a recommended architecture. I used the normal process to run this deployment.

Important Factoids

The target machine is a windows 2019 server running on Azure.

Note the variables documentation do not detail what should be used for the SQL Server installation path. I used a 'DVD' download from Visual Studio. I suspect this was tested using cab files though.

The documentation for the database is limited, and needs explaining.

References

Used a combination of the BaseDeployment-SingleMachine.json and GISServer-Database.json for my config.

[cdbarker]

There are a number of issues with both ArcGIS.psm1 and ArcGISRegisterSQLEGDB.ps1. I've traced through the errors and managed to successfully deploy two enterprise geodatabases in SQL Server 2016 and register them with ArcGIS Server 10.7.1. Code changes required:

ArcGIS.psm1 Line 1146: $DB.Credentials.SDEUser.Password should be $DB.SDEUser.Password Line 1152: $DB.DatabaseUserCredential.Password should be $DB.DatabaseUser.Password

ArcGISRegisterSQLEGDB.ps1: Line 6: $PrimaryServerMachine should be $PrimaryServiceMachine Line 40: The parameter is $DatabaseIsManaged but the configuration sample uses the parameter 'IsManaged'. Change the value in the sample. Line 44: Trailing comma $EnableGeodatabase = $False, Line 60: $PrimaryServerMachine should be $PrimaryServiceMachine

I have created two geodatabases, each with a database user. I want to be able to create two geodatabases with multiple database users, but a second database block in the config with a different database user gets skipped over. Is there a way to configure this?

[cdbarker]

I changed line 568 in ArcGISEGDB.psm1 from $result = $true to $result = $false. This kicks off the process of creating the database, sde user and database user again. As the code already checks if they exist, this is safe. This allows for multiple database users to be added to the same database. One issue remaining is only the first database user created is registered with ArcGIS server. This is because the database is registered with Server as [databasehostname][database]. If the database was registered with [databasehostname][database][databaseuser], Get-ArcGISEGDBDataItems could then return then return these three values. Line 441 in ArcGIS_EGDB.psm1 then checks returned data items. Currently, it only checks hostname and database and if the item exists, skips the process. If it could check the database user too, this would allow for multiple registrations of the same geodatabase with different database users.

[shailesh91]

@cdbarker Support for the "multiple registrations of the same geodatabase with different database users." isn't on the roadmap. PR for adding support for it is always welcomed. @rupertbrowning We will roll out a release with the bug fix for this workflow hopefully sometime in October.

[cameronkroeker]

@rupertbrowning we have included a fix for this issue in v3.1.1:

https://github.com/Esri/arcgis-powershell-dsc/releases/tag/v3.1.1

Closing the issue, however if you still encounter this please feel free to re-open.