Symptoms
Suppose the following scenario: You have a SQL Service Instance which contains multiple tables used in different active workflows.
How it has to be refreshed? Is it necessary to make sure that all the processes leveraging these tables are stopped and not trying to use any of SmOs based on this service instance?
Normally SQL service instance hosts number of tables and some of them are heavily used by workflows and to make some little change in some other tables in the same DB reflected for K2 you have to refresh entire instance. Does instance refresh mean full downtime for any SmOs based on it?
You may observe errors similar to this one shortly after SQL SI refresh:
Error: Value cannot be null. Parameter name: s
These errors may appear shortly after service instance update and it looks like it has happened just because there was an attempt to perform an update operation against SQL SmOs at the time when service instance was in the process of refresh. Afterwards it is possibly to retry workflow and errors do not appear.
Diagnoses
SmartObjects are temporarily unavailable when service instance refresh is being performed.
Normally service instance refresh during deployment of packages/changes. In general it is advised to do the deployment during maintenance window. And even during maintenance window (assuming it means a period when users doesn't work with the system) there is still a possibility for processes to go into error state as users are not the only reason for processes to go into a running state and use the SMO’s. If that happens, a repair should fix this easily.
Potentially it is possible to consider creating multiple service instances to avoid this issue. Or separate the DB’s, although these options may not always be acceptable.
The fact that the service instance is temporarily not available means that you will never be able to do a 100% uptime or upgrade an application without a maintenance window.
Note on K2 NLB farms: the fact that you have more than one K2 server serving clients doesn't help you to avoid downtime during SQL service instance refresh as all your service participating in NLB using single shared SQL server object. During SQL service instance refresh service instance is being deleted and created from scratch to reflect all new changes - this is by design and this is why downtime is required.
Resolution
Service instance refresh is a process which has to be performed during maintenance window. In cases when process tries to access smart objects based on service instance during refresh procedure process may enter into error state and has to be retried manually.
