Copy a Finance and Operations database
from Azure SQL Database to a SQL Server environment
Overview
To move a database, you use the sqlpackage.exe command-line tool
to export the database from Azure SQL Database and then import it into
Microsoft SQL Server 2016. Because the file name extension for the exported
data is .bacpac, this process is often referred to as the bacpac process.
The high-level process for a database move includes the following
phases:
1.
Create a duplicate of the source database.
2.
Download the latest SSMS Link the version number should be greater than Release
number: 17.7
3.
Run a SQL script to prepare the database.
4.
Export the database from the Azure SQL database.
5.
Import the database into SQL Server 2016.
6.
Run a SQL script to update the database.
Before you begin
Stop the following services
·
Microsoft batch server
·
Data import/ Export Service
·
IIS services
Now Create a Copy of the source database with the help of below
script.
CREATE DATABASE AxDB_XXX AS COPY OF
axdb_mySourceDatabaseToCopy
This SQL statement runs asynchronously. In other words,
although it appears to be completed after one minute, it actually continues to
run in the background. For more information, see CREATE DATABASE (Azure SQL Database). To monitor the
progress of the copy operation, run the following query against the MASTER
database in the same instance.
SELECT * FROM sys.dm_database_copies
After compilation of copy database
remove the extra schemas if they are there
·
SHADOW
·
BACKUP
Above mentioned schema need to remove
from your newly copy database.
After the above action removes some users
manually the script provided by Microsoft will not remove them even throw an
error while deleting. So you have to remove them manually.
After this execute
the below script which is available in MS docs.Microsoft.com
--Prepare a database in
Azure SQL Database for export to SQL Server.
--Disable change tracking on
tables where it is enabled.
declare
@SQL varchar(1000)
set quoted_identifier off
declare changeTrackingCursor
CURSOR for
select 'ALTER TABLE ' +
t.name + ' DISABLE CHANGE_TRACKING'
from
sys.change_tracking_tables c, sys.tables t
where t.object_id =
c.object_id
OPEN changeTrackingCursor
FETCH changeTrackingCursor
into @SQL
WHILE @@Fetch_Status = 0
BEGIN
exec(@SQL)
FETCH changeTrackingCursor
into @SQL
END
CLOSE changeTrackingCursor
DEALLOCATE
changeTrackingCursor
--Disable change tracking on
the database itself.
ALTER DATABASE
-- SET THE NAME OF YOUR
DATABASE BELOW
MyNewCopy
set CHANGE_TRACKING = OFF
--Remove the database level
users from the database
--these will be recreated
after importing in SQL Server.
declare
@userSQL varchar(1000)
set quoted_identifier off
declare userCursor CURSOR
for
select 'DROP USER ' + name
from sys.sysusers
where issqlrole = 0 and
hasdbaccess = 1 and name <> 'dbo'
OPEN userCursor
FETCH userCursor into
@userSQL
WHILE @@Fetch_Status = 0
BEGIN
exec(@userSQL)
FETCH userCursor into
@userSQL
END
CLOSE userCursor
DEALLOCATE userCursor
--Delete the
SYSSQLRESOURCESTATSVIEW view as it has an Azure-specific definition in it.
--We will run db synch later
to recreate the correct view for SQL Server.
if(1=(select 1 from
sys.views where name = 'SYSSQLRESOURCESTATSVIEW'))
DROP VIEW SYSSQLRESOURCESTATSVIEW
--Next, set system
parameters ready for being a SQL Server Database.
update
sysglobalconfiguration
set value = 'SQLSERVER'
where name = 'BACKENDDB'
update
sysglobalconfiguration
set value = 0
where name =
'TEMPTABLEINAXDB'
--Clean up the batch server
configuration, server sessions, and printers from the previous environment.
TRUNCATE TABLE
SYSSERVERCONFIG
TRUNCATE TABLE
SYSSERVERSESSIONS
TRUNCATE TABLE
SYSCORPNETPRINTERS
--Remove records which could
lead to accidentally sending an email externally.
UPDATE SysEmailParameters
SET SMTPRELAYSERVERNAME = ''
GO
UPDATE
LogisticsElectronicAddress
SET LOCATOR = ''
WHERE Locator LIKE '%@%'
GO
TRUNCATE TABLE
PrintMgmtSettings
TRUNCATE TABLE
PrintMgmtDocInstance
--Set any waiting,
executing, ready, or canceling batches to withhold.
UPDATE BatchJob
SET STATUS = 0
WHERE STATUS IN (1,2,5,7)
GO
-- Clear encrypted hardware
profile merchand properties
update
dbo.RETAILHARDWAREPROFILE set SECUREMERCHANTPROPERTIES = null where
SECUREMERCHANTPROPERTIES is not null
Export the database
Open a Command Prompt window and run the
following commands.
cd C:\Program Files (x86)\Microsoft SQL
Server\140\DAC\bin
SqlPackage.exe
/a:export /ssn:<server>.database.windows.net /sdn:<database to
export> /tf:D:\Exportedbacpac\my.bacpac /p:CommandTimeout=1200
/p:VerifyFullTextDocumentTypesSupported=false /sp:<SQL password>
/su:<sql user>
Here is an explanation of the parameters:
·
ssn (source server name) – The name of the Azure SQL Database
server to export from.
·
sdn (source database name) – The name of the database to export.
·
tf (target file) – The path and name of the file to export to.
·
sp (source password) – The SQL password for the source SQL Server.
·
su (source user) – The SQL user name for the source SQL Server. We recommend
that you use the sqladmin user. This user is
created on every Finance and Operations SQL instance during deployment. You can
retrieve the password for this user from your project in Microsoft Dynamics
Lifecycle Services (LCS).
Screen
shot of exporting database
After compilation
of Export of the database upload over LCS and download on the target Machine
where you need to import the database.
Import the database
When you import the database, we recommend that you follow these
guidelines:
·
Retain a copy of the existing AxDB database, so that you can
revert to it later if you must.
·
Import the new database under a new name, such as AxDB_XXX.
To help
guarantee the best performance, copy the *.bacpac file to the local computer
that you're importing from. Open a Command
Prompt window and run the following commands.
Use
the following script to import database
cd C:\Program Files (x86)\Microsoft SQL
Server\140\DAC\bin
SqlPackage.exe /a:import
/sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database
name> /p:CommandTimeout=1200
Here is an explanation of the parameters:
·
tsn (target server name) – The name of the SQL Server to import
into.
·
tdn (target database name) – The name of the database to import into.
The database should not already
exist.
·
sf (source file) – The path and name of the file to import from.
For me
Script look like…
SqlPackage.exe /a:import
/sf:C:\backup\AxDB.bacpac /tsn:localhost /tdn:AxDBUAT /p:CommandTimeout=1200
Screen shot of import database
Update the database
Run the following SQL script against the imported database. This
script adds back the users that you deleted from the source database and
correctly links them to the SQL logins for this SQL instance. The script also
turns change tracking back on. Remember to edit the final ALTER DATABASE statement so that it uses the name of your database.
CREATE
USER axdeployuser FROM LOGIN axdeployuser
EXEC
sp_addrolemember 'db_owner', 'axdeployuser'
CREATE
USER axdbadmin FROM LOGIN axdbadmin
EXEC
sp_addrolemember 'db_owner', 'axdbadmin'
CREATE
USER axmrruntimeuser FROM LOGIN axmrruntimeuser
EXEC
sp_addrolemember 'db_datareader', 'axmrruntimeuser'
EXEC
sp_addrolemember 'db_datawriter', 'axmrruntimeuser'
CREATE
USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser
EXEC
sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'
CREATE
USER axretailruntimeuser FROM LOGIN axretailruntimeuser
EXEC
sp_addrolemember 'UsersRole', 'axretailruntimeuser'
EXEC
sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'
CREATE
USER axdeployextuser FROM LOGIN axdeployextuser
EXEC
sp_addrolemember 'DeployExtensibilityRole', 'axdeployextuser'
CREATE
USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]
EXEC
sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'
UPDATE
T1 SET T1.storageproviderid = 0 ,
T1.accessinformation = ''
, T1.modifiedby = 'Admin' , T1.modifieddatetime = getdate()
FROM
docuvalue T1
WHERE
T1.storageproviderid = 1 --Azure storage
ALTER
DATABASE [<your AX database name>] SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)
GO
DROP
PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking
DROP
PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2
GO
--
Begin Refresh Retail FullText Catalogs
DECLARE
@RFTXNAME NVARCHAR(MAX);
DECLARE
@RFTXSQL NVARCHAR(MAX);
DECLARE
retail_ftx CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM
SYS.FULLTEXT_INDEXES
WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1
FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME =
'COMMERCEFULLTEXTCATALOG');
OPEN
retail_ftx;
FETCH
NEXT FROM retail_ftx INTO @RFTXNAME;
BEGIN
TRY
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Refreshing Full Text Index '
+ @RFTXNAME;
EXEC SP_FULLTEXT_TABLE @RFTXNAME,
'activate';
SET @RFTXSQL = 'ALTER FULLTEXT INDEX
ON ' + @RFTXNAME + ' START FULL POPULATION';
EXEC SP_EXECUTESQL @RFTXSQL;
FETCH NEXT FROM retail_ftx INTO
@RFTXNAME;
END
END
TRY
BEGIN
CATCH
PRINT error_message()
END
CATCH
CLOSE
retail_ftx;
DEALLOCATE
retail_ftx;
--
End Refresh Retail FullText Catalogs
Enable change tracking
If change tracking was enabled in the source database, ensure to
enable change tracking again in the newly provisioned database in the target
environment using the ALTER DATABASE command.
To ensure the current version of the store procedure (related to
change tracking) is used in the new database, you must enable/disable change
tracking for a data entity in data management. This can be done on any entity
as this is needed to trigger the refresh of store procedure.
Re-provision the target environment
When copying a database between environments, you will need to run
the environment re-provisioning tool before the copied database is fully
functional, to ensure that all Retail components are up-to-date.
Follow these steps to run the Environment reprovisioning tool.
1.
In the Shared asset
library, select Software deployable package.
2.
Download the Environment
reprovisioning tool.
3.
In the asset library for
your project, select Software deployable package.
4.
Select New to create a new package.
5.
Enter a name and
description for the package. You can use Environment reprovisioning tool as the package name.
6.
Upload the package that
you downloaded earlier.
7.
On the Environment the details page for your
target environment, select Maintain > Apply updates.
8.
Select the Environment a reprovisioning tool that you uploaded earlier, and then select Apply to apply the package.
9.
Monitor the progress of
the package deployment.
Start to use the new database
To switch the environment and use the new database, first stop the
following services:
·
World Wide Web Publishing Service
·
Microsoft Dynamics 365 Unified Operations: Batch Management
Service
·
Management Reporter 2012 Process Service
After the services have been stopped, rename the AxDB
database AxDB_orig, rename your newly
imported database AxDB, and then restart the
three services.
To switch back to the original database, reverse this process. In
other words, stop the services, rename the databases, and then restart the
services.
