Difference between MSDE and sqlserver 2000?

Hi,

MSDE is a royalty free, redistributable and cut short version of the giant SQL SERVER database. It is primarily provided as a low cost option for developers who need a database server, which can easily be shipped and installed. It can serve as a good alternative for Microsoft Access database as it overcomes quite a few problems which Access has.

Below is a complete list, which can give you a good idea of the differences:

Size of database: Microsoft Access and MSDE have a limitation of 2GB while SQL SERVER has 1,048,516 TB1.
Performance degrades in MSDE 2000 when maximum number of concurrent operations goes above 8 or is equal to 8. It does not mean that you cannot have more than eight concurrent operations but the performance degrades. Eight-connection performance degradation is implemented by using SQL SERVER 2000 workload governor (we will be looking into more detail of how it works). As compared to SQL SERVER 2000, you can have 32,767 concurrent connections.
MSDE does not provide OLAP and Data warehousing capabilities.
MSDE does not have support facility for SQL mail.
MSDE 2000 does not have GUI administrative tool such as enterprise manager, Query analyzer or Profiler. But there are roundabout ways by which you can manage MSDE 2000:

Old command line utility OSQL.EXE
VS.NET IDE Server Explorer: Inside VS.NET IDE, you have a functionality which can give you a nice GUI administrative tool to manage IDE.
SQL SERVER WEB Data administrator installs a web based GUI which you can use to manage your database.
For any details refer here.

SQL-DMO objects can be used to build your custom UI
There are many third party tools, which provide administrative capability GUI, which is out of scope of the book as it is only meant for interview questions.
MSDE does not support Full text search.

Summarizing: - There are two major differences: The first is the size limitation (2 GB) of the database and second is the concurrent connections (eight concurrent connections) which are limited by using the workload governor. During an interview, this answer will suffice if the interviewer is really testing your knowledge.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Accepted answer
Hi,

MSDE is a royalty free, redistributable and cut short version of the giant SQL SERVER database. It is primarily provided as a low cost option for developers who need a database server, which can easily be shipped and installed. It can serve as a good alternative for Microsoft Access database as it overcomes quite a few problems which Access has.
Below is a complete list, which can give you a good idea of the differences:
- Size of database: Microsoft Access and MSDE have a limitation of 2GB while SQL SERVER has 1,048,516 TB1.
- Performance degrades in MSDE 2000 when maximum number of concurrent operations goes above 8 or is equal to 8. It does not mean that you cannot have more than eight concurrent operations but the performance degrades. Eight-connection performance degradation is implemented by using SQL SERVER 2000 workload governor (we will be looking into more detail of how it works). As compared to SQL SERVER 2000, you can have 32,767 concurrent connections.
- MSDE does not provide OLAP and Data warehousing capabilities.
- MSDE does not have support facility for SQL mail.
- MSDE 2000 does not have GUI administrative tool such as enterprise manager, Query analyzer or Profiler. But there are roundabout ways by which you can manage MSDE 2000:
- SQL-DMO objects can be used to build your custom UI
- There are many third party tools, which provide administrative capability GUI, which is out of scope of the book as it is only meant for interview questions.
- MSDE does not support Full text search.
Summarizing: - There are two major differences: The first is the size limitation (2 GB) of the database and second is the concurrent connections (eight concurrent connections) which are limited by using the workload governor. During an interview, this answer will suffice if the interviewer is really testing your knowledge.
0