AX 2012 R2 - remove old modelstore

Question Status

Verified
Christian Moen asked a question on 5 Apr 2013 1:39 AM

Are there any reasons for not deleting the RTM/Feature Pack modelstore from the transaction-database on a R2 installation after a succesfull upgrade?

Anyone tried this yet?

With 40+ environments in this project, freeing up 5GB per database would be make a significant impact on the datastore usage here....

Reply
Verified Answer
Kevin Kidder responded on 5 Apr 2013 10:21 AM

If you are certain that your R2 upgrade was successfully completed, you can remove the objects from your transactional database. You will want to make sure that your AOS configuration does not point to this transactional database as the Baseline database (used for your code compare projects as part of the upgrade). You will also need to delete views that relate to the model element data prior to dropping any tables that relate to the model element data.

Of course you will want to make sure you do this in a test environment first with a full backup in case it is needed.

Reply
Christian Moen responded on 5 Apr 2013 11:32 AM

Ok, thanks for your input!

Reply
Tommy Skaue responded on 6 Apr 2013 11:56 AM

Hi Christian

I have a script ready for this. I'll post about it tomorrow.

@Kevin; thanks for confirming.

Reply
Tommy Skaue responded on 7 Apr 2013 3:38 AM

Hi again

Please let me know if this script is flawed or works:

yetanotherdynamicsaxblog.blogspot.no/.../remove-old-modelstore-from-upgraded.html

Cheers! :-)

Reply
Christian Moen responded on 7 Apr 2013 12:56 PM

Great, thanks! I'll check it out tomorrow :)

Reply
Christian Moen responded on 7 Apr 2013 12:56 PM

Great, thanks!

I'll check it out tomorrow :)

Reply
busch responded on 13 May 2013 8:58 PM

Do you also recommend deleting the "* Upgrade" models as soon as upgrade is complete?  I still have the upgrade models for FP installed and didn't even consider deleting them from Production until I saw that was one of the first steps on the in-place upgrade to R2.  

I'd guess the deleting of "*Upgrade" models would be done in conjunction with disabling the "keep update objects *" configuration key?  What order?

Reply
Christian Moen responded on 14 May 2013 5:52 AM

@tommy skaue: forgot to give you feedback on this, but your script worked perfectly :)

Reply
Kevin Kidder responded on 14 May 2013 6:03 AM

The upgrade models should only be removed after you are confident that everything upgraded successfully, which is kind of a subjective measurement. Microsoft's guidelines would be waiting at least 3-6 months before unmarking the configuration key and removing the upgrade models. The order in which you do those two steps doesn't matter much, the config key being disabled for those special Keep Update Objects removes the DEL_ columns from your database, while removing the models also gets rid of the AOT objects.

Reply
Tommy Skaue responded on 14 May 2013 7:18 AM

@Christian: Thank you for confirming. :-)

Reply
Armando Tinoco responded on 18 Jul 2013 8:34 AM

Dear Kevin, We have AX2012 R2. I already unmarked the configuration key to remove upgrade models and I synchronized the database.

However I'm still seeing DEL_ Columns in the Database.

Could you tell me if something is missing?

Reply
Brandon Wiese responded on 1 Aug 2013 8:09 PM

Has anyone else tried and been successful with removing their old model store from the business data database in AX 2012 R2?

I looked at Tommy's script, which is impressive, but it got me to wondering.. couldn't I just start with the known set of 46 tables core to the model store, and use SQL Server's own schema (i.e. sys.sql_dependencies, sys.foreign_keys, etc.) and cascade search all dependent objects to arrive at a valid and (hopefully) exhaustive list of everything else that comes along with them in a model store?

I threw something together and it generates 306 SQL statements that loosely match the output from Tommy's script.  I executed the batch against a test database, and it succeeded (meaning at the very least the order was correct and nothing was missed).  A visual inspection then discovers that some XI and XU stored procedures are self-contained without any actual reference to the model store tables or any objects that reference them, so they remain (but are, by similar reasoning, perfectly harmless and take up no real space in the database).  I want to do some more testing before I post any SQL.

Interested in hearing the experiences of others on this front.

P.S.  I removed my Upgrade model months ago with no adverse affects.

Reply
Christian Moen responded on 2 Aug 2013 12:45 AM

I did execute Tommy's script some weeks ago and haven't seen any issues with it yet.

Reply
Brandon Wiese responded on 4 Aug 2013 6:32 PM

I removed the old model store from my production environment this weekend, followed by a full compile, CIL, and AOS restart.  I used my own script, which I will post later, but it generated almost the identical DROP statements just in a slightly different order.

Reply
Verified Answer
Kevin Kidder responded on 5 Apr 2013 10:21 AM

If you are certain that your R2 upgrade was successfully completed, you can remove the objects from your transactional database. You will want to make sure that your AOS configuration does not point to this transactional database as the Baseline database (used for your code compare projects as part of the upgrade). You will also need to delete views that relate to the model element data prior to dropping any tables that relate to the model element data.

Of course you will want to make sure you do this in a test environment first with a full backup in case it is needed.

Reply