New-D365Bacpac throwing error, Because it is taking too long more the 8 hours for export only.

[WajahatHussain5]

Executing the "New-D365Bacpac" PowerShell command for exporting a Finance and Operations (FinOps) Tier 2 environment database using JIT access (MS time limit for connection is 8 hours) to create a .bacpac file export-only, cmdlet creating .bacpac more than 8 hours resulting timeout error. The termination occurs before the .bacpac file can be created successfully. How can this issue be resolved, improving speed of .bacpac file export. Please note that export can also be possible from LCS, but due to some circumstances I want to export it in VM, either by using SSMS or PowerShell tools. Quick response will be much appreciated. Thanks.

[FH-Inway]

I don't have experience with exporting bacpac directly from a T2 environment using JIT access. Using the LCS export would have been my first recommendation. Since you say that is not an option, maybe you can share some details on why that is? Even if it is not an option, it would be interesting to know how long it takes, because a direct export will likely take a similar long time. If that export is a lot longer than the 8 hours, you may have to get Microsoft support involved.

If you have access, take a look at https://www.yammer.com/dynamicsaxfeedbackprograms/threads/2100280684724224 where some ideas on direct export from T2 database are discussed. In essence it boils down to executing some SQL scripts first to reduce the size of the database. @brtubb-patagonia started that thread and @batetech was also involved, maybe they can share some insights here as well.

One thing to try is different SqlPackage versions to see if that makes a difference.

People have also been experimenting with the SqlPackage parameters and options for importing .bacpac files to achieve better performance, maybe this could also help with the export: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export Note that you can use the -OutputCommandOnly switch of New-D365Bacpac to get the SqlPackage command. You can use that as a baseline and start experimenting from there.

Let us know how it goes, I know a lot of people are struggling with this.

[brtubb-patagonia]

I'll say that the process is so painful that we only run these exports 1-2 times a year. I need to reevaluate how to make it better, as it's a 4-6 day process for me to export the bacpac and reimport to a CHE so I can create a .bak.

Tips: I found that creating your own login after getting access with JIT does not have the 8 hour limitation. So you'd add the firewall rule in LCS, get a JIT, login, then create a permanent login. Use the permanent login for these processes. Note that this login gets removed when copying Prod -> Sandbox.

CREATE USER [YOURUSERNAME]
WITH PASSWORD = '<password>'

-- grant permissions
EXEC sp_addrolemember 'db_owner', 'YOURUSERNAME'

Manually running the export is painful. Sadly required for us. Expect it to take several attempts to nail down the details as SqlPackage doesn't have a way to resume an export if you run into issues, so you'll be back at square one.

For example, we found that there is an index hint in dbo.VALIDATERETAILTRANSACTIONFULFILLMENTLINEVIEW and it was failing to work with SqlPackage without replacing. This is the only one we had to fix.

DECLARE @SQL nvarchar(max);

select @SQL=definition
from sys.objects     o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id( 'dbo.VALIDATERETAILTRANSACTIONFULFILLMENTLINEVIEW')
  and o.type      = 'V';

SET @SQL = REPLACE(@SQL, ' WITH ( INDEX ([RETAILSALESLINE_PERF_IDX1] ))', '');

DROP VIEW dbo.VALIDATERETAILTRANSACTIONFULFILLMENTLINEVIEW;

EXEC (@SQL);

We found we had to do the following: ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);

And set the MAXDOP in the source AxDB to 4 or 8 to improve index rebuild times in the target environment.

The export command is easy. The main obstacle these days is AUTO_DROP stats. This requires using an old version of SqlPackage prior to it supporting this new feature, fixing it up in the model.xml, or updating your CHE to use SQL Server 2022. sqlpackage /a:Export /tf:"Z:\Backup\AxDB_PUTDATEHERE.bacpac" /ssn:"SOURCE_DATABASE_SERVER" /sdn:"SOURCE_DATABASE" /su:"USERNAME" /sp:"PASSWORD" /p:VerifyExtraction=False /p:VerifyFullTextDocumentTypesSupported=false

This takes us over a day to run, just to create the bacpac. Importing the bacpac is the largest time consumer for us. SqlPackage isn't that optimized for imports so it doesn't saturate the resources in a CHE; I didn't see a huge benefit moving to NVMEs in a L8as v3, for example. This is the command we use for import: SqlPackage /a:Import /tsn:. /tdn:"AxDB" /sf:"Z:\SATBackup\AxDB_2023-01-10.bacpac" /ttsc:True /ModelFilePath:"Z:\SATBackup\model.xml" /p:DatabaseLockTimeout=-1 /p:CommandTimeout=7200 /p:RebuildIndexesOfflineForDataPhase=True /MaxParallelism:24 /p:DisableIndexesForDataPhase=False

[FH-Inway]

That is awesome information @brtubb-patagonia , thank you very much!

[Splaxi]

This is community next level stuff unfolding right here 💚

[FH-Inway]

@WajahatHussain5 Hopefully, the information provided by @brtubb-patagonia was helpful. If you have further questions, let us know. If this issue was solved, you can close it (or @Splaxi will do that for you at some time). Feel free to post further comments or open a new issue, even when this one is closed.

[WajahatHussain55]

Thanks @brtubb-patagonia, with your advise I am successfully able to export .bacpac from tier 2 environment but on importing that .bacpac on tier 1 file throwing an error.

[brtubb-patagonia]

@WajahatHussain5 It will depend on your target SQL Server you are trying to import into. I had to run the following in the source database before exporting the bacpac to properly import into my CHE: ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);

To avoid having to re-export, you can modify the model.xml (inside the bacpac when you unzip) to change the Query store option.

[Splaxi]

Or you could use the Export-D365BacpacModelFile cmdlet, to export the file - edit it and use it as a parameter for the Import-D365Bacpac

https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Export-D365BacpacModelFile.md

https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Import-D365Bacpac.md