Issue importing, tier1 to tier1

[abolsen]

Hi

I'm exporting from a tier 1 environment with:
New-D365Bacpac -ExportModeTier1 -BacpacFile C:\Temp\Bacpac\AxDB_staging.bacpac -ExportOnly -MaxParallelism 32

And importing the bacpac in another tier 1 environment:

$currentTime = Get-Date -Format yyyyMMddHHmm
Import-D365Bacpac -ImportModeTier1 -BacpacFile "C:\temp\AxDB_staging.bacpac" -NewDatabaseName "AxDB_staging_$currentTime" -ImportOnly -MaxParallelism 32 -DiagnosticFile "C:\temp\ImportLog.txt"
Switch-D365ActiveDatabase -SourceDatabaseName "AxDB_staging_$currentTime"

1. I'm getting this error:

[12:11:53][Invoke-Process] Error output was: \r\n *** Error importing database:Could not import package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 8171, Level 16, State 2, Procedure PUBPRODUCTATTRIBUTECHANNELMETADATAVIEW, Line 19 Hint 'noexpand' on object 'crt.REFERENCEATTRIBUTEVIEW' is invalid.
Error SQL72045: Script execution error.  The executed script:
CREATE VIEW [crt].[PUBPRODUCTATTRIBUTECHANNELMETADATAVIEW]
AS
(SELECT rppacm.HOSTCHANNEL AS CHANNEL,
        rppacm.ATTRIBUTE,
        rppacm.ATTRIBUTE AS ACTUALATTRIBUTE,
        0 AS ISREFERENCE
 FROM   ax.RETAILPUBPRODUCTATTRIBUTECHANNELMETADATA AS rppacm
 UNION ALL
 SELECT rppacm.HOSTCHANNEL AS CHANNEL,
        rav.ATTRIBUTE,
        rav.REFERENCEDATTRIBUTE AS ACTUALATTRIBUTE,
        1 AS ISREFERENCE
 FROM   ax.RETAILPUBPRODUCTATTRIBUTECHANNELMETADATA AS rppacm
        INNER JOIN
        crt.REFERENCEATTRIBUTEVIEW AS rav WITH (NOEXPAND)
        ON rav.ATTRIBUTE = rppacm.ATTRIBUTE);

2. Since its not tier 1 to 2, or the other way around, which other steps are needed to prep the environment properly for import?

[Splaxi]

Could you share your entire script, to see what you are doing to prep the SQL Server database and the D365FO services?

I have never seen any issues with this, and the noexpand warning / error seems a bit of.

But on another note: If you really just need to move a database copy from a Tier1 to another Tier1, there are far better and faster tools available, than using the bacpac export / import.

Let me know if you need some pointers.

[abolsen]

Pointers would be nice. The noexpand error is no longer there, after upgrading the source environment.
Currently moving db from tier 1 to tier 1 by doing the following, which works, but I can't really seem to fix the Azure blobs for some reason, even though references in docuref and docuvalue seem fine.
If I am to guess, I'm missing something in the post-refresh SQL.

On source:
New-D365Bacpac -ExportModeTier1 -BacpacFile C:\Temp\AxDB_staging.bacpac -MaxParallelism 32

On target:

#Get AzCopy
cd c:\temp
$file = 'c:\temp\azcopy\azcopy.exe'

#If the file does not exist, create it.
if (-not(Test-Path -Path $file -PathType Leaf)) {
     try {
            Invoke-WebRequest -Uri "https://aka.ms/downloadazcopy-v10-windows" -OutFile AzCopy.zip -UseBasicParsing
            Expand-Archive ./AzCopy.zip ./AzCopy -Force
            del azcopy.zip
            Get-ChildItem ./AzCopy/*/azcopy.exe | Move-Item -Destination $file
     }
     catch {
         throw $_.Exception.Message
     }
 }
# If the file already exists, show the message and do nothing.
 else {
     Write-Host "azcopy already exists."
 }

$source_account = "$(d365_rsatstaging_accountname)"
$source_key = "$(d365_rsatstaging_accountkey)"
$target_account = "$(d365_rsat_accountname)"
$target_key = "$(d365_rsat_accountkey)"

$source_context = New-AzureStorageContext -StorageAccountName "$source_account" -StorageAccountKey "$source_key"
$source_token = New-AzureStorageAccountSASToken -Context $source_context -Service Blob,File,Table,Queue -ResourceType Service,Container,Object -Permission "racwdlup" -Protocol HttpsOnly
$source_path = "https://" + $source_account + ".blob.core.windows.net/documents/" + $source_token

$target_context = New-AzureStorageContext -StorageAccountName "$target_account" -StorageAccountKey "$target_key"
$target_token = New-AzureStorageAccountSASToken -Context $target_context -Service Blob,File,Table,Queue -ResourceType Service,Container,Object -Permission "racwdlup" -Protocol HttpsOnly
$target_path = "https://" + $target_account + ".blob.core.windows.net/documents/" + $target_token

Write-Host "
Source: $source_path
Target: $target_path
"

& $file cp "$source_path" "$target_path" --recursive --overwrite false
Stop-D365Environment -All -ShowOriginalProgress
Remove-D365Database -DatabaseName "AxDB_Orig"
Remove-D365Database -DatabaseName "AxDB_reverted"
Remove-D365Database -DatabaseName "AxDB_original"
Write-Host "
Databases present prior to import:"
Get-D365Database

$currentTime = Get-Date -Format yyyyMMddHHmm
Write-Host "
Starting import of db... AxDB_staging_$currentTime
"
Import-D365Bacpac -ImportModeTier1 -BacpacFile "C:\temp\AxDB_staging.bacpac" -NewDatabaseName "AxDB_staging_$currentTime" -MaxParallelism 32
Write-Host "
Switching active db...
"
Switch-D365ActiveDatabase -SourceDatabaseName "AxDB_staging_$currentTime"
Write-Host "
Executing post refresh SQL...
"
Invoke-Sqlcmd -Query "
USE [AXDB];
update sysglobalconfiguration
set value = 'SQLSERVER'
where name = 'BACKENDDB';

update sysglobalconfiguration
set value = 0
where name = 'TEMPTABLEINAXDB';

UPDATE DOCUVALUE SET STORAGEPROVIDERID = 1 WHERE STORAGEPROVIDERID = 0
UPDATE DOCUVALUE SET ACCESSINFORMATION = CONCAT('https://$(d365_rsat_accountname).blob.core.windows.net/documents/',FILEID) WHERE ACCESSINFORMATION = ''
" -ServerInstance localhost

Write-Host "
Starting db sync...
"
Invoke-D365DbSync -DatabaseServer localhost -DatabaseName AxDB -SqlUser axdbadmin -SqlPwd "$(d365_rsat_axdbadmin_password)" -LogPath "C:\Temp\synclog.txt"
Write-Host "
Starting services...
"
Start-D365Environment -All -ShowOriginalProgress

[Splaxi]

So a few comments.

It seems that you are running this from Azure DevOps pipelines, based on several "$()" where you are getting the pipeline variable and persisting that into a powershell variable. Right?

It seems that you download the azcopy tool by hand. Why is that? We have packaged that for you with: https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Invoke-D365InstallAzCopy.md

And to help you make simple command that copies files, using AzCopy we have packaged that for you with:
https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Invoke-D365AzCopyTransfer.md

A comment on the:

$source_account = "$(d365_rsatstaging_accountname)"
$source_key = "$(d365_rsatstaging_accountkey)"
$target_account = "$(d365_rsat_accountname)"
$target_key = "$(d365_rsat_accountkey)"

$source_context = New-AzureStorageContext -StorageAccountName "$source_account" -StorageAccountKey "$source_key"
$source_token = New-AzureStorageAccountSASToken -Context $source_context -Service Blob,File,Table,Queue -ResourceType Service,Container,Object -Permission "racwdlup" -Protocol HttpsOnly
$source_path = "https://" + $source_account + ".blob.core.windows.net/documents/" + $source_token

$target_context = New-AzureStorageContext -StorageAccountName "$target_account" -StorageAccountKey "$target_key"
$target_token = New-AzureStorageAccountSASToken -Context $target_context -Service Blob,File,Table,Queue -ResourceType Service,Container,Object -Permission "racwdlup" -Protocol HttpsOnly
$target_path = "https://" + $target_account + ".blob.core.windows.net/documents/" + $target_token

I get a feeling that you are storing the AccountKey, either directly in the pipeline variables or fetching it from an Azure KeyVault earlier in the pipeline. But having that at hand, at this point in time, in the pipeline - seems counter intuitive. If you know that you need read and write access for your different storage accounts and blob containers, then create a SAS key/token that is valid 1 year, and store that (KeyVault or Pipeline variable). Then all of the above code would be obsolete.

I don't know how fast/slow the Import-D365Bacpac -ImportModeTier1 operations works for you, but I would argue it would be up to 10x times faster running with old fashioned SQL bak files instead. That goes for exporting the bacpac vs. generating a SQL bak file from the source system. The same goes for importing the bacpac file vs. restoring a SQL bak file into the target system. www.dbatools.io would be the best option for that. They have the following cmdlets available:

Backup-DbaDatabase -SqlInstance "localhost" -Database "AXDB" -FilePath "c:\temp\axdb.bak" -Type Full -CopyOnly -CompressBackup

and

Restore-DbaDatabase -SqlInstance "localhost" -DatabaseName "AXDB_Import" -Path "c:\temp\axdb.bak" -WithReplace -ReplaceDbNameInFile

Without knowing all the finer details in your environment and how things are configured, I would argue that it shouldn't be necessary to handle the "axdbadmin" & "$(d365_rsat_axdbadmin_password)" details like you do. If the VM that you are importing/restore the database into, is a fully working D365 DEVBOX, be that a Onebox (VHD from LCS) or a LCS deployed VM, the module will auto load the "axdbadmin" and the password whenever you import the module.

I would argue that you should be able to utilize:
https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Invoke-D365SqlScript.md

Instead of your current usage of "Invoke-Sqlcmd", but that is more to help you with making sure the script uses the correct user account and it will automatically hit the AXDB database.

For the comment about you not being able to fix the Azure Blobs - I'm taking it that you want to override the current DocumentHandling configuration, to make sure the environment is referencing the correct Azure Blobs for these things. Right? Did you try to update the details directly in D365FO, while doing a SQL Profile, to see what statements are executed? Does it work if you update these details directly in D365FO? If yes - and I take it that you are running RSAT tests, wouldn't it be easier to simple the RSAT test with a test case that configured these details, before running the rest of the test cases?

[Splaxi]

Maybe this could be of help:
https://dynamics365ax2012.blogspot.com/2018/04/how-to-activate-document-management-for.html

[abolsen]

Hey, thats's more than just pointers! Thanks!

It is classic DevOps pipelines, and is a bit of mix of snippets, but thank you for going the extra mile, I'll definitely give the scripts an overhaul based on your comments.

The export/import isn't exactly quick (like 3 hours in total, but only runs in the night anyway), so I'd like to go back to bak :), but I guess I'll have to extend the SQL a bit then (like updating users, etc.). I guess it would've been perfect with an "Export/Import-D365Bak -ImportModeTier1"-option.

I'll dive into the document handling part and see if it affects the Azure Storage access I suppose. It was my impression that the only thing that was necessary to change was the two fields in the docuvalue table, but I guess not.

Another idea: I'm thinking it would be nice to be able to include a source connection string for Azure, as an option in a importmodetier1-case, or as a separate cmdlet, and thus make the tools support movement of those files, including configuring D365 references correctly.

Connection string is found here by the way:

using Microsoft.Dynamics.Clx.ServicesWrapper; 
public static void main(Args _args)
{
    info('CsuStorageConnectionString = ' + CloudInfrastructure::GetCsuStorageConnectionString());
    info('CsuClientCertificateThumbprint = ' + CloudInfrastructure::GetCsuClientCertificateThumbprint());
}

[Splaxi]

So you are not the first to ask for us to support bak files, but let me explain the reasoning behind why we don't support it. We (aka me), need to make as great of an impact with the time at our hands, and when I know that the entire SQL community has a battle proven powershell module, that is backed by more than 20 active developers on a weekly basis, I don't see the value in adding support for bak files. The bacpac on the other hand, was this shiny new thing that none of us knew how to handle and that drove the initial efforts of the module and is today still one of the biggest features, along the LCS API support we have. Which is also why I ensure that people learn about the bak option, even though it will be handled by another module.

Could you elaborate on the comment:

Another idea: I'm thinking it would be nice to be able to include a source connection string for Azure, as an option in a importmodetier1-case, or as a separate cmdlet, and thus make the tools support movement of those files, including configuring D365 references correctly.

[abolsen]

That sounds reasonable. What I'm really asking for is support for the process of configuring D365 properly after the bak-refresh. So things like:

• enabling the correct users on the db (just like the bacpac-import cmdlet)
• ensuring that various system tables are truncated and set up correctly for the target environment
• enabling change tracking
• setting up retail config
• etc..

I know that MS provides us with (some) SQL for this, but would be nice to have it wrapped in a cmdlet, perhaps with some additional options.

For my comment, it's just an addition to that really. Would be nice to have a cmdlet that would migrate the Azure storage from one environment to another - and not just the containers, but ensuring that D365 is configured correctly afterwards, which is the hard part.

[Splaxi]

enabling the correct users on the db (just like the bacpac-import cmdlet)

We support specifying a new administrator of the environment:

• https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Set-D365Admin.md

We support adding any user, from any tenant:

• https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Import-D365ExternalUser.md
• https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Import-D365AadUser.md

We support enabling / disabling users:

• https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Enable-D365User.md
• https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Disable-D365User.md

What else in terms of users are you missing? Are you talking about system users like axdbadmin and stuff like that?

ensuring that various system tables are truncated and set up correctly for the target environment

All the scripts that we run, you can execute your self:

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

All SQL scripts are available online for reference, but will also be available locally on the machine and it should be straightforward to locate these and use them in combination with Invoke-D365SqlScript.

• https://github.com/d365collaborative/d365fo.tools/tree/master/d365fo.tools/internal/sql

But could you elaborate on what system tables that you feel that should be truncated?

As a tool / framework, that takes a lot of the effort out of automating some of the more cumbersome things, I feel we are close to what you are looking for. You might have special needs / use cases that you need to support. But that is where you as the partner for the customer is adding value, being able to develop a set of steps / tasks, that fits their need. For every single thing that you feel should be part of the module, please create an issue and share as much detail that you can about the need. If you can solve the need in any way, but don't feel comfortable with the PR's and adding it to the module, you can share it directly with me and I'll make sure to up take the changes and make it part of the module. That is, if I see that the broader community can gain from that - which more often than not 😉

As a last note: I get a sense that the source Tier1 and target Tier1 isn't fully aligned, since your concerns are focused around making sure the different things are in place. If the source Tier1 has been working and configured correctly, and the versions between the source D365FO and the target D365FO isn't several versions apart - I would argue that a backup / restore (bak file) approach, should solve most of the mentioned things.