Analyze your Databases

Not sure how ready you are to move to the cloud? With Tidal, you have the option to analyze the databases associated with your applications.

The analysis will calculate the difficulty of migrating your databases to each target platform, and give details on database features that may complicate the migration.

It is capable of analyzing Oracle, SQL Server, MySQL, and PostgreSQL databases. Providing analysis on migrating a database to a variety of services on AWS, Azure, and Google Cloud.

Supported Database Versions

Tidal Tools is able to analyze databases with the following versions:

If you have a use case for a different version, definitely let us know at info@tidalcloud.com, we are always adding new capabilities.

Migration Complexity

The databases are analyzed to look for patterns and feature usage that may be difficult to migrate due to lack of support or compatibility in their new environment. The databases are analyzed based on their metadata, looking at specific schema objects that are used within your databases as well as the usage of proprietary features that will not be available in the target platforms.

For example, in Oracle databases, the Data Dictionary and AWR repository tables are read and analyzed. The scoring is calculated based on the type of attributes, features or schema objects that are used and the frequency of use throughout the database.

• Over 100 unique characteristics are considered
• Feature-fit is executed against all supported cloud data platforms.
• Migration difficulty score is calculated based on a weighted model

Getting Started

1. Before you can analyze a database, You must enable the Database Analysis feature for your account. To do so, go to your workspace Settings/Preferences (https://[your workspace].tidal.cloud/preferences). You will find the Database Analysis section at the bottom of the page.
2. Now that you have activated the Database Analyze feature, You need to install, configure and authenticate via Tidal Tools. Make sure you follow these guides.
   • How to install Tidal Tools.
   • Install Tidal Tools dependencies.
   • Make sure you can connect with your workspace using Tidal Tools.
   • As a last step, You should run the tidal doctor command to verify that your environment has been configured properly.
3. Tidal Tools will need a YAML configuration file with values and credentials needed to connect to your database. You can check this section for more information on how to create such a file.
4. Configure your Database to allow access to Tidal Tools. This section will walk you through the creation of a tidal user and granting the necessary permissions.
5. Perform the Database Analysis. There are 2 options to do so. You can find more details in this section

That is all, You should be able to see the results in your workspace within seconds.

Create your Database configuration file

For the most part, all supported Database engines share very similar configuration with Oracle being the exception.

Your YAML configuration file for Postgres, MySQL, and SQL Server must contain the following information.

• id - The id of the database from your Tidal account. You can find it in the URL bar when looking at a database instance. ex. If you are viewing a database instance in Tidal, the URL will show https://[your workspace].tidal.cloud/database_instances/111/overview in this case 111 is the database instance ID.
• name - A common name for your database could be the same or different from db_name, but this value is arbitrary and only for your reference.
• engine - The database vendor, either SQL Server, MySQL, or PostgreSQL, it is not case sensitive.
• host - The hostname of the server that the database is located on and is accessible via a network connection from your current device and location.
• port - The port that the host has open and the database can accept connections on, for SQL Server it is 1433 or the port of the named instance, for MySQL it is 3306, and for PostgreSQL the default port is 5432.
• db_name - The name of the database you are analyzing // or in the case of Oracle, the “service name”.
• user - A username to authenticate with the database, check the section below for more details about creating a user and granting permissions.
• password - A password for the corresponding user.

When analyzing Oracle databases, the attributes in your configuration file must contain the following details.

• id - The id of the database from your Tidal account. You can find it in the URL bar when looking at a database instance. ex. If you are viewing a database instance in Tidal, the URL will show https://[your workspace].tidal.cloud/database_instances/111/overview in this case 111 is the database instance ID.
• name - A common name for your database.
• analyze_workload - When set to true workload analysis is enabled. Be sure to configure AWR data retention and verify licensing. For more information check the advance configuration section
• engine - Oracle
• host - The hostname of the server that the database is located on and is accessible via a network connection from your current device and location.
• port - The port that the host has open and the database can accept connections on, the default for Oracle is 1521.
• db_id_type - Specifies the identifier type. Must be one of the following values:
  • SID if entering an Oracle System ID (SID) in the next field.
  • Service Name if entering a service name in the next field.
• sid_or_service_name - If you selected SID as your db_id_type, you should add the Oracle database SID, otherwise enter your Oracle service name.
• user - A username to authenticate with the database, check the section below for more details about creating a user and granting permissions.
• password - A password for the corresponding user.
• is_pdb - An Oracle Multitenant Pluggable Database (PDB). If set to true, You will need to provide credentials for the Container Database in following fields.
• cdb_sid - The Service ID of the container database.
• cdb_user - The common username of the container database user.
• cdb_password - The password of the container common database user.

The simplest way to create your configuration file is to use the tidal analyze db init command in Tidal Tools. The command will generate for you a databases.yaml file with a template with each one of the supported engines. All you will need to do is populate the file according to your needs.

Configuring a User

To make the process as easy and secure as possible, you can create a user in your database dedicated to tidal. This will provide you full control and transparency on the permission Tidal Tools needs when performing the Database Analysis. Below you will find instructions and scripts for each supported Database.

Oracle User

This SQL script will create a tidal user and set the right permissions for you. Once downloaded, You should edit it and provide a secure password on the first line.

If you are using a CDB database you will also need to create a second user to access the CDB. You can do that with this script and also provide a secure password at the top:

CREATE USER c##tidal_comm_user IDENTIFIED BY "replace_this_with_secure_password" account unlock;
GRANT CREATE SESSION to c##tidal_comm_user;
GRANT select_catalog_role to c##tidal_comm_user;

SQL Server User

You can use this SQL script to create a tidal user and set the correct permissions. You should edit it and provide a secure password on the first line.

MySQL Server User

For MySQL you can create a user (tidal) with all the necessary permissions using the following statements:

CREATE USER 'tidal'@'%' IDENTIFIED BY 'replace_this_with_secure_password';
GRANT PROCESS,REFERENCES, SHOW DATABASES, SHOW VIEW ON *.* TO 'tidal'@'%';
GRANT SELECT ON sys.* TO 'tidal'@'%';
GRANT SELECT ON performance_schema.* TO 'tidal'@'%';
GRANT SELECT ON mysql.slave_master_info TO tidal;
GRANT SELECT ON mysql.slave_relay_log_info TO tidal;
GRANT SELECT ON mysql.user TO tidal;

In addition, use the following command that applies to your MySQL version.

MySQL 5.x

GRANT SELECT ON mysql.proc TO tidal;

MySQL 8.x

GRANT SHOW_ROUTINE ON *.* TO 'tidal'@'%';
GRANT SELECT ON mysql.user TO 'tidal'@'%';

PostgreSQL Server User

Use the following commands to create a user (tidal) on PostgreSQL server.

CREATE USER tidal WITH PASSWORD 'replace_this_with_secure_password';

DO $$ DECLARE comm_rec RECORD;
BEGIN
    FOR comm_rec IN
		SELECT 'GRANT REFERENCES ON ALL TABLES IN SCHEMA '||schema_name||' TO tidal' AS comm FROM information_schema.schemata
	LOOP
        EXECUTE comm_rec.comm;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

GRANT REFERENCES ON ALL TABLES IN SCHEMA public to tidal;

For PostgreSQL versions higher than 9 the following GRANTs should be also applied:

GRANT SELECT ON pg_catalog.pg_config TO tidal;
GRANT EXECUTE ON function pg_catalog.pg_config TO tidal;
GRANT SELECT ON pg_catalog.pg_proc TO tidal;
GRANT SELECT ON pg_catalog.pg_namespace TO tidal;

For PostgreSQL versions 10 and above the following GRANTs should be also applied:

GRANT SELECT ON pg_catalog.pg_hba_file_rules TO tidal;
GRANT SELECT ON pg_catalog.pg_roles TO tidal;
GRANT pg_read_all_settings TO tidal;
GRANT pg_read_all_stats TO tidal;

After creating the user you will need to add the appropriate entry to the pg_hba.conf. For example:

# TYPE  DATABASE    USER    ADDRESS     METHOD
host    all         tidal   0.0.0.0/0   md5

To apply the configuration changes to the running PostgreSQL server you will need to run pg_ctl restart.

Perform the analysis

When analyzing a database (or multiple), You have two options.

1. Perform the database analysis and upload the result immediately to your workspace. This is Tidal Tools’ default behaviour. To do so, all you need to do is run the following command.

   tidal analyze db databases.yaml
   

2. Perform the database analysis and upload the results at a later time. The following section will explain how to run database analysis in offline mode

Running offline

There are circumstances in which you need to perform a database analysis on an environment without or with restricted internet access. In such case, You can perform the Database analysis, capture its results and at a later stage upload those results to your Tidal workspace.

These are the steps you need to follow in order to bypass internet access limitations:

1. On a Machine with internet access, you need to install, and configure Tidal Tools.

2. Package Tidal Tools and its required docker container images into a tar file, This will allow you to move the archive file into your restricted environment. To do so, run the following command.

   tidal backup

   Once it has finished, you will find (in your current location) a tar file called tidal-snapshot_DATE.tar. This is the file you need to transport into your internet restricted environment.

3. On the machine that has no internet access. You will now restore Tidal Tools using the following command.

   tidal restore tidal-snapshot_DATE.tar

   This will load a docker image and all the existing Tidal Tools configurations from the original machine. You can now run the database analysis without any external network connectivity, except to your database host itself:

   tidal analyze db --offline databases.yaml

   Note the --offline flag, it will indicate to Tidal Tools that the output needs to be stored in a file instead of being uploaded.

   After the analysis is completed, you will find a zip file called, db-analyze-<DATE>.zip that can then be transferred into a machine with internet connectivity.

4. Back to the machine with internet access. you can now upload your results to your workspace with this command.

   tidal analyze db --upload db-analyze-<DATE>.zip

   You should receive confirmation that the upload has been completed and can navigate to Tidal to see the results.

Why Docker?

You need to install Docker in order to complete the database analysis. This is because the analysis uses several system-dependent software libraries, so by using Docker the analysis can use those libraries without you requiring to install the correct dependencies with the correct versions.

What about security?

The entire analysis takes place locally on your machine. The only data that is captured and sent from the analysis are the results of the analysis and metadata. No application data, source code, files or the contents of any files on your machine are ever copied, or sent anywhere.

Advanced Configuration

Oracle Standard Edition

To use Oracle features included only in the Oracle Standard Edition (SE) license, you can set the analyze_workload property to false in your configuration file. For example:

databases:
  - id: 111
    name: "your-oracle-db"
    analyze_workload: false
    engine: Oracle
    host: 'my-db-host.com'
    port: 1521
    db_id_type: "Service Name"
    sid_or_service_name: "orcl"
    user: 'tidal'
    password: 'yoursecurepassword1234!'
    is_pdb: false
    cdb_sid: ""
    cbd_user: ""
    cbd_password: ""

Troubleshooting

If you are getting errors when trying to perform the analysis, it can help if you confirm that you do have network connectivity to the database.

Two commands you can use for this are:

1. dig your_db_host - This should return a DNS record, usually an A record, with an IP address. This means you are able to resolve the hostname of the database. If there is no IP address then you either need to adjust the hostname or you need to configure or adjust the DNS server for your operating system.

2. nc -vzn -w 10 your_db_host db_port This command should return Connected to your_db_host:db_port if it is able to connect. If it returns Connection Timed Out this means that it is not able to reach your database on this port. This could mean that you do not have the correct network connectivity to the database and may need to adjust firewalls or other network access. Or you are not providing the correct port that is open and listening for requests on the database.