Multi-database projects are supported for only SQL Server databases.

Creating and Managing the Project

Using the Datical DB GUI to Create the Project

When you create the project, check Schema to be managed are in multiple database (SQL Server only).
When you specify multiple database, the Multiple Schema setting is also set for the project automatically.
When selecting the Schema to Manage in This Deployment Plan the values will be added to the datical.project file as eg. <schemas name="schema"/>. These values will need to be modified after creating the project to be in the format: <schemas name="databaseName.schema"/>. The values must also be copied and added for each database in the project. (See below Example Project section.)

Using the project_creator.groovy script to Create the Project

Alternatively you can use the project_creator.groovy script to create projects.

Please see the example MultiCatalog MSSQL .tsv files and details here: Creating a Datical Project Using the Project Creation Script (project_creator.groovy)

Representation in Project File

The following elements distinguish a multi-database project:

multiCatalog attribute
multiSchema attribute
schemas statement: dot-notated schema names of the form <database>.<schema>.

The dbDefs statements for SQL Server databases also require a databaseName statement, which is used only for connection checking. Use the name of one of the managed databases.

databaseName=<database>

Example Project

Note the following parts of the multi-database datical.project file listing

Attributes on <dbproject>
multiSchema="true"
multiCatalog="true"
trackingSchema="DATABASE_1.TRACKING_SCHEMA"
Dot notation for databaseName.schema in the <schemas> statements. The example specifies two databases, with two schema each.

<schemas name="DATABASE_1.SCHEMA_A"/>
<schemas name="DATABASE_1.SCHEMA_B"/>
<schemas name="DATABASE_2.SCHEMA_A"/>
<schemas name="DATABASE_2.SCHEMA_C"/>
In the dbDefs statement, include databaseName attribute. Datical DB uses this information for an internal check of the database connection. Set this attribute for each DbDef.

databaseName="DATABASE_1"
Important

You will only have one dbDef for an environment, eg. REF, DEV, TEST, PROD, etc. The additional databases will be listed in the schemas nodes as part of the <schemas name="databaseName.schema"/> notation.

The sample project includes one step (REF).

Multi-database datical.project file

?xml version="1.0" encoding="ASCII"?>
<dbproject:Project 
  xmi:version="2.0" 
  xmlns:xmi="http://www.omg.org/XMI" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:dbproject="http://datical.com/db/project/1.0" 
  name="MyMultiDBProject" 
  storedLogicExternal="true" 
  deployThreshold="stopOnError" 
  runtimeCredentials="false" 
  multiSchema="true" 
  dbSchemaDefinition="project" 
  schemaSelectionStep="REF" 
  trackingSchema="DATABASE_1.TRACKING_SCHEMA" 
  enableRowCount="true" 
  multiCatalog="true">
  <dbDefs xsi:type="dbproject:SqlServerDbDef" 
     name="REF" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
     databaseName="DATABASE_1"
	 hostname="localhost" 
	 port="1433" 
	 username="user_name" 
	 password="pwd_for_user_name" 
	 contexts="" 
	 integratedSecurity="false" 
	 applicationName="Datical DB"/>
  <plans name="MyMultiDBProject" databaseDefs="//@dbDefs[name='REF']"/>
  <changelog href="Changelog/changelog.xml#//@databaseChangeLog"/>
  <schemas name="DATABASE_1.SCHEMA_A"/>
  <schemas name="DATABASE_1.SCHEMA_B"/>
  <schemas name="DATABASE_2.SCHEMA_A"/>
  <schemas name="DATABASE_2.SCHEMA_C"/>
</dbproject:Project>

SQL Script Coding

Datical DB requires that you follow some coding conventions in order for it to package SQL scripts correctly or create change sets correctly for a multi-database project.

Each SQL script is required to identify the database it is working on. You can provide the database names using these methods:
- USE DATABASE statement for packaging scripts in ddl and sql_direct folders (or for creating "Execute with sqlcmd for Microsoft SQL Server and Azure SQL databases" change sets)
- With deploy packager you could create a metadata.properties file that specifies schemaName as <databaseName>.<schema>
Object references in a SQL script must be unambiguous.
- Use fully qualified object references: <schema>.<object>.
- Do not use unqualified object references: <object>.
- External (cross-database) object references must be fully specified: <databaseName>.<schema>.<object>

See SQL Server Database Objects and Packaging.

Cross-database References

Within the body of a SQL statement you can refer to an object that belongs to another database.

Use a fully-qualified object name: <databaseName>.<schema>.<object>.

Normal SQL Server rules apply when you create or modify an object in a script. For example, in a script using the context of one database:

You can create an object for the current database object (a table, a procedure, a view, and so on).
You cannot create an object for another database object.

See SQL Server documentation for more information.

Packaging

Provide values for configuration options before packaging SQL scripts. SQL scripts are organized in folders per object type.

See these pages for more information:

File Hierarchy for Multi-Database Projects

A best practice is to use the target database names as top-level folders.

<sql_code>
   DatabaseOne
     SchemaA
       ddl
       [...]
     SchemaB
     [...]
   DatabaseTwo
     [...]
   DatabaseThree
     [...]

Under each database folder you arrange folders by schema and then SQL type as normal, either using the fixed-name folders or flexible-name folders.

Using Only Database Folders

You can manage all files under only the database folder as well. Use a wildcard in the the schemaName attribute in metadata.properties: <database>.*. If you use this arrangement, the object references in the SQL scripts must be fully qualified: <schema>.<object>.

metadata.properties

The best practice for working with multi-database projects to provide a metadata.properties file for each packaging folder. The schemaName attribute provides the database and schema context to be used by all SQL scripts in the folder. The settings apply to the current folder and all sub-folders.

schemaName - Specify both the database and schema in the form <database>.<schema>. For all SQL files this option provides the value of the database name and schema name to use if one is not specified in the SQL script. The packager tests that the object reference is valid, using the list of schema names provided. For multi-database projects, you can use a wildcard for the <schema> if you wish to manage all SQL scripts in the database folder and not create folders per schema. Example: <database>.*

See Using the metadata.properties file, especially the "Using schemaName with Multi-schema and Multi-database Projects" section.

Packaging Files in sqlcmd Directories

During packaging, the database specified is used to add a USE <databasename> statement to the SQL files being packaged.

Operations

Use the Datical DB CLI for managing the project and performing operations

package (including backup, snapshot, and compare)
status
forecast
deploy
rollback

DMC

When you view changes in DMC for multi-database projects, the database name is not currently displayed. If you would like to see the database name for your changes in DMC, consider using labels or file naming conventions that include the database name.

Using Properties for Database Names

Some customers who use the multi-database project configuration use properties for their database names. This can be useful if your database names aren't the same for every environment. You can use the contexts that are set for each dbDef to indicate the properties in the changelog.

Here is an example of using contexts in the dbDefs section of the datical.project file:

<dbDefs xsi:type="dbproject:SqlServerDbDef" name="DEV" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" hostname="host" port="1433" username="DATICAL_USER" contexts="DEV" labels="development" environment="DEV" databaseName="BDM_SMT_INV_DEV" integratedSecurity="false"/>
<dbDefs xsi:type="dbproject:SqlServerDbDef" name="QA2" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" hostname="host" port="1433" username="DATICAL_USER" contexts="QA2" labels="sprint" environment="QA" databaseName="BDM_SMT_INV_QA2" integratedSecurity="false"/>
<dbDefs xsi:type="dbproject:SqlServerDbDef" name="QA1" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" hostname="host" port="1433" username="DATICAL_USER" contexts="QA1" labels="release" environment="QA" databaseName="BDM_SMT_INV_QA1" integratedSecurity="false"/>

Here is an example of how the database names and schema names would look in the managed schemas section of the datical.project file:

<schemas name="${BDM_SMT_INV}.AppData"/>
<schemas name="${BDM_SMT_INV}.AppObject"/> 
<schemas name="${BDM_SMT_INV_EU}.AppData"/>
<schemas name="${BDM_SMT_INV_EU}.AppObject"/>
<schemas name="${BDM_SMT_INV_GB}.AppData"/>
<schemas name="${BDM_SMT_INV_GB}.AppObject"/> 
<schemas name="${BDM_SMT_INV_APAC}.AppData"/>
<schemas name="${BDM_SMT_INV_APAC}.AppObject"/>

Here is an example of how the properties would look in the changelog.xml file:

<property context="DEV" labels="" name="BDM_SMT_INV" value="BDM_SMT_INV_DEV"/>
<property context="DEV" labels="" name="BDM_SMT_INV_EU" value="BDM_SMT_INV_EU_DEV"/>
<property context="DEV" labels="" name="BDM_SMT_INV_GB" value="BDM_SMT_INV_GB_DEV"/>
<property context="DEV" labels="" name="BDM_SMT_INV_APAC" value="BDM_SMT_INV_APAC_DEV"/>

<property context="QA2" labels="" name="BDM_SMT_INV" value="BDM_SMT_INV_QA2"/>
<property context="QA2" labels="" name="BDM_SMT_INV_EU" value="BDM_SMT_INV_EU_QA2"/>
<property context="QA2" labels="" name="BDM_SMT_INV_GB" value="BDM_SMT_INV_GB_QA2"/>
<property context="QA2" labels="" name="BDM_SMT_INV_APAC" value="BDM_SMT_INV_APAC_QA2"/>

<property context="QA1" labels="" name="BDM_SMT_INV" value="BDM_SMT_INV_QA1"/>
<property context="QA1" labels="" name="BDM_SMT_INV_EU" value="BDM_SMT_INV_EU_QA1"/>
<property context="QA1" labels="" name="BDM_SMT_INV_GB" value="BDM_SMT_INV_GB_QA1"/>
<property context="QA1" labels="" name="BDM_SMT_INV_APAC" value="BDM_SMT_INV_APAC_QA1"/>

See also these pages for general information about using properties: