While desktop Dynamics CRM installations make use of fast machines with (nearly) unlimited storage, mobile devices are severely limited in this respect.
You typically get numbers such as:
- RAM Memory: ~1GB RAM (iPhone5/6, high end Android devices approach 3GB).
Low RAM values (~500MB or less) will negatively impact the database speed or, for example, the download speed.*)
- Storage memory: It’s here where the database will be located.**) Today’s mobile devices typically offer 4+ GB of storage memory, which is – by proper planning – enough. We’ll return to the subject later.
- Speed: It is difficult to bring any generally applicable numbers. Instead, here are a few numbers that measure activities done by Resco MCRM:
Database writes on a decent mobile device are 5-10x slower than the same operations done on a rather aging 2GHz W7 desktop. And it can go even worse, for example Xml parsing (used in communication with CRM server) is ~100x slower.***)
- Internet speed: We cannot give any generally applicable statements here. Speed of 3G networks vary between 144kbps to 4+Mbps, 4G networks are even faster.****)
The thing to remember here is that MCRM transfers large data and you’ll appreciate a fast connection more than anything else.
*) In both mentioned cases a smaller data cache will be used.
**) We ignore the SD card intentionally. It is unsupported on iOS. Some Android devices could be tricked to use the SD card as the application data storage. However, can you imagine the database performance ?
***) This difference cannot be explained by the hardware itself. In fact the major bottleneck in this case is Mono Xml parser which is – unlike the native Microsoft parser -programmed in managed C# code.
****) According to PC Magazine, the fastest mobile network in 2014 has average download speed 19.6Mbps.
MCRM Data Storage
MCRM uses two kinds of (encrypted) storage:
- Most of entity records are stored in a database.
- Blobs (attachments, images, Sharepoint documents) are stored in the conventional file system – each blob is stored in its own file.
MCRM uses embedded*) SQLite database – the most widely deployed database engine in the world. SQLite excels for simple queries, but it might be slower for complex queries on large data sets.
MCRM database is basically a subset of the full CRM database located at the remote server. Its content is defined by remote MCRM configurator, aka Woodford.
- Tables replicated on the mobile device (entities).
- Table columns (entity fields).
- Table SyncFilter, which in turn determines the data rows (entity records) downloaded to the device.
- Presentation layer (views, forms), security rules etc. In the following we shall ignore these aspects as they are not related to subject of this article.
For example, the Woodford admin can specify that the mobile user:
- Has all his contacts; the admin can additionally exclude half of the contact fields – those that are rarely used.
- Has only emails not older than 30 days (SyncFilter).
- Does not have
a) technical entities (such as audit),
b) marketing-related entities (campaign, list…)
Decisions such as those listed above define the client database.
*) Embedded means that there is no standalone process controlling the database (such as Sql Server). Instead, the code of the DB engine is a part of the MCRM application.
What’s the limit for the DB size?
First of all, SQLite imposes no practical limit at all.*)
In other words, the hard limit represents the free storage space on your device. OK, but what is it – free (available) storage space?
A recent Which? article compared 8 high-end iOS/Android devices with 16GB storage:
– iPhone 5C had the largest free storage – 12.6GB
– Samsung Galaxy S4 had the worst result – 8.56GB.
In other words 25-50% of the device storage capacity was consumed by the OS and by the manufacturer preinstalled software.
So we have an estimate for the device imposed limits.**)
Before drawing any conclusion, we should discuss yet another technical topic – the database temporary space.
It is the space SQLite needs to store temporary data, such as intermediate results of complex queries or the new data downloaded from the server which was not yet marked as permanent. Without going into the details let us say that when you attempt to maximize the DB size, you should definitely use MCRM v8+ as this version optimized the temporary space consumption.***)
If you expect a practical advice, here it is:
- Analyze the free/available application storage on the devices.
- You can safely assume that roughly half of the free storage can be used for MCRM database.
*) Max. size of an SQLite database is 2 terabytes. (For the DB page size used by MCRM.)
**) Other potential limits: iOS file size limit is 4GB, older versions had the limit 2GB. There is no limit for the total application storage. Android seems to impose no limits at all.
***) Remark for the SQLite experts: For huge write transactions the size of the WAL log (stores uncommitted data) may end up being many times larger than the original database. While this is a temporary storage, it might exceed the available device storage. The largest write transactions performed by MCRM happen when a new table is downloaded during the synchronization. (Only the really huge tables are dangerous – those having ~100K rows.) MCRM version 8 breaks up large table transactions into smaller chunks, controlling thus the growth of the WAL log.
DB size – rough estimates
Ok, we can now estimate available storage. What we don’t know yet is to estimate how much CRM data fits into available space.
“Normal” records do not exceed 1-2K. Consider an installation with 100K of such records, which looks like a quite large amount. (Most customers use smaller counts.)
If you multiply both numbers you get the DB size 100-200MB.
Above estimate will fail if there are tables with “large” columns or with many columns.
Large columns are rare. A common example represents email entity with its description column. (Stores email body.*))
In cases like this consider decreasing the number of records downloaded from the server by specifying proper SyncFilter in Woodford.
Tables with many columns usually indicate bad design.
We had a customer that used account entity with 120+ columns. The database contained 10,000 accounts with the average size 21KB; the account table took 210MB. Because all MCRM forms together used only ~30 account fields, the remaining 90 fields represented basically dead data. It is clear that the customer could reduce the DB size by 100+ MB by a better design. (I.e. by eliminating unused columns.)
*) You can decrease the column size by stripping off html markup. (Woodford configuration, checkbox HTML email.)
Attachments (images etc.) are stored as blobs, i.e. outside of the database in the conventional file store.
Attachments can potentially take up considerable space; hence they need to be considered when discussing MCRM limits.
However, there is a simple tool that solves the worst problems: You can set up max. attachment size to a relatively low number*).
If you do so, then “small” attachments will be downloaded during the synchronization, while the large ones will be skipped and downloaded on demand, i.e. when the user explicitly tries to open the attachment.
Dynamics CRM entities can have (at most) one image field.**) Images use jpg format and are limited to a maximum of 144x144px. Such images will typically take 2-2.5KB, i.e. a bit more than the remaining record data.
*) Can be specified in Woodford, but also on the client side in the MCRM setup dialog.
**) Feature available in CRM 2013+. By default these entities have the image field: Account, Competitor, Contact, Lead, Product, Publisher, Resource, User.
Direct estimates of the DB size
Above considerations might help you to make a rough estimate of the MCRM storage size. Provided, of course, you “know” your data.
If you are an SQL Server expert, you could try this:
- Make csv exports (UTF8 encoded) of all tables and all columns of the Dynamics CRM database that should be used by the MCRM clients.
- Sum up the size of all csv files. Multiply the result by 2 to account for DB indexes and temporary files. You have now a very good estimate of the MCRM storage size.
(Except it ignores the user rights. Typically an MCRM user downloads only a portion of this data – those records he is entitled to see.)
Well, most of us need a different procedure. That’s why I will run you through an application test run in my next post, so we can investigate the database the app creates.