Hi Jason,
I’m a member of the team that worked on the white paper about moving code to a production environment by importing the model store.
The team has discussed the issues that you are seeing, and we have some comments and questions.
Question: can you post the precise syntax that you are using for import modelstore? We’re wondering whether you are running with –verbose, which may slow things down.
For comments on each section of your post, see below:
1) the overall import time took almost 1 hour to complete (Xeon Dual Core Processor with 12GB memory, Windows Server 2008 R2 64 bit OS).
--The import time that you are seeing is much longer than what we are experiencing on similar equipment. Our experience is that it shouldn’t take more than 15 minutes on a fair hardware setup, unless you have limited the memory usage of SQL server or have a very slow disk.
2) The database log file grew until 17-19GB in size after the import, while the actual data file size was only approx. 5GB (customer data is only within the 1GB size range).
--This is to be expected, because the import modelstore operation is processed within one transaction that does massive data updates and deletes.
There is one optimization step recommended by microsoft before importing ModelStore, but I am reluctant to try that step at the moment because the changes is less than 5% of the existing target ModelStore (Microsoft recommends this step only if you have more than 50% of changes). Should I try the optimization step in hopes that I would decrease the time to say 50% faster?
--To be clear—optimization is triggered automatically when there is greater than 50% change. Running optimization before importing the modelstore shouldn’t harm anything, and may help. It will reindex the model store and shrink the database file. If you do run optimize-modelstore, we do not think that you will necessarily decrease the time required by 50%, but it may help.
And for the Database Log, what is the normal or recommended practice that should be put in place to manage the size of the database file? I am thinking on my own, truncating the log file would not do good in the long run since it will create fragmentation and worst still, usually in the Production environment, you will be running Full recovery model which will not allow you to truncate the log file (SQL Server 2008 R2), unless if you would want to set it to Simple, truncate it and setting it back to Full - but again this will cause fragmentation? So do you just perform your normal backup rounds and maintain the actual physical log file size to be in the 17-20GB manner at all times? Just wanted to know what is the usual way for those who is implementing the ModelStore export/import for code deployment.
--We agree that truncating the log file is not a good idea in general in a production environment, but right after importing the model store, truncation should not be a problem.
