MSDE 2000 Vs MS SQL 2000 Server

I am about to setup an application server. This app will generate reports and instead of just having a HTML page up, I am considering running dotnetnuke type portal to offer the userbase a better interface for the reports, info, announcement, etc.

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...

MSDE & SQL2000

I am developing a Crystal Reports App in VS2005 (VB). I was originally working on an XP machine with MSDE and stored procedures where it worked fine.

I then transferred the development to a Win2003 machine with SQL2000 and am now getting the following error;

Failed to open a rowset. Description: 'Get_Calls_By_MLO_Date' expects parameter '@.DT1' which was not supplied. Plus some other error detail.

Here is the SP.

CREATE PROCEDURE Get_Calls_By_MLO_By_Date
@.DT1 datetime,
@.DT2 datetime
AS
SELECT MLO, CallNo, DT, Type FROM Actions
WHERE dt >= @.DT1 AND dt <= @.DT2 AND ActionID=1ORDER BY MLO, DT
GO

If I transfer everything back to the XP machine it works fine.

Any ideas?

Thanks

Terry.

How did you call the stored procedure that causes the error?|||

Hi,

It's called from the Crystal Report object. The function below accepts 2 parameters; 1. The report viewer and 2. (optional) a hashtable with the key/value pairs being the parameter info e.g. Key=@.DT1, Value = 13/01/2006 12:00:01 AM

As I mentioned in the original post, this works fine on the XP box but not the Win2003. The are a number of reports; some accept parameters (all of which appear to fail with the same error) and others that don't, all of which work as expected. Other than the OS, and SQL as opposed to MSDE the only other differerence I can think of is the copy of VS2005.

Thanks.

Private Function LoadReport(ByVal ReportNameAs String, _ByVal ViewerAs CrystalDecisions.Windows.Forms.CrystalReportViewer, _Optional ByVal ParametersAs Hashtable =Nothing)As Boolean Dim rptAs New ReportDocumentDim pvCollAs New CrystalDecisions.Shared.ParameterValues' Objects used to set the proper database connection informationDim tbCurrentAs CrystalDecisions.CrystalReports.Engine.TableDim tliCurrentAs CrystalDecisions.Shared.TableLogOnInfoDim pdValAs New CrystalDecisions.Shared.ParameterDiscreteValueTry Dim appPathAs String = Application.ExecutablePath appPath = appPath.Substring(0, appPath.LastIndexOf("\") + 1)Dim fileExistsAs Boolean = false fileExists = My.Computer.FileSystem.FileExists(appPath &"Reports\" & ReportName)If fileExistsThen rpt.Load(appPath &"Reports\" & ReportName)Else MsgBox("Cannot locate report file", MsgBoxStyle.Critical,"Load Error")     pvColl =Nothing    pdVal =NothingExit Function End If For Each tbCurrentIn rpt.Database.Tables tliCurrent = tbCurrent.LogOnInfoWith tliCurrent.ConnectionInfo .ServerName = ServerName .UserID ="" .Password ="" .DatabaseName = My.Settings.DBName.ToStringEnd With tbCurrent.ApplyLogOnInfo(tliCurrent)Next tbCurrentIf Not IsNothing(Parameters)Then Dim myEnumAs IDictionaryEnumerator = Parameters.GetEnumeratorWhile myEnum.MoveNext pvColl.Clear() pdVal.Value = myEnum.Value pvColl.Add(pdVal) rpt.DataDefinition.ParameterFields(myEnum.Key).ApplyCurrentValues(pvColl)End While End If Viewer.ReportSource = rptCatch ExpAs LoadSaveReportException MsgBox("Report " & ReportName &" not found.", _ MsgBoxStyle.Critical,"Load Report Error")Catch ExpAs Exception MsgBox(Exp.Message, MsgBoxStyle.Critical,"General Error")Finally pvColl =Nothing pdVal =Nothing End Try End Function

|||

Thanks for the help. The problem is a crystal reports one and is detailed here

http://support.businessobjects.com/library/kbase/articles/c2017367.asp