Assuming you already have a database and you wish to take advantage of PartitionDB. This section will guide you how to migrate your existing database to PartitionDB.
The process includes two steps:
- Create the database – Create the database configuration that works for your needs.
- Migrate the data – Migrate your data to the new database that you created.
Dry run before you start
Before you start migrating, you can try PartitionDB without affecting your database. The below script will go over your database, analyse it and will recommend the following:
- Ten most suitable columns to serve as partition column.
- List of the ten largest tables, from which it is recommended to select the partition column in case you could not find the a proper one in the first list.
Running this script will not change anything in your database, and will help you decide how to migrate your database once you start the migration. Running this script is optional, you can skip it and start the migration.
Make sure to set your database name in the first line of the script before you run it.
Create a database
The wizard walks you through the required steps to migrate your database. If you choose not to install PartitionDB user interface, the migration can be performed using scripts. This tutorial describes how to use the wizard. Select Tools->Migration from PartitionDB menu to start.
The result of this process is an auto generated script based on your preferences. You will then run the script to create your database configuration.
Note that every step has an explanation on the bottom of the screen, so you don’t need to refer to any other reading material while migrating. Also, you can always go back to previous steps.
Select a database and a partition column
First select the database that you wish to migrate from the list of databases available (at this point everything is done on the local server). Once you selected the database, the system recommends by which column to partition the database. The top ten options are displayed in the “Partition columns recommendations”. If you prefer to partition by a different column you can choose a table from the list of columns (“choose partition column from existing tables”) and select the desired column.
The selected column and its type will be shown in the “Partition column” field.
Select tables for the new configuration
At this step select the tables you wish to have in the partitions and the table that will be stored in Common database. The system displays the default settings, but you can change it upon your needs. Selecting a table as a partition table will unselect it from the Common database and vice versa. At this step you can also choose to omit tables that you don’t need in your new configuration by deselecting them from both sides.
Select unique column for partitions
A table that resides in a partition must have a unique column. At this step you have the opportunity to change the unique column as the system chose for you. In most cases there is no reason to change the defaults.
Set gate’s attribute
In this step you can review the gate’s attributes and set them according to your preferences. For a description of the attributes, please refer to start from scratch post.
Set the database architecture
At this step you will define how many partitions you wish to create, and create a GlobalDB and a CommonDB.
Clicking on “System advice” will show you how many partitions are recommended for your configuration, however, you can set this number upon your needs. Each partition that is created needs to have a name (database filed), a value (for the partitioning) and a description. As before, at this point we keep all the configuration on the local server.
Generate the script
You have reached to the last step of creating your database. You can choose not to include some of the objects in your new database, however, the default suggest to have all objects.
Select “Generate scrip” to create the script and save it locally. Review it and then run. Another option is to hit “Run script auto” to run the script on the spot.
Clicking “Generate Script” will create the script in the location you have selected.
Migrate the data
You have completed the first step of creating the database. The second step is to migrate the data from your current database to the new one. You can choose to migrate all at once, or to try out by migrating a small number of entries. In any case, this process does not change your original database.
In this example we migrate 1000 entries from each table. As before, select “Generate Script” to create the data migration script. If you wish to run it without saving the script, select “Run Script Auto”.
Review the results and make sure they match your expectations. Then you can repeat the migration process for the whole database, by selecting “Full migration”. You don’t need to worry about the partial migration that you did before; the script will take care of that.