Back to articles list

How to install and configure MSDE (10/28/2005)
Long years after MSDE (Microsoft SQL Server Desktop Engine) release still I see some professional developers, use Microsoft Access or even My SQL for their data storage in their applications because it is FREE. I came to know most of the people don’t use MSDE because they don’t know such a product exists or they don’t know how to install and configure it. It is not a technical article about MSDE. I am simply explaining how install MSDE as your database server, the client tools for MSDE and finally SQL Server Service Pack 4 for MSDE. This article will cover the default installation and configuration process for MSDE default instance setup. Configuring MSDE as a named instance installation is not much different from SQL Server standard edition or enterprise edition.
  • Why MSDE
    If you are looking for a Database server which (1) is supposed to serve your application on Microsoft Windows platform, (2) is free license (3) can adapt up to 2 GB of data, (4) can adapt to 10 concurrent Windows users or up to 25 to 30 concurrent web users, (5) has excellent performance with (6)full support of basic database programming elements including Views, Stored procedure and functions (7) with full security support as required for a production database, most probably MSDE (Microsoft SQL Server Desktop Engine) is the best choice. I have noticed that not very often the programmers use MSDE and I found the following reasons that why programmers do NOT show that much interest. 
    1. They don't know that such a product exists and even more surprisingly they don't know it is included in their SQL Server 2000 CD and it is also FREE for redistribution.
    2. Installation of MSDE is a bit more than just a double-click on the Setup.exe and clicking next buttons after that.
    3. Client tools do NOT exist for MSDE
    4. They cannot install the service packs.

As a result I decided to cover all those typical complains for MSDE installation, configuration, client tools and service packs. 

  • How to get and install MSDE
    Getting MSDE is quite simple, actually if you have SQL Server 2000 ( Enterprise , Standard, developer edition or even the evaluation copy) CD, just by browsing the CD you will find a folder called MSDE that contains all the installation files for MSDE. If you don't have it, don't worry at all. Smile and click here. It is there for free download but required a bandwidth that can manage around 110 MB of download (40 MB for the MSDE and 80 MB for the SP 4). The download is a self extracting compressed file that you need to extract it before you install it.
    (I assume that the installation files are in "C:\MSDERelA" folder)
    1. Define the command line parameters. There are two very important parameters that you have to define as command line parameters. First is the instancename and second the sa password. You cannot install MSDE release-A without the instancename parameter even if you are installing a default instance of SQL Server. If you don't specify the password for sa , you can have a successful installation but you cannot install the latest service pack unless you change the blank password for sa.
      Here is my command line choice C:\MSDERelA\setup.exe INSTANCENAME="" SAPWD="P@ssw0rd" that makes a default instance installation of MSDE with the sa password set to P@ssw0rd.
    2. Verify and start the MSDE service. Go to Startà Settings à Control Panel à Administrative Tools à Services. You will find the MSSQLSERVER in the services list. Just make sure that the Startup type is set to automatic and then right-click and start the service.
    3. Congratulations. You have successfully installed MSDE.
  • How to get and install Client Tools for MSDE.
    Actually there is no client tool developed by Microsoft specially designed for MSDE. Once you install MSDE, the only client tool that you have is osql.exe. If you want to have Enterprise manager or Query analyzer, you need to borrow it from SQL Server 2000, Standard Edition, Developer Edition, Enterprise Edition or even Evaluation Edition.

    IMPORTANT NOTE: For client tools licensing, look here.
    Simply follow the normal installation of mentioned edition of SQL Server 2000 and go for "Install database server". Follow the default installation process and in the Installation Definition page select Client Tool Only. Once installation is complete open the Enterprise Manager and if you don't see the SQL Server registration just add the registration like any instance of SQL Server and you have the MSDE connected to the client tools and every thing is accessible through Enterprise manager. Of course Northwind and Pubs database are not there. If you need them just go to this link and download and restore the sample databases.
  • How to get and install SP4 for MSDE.
    You can download the service pack 4 from this link ( SP 4). You can simply expand the files and run the setup. Installation is quite straight forward. Remember if you cannot install SP_4 with blank password for sa . If you would like to go for service pack 4 you should know that SP-4 has a complete installation of MSDE inside, so by installing SP-4 you are upgrading your installation of MSDE to MSDE+SP4 so the following command will do the job for you.
    By the way, if you are installing SP3 you can just run the setup.exe without any parameters.
    If you plan to install MSDE Service Pack 4 from the scratch without installing MSDE first, simply run the setup.exe for MSDE+SP4 with the parameters mentioned for normal installation of MSDE. Then install the client tools.

    Since SP4 installation restarts SQL Server service, after installation of SP4 on production environment just make sure that all the services that rely on SQL Server service are restarted.

Back to articles list