,NOTE: We currently recommend SQL Server Express instead of MSDE as a free alternative to SQL Server.
1) Introduction
In other articles, we have discussed the technical advantages of using database managing systems, known simply as Databases. Among other advantages, we have:
- information access control
- optimized queries
- data security
- support to the network
- support to procedures
- storage capacity
- support to transactions
We commented on these features in previous articles; when we analyze these advantages, it becomes clear that this is the ideal solution for most automation and supervision systems that generates data requiring storage and regular updates.
On the other hand, a serious obstacle for propagating database server systems is their costs. Among them, we have:
- Software copies: although many of the companies distributing these servers usually offer packs that suited for each client’s profile, the initial cost in authorizing software can be too high; you can see that when you compare their costs to MS Access, Elipse E3’s default file format, for example.
- Employees training: not every company has employees that are trained for servers maintenance and management tasks in their staff .
- Investment in servers: depending on the selected database, there needs to be a high investment in dedicated server machines using a differentiated hardware, such as redundant disks and SCSI interfaces; these usually run under operational systems, like Unix’s several versions, among others.
As seen before, Microsoft SQL Server is considered one of the simpler, easier databases available in the market. In order to save money on your project, you can use MSDE, a free version of Microsoft’s software. MSDE has limited database size and limited amount of tools, but meets most part of the supervisory projects’ needs.
2) Software and tools: installation and configuration
MSDE, a free alternative to MS SQL Server, is available at the Elipse E3’s installation CD, and can also be downloaded from Microsoft’s site. Its basic configuration has no database setting tools, just some basic programs, most of them with DOS interface.
After the software has been installed, the first step is to configure the database so that Elipse E3 can access it. To do so, there are at least three possibilities: using the tool SQL Enterprise Manager (enclosed in MS SQL Server complete installation); using Web Data Administrator; or using the software OSQL (enclosed in MSDE basic installation).
SQL Enterprise Manager
This is the software used by MS SQL Server to manage databases. It allows creating or altering users’ accounts, configuring users authentication mode, including new databases, etc. For further analysis on this software, check the documentation on MS SQL Server used with Elipse E3 at Elipse’s website. When installing it, the software MS SQL Server comes along, and the option to install this software’s client software should be enough to install Enterprise Manager.
For further information regarding this software’s usage and installation, see the documentation previously referred.
Web Data Administrator
The Web Data Administrator is a tool which allows configuring MSDE via a very simple Internet interface. There are restrictions regarding the use of this tool because the software must be downloaded from the Internet, and it is required that it operates alongside Microsoft .Net Framework 1.1 (which must also be downloaded from the site Microsoft Windows Update). In addition, IIS (Windows Internet server) must be installed in the machine where E3 will run, which is not always the case.
OSQL
The software OSQL is a program that runs via command line. Before using it, it is necessary to perform some configurations at Windows Registry.
Settings
To configure MSDE via software OSQL, firstly you will need to install MSDE. Next, you will need to change how the database authenticates users at Windows Registry.
To do so, run the command regedit on the command line at Windows Start menu. Then, look for the key LoginMode, at HKEY_LOCALMACHINE\SOFTWARE\Microsoft \MSSQLServer\MSSQLServer. Alter its value to 2 (which corresponds to authentication via SQL Server) and then go to the key DefaultLogin to alter its value to sa. MSDE considers DefaultLogin as default user login, and does not add any password to this login. You can implement any additional security measures for the database with Enterprise Manager or OSQL (see above) via SQL commands.
Execution
Soon after setting up authentication mode and default login, start a command line session (type “cmd” on Windows Start menu, at Run). Next, go to the folder where MSDE is installed. To do so, execute the commands listed below:
- Go to Start menu and select the option Run.
- Type the command cmd.
- A command window is then open. On this window, go to MSDE’s Bin folder. To do so, run the command cd\, and next type the command: cd arquivos de programas\microsoft SQL Server\80\Tools\Bin
- After that, run the command: OSQL -U as -q “Create database E3Database”
- You will need a password to log in; then, press [Enter] (to keep the password field blank).
- Finally, a message is displayed informing that the database has been created. A second log file is also created, and its message displayed.
- To leave OSQL, press [Ctrl]+C.
From this point on, the Database is ready to be used by Elipse E3, according to the necessary configurations to access an SQL Server database. The parameters necessary to access the newly created database, as well as a small list of possible errors and their respective solutions, can be seen in the previously mentioned document on SQL Server database usage at Elipse E3.
3) Conclusion
The choice of a database depends on technical criteria, and can be restricted due to the cost of implementing the project. With MSDE, the free alternative to MS SQL Server discussed in this article, you can eliminate the problems of database installation cost.
Notice that the limitation on management tools, as well as the database’s total capacity, can make the use of this software to substitute the complete, unrestricted software MS SQL Server prohibitive. However, as an alternative with no cost to Access, this system is highly recommended.
