Let's talk about desktop / web app DB considerations.

[DSect]

What do you do if you have a desktop app that uses a locally-housed DB on a local server, but you also have a website that would also like to use the local DB's data?

A good example is a scheduling application: Your desktop app handles scheduling and you'd like customers to be able to schedule and view schedules.

Now, you can have 2 DBs. One local DB server and one remote DB housed at your provider. You can replicate to synch them.

-or-

You can store everything on the hosted DB and access it via your application.

-or-

You can expose your local DB to the internet and have your web app query it.


Which will you do?

I don't like storing everything on an off-site DB. I like to have my DB local so I can admin it. Also, I wouldn't want to query my local DB. So, what is a good method of doing this kind of system?

I would like the remote web users to have near or real-time data, with the desktop app. being real-time.

 

[SiriusBlackOp]

I would create a .NET Web Service that lives on a separate/new local server and expose port 80 of that machine to the outside world.

Write web service methods that only query/read the data from the local database that you need, and provide NO Way for writing back unless you absolutely must. Everything in the web service should be as set in stone as possible... which means you never want to be passing SQL Query strings to the web service to relay to the database... or else you might as well expose your database. Understand?

You may even want to use some sort of authentication to access the web service depending on if the data is sensitive. But if you are showing all the data that the web service can access on a public site then it may not matter much that it be secured.

You certainly do not want to expose your database to the world... you would be asking for trouble to find you if you did.

Senior Software Developer

http://www.scoutsft.com

 

[SiriusBlackOp]

This is quite the conversation for a "Let's Talk" thread. LOL!

Senior Software Developer

http://www.scoutsft.com

 

[BoulderBum]

I would dump your host and go with someone like DiscountAsp.net who lets you attach to a hosted database with SSMS (so you can manage it as you normally would). You can still have a local database, then copy objects, export data, and everything you'd normally do with SSMS and SSIS.

The setup you're hoping for requires more configuration, code, and risk. It will take a bit of time to get working, and then there's a good chance it wouldn't work properly. Better to just let the host handle the DB.

MCP, MCTS - .NET Framework 2.0 Web Applications

 

[SiriusBlackOp]

I guess it all depends on what type of DB he has. If it is SQL Server 2005 or newer, then BolderBum's suggestion is a possibility. Also, what it the website written in and do you have the source code? It also depends on DSect's experience and knowledge.

I've never used SSMS or SSIS... does that work like DTS by copying data in batches? I looked into this a little, but it looks like it's a complicated and possibly dangerous little animal.

Senior Software Developer

http://www.scoutsft.com

 

[BoulderBum]

Sirius, have you worked with SQL Server 2005 before? If so, you may have worked with one or both of those technologies without knowing what they were called (I did at first).

Anyway, as far as I'm concerned "SSMS" and "SSIS" are just the new acronyms for the 2005 equivalent of "Enterprise Manager" and "DTS". A SQL Server guru may disagree with that assessment, but that's how I like to think about it.

MCP, MCTS - .NET Framework 2.0 Web Applications

 

[SiriusBlackOp]

Oh... Ok. So you are thinking about maintaining a second DB on the site side and syncronizing the data? Thus only the web DB would be exposed??? I'm trying to figure out how you move data directly from one DB to the other without at least one of them being exposed to the internet.

Senior Software Developer

http://www.scoutsft.com

 

[BoulderBum]

Yeah, pretty much.

DiscountAsp.net allows you to connect to your database with SSMS using SQL Authentication over the internet. From the standpoint of the SSMS interface, it's as if you were dealing with two local databases. You can copy database objects like stored procedures and tables from your local database to the DiscountAsp.net hosted database. You can export data, etc., etc., all from the comfort of SSMS.

The only clumsy part of it is that you have to find your database among the hundred other ones hosted on DiscountAsp.net in the databases folder. They have security locked down so you can't access] anyone else's database, but they still show up in interface.

MCP, MCTS - .NET Framework 2.0 Web Applications

 

[SiriusBlackOp]

Now I understand where that is going... but the problem I have with that option is that it leaves your main database exposed. I personally wouldn't sleep well at night knowing that some fool via the internet could be attempting a brute force attack on my main database... know what I mean?

In this situation, and because they are so easy, I would create a quick web service. In this way, you would create a web reference in the hosted web site's project to the newly created web service which resides inside of your network and port 80 is opened up in the firewall to it. The web service then has a handful of methods that you created to get only the data you need from the database. Therefore there is no way for anyone to directly access the database... and even if they found the web service, they could only get read-only access to data you intended to be posted on a public site anyway. Continuing on, the web service gets the data from the DB and sends it back to the website as a DataSet, where the website can then bind and display it. You could, if you wanted write a method that changes data in the database, but again (hopefully) it would be very restrictive by your design.

So the web service might have two functions:
1.) Function GetWeekSchedule(dtStartDate as Date) As DataSet
2.) Function ScheduleAppt(iCustID as Integer, dtTime as Date, dtEndTime as Date) As Boolean

That way, those are the ONLY 2 capabilities exposed to the outside world, and your external website still gets what it needs. If you wanted to you could take further measures to secure the web service.

Security should always be at least proportionate to its value.

Senior Software Developer

http://www.scoutsft.com

 

[BoulderBum]

I personally wouldn't sleep well at night knowing that some fool via the internet could be attempting a brute force attack on my main database... know what I mean?

Sort of, but the setup is actually secure. Granted I wouldn't run a bank site that way, but then I wouldn't use a hosted solution for a bank site period.

At DiscountAsp.net a database server name is prepended with a long random number so it would be difficult to guess. Also, accessing the database server requires a user name and password you have control over, so you can choose a complex password to make a compromise less feasible. Besides, DiscountAsp.net only allows remote connections via SSMS, not via an application.

In other words, you can attach to the database with SSMS, but you couldn't write a local web application and get data from the remote database because of the way they've configured security.

DiscountAsp.net is therefore reasonably secure, and, in fact, it's little different than having a hosted database solution that doesn't allow the use of SSMS. Most other solutions offer web tools of some sort to access the database, and I'd wager most of them are a lot less secure (and certainly harder to use) than SSMS.

The convenience the approach gives and complexity it saves makes it well worth it.

The problems with web services is that you effectively have to write and maintain a web method on top of every SQL operation you'd like to expose. The approach may more than double the amount of code you'd have to write, and it wouldn't perform as well because of the overhead of connecting to the service and the bloated nature of SOAP messages.



MCP, MCTS - .NET Framework 2.0 Web Applications

 

[ca8msm]

I also connect to my website's hosted SQL Server via SSMS. In my case it is provided by

http://webhost4life.com

so I suspect quite a lot of hosting companies provide this kind of access.


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[BoulderBum]

Those guys look like they charge pretty reasonable rates. How do you like them, ca8msm?

MCP, MCTS - .NET Framework 2.0 Web Applications

 

[ca8msm]

I've been fairly impressed with them. I run both the websites in my signature from the same Windows 2003 Advance Plan ($11.95) a month. Obviosuly this is shared hosting so the speed of the site isn't what it would be from a dedicated server but I get quite a bit of traffic on the aspnetlibrary site and it seems to cope fine.

With the plan I'm on, I'm limited to 8 sites and 2 SQL Server databases although I can also add MySQL and MS Access databases on top of that if needed (plus you can buy additional SQL Server ones).

They provide quite good support (there's a live chat where you can talk to operators) but anything that they can't fix or help you with has to be forwarded onto the technical team who do seem fairly competant and speedy (at least when resolving a problem I had).

They also offer quite a good referral scheme and you can get about a third of the yearly plan I'm on paid for with each referral which can come in handy.

They offer free statistics for your site via awstats, and there is also a mail facility that you can create (unlimited?) accounts for your site. You also get unlimited free sub domains for each site you run although it will cost you $15 for each new domain that you add.


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[chrissie1]

Yep, they are pretty good.

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett

 

[DSect]

The reason this discussion came up is because my bank's site has the following security blurb:

<------
Network Security

The second facet of our security is protection of the Credit Union's internal network and member information from access by unauthorized internet users. (Host to server is strictly via a serial connection unless utilizing TCP/IP then a firewall is required as discussed above) Internet transactions are routed to the Credit Union from the client via a T1/FRAME RELAY/ISDN or dial-up connections. When requests reach the C.U. router, the traffic is either sent to the home banking server (a specific, secured port address), or to a proxy server. The proxy server allows us to isolate our local network from the public Internet. The @Net Banking server is connected via a serial port or TCPIP to the main computer that houses the account information. Only a valid transaction request from the member via a pin and password can cause information to be downloaded from the C.U. host computer back to the client via the internet. Utilizing these methods will disallow unauthorized access to our network including all member information. The @net server stores no Pin, Password or specific account information what so ever, its solely a pass thru via a serial connection from the host server, unless utilizing TCPIP it then will be defended via a proxy/firewall device.

<------

I'd worked at a place that hosted our own web server, so it was easy to expose our DB's info online, because it was all local to me. Now, it's not that easy..

Thanks for the ideas so far. I thought the idea of transaction-based replication would be good (via triggers), but in a two-way data exchange, it's a little trickier. For example - the synch of the external DB to the local DB, from a user updating their "profile". I'd like to get that info into the local DB right away which can be done with triggers, but I have concerns about durability if there is a spotty connection.

I will add more to this question in a few days when I have had time to model the proposed app a little better.

Thank you again, Tek-Tips community. You never cease to amaze me.

 

[SiriusBlackOp]

Two way database replication is painful.

Why can't you just move the website into your local network again?

Personally, I have to remain in my position of never exposing a business critical database directly to the internet. It's dangerous and irresponsible. -- If it’s not critical enough that if it were destroyed today and it didn’t effect your basic business needs, then I would might bend a bit in my current position.


Senior Software Developer

http://www.scoutsft.com

 

[DSect]

Sirius - I have a local DB, but I want the local DB's data to be queryable by the public. I could expose the local DB to the internet, but I do not want to and I'm also trying to figure out a way to do this if the client has a dial-up connection.

I'm trying to find out the best practice for this type of set-up. This has to have come up many times, since many small business owners have internal systems but would like to attach web-based customer services to their systems - without "hosting" the DB thru their ISP.

I just can't imagine a local DB being open to a remote connection, regardless of how tight the security is. I had it lucky where I was at before because I hosted the site @ the location and had control of the network arch.

 

[ca8msm]

I think the question has moved away from being an ASP.NET issue now. I'd try asking in forum962 (you can reference this thread in your new question so people can see what has already been dicussed) as they'll probably have some more thoughts that you should consider and also some recommeded methods.


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[SiriusBlackOp]

How much data is there? Best method in this case is probably to internally push data from the internal (local) copy to a hosted/exposed copy. Only copy what you need. If it is MSSQL, then you could set insert, update and delete triggers for the data tables you want to copy up to the hosted DB. You may also want to setup a nightly, or weekly full update, where you wipe out the copy and reload if from scratch. -- That being said, you also stated earlier that you wanted to get data back into the main DB. You can't do that without either exposing it or setting up some sort of service (internally hosted web site or web service) to relay the data.

Senior Software Developer

http://www.scoutsft.com