Stored Process Runs on SAS EG but not on Stored Process Web Server

[dobby]

The server validated successfully. I did a restart and they are now running on in both EG and from the Stored Process Web Server. I'm not sure exactly which of the changes fixed the issue but thank you so much. I appreciate your patience and responsiveness.

[dobby]

Looks like I spoke too early. Went to lunch, came back to confirm that all is well before updating the users and now I'm getting the same error with the stored processes running of the stored process server.

[Sajid01]

1.Do you get the same message on the EG?
2.We need to find where your stored process logs are

In the file [SASConfig]]/Lev1/Documents/Instructions.html you will find the details of your installs including the location of log files. You will find the locations of stored process logs.
Please have a look at them.
These types of issues do come, and that is why company's hire SAS Admin. So no worries. 

[dobby]

Got a response from SAS and they asked me to run the following SAS code in the stored process.

%put &=SYSVLONG     &=SYSSITE;

%put &=SYSSCPL      &=SYSSCP;

%put &=SYSUSERID    &=SYSHOSTNAME;

It returns SYSUSERID=sassrvlocal. I didn't find sassrvlocal setup as a user in management console (metadata). It is a local Windows account that's on the SAS server and used as the Spawned Server Account. Should there be a metadata account created for sassrvlocal?

[Sajid01]

This is the SAS general Server account. 

So how did this stored process run?

[dobby]

I really don't know. I validated, restarted the SAS server and was able to run the stored process several times from both EG and the Stored Process Web Server. Came back about 2 hrs later and back to square one.

[Sajid01]

Looks like something is stopping the stored process servers

[dobby]

I added the SAS General Servers group to the Authorization for the SASApp - Stored Process Server, restarted the SAS server and I am able to run the stored processes. I'll watch to see if the error returns.

[dobby]

Checked again after about 2 hrs and the error has come back.

So I guess that when the SAS server is started, the Stored Process server establishes a connection to the Oracle database for the libraries that are pre-assigned, if the library is not used the database connection becomes stale and gets disconnected. The Stored Process Server process thinks it still have a connection to the Oracle database and sends a connection request and is then presented with the error. This doesn't happen with libraries that are not preassigned so it looks like the Stored Process server treats pre-assigned and non pre-assigned libraries differently. We don't see the issue when the stored process is run from the Workspace server because the workspace server will start a session for each client connection and will establish a new database connection. That's my summary of what is happening.

[Sajid01]

The logs need to be seen to under stand the issue.

[dobby]

We continued to troubleshoot the issue and this is what we have found:

1. Somewhere between 30 to 50 minutes of the SAS Stored Process Server becoming idle, it is loosing connection to the Oracle database where it uses a pre-registered library.
2. Any attempt to run a stored process after this time, that uses the pre-registered library after that point fails with the Not connected to Oracle message.
3. The session is still seen within the database and is in a valid state.
4. If I restart the Object Spawner then the stored process runs without an error until it is idle again.

I am working with the Firewall team to ascertain if there is some idle timeout setting that is disconnecting the idle session from the SAS Stored Process server.

Is there any setting in SAS that could force the Stored Process Server/Object Spawner to re-establish a lost connection or to send some form of keep alive to prevent this idle time from occurring? I've try setting the Inactivity timeout (mins) under the Advanced Options - Load Balancing tab for the Stored Process Server but not sure if this is related. We are not using any kind of load balancing for SAS.

[Sajid01]

First step is to see the logs.
Typically there is an indication of the cause.

[SASKiwi]

Might be worth trying the DEFER = YES option on the pre-assigned library definitions. That should ensure that database connections are only activated when used.

[dobby]

Hi,

Thank you. I tried DEFER=YES but it didn't resolve the issue. We have not found the root cause be we have 6 work arounds so far:

1. Do not use Pre-assigned libraries. The issue only appears with Pre-Assigned Oracle libraries.
2. Include an explicit LIBREF statement in the stored process code or a custom LIBNAME statement before deploying
3. Deploy stored process to the Workspace Server. The implication is that a Workspace process is started for each user running the stored process and may impact resources.
4. Set the Stored Process to disconnect after a certain number of minutes of idle time
5. Set the pre-Assigned library to use a UNIQUE connection instead of a SHAREDREAD connection.
6. Modify the Windows Registry on the SAS Server by adding A KeepAliveTime set to 1,800,000 (milliseconds) or 30 mins since my error was happening sometime after 30 minutes. https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/dd... 

Based on workaround number 6, I will have a conversation with the Network/Firewall team to identify what is killing the connections.