Estimating the data size in your Business Central online tenant

Hi,

Kudos to the Microsoft Dynamics 365 BC Cloud Migration team.

A new page just have been added to the documentation on how to estimate the data size for your on-prem database after being migrated to a Business Central online tenant.

Estimating the data size in your Business Central online tenant – Business Central | Microsoft Learn

This is just a “simple” stored procedure that uses an existing SQL stored procedure called sp_estimate_data_compression_savings that’s been there for a while, I’m sure sure about that.

More info here: sp_estimate_data_compression_savings (Transact-SQL) – SQL Server | Microsoft Learn

I was giving a quick test on this procedure and when I executed the EXEC estimate_page_compressed_table_sizes query, I was getting the following error:

I was able to quickly figure out that this is related to some fields name that includes an ampersand which are not properly being handled by the sp_estimate_data_compression_savings stored procedure.

It seems to be a bug and has not been fixed by Microsoft.

This is not a problem for a vanilla BC database but it is an issue when using LS Central and might happen if you’re using other ISVs apps or custom fields.

I was able to run the query by excluding some LS Central tables. To do that, a possible workaround is for you to apply the following changes on the query that creates the estimate_page_compressed_table_sizes stored procedure:

Change from:

DECLARE tables_cur cursor for
SELECT name
FROM sys.tables
-- adjust this part if you want to restrict the tables in the calculation
-- WHERE table_name in ('table name 1', 'table name 2', 'table name 3')
;

To:

DECLARE tables_cur cursor for
SELECT name
FROM sys.tables
WHERE name NOT LIKE '%$LSC POS Mix & Match Entry$%'
AND name NOT LIKE '%$LSC Activity Email Log$%'
AND name NOT LIKE '%$LSC Activity Status Log$%'
;

If you are using extensions from other ISVs, you can exclude all the tables that has ampersands in the field name.

EDITED: 2023-11-20

According to Microsoft (see: https://www.yammer.com/dynamicsnavdev/threads/2531898564968448), the issue with the stored procedure is not related to the fields itself but with the indexes. So I have made a few changes on the recommended script to exclude the tables based not only on the fields that have ampersands but also based on if those fields are part of an active index.

So you would need to change the stored procedure by replacing with:

DECLARE tables_cur cursor for
SELECT name
FROM sys.tables
WHERE name NOT IN
(
  SELECT TableName = t.name
  FROM sys.indexes ind
  INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
  INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
  INNER JOIN sys.tables t ON ind.object_id = t.object_id
  WHERE col.name LIKE '%&%'
  GROUP BY t.name
)
;

To check which tables are being excluded you can run the following SQL query:

SELECT TableName = t.name
FROM sys.indexes ind
INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN sys.tables t ON ind.object_id = t.object_id
WHERE col.name LIKE '%&%'
GROUP BY t.name

I tested this using a LS Central Demo Data database as example and this was the outcome:

Data size is around 42 Mb and when compressed will be around 29 Mb. So that’s nearly 30% of space saving.

Please give it a try and share your numbers!

This information will be available soon in the LS Central Implementation Guide, available online for LS Retail partners: LS Central Implementation Guide – Data Migration

Leave a Reply Cancel reply