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.