Hi SF Team,

As I have mention in the past, I am just a part-time VB.NET programmer.  I am not on here very often, but when I need advice or REAL answers you guys are the ones I come to. That is why I love SF and renew my license every year to have access to this forum. 

My scenario is this.  I have written Win App obviously using SF.  Each database only has to hold up to 500,000 records maximum so that is why I am using SQL Server Express databases.    Currently, I have setup an VPN connection to enable 2 users to connect remotely to the databases within my client's network for running the win app.   In addition, I have serveral small databases running on a single XP box using a single SQL Server Express instance.  Depending on the client records they can connect to any one of the databases they choose.  At the moment, everything is working great.  However, he wants to setup up to 10 remote users.  Therefore, it poses several questions in my mind that I am looking for some suggestions, advice or even How Tos.   I think hosting this stuff on the internet would make my life easier in terms of supporting the remote users and updating changes to the databases, but being a part-timer I have NEVER done any of this before.

1. What is the simplest way to deploy updates to the remote users?  Over internet?

2. If over the internet, would I host the updates on a ftp site that the remote users would download or have some way that the app automatically downloads the update?

3. Should I use a hosting site for the SQL Server Express databases so that I don't have to support 10 VPN connections?

4. If hosting the SQL Server Express databases on the internet is the way to go, do you have any suggestion on who I should use?

5. Also, if am going to host the databases on the internet, how do I make the connection using SF?

Sorry about all of the NEWBIE questions!  But I want to do this the right way and I know you guys will give me the best advice.

Thank you and thanks again for this helpful forum and your great products.

Jeff

Well, in short, the EASIEST way is to use the Enterprise Server as it already runs as a web service and you can put it anywhere, hosted or otherwise, WITHOUT the requirement of a VPN and all you have to change is the connection within your AppMain.vb to use the EnterpriseDataSourceItem instead of the SQLDataSourceItem.



That is the sort answer, but let me tackle your questions.







If you are referring to program updates, then yes. If you have the need to be able to automatically update your clients, then the web will always be the easiest solution. If you want to automate this process, then you will have a web service that your application talks to every know and then to see if there is a new update. If so, download it and install it. Pretty basic. But if you are talking about SQL updates, it will be more dependent on where your SQL Server will be hosted. SQL updates will require more security and a bit more discussion if this is your meaning on the question.







Ftp or Http, either will work depending on your needs. It is actually easier in some ways to program for http versus ftp because depending on where you host your files it may require a TFTP client, which is totally doable, just a bit more work. So in short, you would get a site hosted by GoDaddy (or someone like them, we actually have started using them for more and more and it has worked pretty well). Then you would create the web service that I mentioned, not hard, I promise...that accepts a version coming in and then sends back a response whether there is a new version for download or not. We do this very thing with our medical application and even have the ability to send out updates to specific customers so that we can test things out and not have the entire user base down if there is an issue. But the application sends over the version that it is on, then it checks with the server, if there is a new version then the response will give the url location of the download. Download the file and shell it (launch it) and that is pretty much it.







This would really be entirely up to you. If you do not want to deal with a VPN, then yes, you will want to host this elsewhere. However, if you do this, then the TDS stream (Tabular Data Stream - the SQL formatted data streamed between the server and the client) is pretty fat...that is why we created the Enterprise Server. If you go this route, I would highly recommend the Enterprise Server!







Again, this is up to you, but we have used GoDaddy for some things lately and aside from their horrible marketing...their services are nice. I have not tossed an ES up on their servers, but I see no reason why it would not work.







Using the ES it is just a few lines of code. But if you are going to use straight SQL connections, then you will have to expose a range of dynamic ports and port 1433 (by default) so that you can connect to the server on whichever hosted server you choose. If you use GoDaddy, I am not sure you can do this. You can do this with your internal servers for sure as you would have control, but GoDaddy is pretty restrictive on these types of things. So I am not sure if it would be possible or not.

One note, if you open up your SQL server, makes ure you have a robust firewall that ideally will only allow traffic from your clients IP's. We were brute force attacked less than 24 hours after I exposed our SQL server, even with non-standard ports set up. I learned the true value of strong passwords that day.

We use HostMySite for our dedicated and virtual hosting needs and I could not possibly be happier.

Keith Chisarik

Hi Trent,

Thank you so much for the info.   Being new to all of this, I will take it one step at a time.  Setting up automatic updates seems to be doable and will not require me to dedicate a lot of time do it.  I'll take your word for it that it is easy.  When I get ready to do it, I will probably ask for some coding specifics and How Tos in accomplishing the auto updates. 

In the future when it comes necessary to host the database on the internet, I will certainly use ES.  For someone like me, a part-time programmer, it is the way to go.

Also, I have purchased the license to the Database Deployment tookit, but truly I have not had time to do anything with it.  My understanding is that my updates can include the database updates at the client sites using the DDT.  Am I correct about that?  Is it very difficult to include the DDT updates in my app updates?

Again, thank you so much. You guys are awesome!

Jeff

Hi Keith,

Thank you so much for taking time to give me a heads up about exposing a database on the internet.  Trust me before I do this I will be interested in getting the specifics on how you have setup your system.   This is truly what I love about this forum.  A part-timer like myself can have access to great advice and how tos from guys like you who have the experience.

Jeff

Sure. This is what we do. We wrote a small updater program that extracts all files from a PKG file, copies them over existing files, and then at the same time, if on the server, updates the server database as there is a DDT PKG file in the update package as well. We then use the DataMigrator class to deploy the structures. SF comes with a sample on how to deploy a package file during an update.



So you can create a fully encapsulated update. Even if you just use an installer (like an MSI) you can still use the DDT to update your structures.

I wanted to open this thread again, because my client wants to add additional users with additional databases.  If you read the initial post you will see one of things I am looking for is the best way to host SQL Server databases without breaking the bank.  Among the SF community, I wanted to see if anyone is hosting SQL server databases on the web using a Window application running on a desktop to retrieve, edit and save data.  If so, I would appreciate any info on the following:

1. Who is hosting your databases? (I say databases, because each user(s) will have their own database although each database will only hold up to 500,000 records at the most.)

2. Costs associated with doing this (SQL Client licenses, hosting fees, etc)?

3. How is your app making the connection (SF Enterprise or standard connection pointing to the databases using a URL address)?

4. As I am new to doing something like this, is there anything I need to be aware of or make sure I do before hand in getting this accomplished successfully?

Thank You,

Jeff

You can use most any hosting site. GoDaddy has some really inexpensive options with unlimited bandwidth. If you are going to have a really complex setup, then you can host a machine there, and even that is not expensive. The will setup the machine to specs, etc. But this may be overkill for your needs.







Depends on if you need a full version of SQL Standard or if SQL Express will work for your needs. Again, you will have to check into the hosting sites themselves, but this is not an overly expensive endeavor.







If you are going to do this, then you will definitely want to use ES. Direct connections to an external SQL Server will not work in a production environment like this due to limited bandwidth. The TDS stream alone will consume most of your bandwidth. So you will definitely want a web service that does all of the compression and reformatting of any type of TDS stream for you. In this example, Enterprise Server.







The number one thing I would recommend is to make sure that you are not trying to create a direct connection to SQL. This will set you up for failure from the beginning if you do this. However, if you setup a sounds machine that is hosted someplace with guarenteed up time and lots of bandwidth, then put ES on top of it, then you will be in great shape.

1. We have a dedicated server with HostMySite.

2. We pay in the ballpark of $800 a month that includes the bandwidth, SQL processor license, managed backups and patches, etc.

3. SF Enterprise, we tried straight SQL via web services and direct connection, both were too slow. As soon as we plumbed in ES (easy) we saw a dramatic performance improvement. Our application actually scans images and saves them to the database on the web server and it is fast.

4. Big question. I think getting it right includes equal parts research and testing the environment, and the realization that you may not get it right the first time and have to rethink. That worked for us.

Good luck.

Keith Chisarik

Trent/Keith,

Thanks so much for the quick answers.  I am going to follow your advice.  What I think I will do as you suggested is setup my database at HostMySite and test this out.  

Trent - I believe my SF license gives me 2 ES licenses to do testing is that correct? 

I know this is probably a loaded question, but what else do I need to do to get this working? 

1. Purchase a basic SQL Server Hosting package at HostMySite...Right?

2. Setup ES on HostMySite...How?

3. Implement ES logic in my windows app...How?

4. What else am I missing?

Thanks,

Jeff