StarQuest Technical Documents

Installing SQL Server for a Control Database

Last Update: 31 January 2010
Product: StarQuest Data Replicator
Version: 3.x
Article ID: SQV00DR002

Abstract

The StarQuest Data Replicator requires a control database in which to store definitions for sources, destinations, subscriptions, and groups of subscriptions. You can use any of the versions of SQL Server listed in System Requirements for the Data Replicator control database.

General considerations

• If you do not already have a SQL Server installation, you can obtain the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) free of charge from www.microsoft.com/downloads (search for MSDE) to use as a control database for the Data Replicator. Alternatively, you may use the SQL Server 2005/2008 Express Edition as a control database for the Data Replicator. You can obtain SQL Server 2005/2008 Express Edition free of charge from http://www.microsoft.com/sql/editions/express.
• The SQL Server 2005/2008 Express Edition does not include the SQL Server Agent component, which the Data Replicator Manager uses to schedule subscriptions to run automatically. If you use the free SQL Server 2005/2008 Express Edition as a control database for Data Replicator, you can still use the Data Replicator Manager to run subscriptions manually, and you can use other methods, such as Windows Scheduler, to schedule them to run automatically.
• SQL Server 2008 users must install the SQL Distributed Management Objects (SQL-DMO), which are included in the Microsoft SQL Server 2005 Backward Compatibility Components available at the Microsoft Download Center.

Solution

Following are some guidelines for Data Replicator customers who do not already have any edition of SQL Server installed and want to install MSDE 2000 or SQL Server 2005/2008 Express for use as a control database. Refer to the information that Microsoft provides to ensure that your hardware and software meets the minimum requirements and for the detailed installation instructions.

This document also describes how to create a SQL Server ODBC data source for use with the Data Replicator.

Step 1: Install MSDE 2000 or Install SQL Server 2005/2008 Express Edition.

Step 2: Create a SQL Server ODBC Data Source for Use with SQDR.

Installing MSDE 2000

1. Log on to the computer with a user account that has local administrative permissions.
2. Download MSDE 2000 and then double-click the downloaded .exe file to extract the files to a directory of your choice.
3. Open a command window and change to the MSDE subdirectory where you extracted the files.
4. Run the Setup program from a command prompt so you can specify the appropriate parameters, including a strong password for the sa administrator login account. We recommend that you install a new named instance rather than use the default instance or an existing instance. In addition, the SQL Server collation used as the default collation for this instance should be set to a case-sensitive collation.

To install a named instance that uses Windows authentication and a case-sensitive collation for systems using English (United States):

setup INSTANCENAME="SQDR" SAPWD="<password>" COLLATION="SQL_Latin1_General_CP1_CS_AS"

To install a named instance that uses SQL Server authentication and a case-sensitive collation for systems using English (United States):

setup INSTANCENAME="SQDR" SAPWD="<password>" SECURITYMODE=SQL COLLATION="SQL_Latin1_General_CP1_CS_AS"

Make a note of the sa password as it will be required during the Data Replicator Configuration process.

To install MSDE 2000 on a non-English system, please consult the SQL Server Books Online for the correct collation value for your environment.

Verifying that MSDE 2000 Is Installed and Start the Service

Follow the steps below to verify that MSDE 2000 is installed and running on your computer.

1. Right-click the My Computer icon and select Manage.
2. Double-click Services and Applications.
3. Double-click Services.
4. Locate the MSSQL$SQDR service and start it if it is not already running.

Refer to Microsoft Article ID 324998 titled "How to obtain and install SQL Server 2000 Desktop Engine (MSDE 2000)" for more information on installing MSDE 2000.

Installing SQL Server 2005/2008 Express

1. Log on to the computer with a user account that has local administrative permissions.
2. Download SQL Server Express and then double-click the downloaded file to start the installation process.
3. After the files have been extracted, accept the license agreement and click Next.
4. Install the prerequisite components and click Next.
5. On the welcome dialog, click Next to start the installation.
6. After the system configuration check completes, click Next.
7. Enter registration information and de-select (uncheck) the Hide advanced configuration options box. Click Next.
8. On the Feature Selection dialog, expand the Client Components and choose to install the Connectivity Components. Click Next.
9. On the Instance Name dialog, check the Named instance radio button and enter SQDR in the Named instance field. Click Next.
10. If you are presented with the Existing components dialog, review and click Next. Otherwise, proceed to the next step.
11. Specify the Windows service account to use for the SQL Server service and click Next.
12. Choose the Mixed Mode authentication and enter a strong sa password. Make a note of this password as it will be required during the Data Replicator Configuration process.
13. On the Collation Settings dialog, select the Collation designator and sort order radio button and choose the Latin1_General from the drop down list. Tick the case-sensitive checkbox and click Next. (To install SQL Server Express on a non-English system, please consult the SQL Server Books Online for the correct collation value for your environment.)
14. Select the options on the Configuration Options dialog as desired and click Next. Repeat for the Error and Usage Report Settings dialog.
15. Click Install and click Finish once the installation is complete.

Refer to the "How to: Install SQL Server Express" topic in the SQL Server 2005 Books Online for more information on installing SQL Server 2005 Express Edition, or the "How should I Install SQL Server 2008" topic on the SQL Server Express MSDN weblog.

Verifying that SQL Server Express Is Installed and Start the Service

1. Right-click the My Computer icon and select Manage.
2. Double-click Services and Applications.
3. Double-click Services.
4. Locate the SQL Server (SQDR) service and start it if it is not already running.

Create a SQL Server ODBC Data Source for Use with SQDR

Define an ODBC System DSN for the SQL Server database that the Data Replicator Service will use to access its control database. If the same SQL Server server will also serve as the destination database server, create a separate ODBC System DSN to use as the SQDR Destination.

1. Launch the ODBC Administrator from the Windows Control Panel. For most versions of Windows this is located in the menu path Control Panel—>Administrative Tools—>Data Sources (ODBC).

NOTE

If using a 64-bit SQL Server, you will need to create both a 32-bit and a 64-bit ODBC DSN for the SQL Server database with identical data source names. This is because the SQDR Configuration uses SQL Server's 64-bit osql application to create its control database, while the SQDR service uses the 32-bit DSN to communicate with the control database. Create the 64-bit DSN using the (64-bit) ODBC Data Source Administrator found in Administrative Tools. To create the 32-bit DSN, use the 32-bit ODBC Data Source Administrator (odbcad32.exe) typically located in \WINDOWS\SysWOW64 directory.

2. Click the System DSN tab of the ODBC Data Source Administrator window.
3. To create a new data source for the SQL Server database, click Add. In the next dialog, select SQL Server for the driver and click Finish. (If you are using SQL Server 2005/2008 Express, be sure to select the SQL Server driver instead of the SQL Server Native Client driver.)  If you already have a data source defined for the SQL Server database you want to use for the Data Replicator control tables, select it and click Configure to verify the properties.
4. Specify a name and select which SQL Server you want the data source to connect to, and click Next to proceed.
5. Continue through the remainder of the wizard and click Test Data Source to ensure the DSN can connect to the specified SQL Server. If you do not see TESTS COMPLETED SUCCESSFULLY, modify the data source configuration until it can successfully connect to the server.

Site Map
Products | Trial Download | Customer Support | Partners | About StarQuest | Press & Events | Contact StarQuest

©Copyright 2009 StarQuest Ventures, Inc. All rights reserved.

Legal Notice • Privacy Policy
Email contact@starquest.com with site comments.