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

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

from d365fo.tools.

This is community next level stuff unfolding right here 💚

from d365fo.tools.

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

from d365fo.tools.

@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.

from d365fo.tools.

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/development/docs/Export-D365BacpacModelFile.md

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

from d365fo.tools.

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.

from d365fo.tools.

@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.

from d365fo.tools.

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.

from d365fo.tools.