MSDE vs MS SQL
-
First of all, please understand that I am not a database admin. So I would appreciate simple responses, as if you were talking to a young, dull child.
I have an Blackberry Enterprise Server that is currently using MSDE. I am getting mixed reports as to wheather or not performance will improve if I migrate the data to a full blown version of MS SQL. Other than the GUI, does anybody know if there are truly any differences in the two products? For example, does MSDE have fewer threads that it can run simultaneously?
-
2 questions :
1 - How many users / programs are hitting the server at the same time (talking at the same millisecond here).
2 - Is it too slow now and do you expect your needs to grow on that server?
Basically MSDE uses the same core componants than the full version of sql server. The ONLY difference is the number of simultaneous thread executions. If you don't wait for thread to be freed, then I don't think you need to upgrade. Because you'd be getting the same engine running on the same pc, so you would in fact change nothing except the amount in your bank account.
-
Thanks for the response!
There is only one program using the db, but I don't know of anyway to tell how many users are hitting it at one time. I do get messages in the event log along the lines of "this database is optimized for 8 threads, you have exceeded that by x threads." and other indications of poor performance.
Since we do have several SQL machines at our shop, I'm not to concerned with cost. It's just going to be a difficult migration, and I don't want to do it if there will be no benefit. However, if there are more threads in SQL, then it sounds like there will be a benefit.
-
Well again the question stands. Do the users complain about poor performance on that application? If they only wait like half a second a few times a day and it's not causing any problems, I don't see how I could validate the cost of migration. However if you are losing production over this, then it might be worth taking a closer look (BTW unhappy employees = loss of production).
Also the migration could be as simple as installing a full version of sql server, detach the database on MSDE, reattach on the new instance and point the app to the new server... or even have the new server listen where MSDE was listening. Might be easier than redeploying a fix, or the whole application.
I have never done either so I can't say which is better but as always, test first and see what is the best solution for you environement, make sure you have a backup and then proceed with the plan.
Good luck!
-
If you're receiving that error in the event log, then it means that threads are having to wait. It's not the end of the world, but it does mean that MSDE has reached its performance limit (thread wise). MSDE 2000 is limited by the following: 2GB RAM, 2 Processors, 2GB Database size limit,
5 concurrent connections (25 connections for websites)(this was for MSDE 1.0), and you're limited to only using the simple recovery model.I'm guessing that your co-workers have come to rely on their Blackberries for key businees functions, if this is true, you should probably upgrade to SQL if only for the fact that you can do transaction log backups. If you're unsure whether the above is the case, ask you boss, or boss's boss if it'd be alright to go without having access to his/her e-mail, calendar, etc. on for a day. That should give you your answer.
Enjoy!
A Brown
Manage all of your backups in one simple job using this script. -
just checked our BB database. for around 100 users it's 2.56MB.
at least in our case I think MSDE is OK
-
Thanks, that is what I was looking for. I just couldn't find anybody that really seemed to know the limitations of MSDE. If I can throw out one more question, am I correct that there is no hard limit to the possible number of threads, but there is a resource limit that will determine how many threads can be spawned at one time?
-
This article lays out the details.
http://msdn.microsoft.com/library/?url=/library/en-us/architec/8_ar_sa2_0ciq.asp?frame=true
Also, I made a correction to my previous post, as MSDE 1.0 is limited to 5 connections, not MSDE 2000.
Basically, there's no hard limit, only a governor that degrades performance when more than 8 threads are spawned. The errors in the event log should be #3629, additionally, you can run osql -s . -E -q "DBCC CONCURRENCYVIOLATION" from the command prompt to see the details of how often this is happening.
Enjoy!
A Brown
Manage all of your backups in one simple job using this script.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply