SQL Server

Abstract

By default, Capable 21C is shipped with a database in a Microsoft Access (tm) format.  However, it is possible to migrate this data onto a SQL Server for improved stability and performance in larger networks.  This knowledgebase article describes the considerations and challenges in performing this migration.  Some of the information in this document is of a more technical nature.

Underlying challenge

A SQL Server is a workstation (or server) that has extra software installed on it to provide database services to other workstations in the network.  The extra software is available from Microsoft, and imposes significant demands on the workstation (or server) where it is installed.

Note that Capable Software Pty Ltd does not provide technical support on the installation, maintenance and administration of a SQL Server.  Offices choosing to implement this kind of solution MUST source local technical support to maintain and administer their SQL Server.  These offices should also seek professional advice on appropriate hardware specifications for the workstation (or server) that will operate the SQL Server software (see end of this document for recommendations).

There are several different versions of SQL Server available from Microsoft.  These include:

• SQL Server 2005.  This product must be purchased from Microsoft, and licences must be purchased for all concurrent client PCs.  It may also be necessary to purchase further licences for multi-CPU servers.  This version of SQL Server has known performance issues. It is supported, but not recommended.
• SQL Express.  This product is free from Microsoft.  It has limited storage and performance capabilities.  It can be downloaded from the Microsoft web site.  Whilst these are also free older versions of SQL Server (eg MSDE, SQL Server 2000, etc).  These versions are not supported by Capable Software Pty Ltd.  Both SQL Express 2008 R2 and SQL Express 2012 are supported.
• SQL Compact Edition.  This product is free from Microsoft.  It is intended for use on a single standalone workstation, and is not suitable for a network environment.

Once a version of SQL Server has been selected, obtained and installed, there are several steps that must be performed to migrate the Capable 21C data onto the SQL Server, and to configure the SQL Server to accept incoming connections.

The information provided in the resolution section below is intended to be used by technicians with a working understanding of SQL Server and how to configure it.

Resolution

The configuration and migration steps fall into five main categories:

1. Preparing the server
2. Preparing the workstations
3. Migrating the data
4. Finalising workstation configuration
5. Finalising the server configuration

Detailed Information

To prepare the server, the following configuration settings need to be applied:

1. Ensure that the Guest account is enabled on the server if it is operating in a workgroup environment.  This step is not necessary if the SQL Server is operating in a domain.
2. Ensure that the SQL Server is configured to use Windows Authentication security.
3. Ensure that the SQL Server is configured such that remote connections are allowed.
4. If the SQL Server resides in a workgroup, ensure that the SQL Server has a Login created for the Guest account on that workstation/server.  If the SQL Server resides in a domain, Logins should be created for every domain user (or a group containing those domain users) that will access the SQL Server.
5. Ensure that the SQL Server is configured to allow both Named Pipes and TCP/IP connections via the SQL Server Configuration Manager.
6. If the SQL Server has a Firewall (including, possibly, the inbuilt Windows XP/Windows Vista Firewall), ensure that the Firewall is configured to permit the SQLSERVR.EXE and SQLBROWSER.EXE applications to access the network.
7. Ensure that the SQL Browser service is started on the server, and that its start mode is configured to be Automatic.

To prepare the workstations, the following configuration settings need to be applied:

1. Each workstation should have the latest version of MDAC installed from the Microsoft website.
2. The cliconfg.exe program under \Windows\System32 should be run to add Named Pipes (first) and TCP/IP (second) as enabled connection types.

To migrate the data:

1. Start Capable 21C on any of the workstations.
2. Go to File|Migrate Data to SQL Server.
3. Contact Capable Software Pty Ltd for a conversion password.  This password changes every day.
4. Proceed through the migration prompts.  When asked for the server name, it may be necessary to supply the named instance of the SQL Server as well as the workstation/server name (or IP Address).  For example, the name of the SQL Server may be SERVER\SQLEXPRESS for a SQL Express Implementation.  In workgroup environments, there are sometimes problems with DNS mappings, so Capable Software Pty Ltd recommends the use of fixed IP Addresses to explicitly map to the SQL Server.  For example, the SQL Server might be named 192.168.0.1\SQLEXPRESS.
5. Allow the migration to proceed.  Note that the data migration will take approximately 1 hour for every 50 Mbs of data in the original Microsoft Access database.

To finalise the configuration of the workstations:

1. The Capable.INI file on the workstation that was used to perform the migration will have been changed by the migration process to indicate the SQL Server details.  These same details will need to be copied into the Capable.INI files of the other workstations.  In particular, the DBTYPE setting must be SQL, the DBSERVER setting must match the SQL Server name (shown above), and the DENYADO flag should be set to FALSE.
2. Note that the DATABASE setting in the Capable.INI file no longer indicates the location of the Capable 21C database, but it does still identify the location of associated documents and attachments.  The shared folder where these files resided prior to database migration must remain shared after the migration is complete.

To finalise the configuration of the SQL Server:

1. Using the SQL Server Management Studio, ensure that the Guest account that was created earlier (or the domain accounts/groups) are set to have db_reader, db_writer and db_owner privileges to the newly created Capable21CData database.
2. Configure the SQL Server to take backups of the database at appropriate times.  Capable Software Pty Ltd recommends that the database is backed up at least twice per day, to different files, and that one of these files is copied to an external media (eg a memory stick) and taken off site daily.
3. Tune the SQL Server appropriately to ensure optimum performance.

Special notes for Windows Vista implementations

Note that Capable 21C has been tested against Windows Vista in both a SQL Server environment and a Microsoft Access environment.  However, some aspects of Windows Vista may impair a technician's ability to complete the steps detailed above.  In particular, all applications started on a Windows Vista machine should be 'Run As Administrator'.  This includes the SQL Server Management Studio and SQL Server Configuration Manager.  Capable 21C has an embedded manifest that allows it to start with the highest permissions of the operator logged onto the Vista workstation, so the Run As Administrator option is not required for Capable 21C itself.

Refer to Microsoft Technical Documentation for more information about User Access Control in Windows Vista.

Special notes for Small Business Server implementations

Occasionally, special Windows Components for Remote Database Access are not installed on Windows Workstations that are built for a Small Business Server Implementation.  This can be detected if Capable 21C reports an error related to 'Cannot load MSRDO20.dll' when starting.

If this problem occurs, download MSRDO20.ZIP, unzip into the Windows\System32 folder, and then register the MSRDO20.dll using RegSvr32.exe

Hardware Recommendations for SQL Server implementations

Capable Software Pty Ltd recommends that the SQL Server workstation (or server) should have the following specifications:

• Intel Pentium 4 Core 2 Duo or Quad Core processor.
• A minimum of 2Gb of RAM.  4Gb of RAM is recommended if the machine is used to operate Capable 21C as well as SQL Server, or if Windows Vista is installed, or if more than 10 workstations connect to it.  Note that SQL EXPRESS is limited in that it cannot exploit 4Gb of RAM.
• Raid 1/0 hard drives.  5 RAID drives are recommended, and should be striped to optimise performance.
• Gigabit network interface cards, supported by a Gigabit switch, and Gigabit NICs in the workstations.

Notes regarding images and shared folder

Note that under a MS Access implementation of Capable 21C, the MS Access database is typically located in a shared folder.  Beneath this shared folder are three or four sub-folders that contain the image attachments, audio attachments and supporting document files for Capable 21C.  Capable 21C itself locates these files via the DATABASE setting in the .INI file.

In a SQL Server implementation, Capable 21C continues to locate the supporting files in the same manner.  Whilst the DATABASE entry in the .INI file is no longer relevant for locating the database itself, it is still relevant for locating the supporting files.

Capable Software Pty Ltd recommends the following configuration for a SQL Server Implementation:

• The PC (or Server) that runs the SQL Server software can also be used as a fileserver for the images, audio and supporting files, though another PC (or Server) can be used in this capacity for extra performance.
• The folder where the original database was stored (and shared) for an MS Access implementation should be located on the chosen server.  In many cases, there will be no need to move the folder at all.
• The folder should be shared in the same manner as was required for an MS Access implementation of Capable 21C.
• If the folder has been moved and the shared folder is named differently (eg a different share name or a different server name), then the Capable.INI file should be updated so that the DATABASE line indicates the new location.
• Note that Capable Software Pty Ltd does not endorse the use of mapped drive letters, and recommends the use of fixed IP addresses in preference to workstation/server names.  For example, DATABASE=\\192.168.0.1\Share is the preferred format.

Copyright 2008 Capable Software Pty Ltd

Training Sessions

Fundamentals Training Modules

Intermediate Training Modules

Advanced Training Modules

Other Resources

Return to Capable Software home

Contact Technical Support