We have a smart app client application that leverages SQL Server Endpoints for data access. Clients use this application from several cities in the US across a corporate WAN. The endpoint is secured using kerberos authentication. Users of the application are added to a domain group which is granted rights to the endpoint. Procedures in the database are granted to a SQL role, and the domain group has been added to the database as a login and added to the role.
Everything works fine for users in the home city where the data center resides.
The problem is that when users from a remote office try an connect up, there is anywhere from a 15 to 25 second delay before the application updates it's display.
The remote location has it's own domain controller.
Other data points that may be helpful:
- A custom sharepoint web part that leverages some of the same web services performs normally for all users at all locations.
- SQL Traces of the slow transactions included the full time of the delay experienced by the remote users. I expected the server time would be comparable to the time of local calls, and that I could definitely point the finger at network latency.
I know this is might be out of scope for this forum, but any suggestions are appreciated, or if you have experienced similar issues.
Smart App to Endpoint Kerberos Authentication
akosonom
To further debug your issue, you need to timestamp at the start and end of each connections, queries in your application. At same time, you need a traces from sql profiler so that you know when thoses calls are succeeded in SQL Server. You also need to turn on shared point traces with timestamp if there is any.
If the delay is between sharepoint to your client, the better place to ask is to post question in sharepoint forums.
Dennis Persson
I suspect it is because the network latency. If you can provide the following data. I can try to explain it.
Can you share out
(1) Ping result for both remote and local.
Ping server, Ping both KDC.
(2) SQL Profiler trace for remote and local.
Remulac
One more closing note in the hopes of helping anyone else out there who runs into a similar problem....
Further testing to determine why laptop clients were slower, identified that the VPN software installed as part of the standard laptop configuration was causing the performance problem. The VPN software is leveraging the XML parser heavily, and could not be disabled by the user, even when they were connection locally to the WAN. The issue has been reported to the VPN vendor for resolution.
Jan Stuchlik
Tests of the stored procedures involved indicate no performance issues there, and the ping results are unremarkable, which leaves the end-point authentication process as the remaining suspect. The SQL Traces of calls that come in through the end point include the full delay time experienced by the users, so it is being accounted for by SQL Server, but the extra time is not the result of the procedure's performance.
The big difference from an architectural perspective is that the client application is deployed to the user's workstation, and is authenticating back across the WAN to the SQL endpoints, whereas the SharePoint Web parts are authenticating locally to the SQL endpoints, and serving results back across the WAN as HTML. What I don't know how to do is to debug this further. I'm assuming it is something to do with Active Directory, but that is where this SQL guy's knowledge runs out...
Name:
app_GetMergedLineupDetails
Mean duration, sec
8.5
Std dev, sec
5.7
Min, sec
0.156
Max, sec
18.371
Here are the ping results. They indicate that there is no problem at this lower level of the network stack.
Denver to St. Louis:
1. to SQL Server
Ping statistics for 192.168.145.31:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds
Minimum = 31ms, Maximum = 31ms, Average = 31ms
2. To Sharepoint Server
Ping statistics for 192.168.151.2:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 31ms, Maximum = 31ms, Average = 31ms
3. To STL Domain Server
Ping statistics for 192.168.92.4:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 31ms, Maximum = 35ms, Average = 32ms
St. Louis to Denver:
1. To Denver Client PC
Ping statistics for 192.168.112.41:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 31ms, Maximum = 31ms, Average = 31ms
2. To Denver Domain Controller
Ping statistics for 192.168.113.226:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 31ms, Maximum = 31ms, Average = 31ms
Prabs
The issue ended up primarily being a bad router and a poorly configured router that were causing delays in authentication requests. There were other application related issues that when added to the delay from the router problems, compounded the delay.
Once the big delay was eliminated, the SQL traces were re-run used to identifiy app specific issues. I also ran each proc call individually and captured the XML stream to a file and logged the size of each payload and used it to calculate the KB/second it took for the app to call and consume the data.
There still remains some difference in speed between workstations and laptops, with some laptops taking longer to process larger XML payloads from the web service than a destop workstation, but the delays are now down to the "bearable" level. Now I'm down to hardware and driver differences as the culprits.
Krzysztof Radzimski