Support of MS SQL 2000, MSDE 2000, SQL Server 2005 Express and SQL Server 2008 Express edition

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 installation

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:

Example of the MSDE 2000 installation:
setup SAPWD="SystemD2000" INSTANCENAME="D2000" TARGETDIR="C:\Program Files\" SECURITYMODE=SQL

Creating applications in D2000 Application Manager

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:

• D2SMC connects to the SQL server - the  database master. If the administrator's (sa) password is other than SystemD2000, the user will be prompted to log on with administrator's password only or with a name and a password of another user that is allowed to connect to the database.
• The databases (configuration, log and archive database) are copied from the directory Templates\MSDE to the application directory.
• The databases are to be registered on the server by using the saved procedure sp_attach.
• If the user dba does not exist, it will be created and granted access to all three databases.
• The ownership of all user tables in the configuration, log and archive database will be changed from the user install to the user dba.

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).

Copying an application

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):

• Connect to the database engine D2000 to the database master on the computer named MyComp:
  osql -S MyComp\D2000 -U sa -P SystemD2000
• Connecting the copied database:
  sp_attach_db 'Test.SysCfg', 'c:\D2000\D2000.V60\Test\SysCfg\SysCfg.mdf', 'c:\D2000\D2000.V60\Test\SysCfg\SysCfg.ldf'
  go
• Create the login dba and configure the access rights in the engine (the rights are required when creating backup of the archive database):
  sp_addlogin 'dba','swx'
  EXEC sp_addsrvrolemember 'dba','dbcreator'
  EXEC sp_addsrvrolemember 'dba','diskadmin'
  go
• Go to the database Test.Syscfg:
  use [Test.Syscfg]
  go
  
• Change the ownership of tables to the user install (warnings may be ignored):
  EXEC sp_changeownerD2000 'install'
  go
• Delete the user dba:
  sp_revokedbaccess 'dba'
  go
• Create the user dba and configure the access right in the database:
  sp_grantdbaccess 'dba','dba'
  go
  sp_addrolemember 'db_owner','dba'
  go
  
• Change the ownership of tables to the user dba (warnings may be ignored):
  sp_changeownerD2000 'dba'
  go

Archive

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.

Note to MSDE network connectivity

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:

1. Using SQL Server Enterprise Manager (part of the MS SQL 2000 installation):
   After connecting to the database server (engine) right-click it and select the item Properties in the popup menu that appears. In the tab General click Network Configuration and enable the protocols Named pipes, TCP/IP or others.
2. Using the tool Regedit:
   Into the item ProtocolList (Multi-string type) enter the text np tcp.
   [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_name\MSSQLServer\SuperSocketNetLib]
   "ProtocolList"=hex(7):6e,00,70,00,00,00,74,00,63,00,70,00,00,00,00,00
   
   Into the item TcpPort (typu String) insert the text 1433 (or other number of the port for engine communication).
   [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_name\MSSQLServer\SuperSocketNetLib\Tcp]
   "TcpPort"="1433"

Engine parameters will be modified after restarting the engine (restarting the service MSSQL$instance_name is sufficient).

Note to operating memory use

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:

• By means of SQL Server Enterprise Manager (component of a MS SQL 2000 installation):
  After connecting to the database server (engine) right-click on it, select the Properties item in the popup menu. In the tab Memory, you can set minimum and maximum use of the memory.
• By means of the text-oriented tool OSQL:
  After connecting to the database server enter the following:
  USE master
  go
  EXEC sp_configure 'show advanced option', '1'
  go
  RECONFIGURE WITH OVERRIDE
  go
  EXEC sp_configure 'max server memory', '32' max. memory size in MegaBytes, in this example - 32MB
  go
  EXEC sp_configure 'min server memory', '8' min. memory size in MegaBytes, in this example - 8MB
  go
  RECONFIGURE WITH OVERRIDE
  go

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.