× Heads up!

Aqua Data Studio / nhilam

Follow
IDE for Relational Databases
×
Like  

Schema Synchronization

After reviewing the schema compare results, select objects for synchronization, then click , and complete the synchronization process in the following order.

  1. Review Dependencies
  2. Map Missing Columns
  3. Configure Script
  4. Options
  5. Review

Before synchronizing the source and target schemas, we strongly recommend backing up data of the target schema.

Review Dependencies

The Review Dependencies tab shows objects selected for deployment and the dependent objects. The dependent objects are synchronized to prevent the deployment script from failing. Objects are displayed in the Affected Objects column because these objects are referred by or refer the objects in the Referencing Or Referenced By column. When you click a dependent object, you can view the differences between both the source and target objects in the default viewer.
 
If a dependent object supported in Aqua Data Studio is not supported in Schema Compare, then during deployment, Schema Synchronization will not include dependencies of this dependent object, and the deployment script will fail. To synchronize these dependencies, create the dependent object on the target server before executing the deployment script.
 
From the Dependent Objects area, you can include/exclude dependent objects for synchronization. However, you cannot unselect the dependent objects that are also present in the Objects Selected For Deployment area.
 

We recommend deploying all dependent objects, to avoid deployment script from failing or receiving unexpected results.

 
You can use Search to narrow down the result. You can also copy the list of dependent objects to a different editor.

Map Missing Columns

Schema Synchronization will show you this tab only when the target column name does not match the source column name.
 
To resolve this issue, you can perform any of these actions.

Map Target Column with the Source Column

You can prevent data loss from the target column by mapping the target column to the correct source column. 
 
In the Target Table Column Mapping pane, the columns are automatically mapped to Source Columns based upon on data type or other attributes that are compatible with the target column. You can retain this default selection or choose a different column from the drop-down list. 
 
To map the target column to a different column in the drop-down list, select the target column and then from the adjacent Source Column drop-down list, choose a column to map. 
  
Schema Synchronization will generate a Column renamed warning in the Warnings Tab. This warning means Schema Synchronization will rename the target column to the source column.
 
When you execute the deployment script, Schema Synchronization renames the target column name to match the source column name. 

Drop Column from the Target Table

From the Source Column, if you drop the target column then Schema Synchronization will generate a Column dropped warning, which means, Schema Synchronization will drop the column from the target table. 
 
When you execute the deployment script, Schema Synchronization drops that column from the target table.

Configure Script

Schema Synchronization will show you this tab only if it finds issues that might require user input. 

This table shows the severity for each warning that can affect the deployment script and the action you can take to resolve them.

Severity Action
High () Ignore issue
Medium () Specify custom value
Low () Allow NULL value

Configure Script Issues

Data must be truncated in this column

You will see this issue when the data type length of the target column exceeds the source column, and there is a high possibility that data of the target column will be truncated during synchronization.
 
To resolve this issue, you can perform any of these actions from the Action drop-down list.

Specify custom value

You can retain the data conversion function generated by Schema Synchronization or specify a new data conversion function for the target column. Schema Synchronization will generate a Possible data truncation warning in the Warnings tab. This warning means if the data type length of the target column exceeds the source column then there is a possibility of data truncation from the target column.

Ignore Issue

If you choose the Ignore issue option, Schema Synchronization will add the target column name in the deployment script, instead of the data conversion function and will generate a Data will be truncated warning in the Warnings tab. This warning means if the data type length of the target column exceeded the data type length of the source column, data in the target column might be lost.
 
When you execute the deployment script, the following happens:
  • If the target column contains more data than the source column, then data truncation might happen from the target column.
  • If the issue was ignored, then the possibility of deployment script failure and data loss from the target column will be high because schema synchronization will execute the script with the column name instead of the conversion function.
  • If data of the target column is not in the correct format, then the deployment script will fail, and data loss from the target column will occur.

Data type must be converted in this column

You will see this issue when the data type of the target column is incompatible with the data type of the source column, and there is a high possibility that data loss will occur from the target column during synchronization. 
 
To resolve this issue, you can perform any of these actions from the Action drop-down list.

Specify custom value

You can retain the data conversion function generated by Schema Synchronization or specify a new data conversion function for the target column. Schema Synchronization will generate an Ensure data is in the correct format warning in the Warnings tab. This warning means if data in the target column is not compatible with the data type of the source column, then the deployment script might fail.

Ignore Issue 

If you choose the Ignore issue option, Schema Synchronization will add the target column name in the deployment script, instead of the data conversion function and will generate an Ensure data is in the correct format warning in the Warnings tab. This warning means if data in the target column is not compatible with the data type of the source column, then the deployment script might fail.
 

When you execute the deployment script, the following happens: 

  • If the issue was ignored, then the possibility of deployment script failure and data loss from the target column will be high because Schema Synchronization will execute the script with the column name instead of the conversion function.
  • If data of the target column is not in the correct format, then the deployment script will fail, and data loss from the target column will occur.

No value specified for a NOT NULL column

You will see this issue only when:
  • A table exists in both the source and the target schema. 
  • A source table contains a NOT NULL column and this column has no default value assigned to it.

Schema Synchronization will verify the above-mentioned conditions. Only when these conditions are met, it will check for one of the following conditions:

  • A column exists in both the source and the target tables that are different in any attribute.
  • A column exists only in the source table. 
To resolve this issue, you can perform any of the following actions from the Action drop-down list. 

Specify custom value

You can specify a custom value for a NOT NULL column. Schema Synchronization will add this custom value as the default value for a NOT NULL column in the deployment script and will display a Default value will be added warning in the Warnings tab. This warning means the specified custom value will be added to a NOT NULL column. 
 
When you recompare the source and the target schemas and view the schema compare result, the objects will still be displayed in a different state because the default value was added to the target column.

Allow NULL value

You can allow a NULL value for a NOT NULL column. Schema Synchronization will retain the current structure of the target column and will not generate the deployment script if the target column is NULL. However, in certain scenarios, it would generate the deployment script for the target column, to alter the NOT NULL column to NULL.

Ignore Issue

If you choose the Ignore issue option, Schema Synchronization will add NOT NULL for the target column and will add no value for this column in the deployment script and will display a No value specified for a NOT NULL column warning in the Warnings tab. This warning means no value is specified for the NOT NULL column. 
 
When you execute the deployment script, the deployment script will fail only if the NULL column in the target database contains a NULL value.

Constant value must be assigned in this column

You will see this issue when the data type of the target column cannot be converted to the data type of the source column and the source table contains a NOT NULL column.
 
To resolve this issue, you can perform any of these actions from the Action drop-down list.

Specify custom value

You can retain the constant value generated by Schema Synchronization or specify a new custom value for a NOT NULL column. Schema Synchronization will add the custom value as the constant value for a NOT NULL column in the deployment script and will display a Constant value will be assigned warning in the Warnings tab. This warning means Schema Synchronization generated or new constant value will be added for the NOT NULL column.

Ignore Issue

If you choose the Ignore issue option, Schema Synchronization will add the constant value of a data type in the deployment script defined by Schema Synchronization itself and will display a Constant value will be assigned warning in the Warnings tab. This warning means the constant value of a data type will be added in the deployment script. 
 
For both the above actions, when you execute the deployment script, data in the target column will be lost.

Arithmetic overflow can occur

You will see this issue when the data type range or the data type precision of the target column exceeds the source column, and there is a high possibility that data loss will occur from the target column during synchronization. 
 
To resolve this issue, you can perform any of these actions from the Action drop-down list. 

Specify custom value

You can retain the data conversion function generated by Schema Synchronization or specify a new data conversion function for the target column. Schema Synchronization will display a Column precision or data loss warning in the Warnings tab. This warning means if the data type range or the data type precision of the target column exceeded the source column then there is a possibility of deployment script failure or data loss from the target column.

Ignore Issue 

If you choose the Ignore issue option, Schema Synchronization will add the target column name in the deployment script, instead of the data conversion function and will generate an Arithmetic overflow will occur warning in the Warnings tab. This warning means if the data type range or the data type precision of the target column exceeded the data type range or the data type precision of the source column, the deployment script might fail.
 
When you execute the deployment script, the following happens:
  • If the target column contains more data than the source column, then data loss from the target column might occur.
  • If the issue was ignored, then the possibility of deployment script failure and data loss from the target column will be high because Schema Synchronization will execute the script with the column name  instead of the conversion function.

Grant Issues

Grants for missing role cannot be deployed

If a role exists on the source server but not on the target server, then the deployment script will fail. 
  • If you skip this issue, Schema Synchronization does not generate the deployment script for the missing role. 
  • If you ignore this issue, you will see the Grants for missing role warning in the Warnings tab.

If you execute the deployment script without creating the missing role in the target database, the deployment script will fail.

Grants for missing user cannot be deployed

If a user exists on the source server but not on the target server, then the deployment script will fail. 
  • If you skip this issue, Schema Synchronization does not generate the deployment script for the missing user.
  • If you ignore this issue, you will see the Grants for missing user warning in the Warnings tab.

If you execute the deployment script without creating the missing user in the target database, the deployment script will fail. 

Grants on missing grantees cannot be deployed

If a grantee, such as; Role, User or Group exists on the source server but not on the target server, then the deployment script will fail. 
  • If you skip this issue, Schema Synchronization does not generate the deployment script for the missing grantee.
  • If you ignore this issue, you will see the Grants on missing grantees warning in the Warnings tab.

If you execute the deployment script without creating the missing grantee in the target database, the deployment script will fail.

Grants for missing group cannot be deployed

If a group exists on the source server but not on the target server, then the deployment script will fail. 
  • If you skip this issue, Schema Synchronization does not generate the deployment script for the missing group.
  • If you ignore this issue, you will see the Grants for missing group warning in the Warnings tab. 

If you execute the deployment script without creating the missing group in the target database, the deployment script will fail.

Options

Schema Synchronization will display various options that you can include or exclude from the deployment script.

Statement Separator (All Servers)

The default statement separator is GO, but you can set a new statement separator for the deployment script. When you set a new separator, ensure that you set the same separator in File > Options, so the Query Analyzer identifies this separator while executing the target script. Otherwise, the deployment will fail.
For example, if you change the statement separator from GO to “;” but do not select the same separator in the File > Options > General, then the deployment will fail because on executing the target script, the Query Analyzer will fail to identify “;” as the statement separator.
To set the ';' statement separator for a specific database; on the Aqua Data Studio menu bar, click File > Options > Scripts > [Database_Name] > ';' Statement separator and check it.

Qualify Objects with Schema (All Servers)

You can display the schema name with the object name in the generated deployment script by selecting the Qualify Objects with Schema check box. However, if you have selected All Schemas for compare, then this check box becomes read-only and selected. 
You can display a descriptive header containing detailed information about the generated script by selecting the Include Descriptive Header check box.

Include Descriptive Comments (All Servers)

You can display descriptive comments containing actions to perform by Schema Synchronization by selecting the Include Descriptive Comments check box.

Quote Identifier (All Servers)

You can also set a quote identifier in the script. Schema Synchronization uses the quote identifier set in Script options for schema object names when it generates the deployment script. To choose a quote identifier; on the Aqua Data Studio menu bar, click File > Options > Scripts > [Database_Name] > Quote Identifier.
 
If you change the default quote identifier, you are required to relaunch Schema Synchronization. 

Server-Specific Options

This section contains information on the server-specific options.

MS SQL Server

Include Print Comments 
When you select this check box, you will see a print comment for every action that Schema Synchronization will perform in the deployment script. After the deployment script is executed, you can see the comment of each action in a print command with its execution status in the Text Results Tab.
Refresh views 
When you select the Refresh Views check box, you can update the definition of the target view. Schema Synchronization will refresh the target view only when these conditions are met.
  • Both the source and the target views are created using SELECT *.
  • The same definition is present in both the source and the target views.
  • Tables on which these views are created have different column names/numbers.

When the objects with different column names or a different number of columns are synchronized, Schema Synchronization updates the column definition of the target object like that of the source object. When the column definition of the object is changed, the metadata of the view that depends on this object becomes invalid. 

Now consider this scenario: The source table contains c1, c2 columns, and the target table contain c1, c2, c3, c4 columns.

Check box selected

The definition of the target view will be refreshed based on the updated target object as Schema Synchronization will add the “sp_refreshview” procedure in the deployment script. This procedure will update the definition of the target view. So, in the following example, after the script is executed, c3 and c4 columns are dropped from the target table, and the view is refreshed.

 

 Check box unselected

The definition of the target view does not change based on the changes made to the target table and therefore the view definition will become invalid. So, in the following example, after the script is executed, c3 and c4 columns are dropped from the target table, but the view is not refreshed.

Use a Single Transaction 
When you select this check box, Schema Synchronization inserts transaction statements in the deployment script. If the deployment script fails, these transaction statements allow the target server to roll back to its initial stage by reverting all modifications made from the start of the execution of the deployment script. If a single statement in the deployment script fails, the complete transaction will fail. 

Sybase ASE Server

Generate setuser 
In certain scenarios, the Sybase ASE Server does not add a different schema name in the deployment script. If you register the Sybase ASE Server with Aqua Data Studio using SA/DBO privilege, the default schema is set to “dbo.” Now, when you choose a different schema instead of the default schema (dbo) for the target server, Schema Synchronization overrides your schema selection and execute the deployment script in the default schema (dbo).
 
To use a different schema name, we recommend that you select the Generate setuser check box. On selecting this check box, Schema Synchronization generates a "setuser" command for the desired target schema in the deployment script. Ensure you have SA/DBO privileges for executing this command. When the deployment script is executed, it is executed on the selected schema.

Review

The last tab in Schema Synchronization is the Review tab that shows you the generated script, summary actions and modifications performed on the objects of the target schema and warnings. If the deployment script contains warnings, then the Warnings tab will be displayed first, instead of the Deployment Script tab.

Warnings Tab

Schema Synchronization will display warnings encountered while creating the deployment script. The warnings are categorized as High, Medium, and Low. You can also view the changes that Schema Synchronization will make in the script under the Summary and Detail column. You can hover the mouse on any warning in the Detail column and view the entire warning description in a tooltip. For additional information on warnings, see Schema Synchronization Warnings. Beside this, you can perform various other actions, such as searching warnings by using various search options or copying warnings to a different editor.

Summary Tab

You see actions and modifications that Schema Synchronization will perform on the objects of the target schema. You can view these actions in different groups by selecting an option from the View by drop-down list.

  • Object: Groups actions based on objects.
  • Modification: Groups actions based on modifications.
  • Modification (in order): Groups actions based on modifications and displays them in order.

Other actions you can perform in this tab are searching summary actions by using various search options and copying summary actions to a different editor.

Deployment Script Tab

In the Deployment Script tab, you see the generated SQL script to synchronize the source and target schemas. The comments above each SQL statement are the modifications that Schema Synchronization will perform on the objects of the target schema. There are various actions you can perform in this tab, such as saving the script in the .sql format on your local system, searching for DDL statements within the script, copying the script to a different editor, and collapsing the code to a single line using toggles. After you have reviewed the script, click Open Script in Query Analyzer, and then click , to execute the deployment script on the target schema.
 

We recommend backing up the target database and executing the script in the test environment before executing it in the production environment.

 

When you execute the deployment script, Schema Synchronization drops that column from the target table.

Last updated by nhilam on Aug 14, 2024 2:08 PM

About AquaClusters Privacy Policy Support Version - 19.0.2-4 AquaFold, Inc Copyright © 2007-2017