Skip to Content

SQL Anywhere, on-demand edition Moving a Database

Module 4 - Moving a Database

In this module, you will walk through how to move databases within the SAP Sybase SQL Anywhere, on-demand edition cloud. This includes moving running databases to other cloud servers on the same host and to cloud servers running on a different host.

Pre-requisites

It is expected that you have completed modules 1, 2, and 3, and have a basic understanding of setting up a cloud, adding databases, and making client connections.

Setting up the Cloud

  1. Using your knowledge from the previous modules, set up a cloud with at least two cloud servers (they can either be on the same host or separate hosts). Refer to Module 1 for more information on setting up the cloud and starting cloud servers on different hosts.
  2. Add one database to the cloud using the steps described in previous modules.
  3. In the left hand navigation pane, click Servers. All the servers running in your cloud appear in the Servers tab. In the screenshot below, MyCloud has three cloud servers: MYCLOUD-1#1, MYCLOUD-2#1, and MYCLOUD-3#1.

Moving a Single Database

  1. In the left hand navigation pane, click Databases. The database you just added should appear in the main pane. Click the database name in the main pane to see details about the database in the main pane on the database Overview tab.
  2. The location of the database seen in the Overview tab. In this example, the demo database is running on server MYCLOUD-3#1 on host MYCLOUD-3.
  3. In the Overview tab, click Move Database from the Other Actions box.
  4. A dialog will appear that prompts you to select a server for this move. Select a server and hit Finish.
  5. The cloud will begin initiating the move. It may take up to 30 seconds for the move to complete. Once the move is complete, the name of the server on the database Overview tab will change to reflect where the database has moved to. In this example, the database is now running on server MYCLOUD-1#1. The move has been successful.                  

Extracting Databases

Extracting databases allows you to download a copy of the database file, its log file, or both, and save them locally, away from the cloud. By default, the database remains running in the cloud.

To extract databases:

  1. Return to the Overview page by clicking the cloud name in the breadcrumbs. Then click on Databases in the left hand navigation pane.
  2. In the list of the databases, select the database you wish to extract by checking the checkbox beside it.
  3. Click on Other Actions at the top of the page and then select Extract in the dropdown menu.
  4. Enter the DBA user ID and password in the dialog and click Next.
  5. A list of files available for extracting and their size will appear. Extracting the files may take some time, depending on their size. Click the name of the file to download it and save it to the directory of your choice. Click Close when you have downloaded all of the required files.

Client Connections

In Module 3, you learned how client connections are automatically redirected inside the cloud so that the client application does not need to know exactly which server is running their database. In the final part of this tutorial you will see the effects of moving a database on client connections. To do this, you will use the dbping utility, running in a loop, to connect to the database every 5 seconds. From this you will be able to see the how the client connection is dropped, and then reconnected.

1. Execute the following script (replacing host with a host in your system and dbn with a database on your server) that will cause dbping to run in a loop:

For Windows Command Prompt:


for /L %I in (1,1,1000) do (dbping -d -c "host=MYCLOUD-1;dbn=demo;uid=dba;pwd=sql" & timeout 5)

NOTE: The timeout command is being used to pause the execution for 5 seconds. The timeout command may not be available on all Windows machines by default (e.g. XP). If you do not have timeout on your machine, you can replace it with this code that has the same effect:


ping -n 1 -w 5000 1.1.1.1 > NUL


For Linux terminal:

for I in {1..1000}; do dbping -d -c "host=MYCLOUD-1;dbn=demo;uid=dba;pwd=sql"; sleep 5; done;


2. The script should begin to run in a 5 second loop, connecting to your selected database. In the example, the demo database is still running on server MYCLOUD-1#1.

3. Using either method previously described, move the database onto another server. After starting the move, watch how the script output changes. In the screenshot below, dbping is suddenly unable connect to the database (“Specified database is not found”). A few seconds later, dbping is able to make the connection. Then, the output shows that the connection is now being made to server MYCLOUD-2#1. The database has been successfully

moved with minimal downtime . Furthermore, because of automatic connection redirection, the client connection string never had to change.


Tags:

No comments