ColdFusion Muse

MSDE - the Little DB Engine That Could

You may already know about MSDE. It's a full-featured SQL server with a restriction for file size (2Gig or 4Gig) and number of connections (25). It's a great choice if you have a home business, a small intranet or a dev server and you don't want to shell out 5 grand per CPU for the full SQL server. Getting it correctly installed for use with a Web Application is another matter.

By default MSDE "wants" to be installed using integrated security and no "network library". It wants to play nice in a windows world. Also, If you download the application and just double click on it to install you will get a message about not having an SA password. You will have to either figure out the switches to the command line or use the INI file to get it going. I recommend the INI file. Here's the scoop. Copy these settings file into an ini file (let's call it "setup.ini" for consistency) in the same directory as your setup file.

Next go to the command line and navigate to the directory containing your setup file. Type the following.

MSE will install in SQL "mixed" security mode with your sa password. Network protocols will be enabled by default. There are a slew of other settings including logging that you may want to try. Check them out on MSDN. If you are like me however, you have a few custom uses for this little gem so you won't need most of the switches. One more thing you should do (just to save yourself some aggravation) is to open Enterprise Manager, right click on the database and go to "properties" and then click on the "network settings" button in the dialog box. Select "named pipes" and remove it to the "disabled" side, then restart the server. This will leave you with just TCP/IP and make any connections you try a lot easier to debug.