CODEWITHSUNDEEP
c#sql-serverwcfado.netvpn
rastro
rastro

Reputation: 67

Best way for WinForms client to communicate with remote SQL Server and share data files

After much Google searching and at the risk of asking dumb questions, I could use some help. I’m developing a C# WinForms client application using ADO.NET to read/write data from a SQL Server 2012 database located on the Internet. That same application also needs to upload/download data files. The client application will only be used by a few employees (ever). The employees are all in different locations. The database is only about 20 MB. There will be about 100 data files totaling about 300 MB accessed individually on a periodic basis. SQL Server 2012 is running on a (non-virtual) Windows Server 2008 R2 machine which we have full control over. The client application will be running on Win-XP and Win-7 machines.

Priorities are 1. Internet security – keeping hackers out of the Windows Server machine and off the client/server communications. 2. Performance. 3. Simplicity. Corporate security and scalability are not issues. Also, performance is not that important if the solution is overlay complicated.

Two related questions I could really use help on:

  1. Given the above priorities, what is the best way to communicate with the database? The only two options I’ve found are exclusively; a WCF service or directly through a VPN.

  2. And again given the above priorities, what is the best way to upload/download data files? I’m sure there are many options for this using VPN, WCF, FTP; but I don’t know any specifics. Also, using a SQL Server 2012 FileTable looks promising but I’m not sure how that works over the web. Backup/restore plus being able to do a full-text search over the data would be nice features but not requirements.

I know what a VPN is but have never used one for these purposes. I know there are some security issues with PPTP, but we won’t be upgrading the XP machines for a while. I know what a WCF service is but have never written one. I also don’t know if SOAP or REST is better in this instance. I’ve built a FileTable in SQL Server, but I don’t know how to access the data remotely. I have decent knowledge of C#, ADO.NET, and SQL Server.

I realize these are big questions with subjective answers. Still, any ideas or a shove in the right direction would be greatly appreciated.

Upvotes: 1

Views: 3708

Answers (2)

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

Keep it simple and use standard mechanisms. My recommendation is as follows:

  • Build a WCF service that is capable of performing the operations you want. You can build a SOAP or RESTful service. My general guidance here is to build a RESTful service because you're transferring files and this is much more integrated with REST. With SOAP you have some setting you're going to need to fiddle with to transfer large files.
  • Use SSL to secure the service, keep it simple. A VPN is an added layer of complexity and very likely not needed in this scenario. Further, it will only make the experience for the users less friendly.
  • I would not recommend using the FileTable in SQL Server 2012 for your needs. You own the server so when you send and receive files it will be much more straight forward to deal with the file system.
  • You can also build a simple forms authentication process that creates a session key for the user and passes it back. I'm not sure this is necessary, but if you need that extra layer, just make that one of the operations. Then that session key can then be passed into each method and validated before performing the operation. This will be safe because you're using SSL.

Here is a tutorial that will help walk you through building a RESTful WCF service, and it's fairly new.

Upvotes: 1

Brett Wolfington
Brett Wolfington

Reputation: 6627

My recommendation would be to deploy a VPN server to provide the security you are looking for. There are a number of good VPN servers available, and a Google search should provide a number of options at varying price points.

Once you have deployed the VPN server (and clients to all computers not on your local network that you would like to be able to access the database), you can use ADO.NET to access the database. ADO.NET will work seamlessly behind the VPN.

From the context of your question I am assuming that the files are stored in a file system outside of the database, and the database merely references the files. If this is the case, you could use any number of options for downloading the files, but FTP is a time-tested, easy-to-implement solution. There are others that may or may not work better in your situation (see here for a few options).

Upvotes: 0

Related Questions