Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 (Français) » SQL Anywhere Server - Database Administration » Maintaining your database » SQL Anywhere read-only scale-out


Tutorial: Setting up a read-only scale-out system

This tutorial takes you through the steps of setting up a root database server that automatically adds a child node.


There is a sample in samples-dir\SQLAnywhere\DBMirror that uses a database mirroring system in conjunction with a scale-out system.

For information about the location of samples-dir, see File locations and installation settings.

 ♦  To set up a read-only scale-out system
  1. Create the following directory: c:\scaleoutdemo.

  2. Run the following command to create scaleoutdemo.db that contains data from the sample database:

    newdemo c:\scaleoutdemo\scaleoutdemo.db
  3. Start the root database server for the scale-out system:

    dbsrv12 -n scaleout_root_demo -su sql -x TCPIP(port=6871) "c:\scaleoutdemo\scaleoutdemo.db" -xp on
  4. Connect to the root database from Interactive SQL:

    dbisql -c "UID=DBA;PWD=sql;SERVER=scaleout_root_demo;DBN=scaleoutdemo"
  5. Define the root database server for the scale-out system:

        'CREATE MIRROR SERVER "scaleout_primary_demo"
        AS PRIMARY
            connection_string = ''SERVER=scaleout_primary_demo;HOST=' 
            || PROPERTY( 'MachineName' ) || ':6871''';
  6. Define the root database server as a partner in the scale-out system. The name of the partner server must match the database server name that is used in the command to start the database server.

        'CREATE MIRROR SERVER "scaleout_root_demo"
            AS PARTNER
            connection_string = ''SERVER=scaleout_root_demo;HOST=' 
            || PROPERTY( 'MachineName' ) || ':6871''';
  7. Set the options for the root server for the scale-out system:

    SET MIRROR OPTION auto_add_server='scaleout_root_demo';
    SET MIRROR OPTION child_creation='automatic';
    SET MIRROR OPTION authentication_string='abc';
    SET MIRROR OPTION auto_add_fan_out='10';
  8. Make a backup copy of the database, placing it in the directory c:\scaleoutdemo\copynode.

    BACKUP DATABASE DIRECTORY 'c:\\scaleoutdemo\\copynode';
  9. Start the backup copy of the database as a child (copy node) of the scaleout_root_demo database server:

    dbsrv12 -n scaleout_child_demo -su sql -x TCPIP(port=6873) "c:\scaleoutdemo\copynode\scaleoutdemo.db" -xp on
  10. Connect to the child node from Interactive SQL:

    dbisql -c "UID=DBA;PWD=sql;SERVER=scaleout_child_demo"

    Once the child node connects to its parent, you are warned that the child is a read-only copy of the database. You can now connect to the copy node and execute queries against the copy of the database.

  11. You can view the mirror servers in the scale-out system by running the following query:

  12. Disconnect from Interactive SQL and shut down the database servers.

  13. (optional) Delete the c:\scaleoutdemo directory.