From the version D2000 v6.00, release A040419730, D2000 system supports storing the configuration database and archive not only in Sybase SQL Anywhere database, but also in MS SQL 2000 (Microsoft SQL Server 2000) and MSDE 2000 (Microsoft SQL Server 2000 Desktop Engine). MSDE is a modified version of runtime MS SQL server with limited performance, that can be used without any licence fees. It also substitutes runtime Microsoft Access, that can be used without any licence fees, too.
For more information on limiting the MSDE performance visit the following URL addresses: http://www.microsoft.com/sql/msde/productinfo/features.asp and http://msdn.microsoft.com/library/?url=/library/en-us/architec/8_ar_sa2_0ciq.asp?frame=true. Another important limitation is the size of database - 2 GB - important when you design an archive. This limitation does not apply to MS SQL 2000.
Note: In February 2006, we were tested SQL Server 2005 Express Edition database and in August 2010 MS SQL Server 2008 Express edition database which are the successor to MSDE. Unlike MSDE, the database size is limited to 4 GB, maximum memory consumption is 1GB and the databases only use one processor. For further restrictions and information see http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx. Unlike MSDE, the database probably does not implement workload governor (that limits the number of concurrent operations that the database engine can perform). A comparison of SQL Server 2008 Express edition with MSDE is shown at http://msdn2.microsoft.com/ms165672.aspx.
Note: In August 2010 a support for the databases SQL Server 2008, SQL Server 2008 R2 as well as the free editions SQL Server 2008 Express
and SQL Server 2008 R2 Express was added.
SQL Server 2008 Express has a database size limited to 4 GB, maximum memory consumption is 1GB and the database only uses one processor.
For further restrictions and information see
http://www.microsoft.com/sqlserver/2008/en/us/express.aspx.
SQL Server 2008 R2 Express has a database size limited to 10 GB, maximum memory consumption is 1GB and the database only uses one processor.
For further restrictions and information see
http://www.microsoft.com/express/Database.
MSDE 2000 can be installed either from your D2000 installation DVD (the directory MSDE) or downloaded from the URL address www.microsoft.com. The installation is not interactive, you must run the file setup.exe with the following parameters:
SAPWD="any_password" | Password of the user named "sa" (administrator). If you have defined a password other than the password System D2000 when creating the application or adding an archive by means of the D2SMC process, you must enter the password here. |
INSTANCENAME="instance_name" | MSDE instance name, there can be defined up to 16 instances on one computer. If the instance name is defined e.g. D2000, the instance will be visible as MSDE$D2000 in D2SMC. |
TARGETDIR="disk_path" | Directory, where MSDE will be installed in (you must enter slash path "\" at the end of path). In the directory, there will be created the MSSQL$instance_name, e.g. MSSQL$D2000. |
SECURITYMODE=SQL | User will be verified in MSSQL. |
Example of the MSDE 2000 installation:
setup SAPWD="SystemD2000" INSTANCENAME="D2000" TARGETDIR="C:\Program Files\" SECURITYMODE=SQL
Process D2000 Application Manager does not allow to create new engines for MSDE (unlike Sybase), because they are created by the MSDN installation.
When you create a new application, select the item SQL Server in the combo box DB Driver in the dialog box to create a new application. Then you can select an engine. Names of MSDN engines are MSDE$instance_name, e.g. MSDE$D2000. The name of MS SQL 2000 is MSSQLSERVER.
When you create an application using MSDE or MS SQL, then after clicking the OK button in the dialog box:
After creating an application (or archive process) using MSDE or MS SQL, it is not possible to change the engine used by means of D2SMC. If needed, it is possible to do it manually (to change particular DSN, to disconnect the database, to connect to other engine, to create the user dba, to change ownership, etc..., see the below text).
Unlike Sybase SQL Anywhere, it is not possible to copy database files only (e.g. SysCfg.mdf and SysCfg.ldf) when copying an application from one computer (or engine) to another.
The problem lies in the definition of users: the database user (user name) dba who owns all tables, is identified in the MSDE engine by means of the login name dba. This assignment is not based on text name of the user, but on the unique ID (UID). Therefore, after copying and connecting to the database, the user dba is included in the database, but cannot be used, because the MSDE engine does not contain login with required UID. The solution is to delete the user dba (the ownership of his tables have to be passed to another user), create the user again and give the ownership of the tables back to him.
Before copying database files, you must either stop the engine that uses them, or disconnect the database for a while using sp_dboption.
Example: Copying the configuration database of the application Test using the tool osql.exe (text-oriented tool for managing a MS SQL database):
An archive working with MSDE 2000 or MS SQL 2000 does not support operations with depositories and the
archive reorganization.
Archive database backup can be performed in two ways - generation of .dat
file or copying .mdf and .ldf files when the archive database is temporary.
For more information see the topic Start parameters of
processes - the parameter /RS. *
Since the size of an database in MSDE 2000 can be up to 2 GB, the archive displays the report "database
is full" and is to be terminated after it has reached the size.
* Note: When you create a backup and
the .dat file already exists, MSSQL
appends the new backup to the end of the file. Therefore, it is recommended to move, rename or delete the old
.dat file to avoid creating files that are large enough to use up all free
space on a smaller disk.
The following information is not guaranteed and its use is on your own risk.
After the installation, MSDE is available only locally, not through computer network. This feature can be changed as follows:
Engine parameters will be modified after restarting the engine (restarting the service MSSQL$instance_name is sufficient).
Use of operating memory by MSDE and MS SQL Server is described in the article, available on the address http://support.microsoft.com/default.aspx?scid=kb;en-us;321363. Default behaviour causes consumption of almost the whole memory up to 2 GB. Maximum operating memory consumption can be reduced as follows:
Size of allocated memory may affect the speed of database response as well as swapping. Swapping optimization depends on the demands of the particular application.