Switching database fails

[svandesnepscheut]

Hi,

when switching the database we experience sometimes the issue below. If we open SQL Server Management Studio we can see that the AxDB is in Single User mode after failure. We cannot find the cause of this issue.

VERBOSE: [22:17:13][Get-SQLCommand] Building the SQL connection string. VERBOSE: [22:17:13][Switch-D365ActiveDatabase] Testing the new database for being a valid AXDB database. VERBOSE: [22:17:13][Get-SQLCommand] Building the SQL connection string. VERBOSE: [22:17:13][Switch-D365ActiveDatabase] Switching out the AxDB database with: AxDB_20220429_170833. [22:17:22][Switch-D365ActiveDatabase] Something went wrong while switching out the AXDB database. | Exception calling "ExecuteNonQuery" with "0" argument(s): "Database 'AxDB' is already open and can only have one user at a time. Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." WARNING: [22:17:23][Switch-D365ActiveDatabase] Stopping because of errors. | Something went wrong while switching out the AXDB database.

Thank you for your help!

[Splaxi]

We have had reports on this previously, but have had a hard time replicating the issue across machines to see what is actually going wrong.

If you can, consistently, replicate the issue and helps us with some testing / screen sharing - it would be very helpful for us, to look into the issue.

[svandesnepscheut]

Hi Splaxi,

thank you for your rapid response. Unfortunately, we cannot replicate this issue. It sometimes happens, it sometimes doensn't. We have not seen any possible cause. After failure we can see the logs and the result (AxDB in Single User Mode), but that is it.

Let me share the following with you. I assume the script puts the AxDB in Single User mode to assure there is only one connection to the database in order to be able to rename the database. It appears to me that during this process the script is bothering itself in some way. How this is possible, I don't know. Perhaps it would be possible to include some sort of exception handling: IF the error "Database 'AxDB' is already open and can only have one user at a time." is thrown THEN get the DB out of Single User mode, kill all connections to the DB and try again to rename.

It is just a thought. Maybe one thought leads to another. At least thank you for your assistance and if you have any remarks/questions/ideas please let me know.

[fhoo]

I have just noticed, that after the import of a database with Import-D365Bacpac there is an active process using the freshly imported database from the tools:

This might cause the issue with renaming the database directly after the import. I usually close the PowerShell session after the import command, which also closes the active process on the DB, and do the renaming in a new PowerShell session, to avoid the issue.

Would be interesting what opens the DB process after the import, though.

[Splaxi]

One of the many sanity scripts that we are running.

But this provides details what to look for and see if we can implement a generic pattern that ensures the connections are being dropped.

Awesome work!

[FH-Inway]

@Splaxi Do you think it could be this part? Even if not, we should probably not return silently if there is no $res, but add at least a debug information. https://github.com/d365collaborative/d365fo.tools/blob/c34e0fd70311f7b368c618f0a54c29a917428d53/d365fo.tools/functions/import-d365bacpac.ps1#L342-L346

This calls into the internal cmdlet Set-SQLBacpacValues, which seems to correctly terminate the connection.

The only other option I can see is that SqlPackage is responsible, which seems unlikely. A first cursory search did not produce any results that indicate that SqlPackage leaves connections open.

[Splaxi]

That is most likely the call that we need to look into, here is the code that is actually calling the sql server:

https://github.com/d365collaborative/d365fo.tools/blob/6bc0c25fd5f035568f63f8e4df0a3f1b7e30d818/d365fo.tools/internal/functions/set-sqlbacpacvalues.ps1#L79-L100

The only thing I can suggest as of now, is to dispose the connection object, via the command object. Closing the connection might not be enough, for powershell to release the connection. Or we need to implement the using pattern from C# - which will take quite some time to implement. A work around could be to force the garbage collector to run and see if that can clear any stale objects.

[Splaxi]

Nothing like reading the docs and learning something:

https://learn.microsoft.com/nl-nl/dotnet/api/system.data.sqlclient.sqlconnection?view=dotnet-plat-ext-6.0#remarks

[Splaxi]

So we need to set pooling to false / no

And we need to dispose the connection object itself - even though they say close and dispose is the same, dispose will tell what our intention is: Killing the object and thereby the connection....

[Splaxi]

More learning:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

[Splaxi]

And here we are at the connection string:

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=dotnet-plat-ext-6.0

[Splaxi]

I can see we need to update 40+ places to handle the suggested dispose. Maybe we should start with just changing the connection string, and see if it works.

FYI: The pool manager kills idle connection after 4-8 minutes, which is why we sometime hit this issue and not - because it depends on how the execution is run. And like mentioned earlier, exiting the PowerShell session will most definitely kill any connection 😉

[FH-Inway]

Nice finds. Seems like the Pooling keyword set to 'false' in the connection string should prevent the connection going back into the connection pool.

When the value of this key is set to true, any newly created connection will be added to the pool when closed by the application. In a next attempt to open the same connection, that connection will be drawn from the pool.

Connections are considered the same if they have the same connection string. Different connections have different connection strings.

The value of this key can be "true", "false", "yes", or "no".

• from the keyword values table list in section "Remarks" of the ConnectionString documentation.

That would be a change in the internal cmdlet Get-SQLCommand, which would impact a lot of cmdlets that are using it. Maybe better to add an optional switch parameter that controls if the Pooling keyword is added to the connection string or not. Then each cmdlet can decide if it wants to use this or not.

[Splaxi]

Wise words.

Which default are we aiming for? Pooling = false, to ensure that we don't have a open connection? Or Pooling = true, and then change all cmdlets that are working with bacpac files and renaming database, etc?

[FH-Inway]

I'd say we use the same default as ConnectionString, i.e. true. For now, I feel we just need to change the Set-SQLBacpacValues cmdlet to use Pooling = false. This should address this issue. Other cmdlets can be changed when needed or someone finds the time to do so. It may be a good idea to create a new issue or discussion that lists all the cmdlet that should be checked/changed.

[Splaxi]

Next release - being published as we speak, will have this change implemented.

@fhoo - Would you be able to replicate / validate your findings with 0.6.74? It should be ready in 10-15 minutes

[Splaxi]

0.6.75 has the issue resolved - at least in the invoke-d365sqlscript, which is one of the 2 cmdlets utilized by the bacpac import.

Running the cmdlet without the -NoPooling - keeps an open connection, even for the simplest sql command.

Using the new -NoPooling, then the connection is removed instantly.

[fhoo]

Didn't had the chance to test today, but try to find a minute over the weekend.

[Splaxi]

@fhoo Any luck?

[fhoo]

It took a while to prepare a backpack and model file for testing without waiting for several hours to complete the import 🤪 But I have successfully reproduced the open connection with an older version of the tools, and also verified that there is no open connection after the import with the new version 👍

[Splaxi]

Awesome 🤘

We finally nailed the issue - thanks for all the help and assistance 🥳🥳🥳

[svandesnepscheut]

Great work! Thank you all.

[FH-Inway]

For reference, this was resolved with #674