MSDE Advice Please

Hi I am planning a desktop application I want to develop in C# and I was thinking about using MSDE instead of an Access database. I have never used of even looked into MSDE before. Do you simply create a database on SQL Server add your tables and Sp's, setup your conection and program against this as normal, then with the install of the software setup MSDE and install the tables and SP's etc into the instance of MSDE?

TIAFrom my experience MSDE has some limitations (db size, number of connections, etc.) but it is real database engine with all features. You can manage it by ISQL or OSQL (if you have installed somewhere in NET enterprise manager - you can use it for MSDE).|||Hi,

The database size is limited to 2GB. Once the DB gets to this size MSDE will not allow the program to use it. You can still go into the DB with Enterprise Manager or Query Analyzer or shareware tools. But nothing will run.

As for connections I beleive the limit is 30.

As for your install.

Create the database on the SQL server and write your program against it. Then back the database up when you are finished debugging it. Include it with your install packaging (I am not a developer so I won't tell you how).

When the install process starts have the MSDE install first (See MS Knowledge Base or google "MSDE install"). Then restore the database to the MSDE ( I suggest osql). Then create your ODBC connection. And finish with your program.

Have fun !|||Thanks for all your comments and advise :)

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 & indexing service

I have written an SQL Server 2000 Application using the
indexing service for full-text indexing a table. Try to
use MSDE instead of Sql Server
(SQLDMO) oSQLServer.FullTextService.isFullTextInstalled
returns always false
Has anyone an idea what to do?
Thanks Alex
Hi
It is not installed on MSDE. You need to use a full edition of SQL Server
From BOL:
"The Microsoft Search service itself is not installed during an installation
of SQL Server 2000 Desktop Engine (MSDE 2000). While this means that the
Microsoft Search service is not installed on Microsoft Windows 95, Windows
98, Windows NT Workstation, or Windows 2000 Professional clients, these
clients can make use of the service when connected to an instance of SQL
Server 2000 Standard Edition, SQL Server 2000 Developer Edition, or SQL
Server 2000 Enterprise Edition."
(Index: Keyword: MSDE 2000, Full-text Search)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" <anonymous@.discussions.microsoft.com> wrote in message
news:03ce01c4e38a$e7dda0b0$a301280a@.phx.gbl...
> I have written an SQL Server 2000 Application using the
> indexing service for full-text indexing a table. Try to
> use MSDE instead of Sql Server
> (SQLDMO) oSQLServer.FullTextService.isFullTextInstalled
> returns always false
> Has anyone an idea what to do?
> Thanks Alex
>
|||
>--Original Message--
>Hi
>It is not installed on MSDE. You need to use a full
edition of SQL Server
>From BOL:
>"The Microsoft Search service itself is not installed
during an installation
>of SQL Server 2000 Desktop Engine (MSDE 2000). While this
means that the
>Microsoft Search service is not installed on Microsoft
Windows 95, Windows
>98, Windows NT Workstation, or Windows 2000 Professional
clients, these
>clients can make use of the service when connected to an
instance of SQL
>Server 2000 Standard Edition, SQL Server 2000 Developer
Edition, or SQL
>Server 2000 Enterprise Edition."
>(Index: Keyword: MSDE 2000, Full-text Search)
>Regards
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"alex" <anonymous@.discussions.microsoft.com> wrote in
message
>news:03ce01c4e38a$e7dda0b0$a301280a@.phx.gbl...
>
>.
>
|||Dear Mike,
thanks a lot for your reply. I havn't found any
information about this topic.
Alex
>--Original Message--
>Hi
>It is not installed on MSDE. You need to use a full
edition of SQL Server
>From BOL:
>"The Microsoft Search service itself is not installed
during an installation
>of SQL Server 2000 Desktop Engine (MSDE 2000). While this
means that the
>Microsoft Search service is not installed on Microsoft
Windows 95, Windows
>98, Windows NT Workstation, or Windows 2000 Professional
clients, these
>clients can make use of the service when connected to an
instance of SQL
>Server 2000 Standard Edition, SQL Server 2000 Developer
Edition, or SQL
>Server 2000 Enterprise Edition."
>(Index: Keyword: MSDE 2000, Full-text Search)
>Regards
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"alex" <anonymous@.discussions.microsoft.com> wrote in
message
>news:03ce01c4e38a$e7dda0b0$a301280a@.phx.gbl...
>
>.
>