Author: brian j. parker

As a Dynamics 365 for Finance and Operations developer, you will probably find it easier to develop, test, and debug problems if you have a recent database available to you in your development VM. Although there does exist general documentation on this process, as of this writing, I’ve found it less than ideal for developers; a couple of minor errors, some extra steps, and lacking a few tips for problems you might run into.

Prerequisites

For sake of this post, I’m going to assume that a system administrator has tackled providing you with an exported BACPAC file, and that you are able to get it copied to your development VM.

You should install the latest version of SQL Server Management Studio (or at least some 17.x version), which is an easy way to get the updated SQL tools you will need to work with BACPAC files from current versions of SQL Server Azure. Honestly, I recommend this as part of the initial VM setup.

If you’ve been running Dynamics 365 for Finance and Operations for a while, your VM hard drive might not be big enough. You might need to make the virtual drive larger.

Import the database

You need to run an command prompt as administrator. If you don’t run the command prompt as administrator, you will probably get an error like “Unable to connect to master or target server …” At the administrator command prompt, use the following:

cd "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin"
SqlPackage.exe /a:import /sf:C:\TEMP\BackupFilename.bacpac /tsn:localhost /tdn:AxDB_YYYYMMDD /p:CommandTimeout=1200

You should note three parts of these commands:

1. If the first command gives you an error, and/or you only have a “C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin” directory, and/or you get an error similar to “Error importing database:Could not read schema model header information from package. The model version ‘3.5’ is not supported.“: you should, as mentioned in the prerequisites, install the latest version of SQL Server Management Studio (or at least some 17.x version), which is an easy way to get the updated SQL tools you need.
2. For the second command, you need to change the /sf: switch to point to the BACPAC file.
3. For the second command you also need to change the /tdn: switch to whatever temporary name you want to use to import the new database. You will change it later, but the name you choose will determine filenames used for the database; so it’s best to choose something you haven’t used before. I recommend using a AxDB_YYYYMMDD format. If importinf gives you an error about the filename being in use, the easiest thing to do is pick a different database name. Remember, it’s just temporary!

Once you have it running, be prepared to wait a while. Like, a couple of hours is very possible.

Update the database

This is pretty much just running code in a query window of SQL Server Management Studio (SSMS). I offer some minor tweaks (and comments) to what Microsoft does.

-- Following is the SQL script you use after importing the database.
-- You might want to run it one "chunk" (up to each "go" statement) at a time.
-- You need to change the next line to use the "temporary" database name you picked.
use AxDB_YYYYMMDD
go


CREATE USER axdeployuser FROM LOGIN axdeployuser
EXEC sp_addrolemember 'db_owner', 'axdeployuser'
go

CREATE USER axdbadmin FROM LOGIN axdbadmin
EXEC sp_addrolemember 'db_owner', 'axdbadmin'
go

-- might error - deprecated or retail?
CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser
EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'
EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'
go

CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser
EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'
go

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser
EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'
EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'
go


CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'
go


-- Not everybody needs this, but it doesn't hurt.
UPDATE T1
SET T1.storageproviderid = 0
 , T1.accessinformation = ''
 , T1.modifiedby = 'Admin'
 , T1.modifieddatetime = getdate()
FROM docuvalue T1
WHERE T1.storageproviderid = 1 --Azure storage
go


-- It is very unlikely you need this in a development environment. Microsoft includes it, but I do not recommend it for developers.
ALTER DATABASE CURRENT SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)
GO


-- Begin Refresh Retail FullText Catalogs
-- If you do not use retail components, you do not need this section, but it doesn't hurt.
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; 
go
-- End Refresh Retail FullText Catalogs


-- Microsoft does not tell you to do this, but in a development environment, it is very likely that you want to change the database "recovery model."
-- Otherwise, you need to set up backups and/or clean up the transaction log; otherwise the database can grow at an unmanageable rate, eating up your whole VM drive.
ALTER DATABASE CURRENT SET RECOVERY SIMPLE;
go

-- You might want to shrink the size of the transaction log, just to keep room free on your VM's drive.
-- You can do it in the SSMS GUI, or use the following statement (with a change to reflect your DB name).
DBCC SHRINKFILE(AxDB_YYYYMMDD_Log, 50);
go

Re-provision the target environment & Reset the Financial Reporting database

I do not use Retail components or Financial Reporting, especially not in my development environment; so I have no reason not to follow exactly what is in the Microsoft documentation.

Start to use the new database

To switch to using the new database, you must first stop these three services (the Microsoft documentation is out of date on the service names):

• Management Reporter 2012 Process Service
• Microsoft Dynamics 365 for Operations – Batch Management Service
• World Wide Web Publishing Service

Then rename the databases so your new one is named AxDB (the old one can be named AxDB_ORIG or AxDB_old or whatever; eventually you will want to delete it, to save space). Then you can re-start the three services above, and you are good to go!

You can rename the databases using the SSMS GUI (in Object Explorer) or by using T-SQL commands similar to:

USE master;
go
ALTER DATABASE AxDB MODIFY NAME = AxDB_OLD;
ALTER DATABASE AxDB_YYYYMMDD MODIFY NAME = AxDB;
go

Build/synchronize the new database

You should launch Visual Studio, go to Dynamics 365 > Build Models, and do a full build (with the “Synchronize Database” option) all of your customized and/or extended models.

After that, you are ready to go!