Data Replication with SQL Remote
   PART 2. Replication Design for SQL Remote
     CHAPTER 6. Principles of SQL Remote Design       

Design overview

This chapter describes general publication design issues that you must address when designing a SQL Remote installation. It also describes how SQL Remote replicates data. SQL Remote is software for performing a complex task.

Design at the consolidated database 

Like all SQL Remote administrative tasks, design is carried out by a database administrator or system administrator at the consolidated database.

The Adaptive Server Enterprise System Administrator or database administrator should perform all SQL Remote configuration tasks.

  Ensuring compatible databases

You should ensure that all databases participating in a SQL Remote installation are compatible in terms of sort orders, character sets, and database option settings.

If your installation includes both Adaptive Server Enterprise and Adaptive Server Anywhere databases, you should ensure your Adaptive Server Anywhere databases are created in an Adaptive Server Enterprise-compatible fashion.

     For a full description of how to create Enterprise-compatible Adaptive Server Anywhere databases, see "Creating a Transact-SQL-compatible database", in the chapter "Using Transact-SQL with Adaptive Server Anywhere", in the Adaptive Server Anywhere User's Guide. This section provides a brief description only.

  To create an Enterprise-compatible Adaptive Server Anywhere database using Sybase Central:

1. The Create Database wizard provides an button that sets each of the available choices to emulate Adaptive Server Enterprise. This is the simplest way to create a Transact-SQL-compatible database.

  To create an Enterprise-compatible Adaptive Server Anywhere database from the command line:

1. Ensure trailing blanks are ignored     You can do this using the dbinit -b command-line switch.

2. Ensure the dbo user ID is set     If you have a database that already has a user ID named dbo, then you can transfer the ownership of the Adaptive Server Anywhere Transact-SQL system views to another user ID. You can do this using the dbinit -g command-line switch.

3. Remove historical system views You can do this with the dbinit -k command-line switch.

4. Make the database case sensitive You can do this with the dbinit -c command-line switch.

The following command creates a case-sensitive database named test.db in the current directory, using the current dbo user, ignoring trailing blanks, and removing historical system views:

dbinit -b -c -k test.db

  Using compatible sort orders and character sets

The SQL Remote Message Agent does not perform any character set conversions.

Character sets in Adaptive Server Anywhere installations 

For an Adaptive Server Anywhere installation, the character set and collation used by the consolidated database must be the same as the remote databases. For information about supported character sets, see Database Collations and International Languages.

Character sets in Adaptive Server Enterprise installations 

The Open Client/Open Server libraries perform character set conversions between SSREMOTE and Adaptive Server Enterprise whenever the LOCALES.DAT character set is different from the Adaptive Server Enterprise character set. Both character sets must be installed on the Adaptive Server Enterprise server and conversion must be supported.

Character sets in mixed installations 

The locales.dat settings (which are used by all Open Client applications) must match the remote Adaptive Server Anywhere settings.

The following table provides recommended matches between Adaptive Server Enterprise and Adaptive Server Anywhere character sets. The matches are not all complete.

Adaptive Server Anywhere collation name	Open Client / Open Server name	Open Client / Open Server case-sensitive sort order	Open Client / Open Server case-insensitive sort order
default	cp850	dictionary_cp850	nocase_cp850
850	cp850	dictionary_cp850	nocase_cp850
437	cp437	dictionary_cp437	nocase_cp437
852	cp852	bin_cp852	bin_cp852
860	cp860	bin_cp860	bin_cp860
437LATIN1	cp437	dictionary_cp437	nocase_cp437
437ESP	cp437	espdict_cp437	espnocs_cp437
437SVE	cp437	bin_cp437	bin_cp437
819CYR	iso_1	bin_iso_1	bin_iso_1
819DAN	iso_1	bin_iso_1	bin_iso_1
819ELL	iso_1	bin_iso_1	bin_iso_1
819ESP	iso_1	espdict_iso_1	espnocs_iso_1
819ISL	iso_1	bin_iso_1	bin_iso_1
819LATIN1	iso_1	dictionary_iso_1	nocase_iso_1
819LATIN2	iso_1	bin_iso_1	bin_iso_1
819NOR	iso_1	bin_iso_1	bin_iso_1
819RUS	iso_1	bin_iso_1	bin_iso_1
819SVE	iso_1	bin_iso_1	bin_iso_1
819TRK	iso_1	bin_iso_1	bin_iso_1
850CYR	cp850	bin_cp850	bin_cp850
850DAN	cp850	scandict_cp850	scannocp_cp850
850ELL	cp850	bin_cp850	bin_cp850
850ESP	cp850	espdict_cp850	espnocs_cp850
850ISL	cp850	scandict_cp850	scannocp_cp850
850LATIN1	cp850	dictionary_cp850	nocase_cp850
850LATIN2	cp850	bin_cp850	bin_cp850
850NOR	cp850	scandict_cp850	scannocp_cp850
850RUS	cp850	bin_cp850	bin_cp850
850SVE	cp850	scandict_cp850	scannocp_cp850
850TRK	cp850	bin_cp850	bin_cp850
852LATIN2	cp852	bin_cp852	bin_cp852
852CYR	cp852	bin_cp852	bin_cp852
855CYR	cp855	cyrdict_cp855	cynocs_cp855
857TRK	cp857	bin_cp857	bin_cp857
860LATIN1	cp860	bin_cp860	bin_cp860
866RUS	cp866	rusdict_cp866	rusnocs_cp866
869ELL	cp869	bin_cp869	bin_cp869
SJIS	sjis	bin_sjis	bin_sjis
SJIS2	sjis	bin_sjis	bin_sjis
EUC_JAPAN	eucjis	bin_eucjis	bin_eucjis
EUC_CHINA	eucgb	bin_eucgb	bin_eucgb
EUC_TAIWAN	eucb5	bin_big5	bin_big5
EUC_KOREA	eucksc	bin_eucksc	bin_eucksc
UTF8	utf8	bin_utf8	bin_utf8