Creating multiple remote databases

Use the following steps to increase efficiency when creating more than one remote database.

 To create multiple remote databases

  1. Make a copy of the consolidated database and start the subscriptions for the remote users from the consolidated database. For example:

    1. Start the subscriptions and then immediately shut down the consolidated database and the SQL Remote Message Agent (if it is running).

      The subscriptions must be started at the same time that the consolidated database copy is made. Any operations that take place between copying the database and starting the subscriptions can be lost, and can lead to errors at remote databases. Starting subscriptions on the consolidated database allows messages to be packaged and sent to subscribers, even if the subscriber databases do not exist yet. See START SUBSCRIPTION statement [SQL Remote] and Start subscriptions.

      To start several subscriptions within a single transaction, use the REMOTE RESET statement. See REMOTE RESET statement [SQL Remote].

    2. Copy the consolidated database.

      By default, both the Extraction utility (dbxtract) and the Extract Database Wizard run at isolation level 3. This isolation level ensures that data in the extracted database is consistent with data on the database server; however, it can prevent other users from using the database. It is recommended that you extract your remote database against a copy of the consolidated database.

    3. Re-start the consolidated database, and if it was running, re-start the SQL Remote Message Agent on the consolidated database.

  2. Extract the remote database schema from the copy of the consolidated database. As the database is a copy, there are no locking and concurrency problems; nevertheless, for a large number of remote databases, this process can take a while.

    When extracting the remote database schema, choose the following options:

    1. Extract only the schema for the remote database.

      By default, both the Extraction utility (dbxtract) and the Extract Database Wizard extract one database at a time, including the schema and data for each user. However, in most deployment scenarios the remote databases use the same schema but different data. Using the Extraction utility (dbxtract) or the Extract Database Wizard to extract both schema and data for each user results in repeatedly extracting the same schema. See the -n option Extraction utility (dbxtract).

    2. Order the data by primary key.

      By default, the data in each table is ordered by primary key. Loading data into the remote database is faster when the data is ordered by primary key. See the -u option Extraction utility (dbxtract).

  3. Create an empty remote database using the reload.sql file. Copy this database file to create the required number of remote databases. See Extracting remote databases to a reload file.

  4. For each remote database, define the SQL Remote definitions specific to each remote user. See User permissions.

  5. For each remote user, extract only their corresponding data from the consolidated database. See the -d option Extraction utility (dbxtract).

  6. Load the data for each remote user into the corresponding remote database.

    As each remote database is created, its data is out of date with the live consolidated database.

    However, when you run the SQL Remote Message Agent (dbremote), each user can receive and apply messages that have been sent from the live consolidated database to bring themselves up to date. See Understanding the SQL Remote Message Agent (dbremote).

 See also
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0