|Load Balanced Servers and a Database|
We have two load balanced servers and just one external IP address. I want to have a form on both servers write to a single database.
My solution is to place the database on server "A" and set up a redirect on server "B" using in internal IP address. It works fine internally but I am not sure if this works for visitor from outside of the firewall.
Is there a better solution for this problem?
Thanks for your help.
Why don't you have server B access the database on server A directly, just as server A does. Then you don't need any redirects. Do it in code on the server.
OK, I feel like an idiot but...
I think you are refering to my connection string and I can't get it to work with an absolute path. Here is my string:
Access_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=http://***.***.***.***/test/test.mdb;"
Using Server.Mappath works fine but of course that only works locally. What am I doing wrong?
Ah, you're using MS Access. I assumed you were using MS SQL or MySQL. No problem, simply create a share on server A called 'db' for example, and map a drive from server B to server A. Or you may be able to access it directly as \\ServerA\db\test.mdb from both Server A and B.
If you are only doing infrequent writes to the database this should be fine, but if you start accessing the db more, then you should look to migrate to either MS SQL or MySQL as they will both work more robustly. (Gee never thought I'd say that about MS software)
I'm not too experienced with Microsoft databases, but have you made sure your server B has access to use your database on your server A?
I'm sure someone with more Microsoft experience has better advice - but in MySQL there is a "user" table where you define "who" can connect and specify "where" you will allow that user to come from.
You may want to make sure your database on Server A is allowing external connections (make sure you specify the user and IP address of server B)
Bob, I'll look into it.
The first thing I did when I started here was ask for a SQL server, 18 months later it's still in the works...
Is MySQL robust enough to run a dynamic web site generating 1 million page views per month? (The cost of the SQL server is holding up my grand plans.)
Thanks for the help.
MySQL will be robust enough. It is much faster then MS SQL but is missing some of the features.
I almost develop exclusively with MySQL, and would recommended it for most database needs.
|It is much faster then MS SQL but is missing some of the features. |
"missing some features" heh. You know, like Stored Procedures, User Defined Functions, OLAP, the TABLE data type to avoid having to use temp tables, true clustering capabilities, and so on ... basically all of the reasons you WANT SQL to begin with. I have never seen mySQL outperform a MS SQL database - MS SQL has always been faster for me on the same hardware. Granted MS is no Oracle, but IMHO mySQL doesn't even come close.
Anyway back to the question at hand ... if you're going to do true load balancing, why are you trying to access a database on the other server you're balancing against? Also what are you using as a load balancer (F5, Cisco switch, Extreme Networks, etc.)? Is your primary reason for redundancy or performance? Your answers will determine what the best solution is.
If you have never "seen" it outperform MS SQL and Oracle that means that you never looked. It is faster for the exact reasons that you said. Oracle and MS SQL both have a lot more overhead because of the extra functionality that they have.
|MySQL in April will ship MySQL Cluster, a high-availability version of its open source database for deployment across clusters of systems. |
|It handles the high-volume, 260 million record tables just as well as the small, low-volume ones. One of our servers handled over a quarter of a billion queries in a month-and-a-half, and it still has capacity to spare. |
Doesn't google use MySQL too?
But this is gettting off topic
Yes, Google uses mySQL in their office - it certainly doesn't power their search engine though.
As for Yahoo, 260 Million records is NOTHING in terms of the types of databases I've run with MS SQL. For one of my projects, the SQL cluster hanldles just under a million queries per HOUR. The database size is presently more than a terrabyte (fully optimized by the way). There are several hundred new inserts per SECOND on indexed tables. And so on, and so forth.
mySQL might be great if you have infrequent inserts and just need to run some basic SELECT statements, but it doesn't stack up to MS SQL or Oracle by a LONG SHOT when it comes to real performance where it counts. Besides, I never run straight queries against the tables in the database. I always code through stored procedures for the extra performance boost and of course security. You just don't get that with mySQL.
It's GETTING there, I'll give you that - in another year or two my will quite possibly outperform MS & Oracle on the same hardware, but it's not there yet. Anyone who has ever run a database where the requirements go beyond simple INSERT / SELECT / UPDATE / DELETE statements knows that mySQL isn't an option.
Anyway, to get back on topic the reason I brought up the DB type is because its relevant to what the poster is doing. There are functions in MS Access that you wouldn't be able to duplicate if you convert to mySQL or Oracle, but you WILL be able to do with Microsoft. For example the MS Access software can be used as a front-end to MS SQL data which can make the transition smoother if you're using forms in your Access DB. I have a feeling that this may not matter the poster, but I figured I'd put it out anyway.
Either way, don't let your apparent distaste for Microsoft or "love" for Open Source cloud the fact that Open source is not always superior to commercial applications.
|if you're going to do true load balancing, why are you trying to access a database on the other server you're balancing against? Also what are you using as a load balancer (F5, Cisco switch, Extreme Networks, etc.)? Is your primary reason for redundancy or performance? Your answers will determine what the best solution is. |
My near-term issue is just a minor form where only a select group of members will provide responses that will be stored in a database. It is not mission critical and I just want to have one collection point. I suppose that I could update the database on both servers each time but I was being lazy. This would of course still require me to write to server "B" from server "A" and vice versa (the original question).
As for the hardware, I think it is Cisco but I don't get to play with it (they stuck me in the Marketing dept).
The reason I ask about MySQL is because we are planning to replace our current hardware (including the load balancer) and one of my requirements for the future is to have redundant database servers behind redundant web servers with the additional caveat of bi-location (which I think is a step too far). The MS SQL licensing has already set us back six months because we can't find an affordable solution.
At present my requirements do not go beyond "INSERT / SELECT / UPDATE / DELETE" but I am supremely concerned about security. Additionally, in the future I expect we will have CRM, a customer service chat package, and content management software that will all utilize the database. I know that they are all compatible with MS SQL but am not sure about MySQL's compatibility.
Thank you for the already thought-provoking input.
When you say "a form" are you referring to a form in Access, or a web-based form?
If it's web based, I think the best solution for you is going to be to stay away from load balancing until you have an independent SQL server in place to eliminate access. Have both locations using the same form on the same server - it doesn't matter where the server is physically as long as they can both reach it.
Then when you're ready to move to SQL, set it up on its own server and you can have as many web servers as you want accessing the data. As for the differences between mySQL and MS SQL, you will need to ask the vendor. If the product uses stored procedures, functions, and other features then you're going to have to go with MS. The licensing isn't too bad, not everyone needs the $27,000 internet license. You're really only going to have ONE user account - your application will always log in as the same user.
|When you say "a form" are you referring to a form in Access, or a web-based form? |
|Have both locations using the same form on the same server |
Are you suggesting that I use a redirect from server "B" to "A" and let the form on server "A" do all the work? This is my current solution but was the cause of my original question.
Asside from the lack of stored procedure are there any other security concerns with MySQL?
|Are you suggesting that I use a redirect from server "B" to "A" and let the form on server "A" do all the work? This is my current solution but was the cause of my original question. |
For an MS Access database I wouldn't do it any other way but keep in mind that even then you're likely to have some issues though with multiple users. The problem with Access is that its a single file: when someone requests it, the file loads into memory and then (assuming you coded your ASP properly) closes the database. This is fine for SELECT statements against the data in the DB because it can be opened multiple times. When you have multiple users INSERTING data though, you have to rely on the driver to make sure that the database isn't opened twice or you run the risk of corruption when it tries to put the file back together with the new data. So having two servers with two access databases and trying to keep them in sync while both are being written to would most likely fail and/or corrupt one of the databases.
At this point I would say your best option would be to download mySQL now and begin the task of dumping Access. It doesn't seem like you're doing anything right now that mySQL couldn't handle, and it would be a great improvement over what you're doing in Access.
Even if mySQL won't handle your future needs, you can always run it in the meantime and then import to MS or Oracle if you ever need it. It's not like you'll be throwing money away on software :)
I am not letting my 'apparent distaste for Microsoft or "love" for Open Source' cloud my judgement.
I did say that I recommended it for 'most database needs.'