Hi Kevin--we're in the midst of going through this same process at my company so I'll answer as best I can based on my latest experience with this.
On 3/6/06, Kevin Fricke <[EMAIL PROTECTED]> wrote: > 1) how much will the performance of our applications be helped by moving to > MSSQL from mySQL (if any). For obvious reasons ($$$) we would like to stay > with mySQL. We have not made the switch to 5.0 yet but will be when these > upgrades have been completed. Completely depends. There are plenty of statistics to show that for read operations, MySQL actually performs better than MSSQL and holds up better under load. Other benchmarks show that MSSQL wins hands down even over the other big players on small to medium-sized databases that are transactional in nature. Both MSSQL and MySQL are used for various purposes by big named companies and both can perform well depending on your particular circumstances. This being said, choice of database depends on what you're doing with your database (particularly the types and volume of operations), what drivers you're using, and I don't believe you include here what version of CF you're running. If you're on CF 5 hitting MySQL via ODBC, for example, this is going to be a lot slower and less stable than using the latest JDBC drivers and CF 7. In short, I wouldn't necessarily expect that you're going to have better performance from MSSQL than what you're getting out of MySQL, but there are a lot of variables involved. We're using MSSQL 2000 right now and are launching on MSSQL 2005 in our new data center, but I can tell you right at the moment we're not entirely happy with SQL 2000 from a performance standpoint, particularly when we're doing reads against tables that also have a fair amount of insert and update activity. (Yes, changing this all around is something else we're looking into.) The good news is you can test MSSQL in a relatively real-world way by downloading SQL 2005 Express and trying it out, and you might as well try out MySQL 5 while you're at it. Since you're on MySQL now you shouldn't have to change your queries much if at all to get it running on MySQL 5 and see how it performs, and once you're on MySQL 5 you can also start taking advantage of the new features like views and stored procedures. > 2) in a nutshell, what are the primary benefits for moving from cfPro to > cfEnterprise? Other than the beneifts of larger verity collections, event gateways, and screaming fast performance for cfmail, the big advantage you're going to find is in the flexibility of server configuration and deployment. The multi-instance installation of CF completely rocks, to put it in technical terms. And if you're not using a central data storage device for all your clustered servers (i.e. you're putting the application code on each server individually), bundling everything up into an EAR or WAR file for deployment is darn handy. Also you can take advantage of the built-in clustering capabilities if you choose. (How are you specifically doing your clustering, by the way?) I can't stress enough how cool the multi-instance installation is. We have two CF apps and right now on CF Standard, if one app tanks chances are it takes the other one down with it. In our new environment each of these apps will have their own clustered instance which not only allows each team to manage CF independently of one another, it makes for better stability across the board. > 3) how complicated is maintaining application and session variables in a > cluster? Application variables--not complicated at all if you don't plan on updating them during the running of the app. ;-) I half-joke but the way I tend to use Application variables I don't even worry about clustering, because these variables don't (in my apps anyway) change during execution. If one server goes down and the user gets bounced to another box, the application variables will be the same (or the box-appropriate equivalent) on the second server. Session variables--depends on what you're doing. If you have a ton of session variables that get updated frequently you're going to pay a performance penalty if you decide to do session replication between servers, and actually the typical (and recommended by Macromedia) way of dealing with session management in a clustered environment is to use client variables stored in a database as opposed to session variables so all the servers in the cluster can hit the client variables. Remember that client variables come with a performance hit of their own (and increase load on your database if you don't use a separate database server for this), so the first question to answer is whether or not you even want to replicate sessions or just use "sticky sessions." The answer all depends on the specifics of your application, and you can certainly use a mixture of things like have the client variable store a simple ID that can be tied back to session data stored elsewhere in the database or even in a flat file as needed. (Just throwing out some random ideas.) Also note that you cannot replicate CFCs between clustered servers, so if you're using session CFCs currently they can't be replicated between servers in the cluster. I'm working through our clustering setup right now and was planning on doing a writeup once I have everything configured. > 4) any other advise before we move from a single application server to a > cluster? Do it before it's too late. :-) Living on the edge and constantly worrying about your production server tanking is no fun. Creating a high-availability environment isn't just good for your users, it's good for your sanity as well. Matt -- Matt Woodward [EMAIL PROTECTED] http://www.mattwoodward.com _______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
