ePOS | Retail Implementation using SQL Express
Hi Guys, in this blog I want to share my experience regarding use of SQL express for ePOS (Enterprise POS) – Online Database and Offline Database. The experience is from my recent Dynamics AX Retail project having 300 stores with nearly 620 terminals all using SQL express for Online and Offline Database.
Few of the issues which we faced were –
- DB size growing
- 100% CPU usage
- Slowness in Retail operations
SQL Express is “supported” by Microsoft but they have not (to my knowledge) directly recommended using SQL Express in the store. Most of the Retailers (IT Team) those are having 250+ stores wants to go for SQL express because it provides a lot of value for a very low price (free) implementation. But this comes with the cost of high maintenance of POS Database.
The use of SQL Express requires careful planning and consideration for ongoing maintenance and an understanding of how the database will grow.
The reason why database maintenance is so important is that, over time, the database will become fragmented and slow. With SQL Express, there is no database maintenance tooling to assist in the completion of tasks such as re-indexing, checkdb, backing up and shrinking the database. Alternatively scripts and Stored procedure helped us in these regards. If we had SQL Standard edition, we would be able to setup a maintenance plan in a few clicks.
These maintenance tasks seem like trivial things but when you have a retailer with hundreds of stores (or like in our case approx. 600 terminals DB), this becomes overwhelming. This problem can be solved by some careful scripting running on task scheduler.
SQL Express is limited to 10GB in size. When you first implement the system this is typically not an issue. However, you will not have to wait very long for this to become a big firefight because the database can grow at a tremendous pace. Make sure that you understand how your data replication is initially setup and how the data will change over time. This understanding can really save you a lot of hassle.
Some consideration should be given to the way that replication jobs are designed. Things have significantly changed, between AX2009 and AX2012 but the initial setups provided by Microsoft have not. In AX2009, things were relatively straightforward from a table standpoint. You could pretty easily determine which tables were driving which area. When AX2012 came along, the database normalization that occurred in AX2012 added a significant number of tables. If you compare the behavior of an AX2009 POS Database to an AX2012 POS database, the amount of records and database required to complete the same set of tasks have grown over 30%. Although Microsoft keeps raising the size limit for the Express database, it should not be your strategy to hope for this change each year.
The primary strategy that is used by the Microsoft is to send every record in a table to every data group. The stores that are clubbed in same data group will receive same set of records in POS Database. This means that every store database will have every customer, every item, etc. There are six main areas to consider when setting up replication:
The standard replication setup provided by Microsoft will send every assortment for every store to every single data group. This means that if you have 100 stores and a 1,000 items, you will get 100,000 records that define the assortment sent to every store. In addition, the replication will still send every single item and all of the related tables, whether the item is in the store’s assortment or not. This is a good thing, allowing for cross store returns, but you should understand this when sizing the store database. There are no alternate ways you can reduce the records for item master.
2. ADDRESS BOOK
The DirPartyTable and its related entities, house all of the master entities and their addresses in one giant address book. This entire table and the related tables is sent to every store. If you implement HR for your company and AP, this means that the ENTIRE employee address, vendor address, contacts (prospects), customer and every organizational unit will be sent to every store.
So, you see, as an added bonus, we get to maintain all of this extra, unneeded data in your store database. This extra data is consuming space that counts towards your 10GB limit. With some work on location tables setup, the information in these tables can be limited so that only relevant records are sent. But if you are not concern with the Customer data on POS and all your transactions are cash and carry for walkin customer, then you can avoid syncing few other tables that will count toward reducing your Database size.
3. ITEM IMAGES
With AX2012, Item images were introduced and they really look great at the POS. But, if you have a large number of items, this feature is going to cost you a large amount of space because all of these images are stored in the database. You should avoid images (if possible) when planning for SQL express.
4. ITEM ATTRIBUTES
Another good feature is item attributes. About the only place that this feature is used other than on the website is on the Product Details screen on the POS application. There is a little, undersized panel that appears on that screen that allows you to scroll through the various attributes that are setup for the item. There is a pretty high cost to the attribute data which is very strange because you would think it would be a pretty simple table structure. However, there are about 11 tables included in setting up item attributes. To add insult to injury, the tracking tables required by offline mode for this actually takes up more space than the actual data. There is more about offline mode next.
5. CUSTOM TABLES
Any new feature in Retail POS may bring new tables in POS Database. These tables may range from Master, Setup or transaction tables. Here table designing plays a vital role to ensure you are not capturing or sending unnecessary data into the POS database, that might influence the database size. Because these custom tables will have another setup of tracking tables that will provide the same impact as standard tables.
6. OFFLINE MODE
But wait, there’s more. Another feature that Microsoft added with AX2012 is the “offline mode”. Offline Mode functionality allows each POS to “subscribe” to the main store database. The main store database then “publishes” to each of the POS terminals. Microsoft has implemented the Sync Services for ADO.NET model that provides a “tracking” table for every table (used to figure out what changes have occurred) that is used for Offline sync. This requires to be setup under Offline profile in AX client. If you use the standard configuration, the offline profile adds 215 additional tables to the database. These tables almost double the data requirements on an ongoing basis. This means that if you have data storage figured out to house 10,000 items, you will need about double the amount of space to support the offline mode.
You may be able to cut back on the number of tables required depending on your comfort with limited functionality while in offline mode but the fact is that it will require a significantly higher amount of storage if you choose to implement offline mode.
Another thing to understand is that the tracking tables never get truncated. Even though the Sync Services model supports the maintenance of these tables, the tracking data will increase forever. You will need to write your own truncation routines.
Now that you have some background, let’s see how you should model the POS database and how that database will be setup in order to use it.
- Determine Table Record Sizes
Determine the size, each table is using in the POS database. You can use the standard report in SQL to get these details.This will result in the following data report. If you look closely, you will notice that some of the tables, end with the string “tracking”. These are tables that are added when the offline mode is enabled.
- Determine Base Data & Growing Data
From the above table data, each table can be characterized as either “setup”, “transaction” or “master”.
a. Records related to setups will not change much but there is a large initial data size to get it up and running.
b. Transaction records relate to the actual POS transactions that get created for sales, logins, returns, tender declarations, etc. The size of this data set is directly related to how much history the client wishes to retain prior to truncating it. The amount of history is usually double the return policy. So for a 30-day return policy, keep 60 days of history and truncate the rest.
NOTE: Microsoft has provided some functionality to truncate the transaction tables but it is an incomplete implementation. The RetailTransactiontable is not included in the truncation and will grow indefinitely unless you take steps to truncate it yourself. The assumption that can be used for transactions is that they will grow to a certain point and then stop growing, because you will be truncating them using some scripts in a routine.
c. Master tables are, that store items, prices, and customers related information. You can actually split this out further so that you can better scale the database based upon what is driving the growth. You will need to dig down to the masters that can be set to particular store and with the help of setup you can manage it.For Master table you can analyze the master tables those are really required at POS for your operation. Eliminate the sync of such data which is useless to store in POS database.
d. When you are dealing with Custom tables, you need to design the tables in such a way that it consumes lesser size in the POS database. For instance, in my case, we had a parallel table for transactional tables, as we required to store few more information during sales transactions. This lead to increase in database size at double the pace.
- Gather Input from retailer
Once the base data and growth data are established, additional information is required to “fit” the model to a specific retailer. You will need to collect information to determine the initial number and annual growth rate of records for each data area for input into the model.
- Growth over a Period of Years
After all of these inputs are in place, you will be able to model the initial size of the database and see how it will grow over time. You can see where the database will exceed the SQL Express database size limit and determine whether this is an acceptable horizon or not. If it is not an acceptable horizon, consider how you can better tune the replication model to limit the data being sent to the store database and adjust your model accordingly. Below is a snapshot of a growth model I have done that shows the database exceeding the capacity of SQL Express somewhere in year 3. In this case, I have offline mode enabled.
Using the same exact model inputs, if you turn off the requirement for an offline database, you get the following results.
As you can see from the example above, additional work is required to tune the replication properly to ensure long term viability of SQL Express past year three. You will have to understand how to manage this. If you don’t, your only recourse may be to make a very large investment in database licensing in your near future.
SQL Express is very much a viable choice for use in a Retail store environment. Retailers and implementors seeking to implement SQL Express should understand how the database works and how the retailer plans to implement their system.SQL Express requires additional work related to database maintenance and also tuning of the replication model.
You may need to tweak the Retail channel schema XML to send the Store wise data wherever possible, which can help sizing the POS database to a major extent. It is advisable to perform this exercise during the early stages in the project before you start with development, otherwise when the custom tables are introduced, the work becomes too tedious and harder to manage once your designs are finalized. Understanding how much work is required and whether it will be an issue for your implementation is key for a successful Dynamics AX Retail POS project.
Last but not the least, enabling the Offline Database makes it more challenging for modeling the Database size.
Feel free to reach out for any clarifications. If you like my blog posts then comment and subscribe to the blogs.