Cloning the database

Cloning the database#

Cloning creates a second virtual database with the same objects as an existing one, on a connection you choose. The two databases are then independent: renaming, editing or dropping either one leaves the other untouched.

Cloning copies metadata only. No data is moved, no physical table is created, and every materialized view in the clone starts unloaded.

The connection you clone onto may belong to a different provider than the original. That is what cloning is for: it is the first step of moving a virtual database from one system to another, for example from Spark to SQL Server. It must be a different connection than the one the database already runs on - putting a clone back where it started is not a migration.

Where you can clone from and to#

Cloning works between the database systems it has been established for, in any combination:

  • SQL Server

  • Spark

  • BigQuery

  • Db2

  • MySQL

  • StarRocks

  • Teradata

  • Vertica

  • PostgreSQL

  • Oracle

SAP HANA can be cloned from but not to, because Querona does not create materialized copies on it. Azure Synapse can likewise be cloned from but not to.

Databases on other connections - spreadsheets, files, REST services and the like - cannot be cloned. Their tables are not objects Querona creates; each one is an address in the system it came from, and a copy of that address would still point at the original. To build a second database over such a source, create a database on the connection and import its objects instead.

The Clone command appears only on databases that can be cloned and only when you hold Control on the database, and the target list offers only connections a clone can be created on.

What the clone gets#

The clone keeps everything the users of the database see - schemas, tables, views, functions, virtual object and column names, data types, comments, filters and access rights.

Everything that describes where the data physically lives is worked out again for the new connection:

  • physical and persistent table names are generated by the new connection’s own naming rules, so the clone can never write into the original’s materialized copies,

  • the physical type of every column is derived again from the new connection’s type system,

  • a caching mode the new connection cannot honour is replaced by the closest one it can, and the change is reported,

  • references in a view definition that named the original database by name are repointed at the clone.

Some things are not carried and have to be recreated on the clone. The result step lists each of them by name, so nothing goes missing quietly:

  • scheduled jobs - a job step names its objects as text and would keep acting on the original database,

  • database scoped credentials,

  • extended properties,

  • XML schema collections,

  • business term links - a term is shared across the whole server and objects only link to it, so the clone’s objects start unlinked.

A virtual database that contains encrypted columns cannot be cloned yet, because a column’s encryption is bound to the connection it was encrypted on.

Cloning a database#

Open the database in DATABASES and choose CLONE. The wizard has three steps.

Target. Give the clone a name and choose the connection it is created on. When the chosen connection does not name a catalog of its own, you are also asked for the source database on that connection. Choose ASSESS to see what cloning would do; nothing is created yet.

Review and adjust. The preview lists what will be cloned, the caching mode and generated names of every materialized view, the columns whose physical type changes on the new connection, and the findings. A finding that needs your attention before you continue - a collation the target cannot honour, scheduled jobs and credentials that are not carried, a caching mode the target could not be checked for - is listed as a warning; what was changed on the way, such as a view definition repointed at the clone, is listed as information. Neither stops the clone; the decision is yours.

Two things can still be changed here: the caching mode of a view, limited to the modes the target supports, and the physical name of a table. Changing either marks the preview out of date, because the server works the names, types and warnings out again from your choices. Choose REFRESH PREVIEW, then CLONE.

Result. The clone exists. The last step lists what is left to do by hand.

After the clone#

A cloned virtual database is complete as metadata, but the physical objects behind it do not exist yet. To finish moving a database to another system:

  1. Run the CREATE TABLE statements the result step lists, on the target connection, and move the data into those tables.

  2. Load the materialized views in the order the result step gives. The order puts every view after what it reads, so each one is loaded from data that is already there.

  3. Query the clone and compare it with the original.

What happens to the original afterwards is your decision. While both exist they read the same upstream sources, so a refresh of both costs twice what a refresh of one did; once the clone has taken over, the original can be dropped like any other virtual database.

Permissions#

Cloning requires the Create database server permission, Control on the database being cloned, and Control on the connection the clone is created on.

Control on the original is required because the clone is built from what you can see of it: a user who sees only part of a database would otherwise get a clone that is quietly missing objects. For the same reason, cloning is refused when View definition is denied to you on any object of the original, because you would then see only your own permissions on that object and the clone would carry an incomplete copy.

The clone’s access rights are the original’s, so everyone who could use the original can use the clone, your own access included, and nobody gains anything by the database having been cloned.

If the clone is created but not finished#

Cloning stores the database first, grants the original’s access rights on it second, and writes its materialization configuration last. If a later part fails, the wizard says which one and the clone still exists as a valid virtual database:

  • if the access rights could not be granted, only you can reach the clone, and its caching is not configured either;

  • if only the materialization configuration could not be written, the clone has its rights and every view in it reads through to its sources instead of from a cached copy.

Either drop the clone and clone again, or keep it and grant the rights or configure caching on it yourself.