ARCHIVED
The jncc.defra.gov.uk website has now been archived. For the latest information from JNCC, visit jncc.gov.uk

2 SQL Server / MSDE

 

 
 
 
 

Recorder 6 is a Microsoft SQL Server 2000 system but users who do not wish to buy SQL Server can run the system under MSDE (Microsoft SQL Server 2000 Desktop Engine) as a full license for SQL Server is expensive.  MSDE is the free run-time version of SQL Server 2000 which is supplied on the Recorder 6 install disk.

 

SQL Server 2005 became available sometime ago and Recorder users who have tried it have found that Recorder 6 runs somewhat faster under it.  SQL Server 2005 Express, the free run-time version of SQL Server 2005, will be supplied on the next version of the Recorder 6 install disks instead of MSDE.

 

If you do not have SQL Server or MSDE on your system, a standard installation using a standalone or network installation disk will install MSDE and Recorder 6 version 6.13.2.176.  If you already have a SQL Server instance running you will be able to use it for your installation of Recorder 6 – see the ‘MSDE Settings’ screen shot in the Standalone Installation Guide, or the ‘SQL Server Selection’ screen shot in the Network Guide.  An Instance is a SQL Server database engine, i.e. a number of system and user databases that are administered as a group.  Standalone installations are generally quite straight forward but to run the system on a network users need to ensure that they have access to the necessary expertise to support the use of MSDE/SQL Server.

 

There are some limitations associated with using MSDE, and certain users will require a full copy of SQL Server – see the Network Installation Guide for more information.  The main limitations are the size of the database and the number of concurrent workstations.  Microsoft restricts the size of a MSDE database to 2 GB whereas the maximum size of a SQL Server database is only limited by the hardware used.  Also, performance of Recorder 6 under MSDE will be degraded if there are more than five concurrent workstations on a network installation.

 

The maximum size of an Access 97 database, and hence the Recorder 2002 database, is 1 GB and JNCC have estimated that the system will hold up to about one million observations (records), depending on the richness of the data stored.  A Recorder 6 database is bigger than this before any data is added: the Recorder 6 database in a new installation of version 6.13.2.176 is 1.17 GB.  Recorder 6 running under MSDE will not, however, hold double the number of observations that Recorder 2002 will hold.  In Recorder 2002 the dictionaries (taxon, biotope and admin areas) and the associated index files are held in separate Access 97 databases, nbndict.mdb, Index_Taxon_Group, Name & Synonym.mdb, whereas in Recorder 6 these are in the same database as the observations and locations, etc., and therefore considerably reduce the space for observations and related data when using MSDE.  Transfers from Recorder 2002 to a new MSDE installation of Recorder 6 version 6.10.4.120 without any dictionary upgrades indicate that the capacity of this version is around 700,000 observations.  The capacity of version 6.13.3.182 running under MSDE will be less, mainly due to dictionary upgrades.

 

Using a version 6.13a installation CD users can install Recorder 6 using a SQL Server 2005 Instance.  The MSDE equivalent in SQL Server 2005 is called SQL Server 2005 Express Edition.  It is available as a free download from Microsoft.  If you wish to use SQL Server 2005 Express instead of MSDE you must download and install it then select the SQL Server 2005 Express Instance when installing Recorder 6.  Microsoft restricts the size of a SQL Server 2005 Express database to 4 GB, hence its capacity is much greater than MSDE.  Users running Recorder 6 under SQL Server 2000 are able to convert to running the system under SQL Server 2005.

 

Users are advised to have access to SQL Server tools.  These come with SQL Server 2000, SQL Server 2005 and SQL Server 2005 Express but not with MSDE.  They are needed for certain system functions, particularly managing permissions for network installations, but they can also be used for viewing and querying the Recorder 6 SQL Server database.  The tools provided with SQL Server 2005 Express are in software called SQL Server Management Studio Express which can be downloaded from a Microsoft web-site.  This software can also be used with MSDE.  Microsoft Access can be used to perform certain system functions if SQL Server tools are not available but this option is more complicated.