Copy D365FO Azure SQL Database to a SQL

Copy a Finance and Operations database from Azure SQL Database to a SQL Server environment

Overview

To move a database, you use the sqlpackage.exe command-line tool to export the database from Azure SQL Database and then import it into Microsoft SQL Server 2016. Because the file name extension for the exported data is .bacpac, this process is often referred to as the bacpac process.

The high-level process for a database move includes the following phases:

1.     Create a duplicate of the source database.

2.     Download the latest SSMS  Link the version number should be greater than Release number: 17.7

3.     Run a SQL script to prepare the database.

4.     Export the database from the Azure SQL database.

5.     Import the database into SQL Server 2016.

6.     Run a SQL script to update the database.

Before you begin

Stop the following services

·         Microsoft batch server

·         Data import/ Export Service

·         IIS services

Now Create a Copy of the source database with the help of below script.

CREATE DATABASE AxDB_XXX AS COPY OF axdb_mySourceDatabaseToCopy

This SQL statement runs asynchronously. In other words, although it appears to be completed after one minute, it actually continues to run in the background. For more information, see CREATE DATABASE (Azure SQL Database). To monitor the progress of the copy operation, run the following query against the MASTER database in the same instance.

SELECT * FROM sys.dm_database_copies

After compilation of copy database remove the extra schemas if they are there

·         SHADOW

·         BACKUP

Above mentioned schema need to remove from your newly copy database.

After the above action removes some users manually the script provided by Microsoft will not remove them even throw an error while deleting. So you have to remove them manually.

After this execute the below script which is available in MS docs.Microsoft.com

--Prepare a database in Azure SQL Database for export to SQL Server.

--Disable change tracking on tables where it is enabled.

declare

@SQL varchar(1000)

set quoted_identifier off

declare changeTrackingCursor CURSOR for

select 'ALTER TABLE ' + t.name + ' DISABLE CHANGE_TRACKING'

from sys.change_tracking_tables c, sys.tables t

where t.object_id = c.object_id

OPEN changeTrackingCursor

FETCH changeTrackingCursor into @SQL

WHILE @@Fetch_Status = 0

BEGIN

exec(@SQL)

FETCH changeTrackingCursor into @SQL

END

CLOSE changeTrackingCursor

DEALLOCATE changeTrackingCursor

--Disable change tracking on the database itself.

ALTER DATABASE

-- SET THE NAME OF YOUR DATABASE BELOW

MyNewCopy

set CHANGE_TRACKING = OFF

--Remove the database level users from the database

--these will be recreated after importing in SQL Server.

declare

@userSQL varchar(1000)

set quoted_identifier off

declare userCursor CURSOR for

select 'DROP USER ' + name

from sys.sysusers

where issqlrole = 0 and hasdbaccess = 1 and name <> 'dbo'

OPEN userCursor

FETCH userCursor into @userSQL

WHILE @@Fetch_Status = 0

BEGIN

exec(@userSQL)

FETCH userCursor into @userSQL

END

CLOSE userCursor

DEALLOCATE userCursor

--Delete the SYSSQLRESOURCESTATSVIEW view as it has an Azure-specific definition in it.

--We will run db synch later to recreate the correct view for SQL Server.

if(1=(select 1 from sys.views where name = 'SYSSQLRESOURCESTATSVIEW'))

DROP VIEW SYSSQLRESOURCESTATSVIEW

--Next, set system parameters ready for being a SQL Server Database.

update sysglobalconfiguration

set value = 'SQLSERVER'

where name = 'BACKENDDB'

update sysglobalconfiguration

set value = 0

where name = 'TEMPTABLEINAXDB'

--Clean up the batch server configuration, server sessions, and printers from the previous environment.

TRUNCATE TABLE SYSSERVERCONFIG

TRUNCATE TABLE SYSSERVERSESSIONS

TRUNCATE TABLE SYSCORPNETPRINTERS

--Remove records which could lead to accidentally sending an email externally.

UPDATE SysEmailParameters

SET SMTPRELAYSERVERNAME = ''

GO

UPDATE LogisticsElectronicAddress

SET LOCATOR = ''

WHERE Locator LIKE '%@%'

GO

TRUNCATE TABLE PrintMgmtSettings

TRUNCATE TABLE PrintMgmtDocInstance

--Set any waiting, executing, ready, or canceling batches to withhold.

UPDATE BatchJob

SET STATUS = 0

WHERE STATUS IN (1,2,5,7)

GO

-- Clear encrypted hardware profile merchand properties

update dbo.RETAILHARDWAREPROFILE set SECUREMERCHANTPROPERTIES = null where SECUREMERCHANTPROPERTIES is not null

Export the database

Open a Command Prompt window and run the following commands.

cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin

SqlPackage.exe /a:export /ssn:<server>.database.windows.net /sdn:<database to export> /tf:D:\Exportedbacpac\my.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /sp:<SQL password> /su:<sql user>

Here is an explanation of the parameters:

·         ssn (source server name) – The name of the Azure SQL Database server to export from.

·         sdn (source database name) – The name of the database to export.

·         tf (target file) – The path and name of the file to export to.

·         sp (source password) – The SQL password for the source SQL Server.

·         su (source user) – The SQL user name for the source SQL Server. We recommend that you use the sqladmin user. This user is created on every Finance and Operations SQL instance during deployment. You can retrieve the password for this user from your project in Microsoft Dynamics Lifecycle Services (LCS).

Screen shot of exporting database

After compilation of Export of the database upload over LCS and download on the target Machine where you need to import the database.

Import the database

When you import the database, we recommend that you follow these guidelines:

·         Retain a copy of the existing AxDB database, so that you can revert to it later if you must.

·         Import the new database under a new name, such as AxDB_XXX.

To help guarantee the best performance, copy the *.bacpac file to the local computer that you're importing from. Open a Command Prompt window and run the following commands.

Use the following script to import database

cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin

SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200

Here is an explanation of the parameters:

·         tsn (target server name) – The name of the SQL Server to import into.

·         tdn (target database name) – The name of the database to import into. The database should not already exist.

·         sf (source file) – The path and name of the file to import from.

For me Script look like…

SqlPackage.exe /a:import /sf:C:\backup\AxDB.bacpac /tsn:localhost /tdn:AxDBUAT /p:CommandTimeout=1200

Screen shot of import database

Update the database

Run the following SQL script against the imported database. This script adds back the users that you deleted from the source database and correctly links them to the SQL logins for this SQL instance. The script also turns change tracking back on. Remember to edit the final ALTER DATABASE statement so that it uses the name of your database.

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

EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser

EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'

EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'

CREATE USER axdeployextuser FROM LOGIN axdeployextuser

EXEC sp_addrolemember 'DeployExtensibilityRole', '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

ALTER DATABASE [<your AX database name>] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)

GO

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

Enable change tracking

If change tracking was enabled in the source database, ensure to enable change tracking again in the newly provisioned database in the target environment using the ALTER DATABASE command.

To ensure the current version of the store procedure (related to change tracking) is used in the new database, you must enable/disable change tracking for a data entity in data management. This can be done on any entity as this is needed to trigger the refresh of store procedure.

Re-provision the target environment

When copying a database between environments, you will need to run the environment re-provisioning tool before the copied database is fully functional, to ensure that all Retail components are up-to-date.

Follow these steps to run the Environment reprovisioning tool.

1.    In the Shared asset library, select Software deployable package.

2.    Download the Environment reprovisioning tool.

3.    In the asset library for your project, select Software deployable package.

4.    Select New to create a new package.

5.    Enter a name and description for the package. You can use Environment reprovisioning tool as the package name.

6.    Upload the package that you downloaded earlier.

7.    On the Environment the details page for your target environment, select Maintain > Apply updates.

8.    Select the Environment a reprovisioning tool that you uploaded earlier, and then select Apply to apply the package.

9.    Monitor the progress of the package deployment.

Start to use the new database

To switch the environment and use the new 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 the services have been stopped, rename the AxDB database AxDB_orig, rename your newly imported database AxDB, and then restart the three services.

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

Support Faryal's Cusine