Importing D365 F&O UAT Database to Cloud Hosted Environment (CHE) Using SqlPackage and SQL Server Management Studio (SSMS).

 This is a technical walkthrough on the steps involved in importing your User Acceptance Testing (UAT) environment database to your Cloud Hosted (Tier-1) Environment.

This technical walkthrough seeks to address the increasing challenges Microsoft D365F&O clients encounter when importing their databases from the cloud.

I have gone through every step and tried to be as detailed and as practical as possible.

Note: This technical walkthrough is quite detailed, thus it might be really long, but I promise you will understand every step of it.

Prerequisites:

You should be comfortable using:

• Microsoft SQL Server Management Studio (SSMS)
• D365F&O Lifecycle Service (LCS) - Asset Library
• Command Prompt terminal (Windows, Linux, or MacOS)
• Managing Windows (Linux/MacOS) Services

Importing The UAT Database to Your Cloud Hosted (Tier-1) Environment

Before you can perform a dataset import into your Cloud Hosted Environment (CHE) also referred to as a Tier-1 environment, you must have exported the database to Asset Library on LCS.

Reference the article below to see how to export your database to Asset Library. Export a copy of the standard user acceptance testing (UAT) database - Finance & Operations | Dynamics 365 | Microsoft Learn.

After a successful database export, you need to download the database backup file (.bacpac file) into your CHE environment. It usually best practice to keep a copy of your existing database, so that you can always refer back to it when necessary. Also, ensure you import the new database with a new name indicating the source environment e.g AxDB_Dev or AxDB_Prod. For best performance and to prevent network issues while copying the database from Asset Library on LCS. It is better to download the .bacpac file directly from LCS into your CHE/Tier-1 virtual machine (VM). You need to generate a Shared Assess Signature (SAS) link of the database .bacpac file and download the database into your VM Follow the steps below on LCS Asset Library: 

Fig.1: UAT database backup file in Asset Library

Steps:

1. Click on Database backup

2. Select the database you want to import to your Tier-1 environment

3. Click on Generate SAS link

Fig.2: SAS link generated for the database backup file

Immediately you click on the Generate SAS link, the database backup file (.bacpac) downloadable link will be copied to your clipboard. You can use this link to download the database into your CHE virtual machine. 

Fig. 3: A screenshot of the database (.bacpac file) downloaded using the SAS link.

To start the import operation, you need to prepare your environment by have the following tools available: Depending on the operating system you are using, you need to download and install sqlpackage .NET Core for either Linux, Windows, or MacOS. Use the link below to download the sqlpackage .NET Core

Download and install SqlPackage - SQL Server

This application has some dependencies that needs to be installed in your virtual machine. You need the Dotnet tools, Dotnet SDK, DacFramework installed. The article above will guide you.

Fig. 4: Dependencies and SqlPackage file in my CHE Virtual Machine

After installing all the required packages. You have to use the Command Prompt terminal to navigate to the path where the sqlpackage is located. Mine is located on my desktop and renamed sqlpackage as seen in the screenshot above.

To navigate to the location of the sqlpackage, just copy the path and change directory into the directory in your Command Prompt terminal. 

CD C:\Users\Administrator\Desktop\sqlpackage

Note: I renamed my downloaded sqlpackage folder name to just sqppackage. Your folder will have a name longer than just sqlpackage. It is always better to rename the folder. There is no cost for not renaming the folder. I just prefer my Command Prompt terminal to clean as in the screenshot below:

Fig. 5: Sqlpackage shell

Now in the Command Prompt terminal, run the following command in the sqlpackage shell:

SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn: /p:CommandTimeout=1200 /TargetTrustServerCertificate:True 

Here is an explanation of the parameters: 

•  tsn (target server name) – The name of the Microsoft SQL Server instance to import into. Usually, localhost or MSSQLSERVER 
• tdn (target database name) – The name of the database to import into. The database should not already exist. In our case we will use AXDBImport
• sf (source file) – The path and name of the file to import from. You can get this from the path of the database (.bacpac file) you downloaded into your CHE virtual machine.

Here is a summary of the parameters we will use:

• tsn: localhost tdn: AXDBImport
• sf: C:\Users\Administrator\Desktop\supselfsandboxbackup.bacpac

Finally, our SqlPackage import operation command will be as seen below:

SqlPackage.exe /a:import /sf:C:\Users\Administrator\Desktop\supselfsandboxbackup.bacpac /tsn:localhost /tdn:AXDBImport /p:CommandTimeout=1200 /TargetTrustServerCertificate:True 

Fig. 6: SqlPackage Command to initiate the database import operation

The deployment of AXDBImport is now being initialized. This will take some time to complete You have to ensure you have cd into the folder containing the sqlpackage. 

Fig.7: AXDBImport Successfully imported

The AXDBImport database is now successfully imported. Next, we need to confirm this in Microsoft SQL Server Management Studio (SSMS).

Fig.8: AXDBImport database import confirmed in SSMS

Awesome! We can confirm 100% that the database was successfully imported into our Cloud Hosted (Tier-1) environment.

Before you can use this database, you have to update the database.

Updating the Imported Database

We will update the database by using the SQL script below. As you can see, the script contains series of stored procedures to create users and add back users that were deleted from the source database and link them back to the SQL logins for the SQL Server instance in the virtual machine, drop statements to drop existing change tracking existing in the source database and also update statement to turn on change tracking on the new SQL Server instance. 

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


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


CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser
EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'
EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'


CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser


CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser


CREATE USER axdeployextuser FROM LOGIN axdeployextuser


CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'


UPDATE T1
SET T1.storageproviderid = 0
    , T1.accessinformation = ''
    , T1.modifiedby = 'Admin'
    , T1.modifieddatetime = getdate()
FROM docuvalue T1
WHERE T1.storageproviderid = 1 --Azure storage


DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking
DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2
GO
-- Begin Refresh Retail FullText Catalogs
DECLARE @RFTXNAME NVARCHAR(MAX);
DECLARE @RFTXSQL NVARCHAR(MAX);
DECLARE retail_ftx CURSOR FOR
SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES
    WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = 'COMMERCEFULLTEXTCATALOG');
OPEN retail_ftx;
FETCH NEXT FROM retail_ftx INTO @RFTXNAME;


BEGIN TRY
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        PRINT 'Refreshing Full Text Index ' + @RFTXNAME;
        EXEC SP_FULLTEXT_TABLE @RFTXNAME, 'activate';
        SET @RFTXSQL = 'ALTER FULLTEXT INDEX ON ' + @RFTXNAME + ' START FULL POPULATION';
        EXEC SP_EXECUTESQL @RFTXSQL;
        FETCH NEXT FROM retail_ftx INTO @RFTXNAME;
    END
END TRY
BEGIN CATCH
    PRINT error_message()
END CATCH


CLOSE retail_ftx; 
DEALLOCATE retail_ftx; 
-- End Refresh Retail FullText Catalogs


--Begin create retail channel database record--
declare @ExpectedDatabaseName nvarchar(64) = 'Default';
declare @DefaultDataGroupRecId BIGINT;
declare @ExpectedDatabaseRecId BIGINT; 
IF NOT EXISTS (select 1 from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName)
BEGIN 
	select @DefaultDataGroupRecId = RECID from RETAILCDXDATAGROUP where NAME = 'Default'; 
	insert into RETAILCONNDATABASEPROFILE (DATAGROUP, NAME, CONNECTIONSTRING, DATASTORETYPE)
	values (@DefaultDataGroupRecId, @ExpectedDatabaseName, NULL, 0); 
	select @ExpectedDatabaseRecId = RECID from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName; 
	insert into RETAILCDXDATASTORECHANNEL (CHANNEL, DATABASEPROFILE)
	select RCT.RECID, @ExpectedDatabaseRecId from RETAILCHANNELTABLE RCT
	inner join RETAILCHANNELTABLEEXT RCTEX on RCTEX.CHANNEL = RCT.RECID
        update RETAILCHANNELTABLEEXT set LIVECHANNELDATABASE = @ExpectedDatabaseRecId where LIVECHANNELDATABASE = 0
END; 
--End create retail channel database record

Fig.9: Database update script executed against AXDBImport database in SSMS.

The query will complete with errors. This is an expected outcome.

Fig.10: Query completed with errors on AXDBImport.

You can see that some rows were affected by the query. The next step is to turn on change tracking since the database update query already disabled the change tracking from the source database. 

Turn On Change Tracking

If change tracking was turned on in the source database, be sure to turn it on in the newly provisioned database in the target environment. To turn on change tracking, use the ALTER DATABASE command. Run the script below to turn change tracking on for the AXDBImport database.

ALTER DATABASE AXDBImport SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON);

Note: Please replace AXDBImport with the name of your imported database.

Fig.11: Change tracking successfully turned on for AXDBImport database.

After changed tracking has been successfully turned for the imported database, the next step is to prepare the environment to start using the new database. To start using the new database, we will need to switch to the new database using SSMS.

The following operations must be done before switching the database:

First stop the following services: 

• World Wide Web Publishing Service
• Microsoft Dynamics 365 Unified Operations: Batch Management Service
• Management Reporter 2012 Process Service

After these services have been stopped, rename the AxDB database AxDB_original, rename your newly imported database AxDB, and then restart the three services.

Follow the steps below to accomplish the task above.

Fig.12a Run services.msc to access the list of services in your CHE virtual machine.

Run services.msc to open the Services in your virtual machine.

Fig12b: List of services in your CHE virtual machine

Search for the three (3) services below and stop them:

• World Wide Web Publishing Service
• Microsoft Dynamics 365 Unified Operations: Batch Management Service
• Management Reporter 2012 Process Service

Fig.12c: Click on Stop to stop the World Wide Web Publishing Service

Fig.12d: Click on Stop to stop the Management Reporter 2012 Process Service

Fig. 12e: Click on Stop to stop the Management Reporter 2012 Process Service

Now, go back to SSMS and rename the databases.

Rename AxDB to AXDB_original Rename AXDBImport to AxDB. This way SSMS will accept the AxDBImport as the original AxDB database used by Microsoft Dynamics 365 Finance and Operation.

Fig.13a: Renaming database name for both the original AXDB and the AXDBImport.

Right click on the database you want to rename and then click on rename. Then proceed to renaming the database. 

Fig.13b: Renaming the databases in SMSS

Fig.13c: The databases have been renamed successfully.

Note: You must rename AxDB to AXDB_original before renaming AXDBImport to AxDB. This is because database name must be unique i.e., no duplicate database name is allowed. Also, you must close the open query pane for AXBImport database before you can rename (you cannot rename a service that is currently in use/opened). After completing these steps, you have to go the list of services again and turn on the services that was turned off before.

Turn the services below ON again and then restart SSMS: 

• World Wide Web Publishing Service
• Microsoft Dynamics 365 Unified Operations: Batch Management Service
• Management Reporter 2012 Process Service

To switch back to the original database, reverse this process. In other words, stop the services, rename the databases, and then restart the services.

Congratulations! If you made it this far without any mistake, then you have successfully imported your UAT/Sandbox database into your CHE/Tier-1 environment and configured it to be used by the environment.