StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » How To on connecting to a hosted SQL Server Express database

How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Wed, 02 Sep 2009 08:35:22 GMT

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

RE: How To on connecting to a hosted SQL Server Express database

Trent L. Taylor — Wed, 02 Sep 2009 08:35:22 GMT

[quote]Greg that is a great suggestion. However, how do I encrypt the connection via ES?[/quote]

When you setup the ES data source, there is simply a property (flag) that indicates whether you want encryption. There are others as well that include compression, etc. These are all in the docs in how to setup the ES server.

RE: How To on connecting to a hosted SQL Server Express database

Greg McGuffey — Tue, 01 Sep 2009 23:44:22 GMT

It's pretty easy. I don't have the help docs available at the moment and since this is rarely done, I'm working with from a faulty memory board (er...my brain), so you'll need to check the help files. This is setup for each data source, so check that part of the help file. You setup a key and a salt for the encryption in the data source config file, then when you setup the ES data source in the application, you provide the same key and salt. There may also be a flag to set that says it's encrypted. That's it!

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Tue, 01 Sep 2009 22:05:23 GMT

[quote]SQL server is not access directly by users (it is access by ES) so SQL Server should NOT be available beyond firewall. ES is made visible via IIS to the outside world. Note you can encrypt the connection via ES, so only your app can get access (because it knows the key setup for ES). [/quote]

Greg that is a great suggestion. However, how do I encrypt the connection via ES?

Thanks,

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Keith Chisarik — Tue, 01 Sep 2009 20:05:40 GMT

[quote]Do you think I would be able to get away with SQL Server Express to handle this volume of clients and databases?[/quote]

Yes, you can always move up to another version of SQL Server as you grow. As long as you are comfortable with the limitations of Express you should be fine starting there (1 GB RAM, 4GB database size limit, etc)

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx?PHPSESSID=0324345d45ef1bf1f764044e03584cd0

[quote]Because my clients are using ES to access the SQL Server database, only ES needs to have access to SQL Server and I don't need to exposed the database to the internet for my clients to access the data...correct?[/quote]

Correct

RE: How To on connecting to a hosted SQL Server Express database

Greg McGuffey — Tue, 01 Sep 2009 20:02:51 GMT

Jeff,

Yep that is correct. SQL server is not access directly by users (it is access by ES) so SQL Server should NOT be available beyond firewall. ES is made visible via IIS to the outside world. Note you can encrypt the connection via ES, so only your app can get access (because it knows the key setup for ES).

Not sure about using SQL Server Express, but that sounds like real low volume. However, you might want a beefier version just for the admin tools. Hopefully others can weigh in on that issue with more knowledge.

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Tue, 01 Sep 2009 18:23:03 GMT

Keith,

It looks like I am going to use Hosting.com (HostMySite.com) which is the hosting site I believe you are using. They are sending me a quote for the server and SQL Server Workgroup edition

I am going to have about 30 clients and 10 databases running on the server. This app is a very low transactional system. Probably about 1 update per minute to 5 tables. Do you think I would be able to get away with SQL Server Express to handle this volume of clients and databases?

Also, let me see if I understand what you meant by not having my SQL Server exposed. Because my clients are using ES to access the SQL Server database, only ES needs to have access to SQL Server and I don't need to exposed the database to the internet for my clients to access the data...correct?

Thanks so much for the help and advice!

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Keith Chisarik — Tue, 01 Sep 2009 17:13:35 GMT

Trent can say more about ES, but since you will be moving to a dedicated server you should have RDP access, therefore you will not need your SQL "exposed", ES runs via HTTP via IIS so if you just have your host allow that protocol and RDP for remote access, you should be fine. They SHOULD provide simple firewall requests, if they dont, look for a new host :)

Before we got our dedicated hardware firewall, I used the Windows firewall on Server 2003 and it was fine, only once we started doing more with the server did I need to go beyond that.

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Mon, 31 Aug 2009 19:51:27 GMT

[quote][b]Keith Chisarik (06/24/2009)[/b][hr]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.[/quote]

Hi Keith/SF,

To be able to use ES, I found out I need to have a dedicated server (using HostMySite), because ES licensing activation requires a MAC address which I did not know about.

Anyway, I am new to having a dedicate server running SQL Server Workgroup 2005 hosted on the internet. I was hoping that you or SF could give me some best practices on securing the server, SQL Server and IIS. I am going to have clients connect from many different locations including their homes and so hard coding client IPs in the firewall will be I think difficult to setup and manage. I am willing to do that if necessary, but I just don't know about this stuff. I know there is a first time for everything and I am willing to learn and follow advice.

Any advice anyone can give me would greatly be appreciated :D

Thanks,

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Mon, 31 Aug 2009 16:18:34 GMT

Hi Trent,

Thank you so much for the info. I am really looking forward to using ES. I will pursue this avenue.

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Trent L. Taylor — Mon, 31 Aug 2009 15:05:47 GMT

I spoke with another SF User and he recommended the following:

[url=http://www.hosting.com/dedicatedservershosting/]http://www.hosting.com/dedicatedservershosting/[/url]

There is a rep named Eric Hutts that has been recommended as well. This is a dedicated box for around $89 a month versus a shared server box. I know that GoDaddy has an option like this as well. If you do this you will have a full machine dedicated and it will resolve many of these types of issues.

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Mon, 31 Aug 2009 10:07:19 GMT

Hi Trent,

I am not sure what I am to do. This is a brand new application I have been working on and testing in a beta site. All has gone well and now it looks like I am ready to release the production version of the software in the next couple of weeks. The very last piece of the puzzle is to move the database from the local network to a hosting site and implement ES. If all fails, I will need to set-up local installs of the database...Big Yuk!!!! Unfortunately for me, I am new to web hosting and I was not aware that something like this would be a problem. This will be very disappointing, because I decided to build my solution with SF so that I could use ES for this purpose, and unfortunately, I told my customers about this great feature.

I don't know who Keith is. I am willing to use another hosting site if it will guarantee me the ability to do all of this.

Is it possible for your to get me in touch with Keith? Or just tell me what hosting site I should use.

Thanks,

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Trent L. Taylor — Mon, 31 Aug 2009 09:30:36 GMT

Well, I know that we have a number of users that are using hosting. I think that Keith may be using this very hosting site so we may want to ask him what he did with them in regards to this. I do not recall having a conversation with Keith when he published this, so he might be the person to ask in regards to the MAC address here.

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Mon, 31 Aug 2009 06:54:26 GMT

Hi Trent,

Here is another response about the MAC address I received from WebHost4Life:

Hi Jeff, MAC address is sentative information and we will not provide it.

Are you using MAC address to activate your License? I checked their official site and seems you can activate it at your Local PC first, move it to our server and then contact strataframe.net to reset your account so you can re-activate on the new machine. You activate online through the My Account area and download the license file as opposed to a live activation client.

I'm not sure if you have to use new MAC address during "Re-activation". Would you contact strataframe.net about this first?

Trent, where do I go from here? Please advise!

Thanks,

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Sat, 29 Aug 2009 13:54:22 GMT

[quote][b]
Second, you [b]DO NOT[/b] want to add the public IP as the status IP address. This would allow every person in the world to see the status, password, etc. If you have access to the local IP, then use the local IP. Otherwise, you will want to secure this. This is a lot more detailed in the help documentation. There is a section in the DOCs that explain this setting that will be more helpful than my post. Let me know if you don't get it going.[/quote]

Oops I should have known that about the public IP. However, I have looked in the Help File under Enterprise Server->Step 3: Configure web.config and I do NOT see any additional details on how to secure things regarding the Web.config file for the key="StatusIpAddresses" value="127.0.0.1". Where is the info?

Thanks,

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Sat, 29 Aug 2009 13:17:35 GMT

Hi Trent,

To activate the license I need to provide the MAC address. So I contacted WebHost4Life about supplying me the MAC address of the server hosting my website and this is what they said:

Hi
Please provide details of the .net application you purchased such as its official site and relevant urls of descriptions and requirements so that our supervisors can have an evaluation on the application.
Regards

I am not sure where to go from here. Please advise.

Thanks,

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Trent L. Taylor — Sat, 29 Aug 2009 11:17:12 GMT

First, go ahead and change the version to 1.7.0.0. That will make sure to use the 1.7 references. I will make sure that these are updated for the next point release.

Second, you [b]DO NOT[/b] want to add the public IP as the status IP address. This would allow every person in the world to see the status, password, etc. If you have access to the local IP, then use the local IP. Otherwise, you will want to secure this. This is a lot more detailed in the help documentation. There is a section in the DOCs that explain this setting that will be more helpful than my post. Let me know if you don't get it going.

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Sat, 29 Aug 2009 11:09:11 GMT

I am attempting to setup ES today. I know it's a Saturday, but with my busy schedule it is the best time to attempt to do something like this without any interruptions. However, I notice that after I extracted the ES.zip file into the website, the DataSources.config file references <TypeAssemblyVersion>1.6.0.0</TypeAssemblyVersion>. Since I am now using 1.7.0.1 version, am I going to have a problem with the ES website communicating with my windows app using 1.7.0.1?

Also, in the Web.config file, for the key="StatusIpAddresses" value="127.0.0.1", am I to add the ip address of www.prospecthelper.com which is 66.226.20.91? Just so that I am clear, I don't have to add every ip address of the computers running my windows app to this key...right?

Thanks,

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Fri, 28 Aug 2009 07:06:01 GMT

Hi Greg,

Thanks for all of the great info. I am going to setup a test this weekend and see if I can get everything working with ES

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Greg McGuffey — Thu, 27 Aug 2009 00:54:52 GMT

[quote]I believe for my scenario, everytime I add a new database to my hosted SQL Server for a new group of clients I will add a new entry in the ES DataSources.config on the website assigning an unique DataSourceKey="Database100" along with the connection information pointing to the new database hosted on the internet. When I setup the new group of clients' apps on their local computers, I will dynamically create a New EnterpriseDataSourceItem("Database100", "www.prospecthelper.com/databases", "Database100", ....). Am I correct about this?[/quote]

Close. I don't have easy access to the help file, but there are two data source keys when using ES. One identifies the data source to ES and is setup in the DataSource.config file on the ES server. This is one the one you'd prompt the user for and store in the registry/wherever. However, there is also one that is used by the BOs of your app. This one should be the same for all the client datasources. I.e. you app is designed to hit a database with a specific schema, abstracted into BOs within the app (E.g. imagine a db has a Customers table, which is abstracted as a CustomersBO in the app). The BO has a key that links it to a datasource setup for the app (your EnterpriseDataSourceItem). Look at the help page again to see which is which. In your example, you are using "Database100" for both, which you don't want (if you did it this way, you'd have to change the key on ALL your BOs based on client...ugly and unnecessary).

Because the EnterpriseDataSourceItem maps its own data source keys to those used by BOs, you can program against one set of BOs, with a set data source key, yet change what database the BO is hitting by simply changing the data source associated with that key (that is what you do in the SetDataSources method will code like DataLayer.DataSources.Add(...)). As I mentioned, in my app, the same BOs can hit an arbitrary dev database using a direct connection via a standard SqlDataSourceItem or it can hit either a test database or the production database via EnterpriseDataSourceItems.

So, I'm gonna assume that the first data source key is the one for the BO and the second one if for ES (check, I could have it backwards), I'd assume you'd have code like this (pseudo code):

[codesnippet]Sub SetDataSources
  '-- Check registry for existing ES datasource key.
  '   The GetESKey function would handle prompting and saving key
  '   if it wasn't already in registry
  Dim esKey As String = GetESKey()

  '-- Create ES data source item. Note that default BO key of "" is used.
  '   This not only points to the correct ES server it also maps
  '   the appropriate ES data source to the data sources used by
  '   the BOs in the app. I.e. if the BO has a key of "", then ES will use
  '   the datasource configured with the esKey to connect to the db.
  Dim esSource As New EnterpriseDataSourceItem("", "www.prospecthelper.com/databases", esKey, ....)

  '-- Add this as a data source for app.
  DataLayer.DataSource.Add(esSource)
End Sub[/codesnippet]

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Wed, 26 Aug 2009 22:58:03 GMT

Hi Greg,

Thank you. Your explanation truly helped. I have looked at the help file as you suggested and I think I understand how it works. I believe for my scenario, everytime I add a new database to my hosted SQL Server for a new group of clients I will add a new entry in the ES DataSources.config on the website assigning an unique DataSourceKey="Database100" along with the connection information pointing to the new database hosted on the internet. When I setup the new group of clients' apps on their local computers, I will dynamically create a New EnterpriseDataSourceItem("Database100", "www.prospecthelper.com/databases", "Database100", ....). Am I correct about this?

To dynamically create the EnterpriseDataSourceItem, I was thinking that when the app is started for the first time, it would query the user for the DataSourceKey ("Database100") which I will provide to them. Somehow after verifying they have enter the correct key value, I was going to write it to the registry. Then I can retrieve it and dynamically create the EnterpriseDataSourceItem everytime they start the app. Is this a good way of handling this in my windows app?

Also, I have a Windows management app to enable the customer to manage the system. In some cases, the customer will want to look at (read only) a 100,000 records at one time loaded into a DevExpress grid. Right now without ES, it takes 2-1/2 minutes to load only 25,000 records from the database hosted on the internet. I am assuming by using ES I will be able to load the 100,000 records in a reasonable amount of time (hopefully less than 30 seconds) even when retrieving them over the internet. Is this a reasonable expectation when using ES?

Thanks,

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Greg McGuffey — Wed, 26 Aug 2009 21:22:49 GMT

Jeff,

ES can definitely connect to any number of databases, just like you can have any number of data sources which connect to the various BOs. I'm currently using it to connect to an app database and a security database. I change both of these on the fly (to switch between dev, test and prod database sets). I also change between direct connections and ES connections. It is really flexible.

First, take a looks that the "Configuring DataSources.config" (Enterprise Server > Deploying the Enterprise Server) in the help file. There is an example that shows three data sources being configured. You'd have a datasource for every database available (i.e. lots of them). Then you would need to create the appropriate ES data source items in SetDataSources of AppMain.vb (program.cs). Here you would map the BO key to the ES key. In that same help topic, the EnterpriseDataSourceItem constructor parameters are explained.

Now in your case, I'm guessing you'd need to map a group of users (e.g. by client) to the appropriate enterprise data source. Then you'd dynamically build the EnterpriseDataSource based on the client. This could get complicated because you won't know who the user is yet (login happens later if you are using SF RBS). However, if you used a common security db, which included the table(s) that did the mapping, you could set the security up in SetDataSources and then setup the app db after they logged in, but before any UI is presented.

My understanding of licensing is that you get a license for every user that will access data via ES. Last I checked, you could also increase to more licenses as needed. If you have 50 users spread across 10 clients, you'd need 50 licenses to use SF. I believe when you hit around a 100, it just goes unlimited. Check the SF store for prices. The prices are yearly fees. ES doesn't really care how many data sources you're using, just how many users are using it. This is of course not the "official" word on it, just my understanding. I've been using ES for a couple of years now. Love it.

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Wed, 26 Aug 2009 17:44:18 GMT

Hi Greg,

Thanks for the opinion on separate databases. I also felt it would be better to have separate databases for each customer for the same reasons you said.

However, I am not sure you are correct on the ES setup. See Post# 24301. As you can see, Trent states that a single ES Setup/Instance is configured for a single database. So according to Trent, for all practical purposes, I really can't have different clients connect through a single instance of ES to different databases attached to a single SQL Server hosted on the internet.

If I am still confused about how ES works and its licensing, could someone please explain to me in detail what exactly I can do with ES when it comes to having different groups of clients connect over the internet to different databases attached to a single SQL Server hosted on the internet and in my case hosted by WebHost4Life.com? And how ES licensing works in this scenario?

Also, let me say this to help clarify my scenario. Each group of clients will be given a dedicated database. In other words, a group of clients will only be setup to connect to one database. This group of clients will NOT have the option to connect to another database. Again, all of the databases for different groups of clients will be attached to a single SQL Server hosted on the internet. I hope this helps explain better what I want to do with ES.

If I am not explaining my scenario in terms that SF understands, please let me know and I will attempt to explain it in a different way. Better yet, is there someone I can call to discuss this in detail?

Thanks,

Jeff

RE: How To on connecting to a hosted SQL Server Express database

Greg McGuffey — Wed, 26 Aug 2009 11:17:55 GMT

Jeff,

I'm not sure if you are asking about the databases because your concerned that ES can only connect to a single database or because of licensing issues with a hosting service (i.e. they'd charge per database). There are no technical issues with ES. You can use a single ES installation to connect to N number of databases. I.e. you could have one ES server/instance and connect to 100s of databases through it. Assuming the ES server and the database server(s) are on the LAN together, and that the ES server could handle all the requests (I pretty sure it can be clustered to scale if needed), this would work well.

In general, I'd leave the databases separate if I could. While you have some increased effort when deploying database changes with separate databases, this is negligible when using DDT. You also have better control of what gets upgraded with multiple databases. Situations such as if a client doesn't want to upgrade? Or you have one that needs a critical fix NOW, but you don't want to rush it out to everyone else just yet.

If you have a single master database, this increases the complexity of the app/db. Now every table, BO and browse dialog needs to be client aware. You'll spend more time optimizing queries/views/sprocs. Also, if a database becomes corrupt, all your clients go down while you fix it for the one. If you get a huge client that needs there own server, you'll have more work to do get their data out of the master, etc. In general the master database is just a PIA.

Having said that, I use the master database concept, though in a different situation. In my case the partitions are on "projects" which are in the context of a single business (consulting), so I have to have all the projects in a single database.

Hope that provides some insight into what you might get into going the master database route. I'd be interested in what others think on this matter.

RE: How To on connecting to a hosted SQL Server Express database

Jeff Pagley — Wed, 26 Aug 2009 10:02:56 GMT

Trent,

That's great news.

I do have a question that I would like your opinion on. Right now I am maintaining a separate database for each customer who will connect 2-4 client licenses to it. On the average, each database will contain up to 500,000 records. In my mind and based on my limited knowledge of SQL Server, it seemed like a good idea by keeping a clear separation of the customer databases from one another. Based on my last email and the ES licensing cost issues I was facing, I was considering modifying my database and code so that all of the customer databases can reside in a single master database. Although this requires a lot of work in reconfiguring the database and refactoring of my code, I felt that in the long run it would be worth it to be able to use ES so that I can host the database on the internet. In fact, one of the main reasons why I decided to use SF on this project was because when I was in the training class last year you covered the benefits of ES. I knew at some point I would want to be able to host the database(s) on the internet while at the same time keep my app in Windows Forms with the ability to load thousands of records and not have to redesign it to run as an ASP.NET app. ES was the perfect solution.

Of course, being able to purchase an affordable licensing for my current scenario is the easy way to go. However, I am willing to make this effort if you see some major benefits for me to have a single master database hosted on one web site.

What is your opinion?

Thanks,

Jeff