Database Size and Smart Connector EWS Server Clean Up

[bbradley_jaxcon]

I have had our Smart Connector Processor running for a few months now and everything seems to be running fine. But I am slightly concerned about the size of the database and some of the data revealed in the EWS Server.

We installed Smart Connector using the LocalDB option. SC version : 2.4.23 with SW ES Version : 2.0.3.4002 

Are there some maintenance task we should be doing?

I personally did not write the code for our processor, but I’ll explain what should do. The processor makes a http web request to a server and retrieves a list of points with values. It then creates these points and stores them in the Smart Connector EWS Server,  and then after subsequent polls the value is updated in the Smart Connector EWS Server. In StruxureWare we made a EcoStruxure Web Service to poll the Smart Connector ESW Server.

[JeffBowman · Accepted solution]

Hi Brian,

Yes there is!

There are actually two ways to do this:

• In the processor itself (preferred)
  • The developer can use the SmartConnector.Utilities library EwsItemManager, there is a function called 'DeleteSubscriptionNotifications' that will cleanup the Subscriptions and Notifications. This function should be periodically executed, usually during an Update processor run.
• If there is no way get the above function into the processor itself. There was a Standalone processor developed called 'EwsServerCleanupProcessor' that is part of the SmartConnector.Utilities Library. You could add and configure this processor like any other processor. 

My concern that I might have with your current state, is that your database tables may be too large to run either of these without the cleanup timing out (although one of these should be implemented for future). If this is the case, I would log into your database, and TRUNCATE both the EwsNotificationItems table and EwsSubscriptionItems table. This should hopefully allow the above methods to run without timing out (I know the EwsNotifications, and EwsSubscriptions tables have FK constraints so they cannot be Truncated, but in theory you could run a DELETE command on them if the requests are still timing out, the DELETE command may just just might take awhile.)

Let me know if you have any questions.

Best Regards,

-Jeff

[bbradley_jaxcon]

Thanks Jeff, I'll forward this onto the our developer who is making the changes to the processor. Your correct that the size has been causing some timeout issues when purging. I'll manipulate the database like you suggested and get everything to a more workable size.

[bbradley_jaxcon]

Doing a truncation in the DB, purging via the SC website, and then shrinking the DB cleared everything up and restored all the space.

[JeffBowman]

Hi Brian,

Great news!

If the developer won't be able to add the code for a while, I would recommend, configuring an instance of the EwsServerCleanupProcessor, and run that on a schedule, so the database remains small.

Let me know if you need any assistance with that.

Best Regards,

-Jeff

[SteveGregory]

Hi Jeff, Is the clean up utility available for version 2.2? We have an older site that has this exact issue?

[bbradley_jaxcon]

I suspect that using the same processor for an old version would work just fine

[JeffBowman]

Hi @SteveGregory ,

The cleanup utility (that is part of SmartConnector.Utilities), was never written for 2.2. That said, similar code could be written and added to the Smart Connector extension (but that would require code changes to the old extension).

If the Smart Connector extension that is being used on that site is still maintained, I would think the best thing to do would be to get the most recent version and update the Smart Connector framework along with it.

Best Regards,

-Jeff