Client Access to SQL Server over the Internet

views:

347

answers:

+1 Q:

Client Access to SQL Server over the Internet

Does anyone have any opinions over the best ways to connect a client to SQL Server to get data.

So lets say I have a C# DataGridView in a Winform not on the internal network and I need to load it with a lot of data.

Currently to access the data we call a component SQLDataAdapter in the client which opens a connection to the SQL server, loads a DataSet and then closes the db connection. This is using SQL Server Authentication and there are many applications being ran over the internet connecting the same way at the same time.

So I want to increase security and not degrade performance. Which of these options or other options should I look into?

-Connect the same way but keep connections open for duration of the users time in the app.

-Create WebServices server and pass dataset or collection to client.

-Create WCF server and connect to db. (LINQ, Identify Framework, ADO.NET...?)

-Create both WebServices and WCF server, client connects to WCF, which gets data from WSE server which connects to SQL Server and passes collection or dataset back to client

Personally I like the last option the best for a few reasons. 1. It would be easy to take the components we already have built and drop them into web services. 2. It would allow us to stop having our ASP.NET websites access the database directly but connect through the Web Services server(ASMX to ASMX = good?). 3. We could have better control what data is accessed and given to outside of our network. And we no longer have to keep connection strings in our applications.

Does anybody have experience with these or a combination of these options? I am somewhat wary because I have heard horror stories about servers like this being funnel points when there was no problem just connecting straight to SQL Server.

Thanks

Jason Stone

+2 A:

In my opinion, you need a pretty compelling reason to allow direct connections to your database from outside of your network. Allowing remote Sql connections can be a big security risk if not done correctly. The industry learned this the hard way with the Sql Slammer virus etc...

Winforms/Wpf Client App -> WCF -> Database works really well in the real world. Also, hiding data access behind a service for your remote apps allows you to change your database and related objects without any client changes as long as the data shape passed back and forth remains the same.

On the flip side, any apps that have to serve a lot of concurrent users from one logical instance (such as web applications/sites go) should directly connect to the database.

In either instance, I don't really see the value of having another set of web services to hide the database from your WCF and Web Apps unless we are talking about a huge disparate enterprise system.

Daniel Auger 2009-10-17 18:09:50

We already have our intranet applications on web services and our external winforms already have all data access organized nicely in components. So in my test examples I have all data access on a web services project then in my wcf application I add that as a web reference, then in the client I add the WCF server as a service client. I kind of like the abstraction of a data access layer and then a security layer(WCF). We also may have the WCF run other routines out of the clients possibly as well. I just don't want to do this if its going to slow things down.

JasonBlackwoodStone 2009-10-17 21:16:57

The WCF to WS call will introduce a tiny perf hit (milliseconds if that) if they are on the same network/intranet. The biggest overhead is probably going to be sending soap over the net instead of the strait tcp connection they used to have to the database. I'd say you'll be introducing at least a 10th of a second if not more per call. For most data entry app, it won't be something that a client will notice. If it was an online video game, it would be noticable.

Daniel Auger 2009-10-17 21:54:45

+1 A:

What @Auger said, plus, get yourself an SSL certificate and server everything over HTTPS. This way its secure and it's handled by IIS at a low level, so you know you are getting the best performance.

AngryHacker 2009-10-17 18:18:22

My manager has some hesitancy to SSL. His argument is that it will slow things down. I cannot say on my own. I have yet to notice much of a difference on my own.

JasonBlackwoodStone 2009-10-17 20:58:51

There is overhead - that's for sure. However your boss is wrong, it's not enough to be seen. Fear not, forge forward. It's like when the performance decreases 5 times, it was 1 nanosecond and now it's 5 nanoseconds. You'll never see the difference. I guarantee that the database will be your bottleneck, not SSL.

AngryHacker 2009-10-18 06:34:39

+1 A:

Have you checked out the ADO.NET DataServices (formerly known under codename "Astoria")?

It's a technology built on top of WCF with all the goodness of WCF, that allows you to fairly easily (but selectively and securely) expose a data model (typically an ADO.NET Entity Framework model, but Linq-to-SQL or other technologies work, too) as a RESTful service to the internet.

You have full control over which parts of your database are actually represented in the model, and then you have various levels of access in the model - you can allow read for collections (e.g. read a list of all products), or just read of individual items, or even things like updates, inserts or deletes - all under your control.

With the REST interface, using it is simply browsing to a given URL and then drilling down or linking across the model from there.

There's also a client-side API to use, so you can easily and quickly display your data and offer the user navigation within the data. You should definitely check out the PDC08 talk on Developing Applications Using Data Services with Mike Flasko, and Offline-Enabled Data Services and Desktop Applications with Pablo Castro.

Definitely a good idea, and a very quick and yet safe way to get your data onto the internet! Check it out.

Marc

marc_s 2009-10-17 21:12:22

Looks pretty interesting. I am not sure if we really want to rewrite everything we have done so far. If we can just drag and drop all of our components into a webservice the clients don't need to change how they do things, they just need to add the service reference and do a replace all to the service name from the component name. Possibly a good idea down the road but I think the change to WCF is a big enough change for now. Later we can move to the flavor of the week for data access. I'm sure just being in WCF will help that when that day comes.

JasonBlackwoodStone 2009-10-18 01:24:51

Agreed - in that case, I would definitely recommend using WCF (don't invest time and effort in ASMX or other deprecated technologies), and I would try to change your DataSets into `List<Entity>` where the entities you use are defined as WCF Data Contracts. That makes for the cleanest data transfer in WCF - don't just serialize back the DataSets - **not** recommended.

marc_s 2009-10-18 10:54:23

List<Entity> would be nice but our programs right now all use a lot of data from the database in datasets. Creating List<Entity> for all of them would be a nightmare. Right now if we want to add new data to a DataGridView its simple enough for us to just add the item to the select statement in the SQL Stored Procedure and it will show up in the UI on the datagridview. Yes it wont look as pretty as the other columns in the datagrid because it is not defined as a column in the datagridview but we can get the data there if we needed to without releasing code until the next release.

JasonBlackwoodStone 2009-10-18 16:00:14

ansaurus

tags:

views:

answers:

Client Access to SQL Server over the Internet

related questions