Microsoft Dynamics AX (Archived)

ModelStore Export Import vs. SQL Backup Restore of MODEL DB

(1) Share

Report

Posted on by Jim Bonnie IBIS

900

I would be curious with peoples experience or comments on Model Code promotion.

I have read the white paper that talks about AX 2012 models, model stores vs. XPOS.

Is there a problem with doing a full SQL backup of your ModelStore from your Build machine and restoring that to Staging and later Production ? I am looking at doing the modelstore export and import, but see SQL backups and restore as a viable option. I have done SQL backup of Production back to DEV and TEST.

I wonder if modelstore export/import was the only option an AX2012 RTM when the modelstore and transactional data was in the same DB.

Thanks for sharing your experience with this.

Jim

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (2)

Verified answer

Joris dG 17,775 on at

Like (1)

Report

Yes, in RTM modelstore export and import was the only way to move the full modelstore (at least out of the box). The SQL backups are a viable option, as outlined in the whitepaper as well.

The major thing to keep in mind is that the SQL backup/restore option is ruthless. If you have any kind of object ID conflicts, you won't know until it's too late. The modelstore export/import at least checks for conflicts and allows you to abort or at least know that there are issues. That is the main BIG difference.

So, SQL backup/restore should be faster, but it has no fail safes in case something is wrong. If you consistently move using SQL and never do any manual code moves using models or XPOs, you're good.

Was this reply helpful? Yes No
Verified answer

Tommy Skaue Moderator on at

Like (0)

Report

+1 @ Joris

SQL Server is efficient when backing up and restoring databases compared to AXUtils export and import, but you'll miss out on some built-in AX specific protection.

Here is one more reason to use AXUtil; you can import the modestore to a temporary schema while the system is online, then take it offline and apply the schema in matter of seconds:

yetanotherdynamicsaxblog.blogspot.com/.../deploy-modelstore-with-less-downtime.html

And here is another tip when using AXUtil import; turn off database logging (unless really needed). Every single statement updating the database will be logged, so your database log will grow tremendously if the recovery mode is set to Full.

Was this reply helpful? Yes No
Joanna Demetriou 355 on at

Like (0)

Report

Hi there Tommy

We are facing a situation where deploying customizations using modelstore export/import mechanism causes the log file to explode.

As per your previous response, you recommend turning off database logging.

Won't that be causing any issues??

As an alternative, is there a guaranteed way to estimate the required disk space based on the size of the modelStore file to prevent the import from running into errors?

Thanking you in advance

Joanna

Was this reply helpful? Yes No
Tommy Skaue Moderator on at

Like (1)

Report

Hi Joanna

I should blog about this, if I just had the spare time. :-)

So having the Recovery Mode set to "Full" is necessary in order to restore the database to a specific "point-in-time", ie at 5:35 pm two days ago. This might be interesting on the AX ERP transaction database, but not as much on the modelstore database. It might be of interest on a modelstore database in a pure development environment where there is no other good way to "restore" modifications done by the developers, but in any other environments (like LIVE, TEST, UAT or whatever), I do not see any good reason to have "Full" recovery mode on for the modelstore database.

As for the log growth, I've seen the log explode on modelstore databases *even* if it is set to "Simple" mode. Just for clarity, "Simple" means you can only restore to whatever last full database backup you have. I have not been able to narrow down why the log explodes (yet), but in my experience the best way to import a modelstore using axutil is to first import it to a temporary schema, then apply the modelstore and finally drop the temporary schema. I believe this will not make the log explode, though it will grow to a certain size. Some operations when importing lives inside a transaction, and until it is commited, it wil use the log to keep track of the operations in case it needs to roll them back.

The steps can be found here:
http://yetanotherdynamicsaxblog.blogspot.com/2013/02/deploy-modelstore-with-less-downtime.html

Now, the size of the modelstore will be the size of the modelstore file, plus the indexes within the modelstore database, plus the possible extra schema, and finally the space needed for the log. On AX2012 R2 CU7 I find it perfectly normal to have a modelstore database around 8-14GB.

I hope that helps a bit.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

as Tommy suggested, turning off Log is okay during model import. to be on a safer side, just take a full back-up of model DB before doing model import.

Was this reply helpful? Yes No
Joanna Demetriou 355 on at

Like (0)

Report

Hi Tommy

Thanks a lot for the down-to-the-point reply!

That certainly helps a lot.

There's only one unknown..

After failing to import the modelStore due to log file extreme growth & lack of available disk space, we decided to shrink the log file to 1MB, drop the temporary schema and retry the import hoping that that would resolve the problem.

However, the second attempt also caused the log file to grow to ~23GB and ran out of disk space.

We decided to revert and not attempt another modelStore import until we analyzed and understood the situation as that is affecting the customer's LIVE system.

The modelStore file that we try to import is ~3.5GB.

- Is it at all normal that the log grows to 23GB (even after being shrunk, and temp schema dropped)??

- Is increasing the disk space on the machine our only solution here?

Thanks a lot for your time!

Joanna

Was this reply helpful? Yes No
Martin Dráb 237,963 Most Valuable Professional on at

Like (0)

Report

All transactions get recorded to the transaction log regardless of recovery model; the difference is only in when completed transactions get removed.

I must admit I've never run into such a problem with model store import, nevertheless I always have more then 23 GB available. Can't you simply add mode disk space? It sounds like an issue anyway.

I wouldn't think about delivering code to production by anything except model store import.

Was this reply helpful? Yes No
Tommy Skaue Moderator on at

Like (0)

Report

Martin is right. Regarding the size of the log, you will have to keep it with enough space so it can log all the transactions within the scope of the bulk insert operation. When it is importing to the modelstore, it will log all the in operations , and 1MB is not enough. As a minimum set it to ie 4GB and if it needs any more space, let it grow accordingly.

Like I wrote before I once saw the log grow when I imported a modelstore, but that was directly into the dbo-schema. Importing it to a temporary schema worked better. I prefer doing it that way, because then I can import even while the system is running. And when it is done, I can prepare for downtime and apply the modelstore to dbo-schema.

There must be some contention issues if the log grows immensely like you describe. I had look at one of the R2 environments here, and the modelstore data was 13GB and the log was 9GB. However the data had over 50% free space and the lo 99%. I could have reclaimed the space if I wanted, but I would probably need it back the next time I need to update with a new modelstore.

The last thing you want to struggle with when you deploy a new modelstore is lack of disk space. That just feels like a waste of time. :-)

Was this reply helpful? Yes No
Maciej Obojski 610 on at

Like (0)

Report

Hi,

Maybe a red herring, but could you double-check that your DB file settings for autogrowth are set to fixed size (e.g. 100 MB), rather than percentages?

I've seen it in the past, that someone has gotten a little bit too click-happy and set the file to autogrow by 100% - with that setup you can hit high GB usage quite quickly.

Thanks,

Maciej

Was this reply helpful? Yes No
Tim Shelton on at

Like (0)

Report

I was thinking about a way to quickly recover from a bad model store move if problems were encountered in production after the update. Normally we would export the current model store first, then import the updated model store. If an issue was encountered we would reimport the saved model store. But I was thinking about how to shorten this cycle and not have to perform a CIL.

Consider the following:

1. Copy the production model store database to another database as an online backup.

2. Perform the normal model store move process from our pre-prod instance to the production instance.

3. If an issue was found in production, could we take the production modelstore db offline in SQL, then copy the online backup model store database back to the production modelstore db and bring it back online.

3. Delete the dll's from the AOS servers.

4. Restart AX

Would this work? Any risks?

Thanks,

Tim

Was this reply helpful? Yes No