Contents
Change History Repository - Tracking and Auditing Deployments and Changes

Change History Repository - Overview

Combine enables users to install a repository database that records all package deployments and database changes in a central location. On top, the Change History tool under Tools → Change History enables users to easily view and search the repository content, for tracking and auditing purposes.

The Change History Repository database (also referred to as the Combine Change History database) contains two tables that store two sets of data to track all code package deployments and outcome. The first table is called dbo.utbPackageDeployments and contains general package info. The second table is called dbo.utbPackageDeploymentDetails and holds extended database change info for each package execution. A detailed description of these tables and their columns is available later in this section.

The definitions and the location of the Change History Repository (i.e., the names of the SQL Server and the database where the repository is installed, as well as the authentication type and credentials to connect to the repository DB) are stored on each client machine. After the repository location is provided to Combine, the application can be configured to only store general package information in response to each package deployment, or to store both the general package info and detailed database change information. If a client machine is configured to only write general package info to the Change History repository, then every time a code package is deployed from that client machine, a single row (per deployment) will be stored in the dbo.utbPackageDeployments table. However, if the client machine is configured to write detailed database change information to the repository, then Combine will populate both the general package info as well as extended deployment details. In this case, Combine will write one row for each script and target-database pair to the repository table dbo.utbPackageDeploymentDetails, where each row describes the execution of a script against that target database.

Example: A code package contains two scripts. The first script is mapped to a Container with four target databases and the second script is mapped to a Container with five databases. If Combine is configured to only save general package deployment information then each time the package is executed, a single row will be stored in the repository in the table dbo.utbPackageDeployments. However, if the application is configured to also store extended package deployment details, then each time the package is deployed Combine will write a single row to the table dbo.utbPackageDeployments and also populate nine entries that describe the detailed database changes for each script and each target database in the table dbo.utbPackageDeploymentDetails.

Note: If you choose to only store general package info in the Change History Repository, you can later recover the detailed database change information. This recovery process is manual and is further explained in the section titled Recovering Detailed DB Change Info from the General Package Info.

GUI vs. CpaExec: When using the application, code packages can be deployed by using the Combine user-interface or through the CpaExec command line utility. If a client machine is configured to write to the Change History Repository, then all deployments that initiate either from the user-interface of from the CpaExec utility will be recorded in the repository. On top, the CpaExec utility offers the "ch" flag with which users can instruct the utility to discard the saving of deployment results to the repository.

Security: In regards to security, the Change History Repository contains four built-in SQL roles. These roles are described below, and different users can be added to one or more roles as dictated by the security policies in your organization (additional information can be found in the section called Change History - Installing the Repository Database):

(i) ChangeHistoryReadOnly - Users that belong to this SQL role can only read the repository content using the Change History tool. If these users deploy code packages, then Combine will not allow them to write to the Change History Repository and will alert them of that fact. This role is useful for non-developer or non-DBA users that need to audit database changes and work with auditors.

(ii) ChangeHistoryInsertOnly - Users in this group can populate the Change History Repository but cannot read, view, or search the repository. When users in this group deploy code packages, then Combine will write the change details to the repository however will not allow them to view the repository content in the Change History tool.

(iii) ChangeHistoryManagers - Users in this group have all the permissions of ChangeHistoryReadOnly and ChangeHistoryInsertOnly users (i.e., they can both populate and read the repository content), and also have direct SELECT and INSERT privileges on the database tables.

(iv) ChangeHistoryAdmins - Users in this group have all the privileges of ChangeHistoryManagers users and also have UPDATE and DELETE permissions on the repository tables.



Repository Tables: The Change History Repository tracks database changes and stores various details as described below. The table dbo.utbPackageDeployments that holds one row for each package deployment contains the following columns:

[ID] - Identity column. Used for internal purposes.

[DeploymentID] - Holds a unique identifier for each package deployment. This column is also used to map between general package information entries in dbo.utbPackageDeployments and the corresponding detailed DB change records in dbo.utbPackageDeploymentDetails.

[PackageName] - The name of the code package as it appears in the Package Explorer.

[PackageVer] - The version of the code package, as it appears in the properties of the root node of the code package.

[PackageFileName] - The path and name of the code package (cpa) file.

[EnvironmentName] - The name of the Environment against which the package was deployed.

[DeployedByWinUserName] - The name of the Windows (NT) user name that was logged onto the machine from which deployment was initiated.

[DeployedByWinDomainName] - The name of the domain that hosts the machine from which a package deployment was initiated. If the machine is part of a workgroup and is not a member in a domain, then this field holds the machine name.

[DeployedFromMachineName] - Machine name from which a package was deployed.

[DeployedFromIPAddress] - IP address of the machine from which a package was deployed.

[DeploymentStartTime] - The start time of package deployment, as it was recorded on the client machine.

[DeploymentEndTime] - The end time of package deployment, as it was recorded on the client machine.

[HadErrors] - A bit field that indicates whether any errors occurred during the execution of a package. If [HadErrors] is one, then the package encountered one or more execution errors.

[DeployedByCpaExec] - A bit field that indicates whether the package was deployed by the CpaExec command line utility.

[EnvironmentVariables] - If any Environment Variables are used during the execution of a code package (either in package scripts or in the Reference Query of Dynamic Containers), then this string stores the names of all Environment Variables and their replaced value.

[TimeZoneName] - The name of the time zone on the client machine from which a deployment was initiated.

[TimeZoneOffset] - The offset in minutes from GMT time, on the machine that initiated the package deployment.

[CombineResultsStream] - Holds the Package Results (Cre) file content.

[CombinePackageStream] - Holds the content of the Wrapped Package (Cpa) file.

[InsertTime], [LastUpdateTime], [LastChangeLogin], [LastChangeHost] - System columns for internal usage purposes.



The table dbo.utbPackageDeploymentDetails holds extended database change info. Each row in this table describes a database change, i.e., all details that pertain to the execution of a single script on a single database. This table contains the following columns:

[ID] - Identity column. Used for internal purposes.

[DeploymentID] - Holds a unique identifier for each package deployment. This column is also used to map between general package information entries in dbo.utbPackageDeployments and the corresponding detailed DB change records in dbo.utbPackageDeploymentDetails.

[ServerName] - The name of the server that holds the database against which a package script was executed.

[DatabaseName] - The name of the database against which a package script was executed.

[AuthenticationType] - The authentication type (SQL or Windows) used to connect and execute the package script on the target database.

[UserOrLoginName] - If a package script is executed by using SQL authentication, then this field holds the SQL login name that was used to execute the script on the target database. If Active Directory or Windows authentication is used, then this field holds the NT user name used to connect and execute the script on the target DB.

[ScriptName] - The name of the script executed.

[ScriptRelativePath] - The relative path of the script as it appears in the code package.

[ObjectName], [ObjectType], [ObjectVer] - System columns for internal usage purposes.

[DeploymentStartTime] - The time (on the client machine) at which the deployment of a script against the target database has started.

[DeploymentEndTime] - The time (on the client machine) at which the deployment of a script against the target database has completed.

[HadErrors] - A bit indicating whether the execution of a script against a target DB encountered errors. When [HadErrors] is equal to one, then the script had execution errors.

[ScriptText] – The text of the script. If the script contains Environment Variables or System Variables, then this field holds the original script text as it appeared in the package before any variable replacements.

[ScriptTextAfterVariableReplacement] - The content of the script as it was executed on the target database, after all Environment Variables and System Variables were replaced. If a script does not include any variables then the value of this field is empty and the deployed script can be found in the column [ScriptText].

[VariableReplacements] - If either Environment Variables or System Variables are used in the script, then this field holds the names of all variables and their corresponding replaced values.

[OutputMessages] - The output messages returned from the target DB in response to the execution of the script.

[InsertTime], [LastUpdateTime], [LastChangeLogin], [LastChangeHost] - System columns for internal usage purposes.



Related Topics:The Change History Repository supports many features and functionality for developers, DBAs, and auditors. These topics are discussed in the corresponding sections below:

1. How to install the Change History Repository database
2. Configuring client machines to use the Change History Repository
3. Configuring Combine to only store general package info in the repository
4. Configuring Combine to store general package and detailed change info in the repository
5. Opening a deployed package (cpa) file from the Change History Repository
6. Opening package results (cre) file from the Change History Repository
7. Populating the Change History Repository from a Cre file
8. Recovering detailed DB change info from the general package info
9. Change History Tool - Viewing Past Deployment Results
10. Change History - How to Search the Change History Repository
11. Change History Tool - Managing Grid Columns





© 2001-2017 JNetDirect, Inc. All Rights Reserved.