Is SQL Express supported for the Hyland IDP's Operational Database

[Jim_Bullen1]

Hello,

Just wondering if SQL Express would be a platform supported by Hyland to run the Operational Database for the Hyland IdP?

I also noticed that there have been additional tables added to the Operational Database in new versions of the Hyland IdP.  Originally my understanding was that it is a "runtime database" and that no data is stored in the tables permanently.  Is this still accurate or does it now retain data in the new tables?

Thanks!

[Chris_O_Connor · Accepted solution]

Hey @Jim Bullen ,

The team was able to test and verify locally that SQL Express works for the Hyland IdP Operational database.

A couple other things to note, as this testing was focused on quickly getting a working environment. Please follow security rules as per the policies for your environment. This also was not tested under a heavy load like we would for the full version of SQL or Oracle. So while we don't forsee any issues, use at your own risk. 

A few things they did during setup that might be slightly different:

• Enable SQL Server and Windows Authentication mode
• Enable TCP
• Add a SQL user login and provide privileges (public, securityadmin)
• Provide the SQL user access to the newly created DB
• Create a new schema (they used HSI) and give the SQL user account db_owner and db-securityadmin

Outside of that, it was just creating the Hyland IdP database through the Admin UI or CLI as you would normally with SQL.

May I ask why you would use SQL Express? For instance an added layer of security or separation from the OnBase database?

If you have any trouble or questions, please let me know. 

[Jim_Bullen1]

Hi @Chris OConnor 

The main reason we want to utilize SQL Express is we want to deploy the IDP at the web layer but do not want to have to setup a direct connection with the OnBase database from that layer.  We also did not want to pay for another SQL instance if we could get away with using SQL Express deployed at the web layer for the operational database.

I appreciate the information and will let you know if I have any other questions as I get into the setup. Thanks again!

[Chris_O_Connor]

Thanks @Jim Bullen,

It always benefits the Team to understand the use case. We figured that was the most likely scenario. It's become more popular recently.

Have a great weekend! 

[Jim_Bullen1]

@Chris OConnor 

I have a question that came up regarding the Operational Database in a high availability setup: 

If we were to use SQL Express it does not support syncing between databases.  In this setup we would have a SQL Express Operational Database per Hyland IdP server.  Would we need to sync data between the two instances of SQL Express or is that not necessary? 

I know the database stores a unique user identifier now but would that just get created the first time they authenticate with each Hyland IdP?  Is there any other functionality that would require the data to be in sync in both databases?

Thanks!

[AdamShaneHyland]

Hi @Jim Bullen ,

From a Hyland IDP perspective, all instances need to connect to the same database.  I would be concerned with attempting data replication between two databases as data will be out of sync and will cause problems in a load balanced / highly available scenario.  Plus it would have to be bi-direction replication as the Hyland IDP is stateless and any request to the Hyland IDP can go to any instance.

I appreciate what you are trying to do, but as soon as you bring in advanced features of SQL such as replication, I would have strong hesitation with this solution.

Take care.