Server isn't too bad (2x1.7Ghz Xeon I think, 2GIG RAM) type of setup (may be little more or less). App doesn't require as much resources, but we are planning for the future )
While I have some resource to play with, I also need to make sure I don't load much that may impact the application performance. In my experience, SQL Server 2000 will take a lot of resources even when not used as much. So I am considering MSDE.
I am curious however, WHAT ARE THE MAIN DIFFERENCES BETWEEN SQL 2000 Server Vs MSDE 2000 ?
Beside no Client tool and mostly command line specific command, what else there to convince that I should go for server vs desktop engine?
Now our company has Enterprise license for SQL, so I am not worried about the cost (well a little maybe), but mostly wondering about performance.
If you think of any reason why I go to SQL Server 2000 over MSDE, please let me. Otherwise, I think MSDE in this case is the appropriate RDBMS to go for.
Regards,
ImpuMicrosoft has a good chart summarizing each of the SQL Server products:SQL Server 2000 Product Overview. You will see that MSDE is the same thing as SQL ServerStandard Edition, with these exceptions:
-- does not include graphical management tools
-- has a workload governor that limits the number of concurrent transactions
-- does not include any analysis capbilities (OLAP, DTS, data mining & warehousing)
-- has different scalability limits
On top of all of that, theEnterprise edition (which you already own), gives you the ability to use failover clustering, log shipping, advanced analysis features, improved scalability, availability, and performance. (SeeFeatures by Edition for more specifics in this area.)
You might find that your database access is your bottleneck, and that you'd want the best performance possible from the database, which would be the Enterprise edition. We inadvertently had moved from the Enterprise Edition to the Standard Edition a while back, and there was a SIGNIFICANT decrease in performance. We had maybe 2 dozen people hitting the database and it was awful. The Standard Edition just would/could not take advantage of the hardware in the server. But looking at the hardware you have on your server (dual processor with 2 gig of RAM, it would seem that the Standard would be able to fully utiltize that)
How many users will be hitting your application at once? MSDE will only handle 25 concurrent processes (note: not USERS), which may or may not be enough for your purposes.
Terri|||Awesome response Terri (Thanks).
since this would be an applicaiton server (reporting software that uses built-in db), we will have very limited use for the MS SQL Server or even MSDE. Mainly I need to install it because I want to install dotnetnuke :) ...
Traffic would be very few to this site as the site will have link to reports that are running off of that other reporting engine. As I said, I could easily get by with a IIS/HTML based website for this purpose.
Having the Enterprise or Standard SQL server will give us a lot of advantages, but you are suggesting I will have performance gain too? Even though MSDE have so little functionality than SQL server (Std or Ent)? Then I oughta go for MS SQL Server then (even though it would be pretty much useless other than housing the dotnetnuke tables.
Regards,
impu|||For others if anyone curious, I found this response from a MSFT guy to another person who asked similar question (Lesson learn, do a little more searching before asking a question :))
*************Start of MSFT engineer's response***********************
SQL Server 2000 Desktop Engine (MSDE 2000) is a version of the
SQL Server data engine designed for redistribution with client-side
applications. Desktop Engine supports most of the functionality of the
other editions of SQL Server 2000, as well as the APIs they support.
Desktop Engine has been optimized for use on smaller computer systems such
as laptops, stand-alone workstations, and servers that support small
workgroups.
Therefore, if there is an instance of SQL server 2000 (Enterprise) on your
computer, it is not necessary to install MSDE 2000 again on the same
machine. In this case, if MSDE 2000 is still installed on your machine, it
will be the named instance. Generally, the application requiring
installation of MSDE will run the .sql files to install the databases on
the SQL Server instance. You can check and see the introductions of your
application if it also does the same. If so, based on my experience, there
is not obvious difference between SQL Server 2000 and MSDE 2000.
Desktop Engine implements all of the basic functionality of SQL Server
2000, as well as most of the additional services. There are, however, a few
differences to keep in mind while planning your installation:
1. The size of Desktop Engine databases cannot exceed 2 gigabytes (GB).
2. Desktop Engine supports symmetric multiprocessing on a maximum of two
processors.
3. Desktop Engine uses the same concurrent workload governor as SQL Server
2000 Personal Edition. When more than five batches are run concurrently,
the workload governor will insert no-ops in direct proportion to the number
of excess batches submitted for processing, thereby reducing system
performance.
4. Desktop Engine participates in replication as:
a. A Subscriber in transactional replication.
b. Both Subscriber and Publisher in snapshot and merge replication.
5. Desktop Engine does not support SQL Mail.
6. Desktop Engine is not supported on Windows? Advanced Server, Limited
Edition.
For more information about the features supported by Desktop Engine, please
refer to the article on SQL Server Books Online.
Topic "Features Supported by the Editions of SQL Server 2000."
817788 Support WebCast: Microsoft SQL Server 2000 Desktop Engine (MSDE)
http://support.microsoft.com/?id=817788
***************end of his response**********************
p.s. Notice #3 (same thing Terri pointed out). May be a decision factor why I should choose SQL Server Ent over MSDE. Didn't think of it from this angle (but then again, would I see more than 4 users? I might. :p
Thanks
Impu|||Take a look at this:http://www.microsoft.com/sql/msde/productinfo/features.asp According to this Microsoft MSDE 2000 Features Article, MSDE does in fact support DTS, it just cannot create them by default because it does not come with the DTS designer (if you have access to SQL Enterprise Manager you should have no problem designing the package).
I will be testing to verify, I will let you know what I find...
