Beware of the BacPac

The other day one of my colleagues needed a database copied from PROD to a DEV OneBox. Unfortunately there is no direct route from PROD to Onebox, you need to make a refresh to a Tier-2 environment and then move the database from the Tier-2 environment to LCS and then restore it to the OneBox environment. The issue I had was that I need to keep the data in the Tier-2 environment.

I used LCS to make an export to LCS, performed the entire process from above and it was finally time to restore the export… did not work… The issue I was getting was an error trying to create a trigger referring to a missing column. When I looked closer at this I found that the column was missing had been removed a long time ago and (in AX/D365 removed columns gets the prefix DEL_ and this column had that). Apparently the column had been removed before the export was done and for some reason the export was not consistent.

There are a couple of different solution to this problem…

In my case I opened up the bacpac file (it is apparently a zip file). Inside is all the data and the schema for the database. I opened the model.xml file and search for the T-SQL statement that created the trigger, I changed the statement to point to the DEL_ table, saved the file and put it back into the zip and renamed it back to bacpac.

When I tried to import it I got an error that said “mismatching checksum”. In the file called origin.xml there is a line containing the checksum for model.xml:

<Checksum Uri=”/model.xml”>8E0D68CEA76ACDB4DD4EE9853065E0E10D9C426F0CC880D18ABC19AAC64624C9</Checksum>

This prevents someone tampering with the model file. To generate a ned checksum for the model file, run the following PowerShell script and point to the model.xml file. Past the HASH in origin.xml

$modelXmlPath = Read-Host "model.xml file path"

$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")

$fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)

$hash = $hasher.ComputeHash($fileStream)

$hashString = ""

Foreach ($b in $hash) { $hashString += $b.ToString("X2") }

$fileStream.Close()

$hashString

Put both Origin.xml and model.xml in the zipfil and change the extension to bacpac. Import it again.

The final takeaway: DO NOT RELY ON BACPAC EXPORTS for backup!!! Or at least test them in some way…

Note added after post:

A safer way to do this is probably (as long as we still have RDP access to t-2 environment):

Creating a new database as a copy of the original (since you are going to mess with it) by running this SQL query:

CREATE DATABASE MyNewCopy AS COPY OF axdb_mySourceDatabaseToCopy

Since this operation runs asynchronously in the background you will have to keep an eye on it before continuing:

SELECT * FROM sys.dm_database_copies

Setting up PowerBI embedded and Analytical workspaces in Dynamics 365 for Finance and Operations in a OneBox Environment

One of the cool features in Dynamics 365 for Finance and Operation are Analytical workspaces which enables Dynamics to display PowerBI workspaces inside of the Dynamics UI which also means that you are able to use the included limited PowerBI license that comes with D365fO

To get this working you can either deploy the includes report packages which are downloadable from LCS or you can create your own and upload them to LCS. There is however some infrastructure required to get this up and running  because the report get their data from aggregetad measurements, which you are required to create.

The measurements are syncronized to the AxDW database and the report uses Direct Query to read the data. Since the report reads its data from the AxDW database it does not have the ferformance implications that OData has.

The process for setting this up in a Tier-2 and up environment is pretty straight forward since these all include the specific service for hosting the PowerBI report but in Tier-1 (OneBox) this service is not present and this means that we will have to do a slight workaround.

First we have to create an Azure Service for hosting the report, you will find the detils in this link

The next step is to copy the AxDW database from the environment to Azure SQL. You find the information here and here.

And the last part is to reconfigure the OneBox environment to use the Azure SQL hosted entity store instead of the internal

I only had one minor issue diring the setup… the AxDW admin user was not able to log in to the AxDW database in Azure SQL resulting in this message:

I resolved it by deleting and recreating the user :

CREATE LOGIN [axdwadmin] 
WITH PASSWORD = 'axdwpassword'

CREATE USER [axdwadmin] 
FOR LOGIN [axdwadmin] 
WITH DEFAULT_SCHEMA = dbo; 

ALTER ROLE db_datareader ADD MEMBER [axdwadmin]; 
ALTER ROLE db_datawriter ADD MEMBER [axdwadmin]; 
ALTER ROLE db_owner ADD MEMBER [axdwadmin];

Links:

https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/deploy-power-bi-embedded-service-for-dynamics-365-for-operations/

https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/deploy-azure-sql-server-for-hosting-entity-store-database/
https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/publish-the-entity-store-database-on-azure-sql-server/

https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/configuring-1box-to-enable-analytical-workspaces-and-reports/

Blocking sessions in Azure SQL

Some environments in Dynamics 365 for Finance and Operations are using Azure SQL. In LCS you can see blocked sessions for the environment but you are not able to do anything about them. To solve the issue you need to log into one of the AOSes using RDP and start Management Studio.

The issue in SMSS when sonnecting it to Azure SQL is that you do not have assess to Activity Monitor… you need to solve the issue using T-SQL.

In order to find the blocking session, run this query:

SELECT
    blocking_session_id AS BlockingSessionID,
    session_id AS VictimSessionID,
   
    (SELECT  FROM sys.sysprocesses
     CROSS APPLY sys.dm_exec_sql_text([sql_handle])
     WHERE spid = blocking_session_id) AS BlockingQuery,
   
     AS VictimQuery,
    wait_time/1000 AS WaitDurationSecond,
    wait_type AS WaitType,
    percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0

To kill the blocking sessions

Kill [SessionID]

That is it…

Note that when you kill a session in SQL the server rolls the transactions back and that might take some time

/Johan

Copy database from a OneBox environment to an Azure Sql Environment

Most of this post is a copy of the Microsoft Article (which is referenced below) but with my remarks, clarifications adn some changes for making the process easier.

1. Create a Backup copy of the Source database

BACKUP DATABASE [AxDB] TO DISK = N'D:\Backups\axdb_original.bak' WITH NOFORMAT, NOINIT, NAME = N'AxDB_golden-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
RESTORE DATABASE [AxDB_CopyForExport] FROM DISK = N'D:\Backups\axdb_original.bak' WITH FILE = 1, MOVE N'AXDBBuild_Data' TO N'F:\MSSQL_DATA\AxDB_CopyForExport.mdf', MOVE N'AXDBBuild_Log' TO N'G:\MSSQL_LOGS\AxDB_CopyForExport_Log.ldf', NOUNLOAD, STATS = 5

Note: You need to change the paths for the database files

2. Prepare YOUR COPY of the database for moving to Azure SQL

update sysglobalconfiguration set value = 'SQLAZURE' where name = 'BACKENDDB' 
update sysglobalconfiguration set value = 1 where name = 'TEMPTABLEINAXDB'
drop procedure XU_DisableEnableNonClusteredIndexes
drop schema [NT AUTHORITY\NETWORK SERVICE]
drop user [NT AUTHORITY\NETWORK SERVICE]
drop user axdbadmin
drop user axdeployuser
drop user axmrruntimeuser
drop user axretaildatasyncuser
drop user axretailruntimeuser
drop user axdeployextuser

3. Export the prepared database to a BacPac file

cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\
md D:\Exportedbacpac
SqlPackage.exe /a:export /ssn:localhost /sdn:AxDB_CopyForExport /tf:D:\Exportedbacpac\my.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false

Note: This operation will take quite a while

4. Copy the bacpac file to the destination server
Note: If you have a large file you can use an Azure Storage Blog

5. Import the bacpac as a new database

cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\
SqlPackage.exe /a:import /sf:C:\Temp\my.bacpac /tsn:azuresqlserver.database.windows.net /tu:sqladmin /tp:<passwordforsqladmin> /tdn:AxDB_New /p:CommandTimeout=1200 /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P2

Note: This operation will take quite a while

6. Update the database with users, passwords and some other settings using the script below:

CREATE USER axdeployuser FROM LOGIN axdeployuser
EXEC sp_addrolemember 'db_owner', 'axdeployuser'

CREATE USER axdeployextuser WITH PASSWORD = '<password from LCS>'
IF EXISTS (select * from sys.database_principals where type = 'R' and name = 'DeployExtensibilityRole')
BEGIN
EXEC sp_addrolemember 'DeployExtensibilityRole', 'axdeployextuser'
END

CREATE USER axdbadmin WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'db_owner', 'axdbadmin'

CREATE USER axruntimeuser WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'db_datareader', 'axruntimeuser'
EXEC sp_addrolemember 'db_datawriter', 'axruntimeuser'

CREATE USER axmrruntimeuser WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'ReportingIntegrationUser', 'axmrruntimeuser'
EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'
EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

CREATE USER axretailruntimeuser WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'
EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'

CREATE USER axretaildatasyncuser WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'

ALTER DATABASE SCOPED CONFIGURATION  SET MAXDOP=2
ALTER DATABASE SCOPED CONFIGURATION  SET LEGACY_CARDINALITY_ESTIMATION=ON
ALTER DATABASE SCOPED CONFIGURATION  SET PARAMETER_SNIFFING= ON
ALTER DATABASE SCOPED CONFIGURATION  SET QUERY_OPTIMIZER_HOTFIXES=OFF
ALTER DATABASE <imported database name> SET COMPATIBILITY_LEVEL = 130;
ALTER DATABASE <imported database name> SET QUERY_STORE = ON;

update [dbo].[SYSSERVICECONFIGURATIONSETTING]
set value =''
where name = 'TENANTID'

update dbo.POWERBICONFIG
set TENANTID = ''

update dbo.PROVISIONINGMESSAGETABLE
set TENANTID = ''

Note: Use these SQL queries to get the TENANTID:
select * from [dbo].[SYSSERVICECONFIGURATIONSETTING]
where name = 'TENANTID'
select TENANTID from dbo.POWERBICONFIG
select TENANTID from dbo.PROVISIONINGMESSAGETABLE

7. Stop services locking the original database

  • World wide web publishing service (on all AOS computers)
  • Microsoft Dynamics 365 for Finance and Operations Batch Management Service (on non-private AOS computers only)
  • Management Reporter 2012 Process Service (on business intelligence [BI] computers only)

Note: this needs to be done on all servers for the environment

8. Run this script to switch the databases

ALTER DATABASE [axdb_123456789] MODIFY NAME = [axdb_123456789_original]
ALTER DATABASE [importeddb] MODIFY NAME = [axdb_123456789]

9. Start services locking the original database

  • World wide web publishing service (on all AOS computers)
  • Microsoft Dynamics 365 for Finance and Operations Batch Management Service (on non-private AOS computers only)
  • Management Reporter 2012 Process Service (on business intelligence [BI] computers only)

10. Syncronize the database

F:
cd F:\AosService\WebRoot\bin
Microsoft.Dynamics.AX.Deployment.Setup.exe -bindir "F:\AosService\PackagesLocalDirectory" -metadatadir F:\AosService\PackagesLocalDirectory -sqluser axdbadmin -sqlserver <azure sql database server name>.database.windows.net -sqldatabase <database name> -setupmode sync -syncmode fullall -isazuresql true -sqlpwd <sql password> >log.txt 2>&1

11. If the environment is running Retail you will need to run the Retail Reprovisioning tool, see instructions in this document

12. Reset the financial Database using these instructions

Note: I have noticed that some integrations are having problems accessing some DLLs after a refresh. This is usually solved by a restart of the environment. To do this easier we usually do it from LCS

Links:
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/database/copy-database-from-sql-server-to-azure-sql
Resetting the financial reporting data mart after restoring a database.

Retail servicing data is not present in the RetailServer web.config when running Retail Retargeting tool

Thi is an issyue we have noticed on especially new environments that we have refreshed with a database from an existing Retail Enable environment. After refreshing the database we need to run the retargeting tool to make sure that all settings are corrected.

When the tool is run it fails at step 3 and when we look in the Step3RetargetRetailServer-logfile we find this:

12/6/2017 9:35:52 AM:     – Trying to get information of the website – AOSService …
Unexpected error. Microsoft.Dynamics.ApplicationPlatform.Environment.Settings.ConfigurationKeyNotFoundException: No setting keys found for prefix ‘CertificateHandler’.
    at Microsoft.Dynamics.ApplicationPlatform.Environment.Settings.BaseConfigSetting.GetSettingsForPrefix(String prefix)
    at ConfigEncryptor.Program.Main(String[] args).
12/6/2017 9:35:52 AM:
PSMessageDetails      :
Exception             : System.Management.Automation.RuntimeException: Retail servicing data is not present in the
                         RetailServer web.config, please contact DSE team.
TargetObject          : Retail servicing data is not present in the RetailServer web.config, please contact DSE team.
CategoryInfo          : OperationStopped: (Retail servicin…ntact DSE team.:String) [], RuntimeException
FullyQualifiedErrorId : Retail servicing data is not present in the RetailServer web.config, please contact DSE team.
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at <ScriptBlock>, F:\DeployablePackages\a2f41617-591e-45c9-bce4-1934c1b17ca6\RetailServer\scrip
                         ts\RetargetRetailServer.ps1: line 299
                         at <ScriptBlock>, <No file>: line 3
                         at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {}
12/6/2017 9:35:52 AM: Executed:
F:\DeployablePackages\a2f41617-591e-45c9-bce4-1934c1b17ca6\RetailServer\scripts\RetargetRetailServer.ps1
Exiting with error code .

This error is due to missing information in the Web.Config file for Retail. Fortunately there is a fairly easy fix… there is a script in the Retail Retargeting tool called Update-RetailServicingInformation.ps1 which populates the web.config file with the correct information. The parameters you will need to provide is the database name which you can find in SQL Server Management Studio and the SQL Server name. The server name for OneBox Environments is Localhost. For Tie-2 and up you will need to provide the Azure SQL name which you can find in LCS, don´t forget to add database.windows.net to make it a FQDN

/Johan

Azure SQL SqlPackage error

Today I tried to do a restore of database to an Azure DB in a Dynamics 365 for Operations environment. When I ran SQLPackage.exe I got the following error:

‘Unable to connect to master or target server ‘AxDB_New’. You must have a user with the same password in master or target server ‘AxDB_New’

The issue here is that the version of SQL Management Studio that is provided in the Azure VM is version 16 and Azure SQL requires version 17… with that background I am not convinced that the error message is totally relevant…

The solution is to upgrade Management Studio to the latest Version.

image

/Johan