{"id":1264,"date":"2024-08-19T12:21:01","date_gmt":"2024-08-19T12:21:01","guid":{"rendered":"https:\/\/markedcode.com\/?p=1264"},"modified":"2025-03-03T13:39:43","modified_gmt":"2025-03-03T13:39:43","slug":"tier1-db-to-tier2-env","status":"publish","type":"post","link":"https:\/\/markedcode.com\/index.php\/2024\/08\/19\/tier1-db-to-tier2-env\/","title":{"rendered":"Tier1 DB to Tier2 Env"},"content":{"rendered":"\n

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:<\/p>\n\n\n\n

Take a backup of the database (.bak)<\/strong><\/p>\n\n\n\n

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.<\/p>\n\n\n

\n
\"\"<\/figure><\/div>\n\n\n
BACKUP DATABASE[AxDB] TO DISK = N'D:\\Backups\\axdb_golden.bak' WITHNOFORMAT, NOINIT,\nNAME = N'AxDB_golden-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10\nGO<\/pre>\n\n\n\n
Import this to have a local copy of the DB<\/strong><\/p>\n\n\n\n
In SSMS, right click the ‘Databases’ node and select ‘Restore database\u2026’, 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.<\/p>\n\n\n
\n
\"\"<\/figure><\/div>\n\n\n
Run script to configure DB<\/strong><\/p>\n\n\n\n
After the import is complete, select the data base and run the following query:<\/p>\n\n\n\n
update sysglobalconfiguration\nset value = 'SQLAZURE'\nwhere name = 'BACKENDDB'\n \nupdate sysglobalconfiguration\nset value = 1\nwhere name = 'TEMPTABLEINAXDB'\n \ndrop schema [NT AUTHORITY\\NETWORK SERVICE]\ndrop user [NT AUTHORITY\\NETWORK SERVICE]\ndrop user axdbadmin\ndrop user axdeployuser\ndrop user axmrruntimeuser\ndrop user axretaildatasyncuser\ndrop user axretailruntimeuser<\/pre>\n\n\n\n
Export database as bacpac file.<\/strong><\/p>\n\n\n\n
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:<\/p>\n\n\n\n
https:\/\/learn.microsoft.com\/en-us\/sql\/tools\/sqlpackage\/sqlpackage-download?view=sql-server-ver16<\/a><\/p>\n\n\n\n
This will output our database as a .bacpac file that can be applied to a tier2 environment.<\/p>\n\n\n\n
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<\/pre>\n\n\n\n
Import to LCS<\/strong><\/p>\n\n\n\n