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.
