Download MSDE 2000

Unlike SQL Server, MSDE did not ship with its own Enterprise Manager tool. Developers and users of MSDE applications lack the tools necessary for database design, management, and administration.

Downloading MSDE

Download MSDE 2000 from Microsoft

 

Database size

MSDE databases are limited to 2 gigabytes (GB) of data. This limit is per database, and not per server, so a single MSDE server can support multiple MSDE databases, each containing up to the 2 GB limit. If you anticipate that your database is currently or will grow beyond 2 GB, consider upgrading to Microsoft SQL Server™ 2000 Standard Edition for a more scalable database platform on which to build.

Multiple users

MSDE is tuned for desktop and shared solutions where there are fewer than five concurrent workloads hitting the database at any one time (only five concurrent batch workloads or 25 concurrent connections for websites.)  If your solution needs to support more than this number of concurrent batches, you may need to migrate to SQL Server or SQL Server Enterprise editions for optimal performance at this higher level of scalability.

MSDE Replication

MSDE can participate in most types of replication with either a centralized SQL Server or another MSDE. Specifically, MSDE can participate fully in snapshot and merge replication, but only as a subscriber in transactional replication (MSDE cannot be a publisher in transactional replication.) Also note that a per-set Client-Access License (CAL), or processor license is required if MSDE is participating in any type of interaction with a SQL Server, including replication. A CAL or processor license is not required if MSDE is replicating with another MSDE.

MSDE Benefits

MSDE 2000 is a redistributable version of SQL Server 2000. It is a database engine provided by Microsoft that is based on the core SQL Server technology and supports single- and dual-processor desktop computers. In other words, MSDE 2000 is a scaled down copy of SQL Server. MSDE 2000 was introduced to provide application developers a database engine that is more powerful than the Jet engine and at the same time expandable to SQL Server. It is ideal for client applications requiring an embedded database and websites serving up to 25 concurrent users.

The common technology base shared between SQL Server and MSDE 2000 enables developers to build applications that can scale seamlessly from desktop solutions to multiprocessor enterprise clusters.

As an alternative to the Jet engine, MSDE 2000 provides a cost-effective option for developers who need a database server that can be easily distributed and installed. Because it is fully compatible with SQL Server, developers can easily target both SQL Server and MSDE database objects with the same application code base. This provides a seamless upgrade path for MSDE database instances to SQL Server if an application grows beyond the storage and scalability that a MSDE can provide.

Multi-server, multi-transaction operations

Multiple-transaction processes across servers cannot include any machines running MSDE. These advanced operations cannot involve any MSDE databases. MSDE databases cannot request services from a SQL Server without a per seat SQL Server CAL (Client Access License) or processor license.

MSDE and under Windows 95, 98 and Me

MSDE cannot use integrated security features of Microsoft Windows NT® when operating on a Microsoft Windows® 95, Windows 98 or Windows Me platform. Also, MSDE cannot be started remotely, and some third-party network protocols are not supported.

SQL Server Express: Management Studio, Downloads, & Limitations

SQL Server 2005 Express Edition is a scaled down version of Microsoft SQL Server 2005. Although the database server is limited in enterprise features, most of the limitations do not affect the database performance for what it is meant to do.

Performance limitations in SQL Server Express

The SQL engine of SQL Server Express supports 1 CPU, 1 GB RAM and a 4 GB database size. This distinction gives SQL Server Express well defined cut-off points to differentiate it from other SQL Server 2005 editions. Unlike MSDE, SQL Server Express eliminates the confusion created by the workload governor.

1 CPU: SQL Server Express can install and run on multiprocessor machines, but only a single CPU is used at any time. This limitation prevents the use of parallel query execution in SQL Server Express.

1 GB RAM: The 1 GB RAM limit is the memory limit available for the buffer pool. The buffer pool is used to store data pages and other information. However, memory needed to keep track of connections, locks, etc. is not counted toward the buffer pool limit. It is therefore possible that the server will use more than 1 GB in total, but it will never use more than 1GB for the buffer pool. This limitation prevents the use of Address Windowing Extensions (AWE) with SQL Server Express.

4 GB Database Size: The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server. There are some minor changes to the startup of SQL Server Express. User databases are not automatically started, and DTC is not automatically initialized. For the user experience, though, there should be no difference other than a faster startup. Applications planning to use SQL Server Express are recommended to keep these changes in mind when designing their applications.

SQL Server Management Studio Express (SSMSE)

SQL Server Express does not ship with any management tools. However, you can download basic management tools from Microsoft.

Limitation in enterprise features

The following SQL Server 2005 enterprise level features are not available in SQL Server 2005 Express Edition:

  • Analysis Services (both OLAP and Data Mining)
  • Integration Services (DTS successor)
  • Notification Services
  • Report Builder (although Reporting Services is included)
  • SQL Agent
  • Database Tuning Advisor
  • Full-text search
  • Log shipping

Enterprise availability limitations

Unlike other editions of SQL Server 2005, the Express edition does not support Fail-over Clustering or Database Mirroring.

Database Mirroring: Database Mirroring extends log shipping capabilities and enhances availability of SQL Server systems by providing automatic fail-over to a standby server.

Fail-over Clustering: Fail-over clustering is the ultimate fail-over mechanism SQL Server can provide. A SQL Server node in a cluster of nodes sharing one disk array can fail-over to another node without affecting the availability of the server cluster. The disk array where databases reside is central to the SQL Server cluster.

Networking support in SQL Server 2005 Express

Only the shared memory on the local machine is accessible by default for SQL Server Express, although the user can explicitly turn on other supported protocols such as TCP/IP and Named Pipes. VIA and HTTP protocols are not supported in SQL Server Express. With only shared memory available by default, connections from a remote machine to SQL Server Express will fail unless the networking is turned on. To turn networking on, Use SQL Server Configuration Manager to enable relevant protocols and start SQL Browser.

SQL Browser is a new service in SQL Server 2005 that is used to identify the ports that named instances listen on. Since shared memory does not use it, this service is turned off in SQL Server Express by default. This means that the user will have to start this service so that network access can work.

Note One interesting fact is that SQL Browser listens on UDP port 1434. However, pre-SP3 versions of SQL Server 2000 holding port UDP 1434 may result in failure of SQL Browser name resolution, since they may refuse to give up the port. The workaround is to upgrade all SQL Server 2000/MSDE instances on the machine to SP3 or higher.

SQL Server Express instances

Multiple SQL Server 2005 Express installations can coexist on the same machine along with other installations of SQL Server 2000, SQL Server 2005, or Microsoft Desktop Engine (MSDE). In general, it is best that SQL Server 2000 instances be upgraded to SP3a or higher. The maximum limit to the number of SQL instances is 50 on the same machine. These instances must be uniquely named for the purpose of identifying them.

SQL Server Express by default installs as a named instance called SQLEXPRESS. This particular instance is to be shared among multiple applications and application vendors. We recommend that you use this instance unless your application has special configuration needs. Some configuration needs, such as the requirement of the Secure Socket Layer (SSL) authentication, affect the installation as a whole and hence need a separate named instance. In all other cases, the shared instance should suffice. Another advantage of using the shared instance is that the application vendors need not worry about installing SQL Server Express along with the application, which simplifies application installation.

Software requirements

Microsoft Internet Explorer 6.0 SP1 or higher

Microsoft .NET Framework 2.0

Operating systems

Microsoft Windows 2000 SP4 Professional

Microsoft Windows 2000 SP4 Server

Microsoft Windows 2000 SP4 Advanced

Microsoft Windows 2000 SP4 Data Center

Microsoft Windows XP SP1 Professional or higher

Microsoft Windows 2003 Server or higher

Microsoft Windows 2003 Enterprise or higher

Microsoft Windows 2003 Data Center or higher

Microsoft Windows Small Business Server 2003 Standard or higher

Microsoft Windows Small Business Server 2003 Premium or higher

 

Sources include the Microsoft website

Client Network Utility: Configure MSDE protocols

The Client Network Utility is a configuration tool that tells ADO which network protocols to use when connecting to SQL Server and MSDE. The utility can be very useful especially if the network connectivity is limited to certain protocols, like TCP/IP when connecting to the Internet.

SQL Server installation, creates a shortcut for the Client Network Utility in the Microsoft SQL Server group in the Start menu. The MSDE installation on the other hand, does not create the shortcut leading many to believe that the Client Network Utility does not ship with MSDE. As a matter of fact, The utility is part of the Microsoft Data Access Components that ship with Windows and can be downloaded from the Microsoft Website.

You can use The Client Network Utility to configure the protocols you wish to support. You can also create aliases that can act like server names.

To open the Client Network Utility:

Click on Start->Run and type cliconfg.exe then click OK.

 

Verify SQL Server and MSDE Installation

To verify whether SQL Server or MSDE is installed on your computer, follow these instructions:

  1. Right-click the My Computer icon.
  2. Select Manage.
  3. Double-click Services and Applications.
  4. Double-click Services.

If MSSQLSERVER is in the list of services, the default instance of SQL Server or MSDE is installed on the computer. Other instances may exist, in which case they will be listed as MSSQL$****, where the asterisks indicate the name of the instance.

Convert Microsoft Access databases to SQL Server or MSDE

SQL Server and MSDE provide a far more reliable database engine that the Jet engine provided with Microsoft Access.

The Upsizing Wizard provided from Microsoft is the easiest way to convert an Access database to SQL Server or MSDE. The Upsizing Wizard preserves database structure, including data, indexes, and default settings, and automatically converts Access validation rules and default settings to the appropriate SQL Server equivalents. The Upsizing Wizard also maintains table relationships and referential integrity after you upsize.
for more information on the Upsizing Wizard and step by step instructions on using it to convert your Access database to SQL Server or MSDE, visit http://support.microsoft.com/kb/q237980/.