Database snapshots

At work, we do our Dynamics AX development on VMs, set up with a full install of AX (SQL Server, AOS, and AX client). Prior to our R3 upgrade, we were using local VMs, under VMWare Workstation. This worked out quite well. One of my favorite things about this setup was the ease with which I could take VM snapshots, allowing me to run destructive tests, then roll back, fix bugs, and rerun the tests without having to jump through hoops to reset my environment or set up new test orders, or whatever. It was all pretty clean and easy.

But, after we upgraded to R3, we set up new VMs on vSphere. There are a number of advantages to this, but one disadvantage is that I don’t have rights on vSphere to snapshot my own VM. (I’m sure I could ask an admin to snapshot my VM, but the typical testing cycle of snapshotting, rolling back, fixing code, snapshotting again, etc., would probably annoy the admins.) So I’ve been looking for an alternative way to manage testing destructive processes.

I’ve settled on using SQL database snapshots. AX 2012 R3 stores all data in one database, and all code in a separate model database. (Versions prior to R2 mixed code and data in one database.) I’ve worked out a process by which I can pretty quickly take a snapshot, run my tests, delete the snapshot, and start again.

Given a database called DAX12_PROD, here’s a quick run-down on how to execute this process.

(1) Stop the AOS server.

(2) Create a snapshot:

CREATE DATABASE DAX12_PROD_SS1 ON
 ( NAME = DAX12_PROD, FILENAME = 'E:\your_sql_data_folder\DAX12_PROD_SS1.ss'
 ) AS SNAPSHOT OF DAX12_PROD

(3) Start the AOS & run your tests.

(4) Stop the AOS.

(5) Restore from the snapshot.

ALTER DATABASE DAX12_PROD
 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE DAX12_PROD
 FROM DATABASE_SNAPSHOT = 'DAX12_PROD_SS1'

(6) If you’re done, then drop the snapshot.

DROP DATABASE DAX12_PROD_SS1

(6) Start the AOS.

So that doesn’t take too much effort and is pretty quick to run. The snapshot file is a sparse file, created with the same size as the actual database file, but not actually taking that much space on disk. So you don’t need to have a ridiculous amount of free space on your VM (as long as your test isn’t changing a ridiculous amount of data).

And yes, I know that it would be so much better if I could just run unit tests that don’t touch actual data, but it’s nearly impossible to do that for a lot of the stuff I have to do in AX. There are some interesting things you can do, in certain scenarios, like getting creative with setTmp, but that’s too simplistic for a lot of the testing I need to do.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.