Tier1 DB to Tier2 Env – Marked Code

Moving a Tier1 cloud hosted DB to a Tier2 environment is not as straightforward as taking a backup of one and restoring it to another. This is because Tier1 environments use Windows SQL while Tier2 environments use Azure SQL. To facilitate this move, we need to do the following steps:

Take a backup of the database (.bak)

In the Tier1 VM’s SSMS, open a new query. Be sure you are running under the ‘master’ section. Run the following query to output a .bak file of the AxDB database. This back up will save to the D drive, you may need to create the folder ‘Backups’ here before running the query. After this is ran you can see the created .bak file here.

BACKUP DATABASE[AxDB] TO DISK = N'D:\Backups\axdb_golden.bak' WITHNOFORMAT, NOINIT,
NAME = N'AxDB_golden-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

Import this to have a local copy of the DB

In SSMS, right click the ‘Databases’ node and select ‘Restore database…’, then follow the wizard to select and import our previously taken database backup. Name this database accordingly, for example, here we call it AxDB_CopyForExport.

Run script to configure DB

After the import is complete, select the data base and run the following query:

update sysglobalconfiguration
set value = 'SQLAZURE'
where name = 'BACKENDDB'
 
update sysglobalconfiguration
set value = 1
where name = 'TEMPTABLEINAXDB'
 
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

Export database as bacpac file.

When this is complete, we can use the command prompt to run sqlpacakge commands. If you do not have SqlPackage.exe you can download it here:

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16

This will output our database as a .bacpac file that can be applied to a tier2 environment.

Use CMD: C:\Users\Admin24e60794fb\Desktop\sqlpackage\sqlpackage.exe /a:export /ssn:localhost /sdn:AxDB_CopyForExport /tf:D:\Backups\AxDBGold2.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /SourceTrustServerCertificate:True

Import to LCS

Now you can go to LCS > Asset library > Database backup
- Select your file and select the backup type as ‘Product database (SQL Server or .bak)’ and upload this file.