SQL Server + PCS slow DB access from remote users

[Guillaume]

Hi,

I have a team sharing a medium sized EA project hosted on a SQL Server DB.
The access is configured on a Pro Cloud Server with a system ODBC DSN using the SQL Server 10.00.14393 ODBC driver.

For remote users working from home using a VPN access, they experience slow access i.e. in using the browser, opening diagrams, creating elements. Local access is not really quick either.
In the user guide, prerequisites suggest using the Microsoft SQL Server OLE DB driver ; could this be the issue ? Is there any other common issue to check in this instance?

In the meantime some users set up a local EAPx on SharePoint which seems a bad idea in my opinion as it should be copied to a local folder before opening it. A user experienced a loss of data doing this.

[Modesto Vega]

We use the OLE DB driver with PCS. Performance with PCS over a VPN is acceptable but not as good as in the office. You can also try using the SQL Server Native Client.

I remember having a performance issue in the past that got resolved by switching from an ODBC driver to an OLEDB or Native Client driver. The difference in performance was remarkable.

[Sunshine]

We have native connection to SQL server and don't have any performance issues with pcs. Model has 1200 diagrams and 74,000 elements

[Guillaume]

Hi,

Thank you for your feedbacks. I changed the connection to OLEDB and noticed is a significant improvement.
An OLEDB server connection from EA is similar to the use of a local EAPx file, whilst using the PCS is slightly slower (but much better than the previous config with the ODBC).
Remote users via the VPN still experience a failry slow connection e.g. creating a stereotyped element takes 3/4 seconds. I'm not sure what is the issue on their infra.

[Modesto Vega]

I was going to suggest changing the connection to a SQL Native Client connection (SQLNCLI) but we just try doing this (moving form OLE DB to Native Client) and got an error.

PCS complains that of an "Invalid Connection String". Essentially, it does not seem to like

Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=<Database Name>;Data Source=<Server Name>;Initial File Name="";Server SPN=""

But it works with

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<Database Name>;Data Source=<Server Name>;DMAlias=<Alias>;

No idea what is going on here.

[Eve]

There are only two drivers for SQL Server that will work with EA below 16 and PCS below 5.

'Microsoft OLE DB Provider for SQL Server' or 'Microsoft OLE DB Driver for SQL Server'

The ODBC driver is known to not work.

[Guillaume]

Hi Eve,

Thank you for information. The user guide is consistent yet it could be useful to explicitly mention on the following page not to use the SQL Server ODBC driver (similarly to unsupported MySQL ODBC drivers).

https://sparxsystems.com/enterprise_architect_user_guide/15.2/model_repository/connecttosqlserver.html