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

Hi SF,

I have already posted these questions earlier, but I did not get a reply to them.  I want setup a test for hosting my database on the internet and I had these questions about the steps or how tos:

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

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

3. Setup ES on HostMySite...How?

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

5. What else am I missing?

Thanks,

Jeff

Some of your questions are more training than questions. So let me answer these as best as I can and just point you in the right direction:







Correct. I have added a Development SDK license to your account. You will just need to login to the My Account area and create the license file once you know what host name you are going to use for testing (i.e. es.mytestsite.com).







Correct.







Correct. You will setup a hostname to which you will access this ES web service (same concept as a web site). So when you setup your hosting environment, you will assign a host name to it like www.jeffpagley.com. Once this is done, you can setup your ES license in the SF My Account area. In order to setup the ES site, though, even before you get your license, you will want to use the ES ZIP file instead of the install. You will just extract this ZIP into your hosted site. After that, you will add your license file to the site, and you should be good to go as far as initial setup is concerned. You will still need to define your data source connection in the config file (look at the docs on this as it shows how to setup all of your config files).







Taking advantage of an ES server is just a matter of changing your connection within the SF app to use an enterprise data source item instead of an SQL data source item. The help docs (and the sample that comes with SF) should be able to get you through this step. If you get stuck then post where you are stuck and we will get you through. But once this is done and your ES server is setup...then you are running.



This entire process is not too complicated. So if you catch yourself getting bogged down, then post where you are stuck and we will help you through the process as best we can. The hardest part in a scenario like this is getting the hosting site configured, etc. which isn't too hard either.



Good luck!

Trent,

Thanks for the info.  I am going to setup the test and I will take you up on your offer to help me when I get stuck.

Jeff

Hi Trent,

I have a test database hosted on WebHost4Life.com.  I have successfully connected to it from my windows application using SF and I have edited and saved data!  My URL is www.prospecthelper.com.  They have given me a default.asp page located in a directory d:\hosting\member\jpagley\site1.  When I type in www.prospecthelper.com in the browser it is loading this page. 

As you mention for setting up ES, is this the directory I upload the ES Zip file and extract it into this directory?  Also, once I extract this zip file, where do I add the license file?

Being new to all of this, do you recommend I rename the folder "site1" to something else or create a subfolder to keep the ES web service separate from my main official ProspectHelper.com site which I am going to create later?  Just looking for best practices here.

Also you mention in another post, it would be easy for me to setup a way for my app to check a version via a web service and have my app automatically download and install the latest version from this site if they have permissions.  How do I accomplish this?

One other question.  I am going to have multiple databases hosted on this site.  When using ES, I believe I can have different clients connected to different hosted databases.  The only limitation would be the number ES licenses I have purchased determines the number of client machines that can connect to different databases using ES.  Is this correct?

All the help you can give me would be greatly appreciated.

Thanks,

Jeff

Yes, just extract to the destination. The license file will go in the "bin" folder.







This is really up to you. Generally I give it a meaningful name.







Well, this is a pretty loaded question You still have to write the logic, but in short you have to create the version that to which your application will query. In suprt-short explanation, I get the version from the local assembly, then query the Version table to see if they match. If not, then I will download from a pre-determined location OR pull the downhload location of the new build from the same version table (i.e. a VarChar field containing the URL for that version).







Well, if you are going to use different databases and then each of those databases have a different DataSourceKey on the BO, then yes (same applicaiton but more than one database being accessed within the application). The data source key on the client and the ES server must match. If you are going to use the same BOs and then want to redirect them to different databases (i.e. 2 different clients with the same data source key names) then this will not work and you will need to have a separate ES site for each database. If your application already handles different clients with the same database, then this is not an issue.

Hi Trent,

Thanks for the info.  Right now I do have the logic to check for new versions by reading the Version Number in a table in the database.  If they are different, the application shells out and runs the update from a predetermine location.  Right now I am in the test phase of my system and I am running all of this on a local network.  But for my production system, I will need to host the database on the internet.  I am going to have a lot of databases hosted on the internet and did not want have to update each database with the new version number. So some how I want all of the clients to receive a version number from a single source by querying the it from a web page or web service to determine whether or not they need to run an update.  Then if different, then have the client app automatically download and install the update exe from the website.  Does this seem to be a reasonable thing to do?

Also, I am sorry, but I guess I am still not sure how the ES Licensing works.  Let me explain what I want to do this way.  All of the clients will be running the same exe, which I believe they will ALL then have the same data source key.  However, each client will need to connect to their own database, BUT all of the databases are attached to the same SQL Server hosted by WebHost4Life site.  How does the ES licensing work in this scenario?

Thanks,

Jeff

Sounds good to me.







Well, the issue is that the database that is used gets configured on the ES side. When the connection string is established within the config file, you specify the database. So it would be better to purchase smaller hosting CALs and then setup an ES site for each client that will have a different database name. That would be the most simple solution. Trying to change the database on the fly on the ES side could be more overhead and a lot more work.

Trent,

Now I understand the licensing for ES.  Purchasing smaller host cals seems to be not a problem as far as I can tell at this point. However, based on your ES licensing as I understand it, I will need to purchase an ES license per hostname/database.  In my scenario, each one of my customers will have their own database and 2-4 client licenses connected to it.  Unfortunately for me, the licensing cost $995.00/year for ES per hostname/database for 2-4 client licenses per customer is not affordable.  There is no way the customers are going to pay for this.  It is certainly cost effective and affordable when you have 20+ users connecting to a single database hosted on the internet. 

As all programmers sometime have to do with programs they create, I am going to have to come up with a created, effective way of delivering large number of records to my windows program that will be acceptable to my customers.

Thank you for your help!

Jeff

Jeff,



If you will contact Jay Taylor we have a model that you can purchase that is fewer than the 25+ listed on the website which will be more palatable for these smaller installs. We can most likely work something out for both 5 and 10 user models.

Sorry....you can call the sales line or email him at wjtaylor@micro4.com.

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

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.

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

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.

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

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):

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