October 20, 2007 at 12:08 am
I have the need to have a client application connect to a remote database server for retrieval of large datasets (~13000 rows at time) and am trying to leverage existing technologies to build my solution.
To test the difference in retrieval times I created 2 test apps - testA and testB. I created an HTTP endpoint in SQL Server (2005) that exposes one web method corresponding to a sproc that executes one select query and configured testA to execute query and bind the results to a data grid. I exposed my SQL Server so that I could connect directly to the SQL Server using a connection string and configured testB to execute the same SQL statement in an ad-hoc fashion, binding the results to a data grid.
On average, the HTTP endpoint would take ~ 34 seconds to return the 13,000 row data set whereas the direct connection returned that same result set in 4 seconds or less.
I have a bunch of questions:
* Why would I be seeing such a dramatic difference in the retrieval times?
* How is SQL Server communicating when there is a direct connection?
* What are the immediate disadvantages to connecting directly to the remote SQL Server?
* If I were to consider allowing my client application to communicate directly with the SQL Server, is there any way to secure the communication without VPN?
* In addition, is it even possible to create a SQL login that has all of these requirements?
** select and update only
** cannot view schema (cannot connect to the SQL Server using SQL Server Management Studio)
** cannot view any system tables
** cannot retrieve any information about the schema of the database whatsoever
I guess I'm wondering if there is a way to overcome the obvious serialization overhead that comes with the HTTP endpoint to get the speed of the direct connection while being secure at the same time. Is that being greedy? 😛
Anyhow, what are your opinions? I'm very interested to hear experiences and/or advice.
Thanks,
Paul
October 23, 2007 at 8:24 am
* In addition, is it even possible to create a SQL login that has all of these requirements?
** select and update only
Yes
** cannot view schema (cannot connect to the SQL Server using SQL Server Management Studio)
Once a login has rights to connect to a server, they can connect with any application, SSMS, ODBC, OLEDB. Unless you use a firewall.
** cannot view any system tables
By default all logins have rights to system tables. You could explicitly deny rights on the system tables. I tried denying rights at the schema level, but that did not work because the user was in the public role. It seems odd that deny on the schema does not override the select on the role
** cannot retrieve any information about the schema of the database whatsoever
As above I think you would have to explicitly deny rights to the system tables/views.
I guess I'm wondering if there is a way to overcome the obvious serialization overhead that comes with the HTTP endpoint to get the speed of the direct connection while being secure at the same time. Is that being greedy?
Anyhow, what are your opinions? I'm very interested to hear experiences and/or advice.
I don't really have an experience in using the HTTP Endpoints, but after having a discussion with a colleague, I don't see where I would use them, particularly over the public internet. I would be uncomfortable exposing my SQL Server that way. I would prefer to write Web Services in .NET and use them as middleware, thus limiting the exposure of my SQL Server and enabling me to use security on the Web Server and within my Web Service(s).
I will try a test of using Native XML web Services vs. a .NET web service when I have chance.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 23, 2007 at 8:36 am
Hi Jack,
Thanks for the reply. My tests with web services (specifically WCF using TCP binding) have shown that the query I mention in my post takes about 14 seconds to complete. This was, in my opinion, also too long. I agree with you and also feel uncomfortable opening up my SQL Server to the public internet unless I could guarantee transport security with a limited permission SQL login that can't access my server via SQL Management Server or other browser app. I'm just now beginning to read the BOL for CLR programming. Essentially, I'm looking for a way to communicate between a SQL Server and a client app the same way two SQL Servers would communicate (as I understand it, using TCP and a Tabular Data Stream). Maybe CLR integration is the way to go but I am very green at the moment.
--Paul
October 23, 2007 at 9:01 am
Paul,
Please post what you find out so I can add to my "tool box".
Thanks
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 2:43 am
The answers are :
I have a bunch of questions:
* Why would I be seeing such a dramatic difference in the retrieval times?
Ans: As usual, HTTP first performs his own functions and then it calls the remote SQL and then it retrieves the data from the sQL server and then it returns that dataset. And, Direct connection does not process as many processes as the HTTP, and thus there is a speed difference.
* How is SQL Server communicating when there is a direct connection?
Ans: As you are connected to SQL, the same time you can also communicate
* What are the immediate disadvantages to connecting directly to the remote SQL Server?
Ans: Speed Issues for the other users who are doing the same tasks.
* If I were to consider allowing my client application to communicate directly with the SQL Server, is there any way to secure the communication without VPN?
Ans: yes, it is secure. based on the SQL credentials.
* In addition, is it even possible to create a SQL login that has all of these requirements?
ans: yes
** select and update only
** cannot view schema (cannot connect to the SQL Server using SQL Server Management Studio)
** cannot view any system tables
** cannot retrieve any information about the schema of the database whatsoever
I guess I'm wondering if there is a way to overcome the obvious serialization overhead that comes with the HTTP endpoint to get the speed of the direct connection while being secure at the same time. Is that being greedy?
Anyhow, what are your opinions? I'm very interested to hear experiences and/or advice.
Thanks,
Paul
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply