[ FAQ ] Epicor ICECommon Database

Kinetic ERP
, , ,
1

What does the ICECommon database store? Why was it introduced?

The ICECommon Database stores information and system data that Kinetic uses as read-only throughout its applications. This database improves installation and upgrade performance, making more storage available across the platform. Kinetic creates one ICECommon database for each SQL Server instance and major version. Each Kinetic database automatically connects to the ICECommon database matching its version number.

Can I remove or rename the ICECommon database?

No, the ICECommon database must be available for Kinetic to run properly. Do not modify or delete it.

When was the ICECommon database introduced? What tables does it contain?

Release Table(s) Added Note
2023.2 Ice.FieldHelp Previously stored in XML files on the smart client. Now available to the browser clients.
2024.1 Ice.ICECommonVer, Ice.ReportStore Previously deployed to each SSRS database during deployments. Now only RDL files used will be uploaded to SSRS.

Kinetic Development will add more read-only and shared data to this key database for each version, ensuring common data is consistent across all Kinetic installations.

Where is the ICECommon Database documented?

  • The Kinetic Architecture Guide contains a chapter on the ICECommon Database since 2023.2.
  • The Kinetic Help contains articles since 2024.1.

What’s the name of the ICECommon database for my release?

The ICECommon database name is suffixed by the ICE version, e.g., ICECommon_4.2.400. The equivalence between Kinetic and Ice versions is covered in KB0119789, Comparing Versions of Kinetic/ERP, UX Platform, Ice Framework, and .NET.

How to create or recreate it?

If you run a Cloud environment:

  • It installs with Kinetic and automatically upgrades with each Kinetic release.
  • The installation or upgrade process creates/updates this database in the background.

If you run an on-premise environment:

  • You must manually upgrade this database using the Epicor Administration Console (EAC) or the Database Migration tool (command-line tool).

  • It will also be created if the ICECommon database is missing when a new or demo database is created via the Epicor Administration Console (EAC).

    You can use this feature if you have no ICECommon database (or it was somehow deleted):

    • Open the EAC to Database Server Management > [server name].
    • Select Add New Database from the Actions menu.
    • Enter a temporary name (e.g., “TempEmptyDb”).
    • Complete the creation process.
    • Check that the ICECommon... database was created.
    • Delete the temporary database.

What happens during upgrades?

When upgrading, the common database will also be upgraded, but the old one will remain. When customers install a test database at that new level or upgrade their pilot environment, the database will be created and updated. But when they upgrade their live environment, they’ll just use the one there. This means that whatever data is in the common database is no longer needed as part of the DB upgrade process, saving time in this critical step of system downtime.

15 Likes
2

Thank you for the write up, I wondered what this was and how it impacted upgrades and how it impacted upgrading a test environment to a different release than the live environment when both were on the same SQL server.

2 Likes
3

For on-prem sites, do not forget to add this database to your SQL backup and maintenance plans.

7 Likes
4

Great point!

1 Like
5

There seems to be a bug… (or a feature) here I have a DB
2023.1 → 2024.1 that Created ICECommon_4_3_100

Then a few weeks later I reset the Db back to 2023.1 and re-did the uplift (same DB name and everything just restore from backup re-uplift)

2023.1 → 2024.1 that created ICECommon_4_3_1004_3_100 ← that looks a hell of a lot like they appended the version to the end of the version?

image

Halp! @Rich, @Olga

the problem is I have 4-5 DB’s in the same SQL box

the problem is I have 4-5 DB’s in the same SQL box am I gonna get
ICECommon_4_3_1004_3_1004_3_1004_3_1004_3_1004_3_1004_3_1004_3_100

3 Likes
6

We need an idea to simply make a new schema in the Epicor Database and get rid of IceCommon :slight_smile:

4 Likes
7

Here’s what I need to understand. I have the following Dbs (yes I know… :gun:me now and put me out of my misery… you try upgrading all these :man_facepalming:)

EpicorTest
EpicorPilot
EpicorTrain
EpicorA
EpicorB
EpicorC
EpicorD
EpicorE
EpicorF

They are all share one or two SQL Servers… As I understand it the ICE Common DB is per version and it stores the ReportStore (Reports and Custom Report?) does this mean that we are assuming that all of those DB’s above are sharing once ICE Common Db (since they are all in the same version) and if so… Can I no longer have different versions of the same report in different Dbs?

IE: Development B has an Updated copy of SSRS report but I don’t yet want to deploy it to Development C or Pilot

4 Likes
8

OK From the Help
Reports save in the following locations:

  • System reports install in the ICECommon database within the Ice.ReportStore view. Kinetic then pulls these system reports into the Ice.ReportStore database table, so you access these system report files from this table.
  • Custom SSRS Reports you create save to your Kinetic database in the Ice.CustomReportStore table. Do this in Report Style Maintenance by selecting the Upload SSRS Report action to upload a new report style.
  • BAQ reports you create save to your Kinetic database in the Ice.CustomReportStore table. Like custom SSRS reports, custom BAQ reports save to this table after you upload them to the server using the BAQ Report Designer.
  • The version history for the custom and BAQ reports you create save to your Kinetic database in the Ice.CustomReportStoryHistory table. This table starts recording version information after you upload the second and later versions of each custom SSRS and BAQ report.

:white_check_mark: It appears that Ice.Common just contains “System Reports” not Custom Reports.

3 Likes
9

It may be a bug as you stated because the Architecture Guide has the following:

Kinetic creates one ICECommon database for each SQL Server instance and major version. Your environments automatically connect to the ICECommon database.

It should share a single one between major versions.

3 Likes
10

Ola hallengran "User databases*

5 Likes
11

The ICE_Common DB contains Static information so a single archive should suffice - you should redo the archived copy after each Update. We are gradually moving our “Seed” and static data from the User DB and the File system to the ICE_Common DB.

Moving static information to ICE_Common reduces the size of the Kinetic DB, reduces the time required to Upgrade, and helps with permissions, performance, and environment management when compared with disk based software elements.

The ICE_Common DB is configured as Read-Only and the DB is not referenced directly from the Kinetic AppServer so additional SQL connections are not needed. Instead, the ICE tables in the Kinetic DB reference the ICE_Common data via SQL Table Pointers.

As already noted, the Base RDL definitions are in the ICE_Common but the Custom RDLs are stored in the Kinetic DB.

@josecgomez - thanks for the information on the Upgrade “oddity”. We are investigating.

10 Likes
12

As long as you can access said schema from tools like baq unlike ecf…

2 Likes
13

Out of curiosity, how many records is this table supposed to have? I’m missing Field Help descriptions for nearly all fields (but still available on the field help “technical details” tab) in a 2023.2 installation. I just did a query and there were under 200 rows in the Ice.FieldHelp table.

3 Likes
14

Is there a location in the Kinetic DB that points to the ICECommon DB? Epicor is advising us to keep the ICECommon outside of our SQL availability group and instead keep a copy of both servers so I’m wondering how Kinetic would know to connect in a failover?

4 Likes
15

Each Table in the Kinetic DB that references an ICE Common Table is just a SQL View that references the ICECommon DB name, and the Table Name in the ICECommon DB. The view is expecting the ICECommon DB to exist in the same SQL Instance as the Kinetic DB - if you have a failover, the ICECommon DB is expected to exist in the Secondary SQL instance.

If you are not going to have ICECommon in the Availability Group, you need to copy the ICECommon DB to the Secondary whenever it changes - which is rare but can occur when a Kinetic Update is applied.

Due to the manual effort and risk of not having the DBs in the AG synchronized, I think you should leave the ICECommon in the AG. It rarely changes so there is little traffic cost of having it in the AG. As it rarely changes, you do not need to do any transactional or even daily backups - the backup schedule is really the only special consideration I would give the ICECommon.

What Epicor team is recommending that the ICECommon should not be part of the AG? I will get in touch as I would like to understand their thoughts on that.

10 Likes
16

Thank you @Rich for taking a look at this for us. All of the details are logged in case CS0004570008.

1 Like
17

Thanks Andrew - the case is enlightening and I have escalated with my team to find a solution that does not include you having to manage DB copies between Primary and Secondary instances.

For those following along at home, the EpicCare issue @amurdock raised was for a failure that occurred during a Kinetic Update. One of the first steps Epicor performs during an Update is to change the ICE Common DB from Read-Only to Read-Write so elements of ICE Common can be updated during the Kinetic Update. Surprisingly (at least to me), the ALTER Database command fails if the DB being adjusted is part of a High Availability configuration.

We will have another look at this as there are options other than needing to manually manage databases in an HA instances. We are also going to raise this with Microsoft to get their ruling on best approach.

As to the question (from the peanut gallery) of why we did not catch this while testing for our Cloud HA enabled environment? Our Cloud QA environment is configured with HA and we do test the software using that environment. That said, our Cloud Update and Upgrade processing does not always follow the steps taken by an on-prem environment as we have to perform the processing at scale. Part of that is to do things in advance (when possible) and Updating the ICE Common is done prior to the actual Upgrade or Update and it is done using processing that is specific to the Epicor Cloud environment. As a result, we missed the implications for an on-prem HA configuration.

13 Likes
18

Wow Rich, thank you for the detail in that response. As always, it’s great to hear from you and I’m thankful you’re part of this forum.

4 Likes
19

Thank you @Rich for taking a look into our case and working with your team to develop a smoother solution for on-prem HA configurations.
I really appreciate your time on this and all of your contributions to this forum.

2 Likes
20

I was going to write a long explination, but as I was typing I started to question myself, going to have setup and play with it a bit. One Question for @Rich is if the ice.common is read only surely it can’t be on the secondary and subsequent dB servers if it is in a AG?

1 Like