Update "Import a bacpac file into a Tier1 environment" topic

[TrudAX]

Hi.
I think this wiki topic should be updated(at least for the current date usage)

https://github.com/d365collaborative/d365fo.tools/wiki/How-To-Import-Bacpac-Into-Tier1

Repair-D365BacpacModelFile should be added to the process. Right now, restore can't be done due to the Kill permission problem

[TrudAX]

Just restored a Tier2
It seems MS removed this Grant.KillDatabaseConnection.Database permission (at least for plain Tier2 databases), maybe Repair-D365BacpacModelFile is not needed anymore

[FH-Inway]

@TrudAX That's good to see that the Grant.KillDatabaseConnection element was removed by Microsoft.

Nevertheless, I think it is a good idea to add some guidance for Repair-D365BacpacModelFile to the How To Import Bacpac Into Tier 1. While the current issues for its use may have been resolved, @Splaxi envisioned the cmdlet as something that can also be used to address similar issues that may arise in the future. Also, even with the issues for new database backups resolved, there are presumably still .bacpac files out there that have the issues.

With that in mind, I've reopened the issue. Let us know if you would be interested in updating the How To.

[kalejjanis]

Hello
If I may continue on this topic.
It looks like indeed the Grant.KillDatabaseConnection element is no longer par of the model file, but another issue appeared. Please see the image below:

Long story short:

• Sandbox DB import to Tier1 fails with that message.
• Both environments are on the same SU/QU (10.0.40 (10.0.1935.95))
• Both have the same codebase

MS added another element with the reason: ​This is due to new changes to allow row version tracking for synapse for the Fintag table. This table uses an insteadofinsert trigger which may be incompatible between the versions you are transferring data to.
More details from MS HERE

I created a ticket as the obvious solutions didn't work and here is a list of fixes from them:

Workaround 1.Please download the latest .net core version of SQLPackage.exe.
Link : Download and install SqlPackage - SQL Server | Microsoft Learn

Then, import the UAT DB and validate.
If fails, please try below.

Workaround 2.Could you please try the below steps in UAT.

• Go to Feature Management and turn on the Financial Tags feature:
  
• Go to the Data Maintenance and click "Check for updates":
  
• Run the "Deploy financial tag table triggers" data maintenance job:
  
• When the job finishes successfully, export the database from UAT.

Import the database now in CHE and validate.

Workarodund 3.If it still fails, please try the below step.
-Convert your bacpac file to .zip (just edit the file extension and change .bacpac to .zip)
-Copy the model.xml from the zip folder to some location.
-Revert back the .zip to .bacpac
-Now edit the model.xml file in notepad++

Just replace INSERT INTO FINTAG SELECT * FROM inserted, with below statement:

INSERT INTO FINTAG (TAG01,TAG02,TAG03,TAG04,TAG05,TAG06,TAG07,TAG08,TAG09,TAG10,TAG11,TAG12,TAG13,TAG14,TAG15,TAG16,TAG17,TAG18,TAG19,TAG20, HASH, DISPLAYVALUE, HASHVERSION, PARTITION, RECID, RECVERSION, MODIFIEDDATETIME, MODIFIEDBY, CREATEDDATETIME, CREATEDBY) SELECT TAG01,TAG02,TAG03,TAG04,TAG05,TAG06,TAG07,TAG08,TAG09,TAG10,TAG11,TAG12,TAG13,TAG14,TAG15,TAG16,TAG17,TAG18,TAG19,TAG20, HASH, DISPLAYVALUE, HASHVERSION, PARTITION, RECID, RECVERSION, MODIFIEDDATETIME, MODIFIEDBY, CREATEDDATETIME, CREATEDBY FROM inserted

and save the file.

-While using the sqlpackage.exe import action, add /mfp:"YourCopiedModel.xmlPath" to the commands, this will allow you use the edited model.xml instead of original one in the .bacpac.

Workaround 4.This workaround was provided by one of our customer.
Remove references on the FINTAG triggers from the model.xml file. and then use the modified model.xml file to import the .bacpac.

I can comment that Workaround 1 and 2 didn't work. But I wanted to let you know the possible solutions. The main goal from my side here is to let you know, that Workaround 3 did actually work.
I extracted the model file and replaced the values as per MS comment and the image below:

Then the import worked finally... after a couple of days of troubleshooting wasted...

The second and most important thing what I wanted to achieve with this comment: Would it be possible to include this "fix" into "Repair-D365BacpacModelFile"? Maybe as an additional parameter? Something like -FixFinTagTrigger

This would reduce the manual work required.

Thank you for your time.

[FH-Inway]

Thanks for the detailed report.
It is curious that you are still experiencing this issue, since your environments are already on 10.0.40 where, from what I understand, this issue has been fixed.
Have you created a new .bacpac export based on the current version of the sandbox environment? If that .bacpac still contains the old FINTAG trigger, you might want to consider reporting this to Microsoft.

You can also try the PathRepairReplace parameter of Repair-D365BacpacModelFile to see if that can be used to replace the old with the new trigger.

I'm a bit hesitant to include a dedicated functionality for this issue in the cmdlet. Microsoft continues to come up with issues around the .bacpac import. So trying to address each issue individually is a bit like a game of whac-a-mole. Unfortunately, we do not have the manpower to play that game :(
If PathRepairReplace does not work in this case, but you can think of another way this issue could be adressed in a more general way that hopefully can be used to address similar future issues, I'm ready to be convinced to include it 😄

It would also be helpful to have access to a model.xml file with this issue. I have yet to encounter this issue, which makes it hard to analyze further.

@Splaxi for awareness, since you implemented the cmdlet.

[kalejjanis]

That's the thing - environments were on 10.0.40 and I took a fresh DB copy. The Financial Tags feature wasn't enabled on any environments, which is even more confusing on why it suddenly had problems related to it. Even if I enable that feature, then next step with running the "Deploy financial tag table triggers" job is not possible as it doesn't exist.

No worries. I totally understand that this isn't something worth the potential effort. The workaround is actually quite easy and fast to perform and taking in mind that Sandbox DB restore on Tier1 isn't a daily task usually, then it's totally doable by hand. At least I know what to do and I'll document this for my colleagues. Might as well be a client specific issue.. who knows.

Hope that at least the MS suggested steps will be of help to others who stumble upon this thread.