Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 9. Deploying and Synchronizing Databases
The extraction utility is an aid to creating remote Adaptive Server Anywhere databases. It cannot be used to create remote Adaptive Server Enterprise databases.
The extraction utility can be accessed in the following ways:
From Sybase Central.
As a command-line utility. This is the dbxtract command-line utility (Adaptive Server Anywhere), or the ssxtract command-line utility (Adaptive Server Enterprise).
Caution
Do not run the Message Agent while running the extraction utility. The results are unpredictable.
Creating a database from the reload filesThe command-line utility unloads a database schema and data suitable for building a remote Adaptive Server Anywhere database for a named subscriber. It produces a SQL command file with default name reload.sql and a set of data files. You can use these files to create a remote Adaptive Server Anywhere database.
Editing of reload.sql may be needed |
To create a remote database from the reload file:Create an Adaptive Server Anywhere database using Sybase Central or using the dbinit utility.
Connect to the database from the Interactive SQL utility, and run the reload.sql command file. The following statement entered in the Interactive SQL command window runs the reload.sql command file:
read path\reload.sql
where path is the path of the reload command file.
When used from Sybase Central, the extraction utility carries out the database unloading task, in the same way that dbxtract or ssxtract does, and then takes the additional step of creating the new database.
The extraction utility does not use a message system. The reload file (ssxtract/dbxtract) or database (from Sybase Central) is created in a directory accessible from the current machine. Synchronizing many subscriptions over a message link can produce heavy message traffic and, if the message system is not completely reliable, it may take some time for all the messages to be properly received at the remote sites.
Before extracting a databaseYou must complete the following tasks before using the extraction utility at a consolidated database.
You must have created message types for replication.
You must have added a publisher user ID to the database.
You must have added remote users to the database.
You must have added the publication to the database.
You must have created a subscription for the remote users.
For a description of how to carry out these steps, see the tutorial in the chapter A Tutorial for Adaptive Server Anywhere Users.
When you use the extraction utility to create a remote database, the user for which you are creating the database receives the same permissions they have in the consolidated database. Further, if the user is a member of any groups on the consolidated database, those group IDs are created in the remote database with the permissions they have in the consolidated database.
Using the extraction utility from Sybase CentralFor full information on using the extraction utility from Sybase Central, see the Sybase Central online Help. This section describes one way to extract a database for a remote user from the current consolidated database.
To extract a database for a remote user:Click the Remote Users folder on the left panel, which is in the SQL Remote folder. The right panel displays the remote users.
Right-click the remote user for whom you wish to extract a database, and select Extract Database from the popup menu. The Extraction Wizard is displayed.
Follow the instructions in the Wizard.
For information about the extraction utility options, available as command-line options or as choices presented by the Database Extraction Wizard, see Extraction utility options.
Designing an efficient extraction procedureIt is very inefficient to create a large number of remote databases by running the extraction utility for each one. You can make the process much more efficient. This section describes one way of making the process more efficient.
There are several potential causes of inefficiency in a large-scale extraction process:
The extraction utility extracts one database at a time, including the schema and data for each user. Commonly, many users share a common schema, and only the data differs. The brute force method of running the extraction utility for each user repeats large amounts of work unnecessarily. Extracting schema and data separately can help with this problem.
Running from Sybase Central, the extraction utility creates a new database for each user. If subscribers share a common schema, you could create a single database, with schema but no data, and copy the file.
By default, the extraction utility runs at isolation level zero. If you are extracting a database from an active server, you should run it at isolation level 3 (see Extraction utility options) to ensure that data in the extracted database is consistent with data on the server.
Running at isolation level 3 may hamper others' turnaround time on the server because of the large number of locks required. It is recommended that you run the extraction utility when the server is not busy, or run it against a copy of the database.
One approach that avoids these problems is as follows:
Make a copy of the consolidated database, and at the same time start the subscriptions from the live database. Messages will now start being sent to subscribers, even though they have no database and will not receive them yet.
To start several subscriptions within a single transaction, use the REMOTE RESET statement (Adaptive Server Anywhere ) or sp_remote procedure (Adaptive Server Enterprise).
Extract the remote databases from the copy of the database. As the database is a copy, there are no locking and concurrency problems. For a large number of remote databases, this process may take several days.
As each remote database is created, it is out of date, but its user can receive and apply messages that have been being sent from the live consolidated database, to bring themselves up to date.
This solution interferes with the production database only during the first step. The copy must be made at isolation level three if the database is in use, and uses large numbers of locks. Also, the subscriptions must be started at the same time that the copy is made. Any operations that take place between the copy and the starting of the subscriptions would be lost, and could lead to errors at remote databases.
Limits to using the extraction utilityWhile the extraction utility is the recommended way of creating and synchronizing remote databases from a consolidated databases, there are some circumstances where it cannot be used, and you must synchronize remote databases manually. This section describes some of those cases.
Cannot create Adaptive Server Enterprise remote databases The extraction utility can only be used for Adaptive Server Anywhere remote databases.
Additional tables at the remote database Remote databases can have tables not present at their consolidated database as long as these tables do not take part in replication. Of course, the extraction utility cannot extract such tables from a consolidated database.
Adaptive Server Enterprise/Adaptive Server Anywhere differences Some features in Adaptive Server Enterprise are not present in Adaptive Server Anywhere. The extraction utility carries out a mapping onto similar features, but the mapping is not complete.
For more information on Adaptive Server Enterprise/Adaptive Server Anywhere issues, see Using the extraction utility for Adaptive Server Enterprise.
Extracting procedures and views By default, the extraction utility extracts all stored procedures and views from the database. While some of these views and procedures are likely to be required at the remote site, others may not be required—they may refer only to parts of the database that are not included in the remote site.
After running the extraction utility, you should edit the reload script and remove unnecessary views and procedures.
Using the extraction utility in multi-tiered setups To understand the role of the extraction utility in multi-tiered arrangements, consider a three-tiered SQL Remote setup.
This setup is illustrated in the following diagram.
From the consolidated database at the top level, you can use the extraction utility to create the second-level databases. You can then add remote users to these second-level databases, and use the extraction utility from each second-level database to create the remote databases. However, if you have to re-extract the second-level databases from the top-level consolidated database, you will delete the remote users that were created, along with their subscriptions and permissions, and will have to rebuild those users. The exception is if you resynchronize data only, in which case you can use the extraction utility to replace the data in the database, without replacing the schema.
Using the extraction utility for Adaptive Server EnterpriseThe extraction utility for Adaptive Server Enterprise takes an Adaptive Server Enterprise database schema, and produces an Adaptive Server Anywhere database. There are several limitations and techniques specific to this tool.
Adaptive Server Enterprise features unsupported in Adaptive Server AnywhereThere are some features in Adaptive Server Enterprise that are either not supported or are only partially supported in Adaptive Server Anywhere. The extraction utility handles some of these features partially, and some not at all.
For a full description of Adaptive Server Enterprise/Adaptive Server Anywhere compatibility, see the part Transact-SQL Compatibility, in the Adaptive Server Anywhere User's Guide.
Features not supported in ssxtract include the following:
Grouped procedures Adaptive Server Anywhere does not support procedure groups, and they are not extracted by ssxtract.
Named constraints and defaults Adaptive Server Anywhere does not support named constraints and named defaults. Any such objects are extracted directly as constraints and defaults that apply to a single object, and the name is lost.
Roles ssxtract extracts roles using the Adaptive Server Anywhere concept of groups. It creates a group with the named role, and assigns users to it.
Passwords If the user for whom a database is being extracted does not have an entry in SYSLOGINS, no password is extracted. If the user does have a login ID, a dummy password is extracted.
NCHAR, NVARCHAR These data types are extracted as CHAR and VARCHAR, with NULLS allowed.
timestamp columns Although Adaptive Server Anywhere does provide a timestamp column, it is a different data type from that of Adaptive Server Enterprise. Timestamp columns are not extracted.
Customizing the system tablesThe objects that are to be loaded into an Adaptive Server Anywhere database are described in the system catalog. The extraction utility for Adaptive Server Enterprise first creates a set of Adaptive Server Anywhere system tables in TEMPDB, and fills them with data from the Adaptive Server Enterprise catalog. It then unloads this set of tables to provide the reload script that in turn builds an Adaptive Server Anywhere database.
There may be cases where you wish to change the content of the Adaptive Server Anywhere system tables held in TEMPDB. SQL Remote provides a place for you to do that.
The stored procedure that creates and fills the Adaptive Server Anywhere system objects in TEMPDB is called sp_populate_sql_anywhere. As its final operation, this procedure calls a procedure called sp_user_extraction_hook. This procedure, by default, does nothing. If you wish to customize the extraction procedure, you can do so by writing a suitable sp_user_extraction_hook procedure.