It is compatible with SqlServer, so one can develop a database application using SqlServer and then deploy it using the desktop engine, or vice versa. It does not include any administration tools. It is not appropriate for applications with large numbers of users, as it is tuned to only handle five connections.
Microsoft is pushing use of MSDE over use of the older JetDatabaseEngine.
See http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp for more information.
MSDE is included with several Microsoft products. Distribution is royalty-free for developers who have an MSDN Universal Subscription, or who use VisualStudioDotNet or Office XP Developer to create their applications. It can be downloaded from the web at http://www.asp.net/webmatrix/download.aspx.