Copy database from an Azure Sql Environment to a OneBox environment

Here is a checklist to copy a Dynamics 365 for Finance and operations database in from a tier-2 (or above) to a OneBox (Dev, Build or Test).

  1. Start by 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
  2. Prepare the database for export
    --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

    Remember to change the database namn on line 23

  3. Export the database
    cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin
    
    SqlPackage.exe /a:export /ssn:<server>.database.windows.net /sdn:MyNewCopy /tf:D:\Exportedbacpac\my.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /sp:<SQLAdmin password> /su:sqladmin

    Note: I usually store the DB backup in an Azure Storage Blog since this is the easiest way to move file between environments. See here

  4. Import the backup in the target environment
    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

     

  5. When the database is imported you will need to prepare it for the new environment
    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
  6. Switch to using the new database
  7. Stop these services
    World Wide Web Publishing Service
    Management Reporter 2012 Process Service
    Microsoft Dynamics 365 Unified Operations: Batch Management Service
  8. Rename the old database to AxDB_orig
  9. Rename the new database to AxDB
  10. Start these services
    World Wide Web Publishing Service
    Microsoft Dynamics 365 Unified Operations: Batch Management Service
    Management Reporter 2012 Process Service
  11. Synchronize the database from Visual Studio

Links
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/database/copy-database-from-azure-sql-to-sql-server

 

 

Leave a Reply