Copy database from a OneBox environment to an Azure Sql Environment

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.

Comments

Leave a Reply

To respond on your own website, enter the URL of your response which should contain a link to this post's permalink URL. Your response will then appear (possibly after moderation) on this page. Want to update or remove your response? Update or delete your post and re-enter your post's URL again. (Find out more about Webmentions.)