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).
- 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
- 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
- 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
- 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
- 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
- Switch to using the new database
- Stop these services
World Wide Web Publishing Service
Management Reporter 2012 Process Service
Microsoft Dynamics 365 Unified Operations: Batch Management Service - Rename the old database to AxDB_orig
- Rename the new database to AxDB
- Start these services
World Wide Web Publishing Service
Microsoft Dynamics 365 Unified Operations: Batch Management Service
Management Reporter 2012 Process Service - 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