Comments (8)
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/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.
Related Issues (20)
- Upload bacpac file to LCS asset library issue HOT 12
- :sparkles: new cmdlet New-D365EntraIntegration HOT 17
- :bug: Environment type for non-us/global regions are not correctly determined
- Move database from one env to another HOT 1
- :sparkles: add Entra integration app registration to wif.config HOT 1
- Understand the LCS cookie and how to work with it HOT 2
- :sparkles: Add Get-D365RsatSelfServiceCertificates cmdlet HOT 6
- Import-D365Bacpac cannot find SqlPackage HOT 11
- Invoke-D365DBSync / Catch error when synchronization went wrong on DevOps HOT 2
- Error importing Tier 2 database to Tier 1 VM - The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server. HOT 15
- D365LcsApiToken' command was found in the module 'd365fo.tools', but the module could not be loaded HOT 3
- Update "Import a bacpac file into a Tier1 environment" topic HOT 2
- variables.ps1 fails on detecting InstallationInfoDirectory on the new 10.0.39 VMs HOT 3
- Service Drive variable is missing on the new 10.0.39 VMs #829 HOT 3
- Retrieve metadata for on-prem environments HOT 3
- Update cmdlets for Entra integration HOT 1
- Clear-D365BacpacTableData : Exception calling ".ctor" with "2" argument(s): "Illegal characters in path." HOT 1
- File Description not correctly set in LCS Asset upload HOT 4
- enableException not working for Invoke-D365AzCopyTransfer HOT 3
- Guidance on `Invoke-D365SDPInstall` usage 🚀 HOT 4
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from d365fo.tools.