Configuring ODBC Data Sources
ODBC is a standard interface for accessing data in relational databases.
An ODBC data source name (DSN) defines the information
that a driver needs to access a specific instance of data in a DBMS.
In older versions of SQDR, it was necessary to create an ODBC DSN for the SQL Server database used as a control database,
plus an ODBC DSN for each host DBMS that you
want to replicate to or from. This is no longer required; we recommend
using connection strings for all database connections - see Using
Connection Strings for details.
The following information is provided for historical reasons and for
users who prefer to use ODBC data sources rather than connection strings.
Using ODBC Data Sources
Define an ODBC DSN for the Db2 for LUW or
SQL Server database that the Data Replicator Service will use as a control
database, and an ODBC DSN for each host DBMS
that you want to replicate to or from. For convenience, the StarQuest
Data Replicator program group contains a shortcut for starting the Windows
ODBC Data Source Administrator.
|
If you are using
the StarSQL ODBC driver to perform incremental replications, change
the isolation level to Read Committed. You can change the isolationLevel value from the Expert Page
of the StarSQL Data Source Wizard.
If the StarSQL-based DSN
is for a SQL Server target database that will receive incremental
changes, also be sure that the collation sequence of the database
is Case Sensitive. Setting the collation sequence to Case Sensitive
helps to ensure there are unique fields for tracking the change
data. You can use the SQL Server Studio to set the collation for
a new database. Use the ALTER DATABASE (Transact-SQL)
command with the COLLATE clause to change the collation for an
existing database. A case-sensitive collation has the characters
"CS" appended to the Collation
Designator, such as SQL_Latin1_General_CP1_CS_AS for U.S. English
systems. |
Selecting the Data Source Type
The ODBC Data Source Administrator allows you to create User
DSNs and System
DSNs. A System data source is visible to all
users on a computer, including Windows services such as the Data Replicator.
The default configuration is to have the Data Replicator service log on
as a System Account and use System DSNs. If
you want the Data Replicator service to use a User data source, which
is visible only when the user who created it is logged in, you must configure the Data Replicator
service to log on as the user account that created the DSN.
Selecting the ODBC Driver
When you create a new data source, the Data Source Administrator prompts
you to select a driver for the data source.
Refer to the technical document SQDR:
Recommend ODBC Drivers for details.
Selecting the Authentication Method
A SQL Server DSN can be configured to use
Windows authentication or SQL Server authentication. The authentication
mode that the DSN uses must be the same mode
that the SQL Server is configured to use. If the DSN
and SQL Server are configured to use Windows authentication, the DSN uses a trusted connection and all connections are
validated using the Windows network login ID. If the DSN
and SQL Server are configured to use SQL Server authentication, the DSN uses a non-trusted connection and all connections
through the DSN are validated using the SQL
Server authentication. See SQL
Server Authentication for more information about the methods SQL Server
uses to authenticate connections.
Specifying the Default Database for the Control Database DSN
If you are creating a DSN to SQL Server for
the SQDR control database, you may need to set the control database to
be the default database. For example, in the following pane of the ODBC
Data Source Configuration Wizard the default database is changed to SQDRControlDB, which is the SQDR control database
for this example DSN.
