Database option Auto Close

[gammelgaard52]

Hi,

When using: Import-D365Bacpac -ImportModeTier1 -BacpacFile "J:\ScaleUnit\uat.bacpac" -NewDatabaseName $newDBName -Verbose Switch-D365ActiveDatabase -SourceDatabaseName $newDBName -Verbose I get these events in the SQL log: The reason for this seems to be because that somewhere in the process of importing bacpac file or during switch operations, the option for Auto Close is toggled to TRUE - which should be FALSE.

Script to correct misconfiguration: USE [master] GO ALTER DATABASE [test] SET AUTO_CLOSE OFF WITH NO_WAIT GO

Running v.0.6.56

[Splaxi]

I would love to see the extracted modelfile/manifest file from the bacpac that you imported.

https://github.com/d365collaborative/d365fo.tools/blob/development/d365fo.tools/functions/export-d365bacpacmodelfile.ps1

Because that contains every single database option, which we don't control, for the database that will be created. When you have the file, please search for the options section and share it here.

When you know what settings translates into AUTO_CLOSE, you can create a new modelfile/manifest, and reference that, while doing the import of the bacpac and then you should get what you want.

Let me know if you need a hand.

[gammelgaard52]

I created a new database, did a .bacpac export of that file, ran the export-d365bacpacmodelfile (Export-D365BacpacModelFile -Path J:\DXC\test.bacpac -OutputPath J:\dxc) to identify the value we look for. Then I search for that same value in the D365 .bacpac model file - no result

Btw - the modelfile is 430Mb in size.

One thing that came to my mind is and does seems strange, is that the challenge is not present with a manual created and empty database - all default. My colleague just reported that he experienced the same, by only running the Switch-D365ActiveDatabase command. The database he restored manually from .bak file.

[Splaxi]

I just searched the entire code base, and nowhere could I find ANY reference for AUTO_CLOSE

I think we need to agree on some things, to make sure we don't confuse ourselves and each other.

What happens in the SQL Server Log, when running this: ALTER DATABASE [test] SET AUTO_CLOSE OFF WITH NO_WAIT (GUI == FALSE??)

Vs.

What happens in the SQL Server Log, when running this: ALTER DATABASE [test] SET AUTO_CLOSE ON (GUI == TRUE??)

The way I currently read the SQL Server Log Entry, that you point out: setting database option auto_close to off for database 'AXDB_Original' Translates into the AUTOCLOSE == FALSE (GUI) / SET AUTO_CLOSE OFF

Can you replicate these things and share them?

[Splaxi]

Next thing is that you can inject the IsAutoCloseOn xml tag directly into your own model file, and then run the import.

Things that are not EXPLICIT mentioned inside the model file, will use system defaults. Could it be that your SQL Server config / model db is configured with AutoClose == true?

At the moment I can't explain what could / would change the AutoClose settings, based on the module. From my point of view, it can only be the SQL Server Config / model db settings or some kind of tool running in your environments. Or an unknown bug of some sorts.

One solution could also be to utilize the -CustomSqlFile parameter while importing, where you can specify any SQL statement that you want us to run against the database. Next option is to run the Invoke-D365SqlScript cmdlet after the import, before the switch, after the switch, before the dbsync or after the dbsync....

https://github.com/d365collaborative/d365fo.tools/blob/development/docs/Invoke-D365SqlScript.md

[gammelgaard52]

Thank you for your thorough investigation. I'll do a test of the ALTER DATABASE [test] SET AUTO_CLOSE ON (GUI == TRUE??) and look at model DB as you suggests and report back.

The thing is that we would love to avoid running to many work-arounds, but thanks anyway for pointing in the direction of injecting our own SQL scripts.

I am a bit confused as well, since it seems strange and what I was able to search here on GitHub, I could not find the option either in any scripts of d365fo.tools. It might be the way that Cloud Hosted DEV environment image is configured that has changed?

[Splaxi]

I know the feeling all to well with avoiding the weight of to many hacks / work arounds, which was why I started on the project back in the days. For every single hack / work around, we have technical debt, which slows us down.

You're the first to ever report an issue with the AUTO_CLOSE - which is why we never saw anything like it. It is a "easy" fix to just implement in the module, and I'll be more than happy to do so - when we know the impact/scale of the issue.

So lets get to the bottom of this, and I'll see if I can test against different machines, across versions and we can compare notes. From there we can plan how we best solves this.

Can you share how you discovered the issue? Did the DBSync break, or was the entire D365FO slow? Curious to understand what to look out for.

[Splaxi]

Please note, that the () was comments.

So: ALTER DATABASE [test] SET AUTO_CLOSE OFF WITH NO_WAIT --(GUI == FALSE??) ALTER DATABASE [test] SET AUTO_CLOSE ON --(GUI == TRUE??)

I just want to make sure we know what the database command translates into in the GUI and what "message" is posted in the SQL Server log, for both commands.

[gammelgaard52]

As soon as I switch the AxDb_original into auto close = true, from GUI, it goes into limbo and recovery mode And now setting back to auto close = false - it leaves recovery mode.

The strange thing is, that ONLY happends to a database that has Dynamics schema. It does not happend to a default created database. I think I'll have to go back to Microsoft documentation and try out their .bacpac restore path and see what happends, trying to find the difference.

[Splaxi]

For the comment about the image could have changed, that is also my bet at the moment, which is why I would love some more details about the different environment / boxes that you are seeing this issue. Are they freshly deployed from the new base image from LCS? Are they newly downloaded VHD/Onebox images from the Shared Asset Library?

Or are talking about upgraded boxes?

[Splaxi]

That is very interesting.

Did you update to the latest sqlpackage.exe on the machine where you are importing the bacpac file?

If you use the same machine + bacpac file and the follow the official guide, you should end up with the same result. We are just wrapping the different commands that they utilize in the commandline and making them available in powershell. If there in fact is a difference, I'm keen to learn more and see where we might be missing something.

Let me know what I can do to help investigate this.

[gammelgaard52]

I know the feeling all to well with avoiding the weight of to many hacks / work arounds, which was why I started on the project back in the days. For every single hack / work around, we have technical debt, which slows us down.

You're the first to ever report an issue with the AUTO_CLOSE - which is why we never saw anything like it. It is a "easy" fix to just implement in the module, and I'll be more than happy to do so - when we know the impact/scale of the issue.

So lets get to the bottom of this, and I'll see if I can test against different machines, across versions and we can compare notes. From there we can plan how we best solves this.

Can you share how you discovered the issue? Did the DBSync break, or was the entire D365FO slow? Curious to understand what to look out for.

So, the way I discovered it was more down to a lucky strike, since the GUI in SSMS is switching between "working normal" and "Recovering", but if you right-click and select Properties on the database at a moment the GUI think it's okay, you can see it's in Recovering. You cannot do that while GUI sees it's in Recovering.

For the development environment I've tested on, it's a 10.0.18 image upgraded to 10.0.19 PEAP. Deployed in LCS as Cloud Hosted environment with DEV topology.

EDIT: Installed latest version with Invoke-D365InstallSqlPackage

List of commands in the order I executed: Install-Module d365fo.tools -AllowClobber -Force Invoke-D365InstallAzCopy Invoke-D365AzCopyTransfer Invoke-D365InstallSqlPackage Import-D365Bacpac -ImportModeTier1 -BacpacFile Switch-D365ActiveDatabase Invoke-D365DbSync Restart-D365Environment -All -ShowOriginalProgress

[Splaxi]

So the list of commands look just like it should. Could you confirm that the version of the sqlpackage.exe is in fact the latest mention on their site? We have seen issues with the download, because they change layouts / html tags on the page, and then we default back to an older version.

[gammelgaard52]

It's latest version https://docs.microsoft.com/en-us/sql/tools/sqlpackage/release-notes-sqlpackage?view=sql-server-ver15

[Splaxi]

Thanks for taking time to validate the different things. I'll see if I can dig some more details out of older versions vs. newer versions from some of our customers.

[gammelgaard52]

No problem. Thanks for looking into it.

[Splaxi]

I know the feeling all to well with avoiding the weight of to many hacks / work arounds, which was why I started on the project back in the days. For every single hack / work around, we have technical debt, which slows us down. You're the first to ever report an issue with the AUTO_CLOSE - which is why we never saw anything like it. It is a "easy" fix to just implement in the module, and I'll be more than happy to do so - when we know the impact/scale of the issue. So lets get to the bottom of this, and I'll see if I can test against different machines, across versions and we can compare notes. From there we can plan how we best solves this. Can you share how you discovered the issue? Did the DBSync break, or was the entire D365FO slow? Curious to understand what to look out for.

So, the way I discovered it was more down to a lucky strike, since the GUI in SSMS is switching between "working normal" and "Recovering", but if you right-click and select Properties on the database at a moment the GUI think it's okay, you can see it's in Recovering. You cannot do that while GUI sees it's in Recovering.

For the development environment I've tested on, it's a 10.0.18 image upgraded to 10.0.19 PEAP. Deployed in LCS as Cloud Hosted environment with DEV topology.

EDIT: Installed latest version with Invoke-D365InstallSqlPackage

List of commands in the order I executed: Install-Module d365fo.tools -AllowClobber -Force Invoke-D365InstallAzCopy Invoke-D365AzCopyTransfer Invoke-D365InstallSqlPackage Import-D365Bacpac -ImportModeTier1 -BacpacFile Switch-D365ActiveDatabase Invoke-D365DbSync Restart-D365Environment -All -ShowOriginalProgress

Oh.

Your list of commands should actually be something like this:

Install-Module d365fo.tools -AllowClobber -Force Invoke-D365InstallAzCopy Invoke-D365AzCopyTransfer Invoke-D365InstallSqlPackage Import-D365Bacpac -ImportModeTier1 -BacpacFile Stop-D365Environment -All #<--- We want all connections to be closed upfront Switch-D365ActiveDatabase Invoke-D365DbSync Restart-D365Environment -All -ShowOriginalProgress

I'm not saying this is the real issue, but just to be very clear. The Switch command just runs below sql script:

ALTER DATABASE ['+ @DestinationName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ['+ @DestinationName + '] MODIFY NAME = [' + @ToBeName + '];
ALTER DATABASE ['+ @SourceName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ['+ @SourceName +'] MODIFY NAME = ['+ @DestinationName +'];
ALTER DATABASE ['+ @DestinationName + '] SET MULTI_USER;
ALTER DATABASE ['+ @ToBeName + '] SET MULTI_USER;

So to minimize the roll back stuff, we recommend that you run the Stop-D365Environment -All command. Please note that the batch service might try to recover and will keep starting again and again. It could be solved with combination of Get-D365Environment -Batch | Set-Service -StartupType Disabled and Stop-D365Environment -All -Kill

Or more general:

...
Import-D365Bacpac -ImportModeTier1 -BacpacFile
Get-D365Environment -All | Set-Service -StartupType Disabled
Stop-D365Environment -All -Kill
Switch-D365ActiveDatabase
Invoke-D365DbSync
Get-D365Environment -All | Set-Service -StartupType Automatic
Start-D365Environment -All
...

[gammelgaard52]

Thanks for the suggestion. I'll take a look at that and see if makes a difference. The core problem is, that the option is being flipped at some point, since default AxDB is actually set to "auto_close = FALSE". I have seen errors where it's related to services running and have connections to SQL, but that is not the case here - at least that is what I have observed.

[Splaxi]

As I said, it is a easy fix - but I would like to know a bit more, to make sure that we aren't chasing ghosts.

If you still see the same behavior after making sure that all dynamics related services have been shutdown, and we know that the batch service isn't recovering all by it self, and creating a new session - I'll see where we can put the AUTO_CLOSE OFF in.

Just for my understanding. Is this issue only for the db that is being switched OUT, or is it also an issue for the db being switched IN?

[gammelgaard52]

As I said, it is a easy fix - but I would like to know a bit more, to make sure that we aren't chasing ghosts.

If you still see the same behavior after making sure that all dynamics related services have been shutdown, and we know that the batch service isn't recovering all by it self, and creating a new session - I'll see where we can put the AUTO_CLOSE OFF in.

Just for my understanding. Is this issue only for the db that is being switched OUT, or is it also an issue for the db being switched IN?

It seems to only apply for the DB being switched OUT - AxDB_original. I looked through the SQL log and could not see a parameter change for AxDB related to auto_close. BTW - SQL model DB has auto_close = false

[Splaxi]

My gut feeling tells me it might be connected to the lack of shutting down the mention services. Let me know when you get result from your testing.

[fhoo]

Not sure if it's related, but I sometimes had issues while switching the database, when I called it in a script directly after the import command. I then started to close the PowerShell session between import and switch and never had the issue again. So maybe there is some open connection to the DB from PowerShell?

[Splaxi]

@fhoo Great thinking.

We had some feedback early on, that removing the original database, after switching and stuff like that, would fail. We never could reproduce the issue on our end, and the user "gave" up on us. So we never got to fix it.

Could be related, as we simple open some connections, run some sql scripts. I'll see if I can locate any connections that aren't being closed correctly.

[Splaxi]

@gammelgaard52

Did you get to spend more time to look further into this?

[gammelgaard52]

@gammelgaard52

Did you get to spend more time to look further into this?

Not yet.

Let me summarize what I have to test:

Install-Module d365fo.tools -AllowClobber -Force Invoke-D365InstallAzCopy Invoke-D365AzCopyTransfer Invoke-D365InstallSqlPackage Import-D365Bacpac -ImportModeTier1 -BacpacFile Stop-D365Environment -All #<--- We want all connections to be closed upfront Switch-D365ActiveDatabase Invoke-D365DbSync Restart-D365Environment -All -ShowOriginalProgress

.. and throughout the process, monitor when and if the auto_close is being flipped to TRUE

@Splaxi Agree?

[Splaxi]

Yes.

Just make sure that the batch service most likely will try to restart when you stop it. So either deactivate the recovery configured before or do the disable trick I shared.

[Splaxi]

Any news to share? 🤞

[gammelgaard52]

Hi, short update. I did not have a chance to do it yet, since I did not have an active case. I talked with my colleagues and they will help verify.

[gammelgaard52]

Hi, The flag is being flipped when I use the switch-d365activedatabase I tried with stop-d365environment before, but same effect.

[Splaxi]

I'll see if I can locate a safe place to put it in, so when the switch is done, it can be applied.

[Splaxi]

I did do a change and pushed it. It should be part of the latest release 0.6.59

[Splaxi]

Okay!

I finally got the same issue as you, and I have finally nailed from end-2-end.

When deploying a new Tier1 from LCS, WITHOUT demo data (contoso) - the freshly deployed Tier1 contains a database that is ..... Auto Close == True

I got it imported into a running Tier1 one, that was deployed with the demo database (contoso) - and that one is Auto Close == False (like we are used to).

I found the issue while trying to DB sync on the newly imported database.

And here I learned that my fix did in fact not work from 0.6.59 - because I was pointing to the wrong database in the fix. That fix will be going out later today.

So your insanity should be intact, there is actually things that have been altered from what we previously were used to.