SCD with SQL Server

Configuring SCD with SQL Server

This is a technical article for system administrators who require to install SCD with MS SQL Server. It assumes the reader is familiar with the basic concepts of client-server applications and can follow Microsoft instructions on installing MSDE.

Pre-requisites

SQL Server

You need to provide your own copy of SQL Server or its free equivalent, MSDE. The latter can be downloaded from Microsoft site. Please read through the installation notes provided by Microsoft.

When installing MSDE, please note that by default only programs run on the same computer as MSDE can connect to the database. To enable SCD workstations to connect to your MSDE database, specify parameter DISABLENETWORKPROTOCOLS=0 during the installation.

We recommend using Windows authentication (without SQL Server user name and password) where possible. If your workstations may belong to different domains or are not arranged into a domain, you must specify user name and password and add parameter SECURITYMODE=SQL during the installation

SCD

You need to include module "Upgrade to SQL Server" into your tailored configuration.

You can choose to run SCD for a while with Access, accumulate a database and then upgrade it to SQL Server. The program will automatically copy all the data into the new database.

Installing SCD with SQL Server

1. Configure and download your database as usual. Make sure to include "Upgrade to SQL Server" add-on. The Configurator will send you an Access database tailored to your requirements.
2. Start SCD and open your database. Go to program's Data Exchange department and run function "Upload to SQL Server". The system will prompt for details of the SQL Server and the database:

sdf

3. Fill in the details. If you have Enterprise Manager installed, we recommend creating a database with Enterprise Manager and specifying the name of that database. Without Enterprise Manager you can specify the name of a new database - the program will automatically create one with default settings. If you specify an existing database with some data in it, the tables with names matching to SCD will be dropped to be replaced with the table structure of your SCD database.
   If your SQL server or MSDE is setup to use Windows integrated security (default mode in MSDE), leave the login and password fields blank.
4. Press Run when ready. The program will script and transfer the database structure, menus and will copy all data from its database.
5. Open file C:\Program Files\Simply Contacts Database\SCD.INI and amend the line starting with "DATABASE=" to point to the new database in SQL Server:



The DATABASE line must include the following elements separated by semicolon:

• ODBC - must be the first in the line. Indicates that connection will be made via ODBC to a database server (rather than to an Access file)
• DSN= - indicates DSN-less connection, ie the one where Driver, Server and Database are nominated directly in the connection string.
• Driver={SQL Server} - indicates that connection is made to a SQL Server
• Server=<servername> - specifies the name or address of the server. Same as parameter "Database Server name or address" in function "Upload to SQL Server".
• DATABASE=<database name>. Same as parameter "Database name" in function "Upload to SQL Server".

If you are not using Windows integrated security, then you also need to specify login and password using the following parameters:

• UID=<database login>
• PWD=<database password>

For example, the database line could look as follows:

DATABASE=ODBC;Driver={SQL Server};DSN=;Server=MSSQL05;DATABASE=SCD03;UID=user;PWD=pass

6. Copy file SCD.INI to all workstations running SCD or contact us for a custom installation procedure incorporating your settings.