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.

Note

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:

    BEGIN
        EXECUTE IMMEDIATE
        'CREATE MIRROR SERVER "scaleout_primary_demo"
        AS PRIMARY
            connection_string = ''SERVER=scaleout_primary_demo;HOST=' 
            || PROPERTY( 'MachineName' ) || ':6871''';
    END
  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.

    BEGIN
        EXECUTE IMMEDIATE
        'CREATE MIRROR SERVER "scaleout_root_demo"
            AS PARTNER
            connection_string = ''SERVER=scaleout_root_demo;HOST=' 
            || PROPERTY( 'MachineName' ) || ':6871''';
    END
  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:

    SELECT * FROM SYSMIRRORSERVER;
  12. Disconnect from Interactive SQL and shut down the database servers.

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