Change History Repository
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
Installing the Change History Repository
Combine enables users to maintain a Change History database that records all details and actions in response to the deployment of code packages (see the Overview section for detailed information regarding the Change History Repository). To install the repository database, please follow the instructions below:
1. Locate the SQL script that installs the repository. It is called “Create Change History Repository on SQL 2005 or later.sql” and can be found at the download section on JNetDirect’ website and are also available under the Combine installation directory (for example, under the folder: C:\Program Files\JNetDirect\Combine\Repository\CombineChangeHistory).
2. Create the repository database on a SQL Server: Locate the SQL Server that will hold the repository database (this SQL Server can reside anywhere on the network where it can be accessed by machines and users running Combine). Log on to the SQL server and to the Master database as an administrator (either as the sa user or as a domain administrator). Run the script “Create Change History Repository on SQL 2005 or later.sql”.
The executed SQL script creates a database called CombineChangeHistory, the schema (i.e., tables and stored procedures) for the database, as well as four SQL roles: ChangeHistoryReadOnly, ChangeHistoryInsertOnly, ChangeHistoryManagers, and ChangeHistoryAdmins. Users that belong to the ChangeHistoryReadOnly user group will be able to read and search the repository content however will not be able to write and track deployment results. Users in the ChangeHistoryInsertOnly group are only permitted to populate the repository to record package execution details, yet are not allowed to read or search the repository. The other two roles, namely ChangeHistoryManagers and ChangeHistoryAdmins can populate the repository and view the recorded content. Additional information regarding these SQL roles is available in the Overview section.
Note: By default, the name of the Change History Repository database is CombineChangeHistory. You can change this name and use any other database by opening and editing the SQL script that installs the repository DB.
3. Define users for the Change History Repository – make sure that all users that will be using the Change History Repository have access to the repository database. At this point you should also set the permissions and access restriction to the repository: It is recommended that all users that deploy code packages will be added to the ChangeHistoryInsertOnly group and that users that need to search and view the deployment data will be members of the ChangeHistoryReadOnly group. Advanced users (e.g., production DBAs) should be added to the ChangeHistoryManagers role, and non-developers or DBA users that work with auditors should be added to the group called ChangeHistoryReadOnly. For your convenience, a SQL script called “Examples – Adding users to Repository roles.sql” contains examples of how to add users to the different SQL roles and is available on JNetDirect’ website as well as under the Combine installation directory (e.g., C:\Program Files\JNetDirect\Combine\Repository\CombineChangeHistory).
4. Instruct users to define the Change History Repository in the client user-interface in Combine. To do so, each user should go to Tools → Options → Packages → Change History, and provide the connection information for the SQL Server and database that hold the repository. For more details please see Configuring Client Machines section.
Updating the Change History Repository
Combine enables users to maintain a Change History database that records all details and actions in response to the deployment of code packages (see the Overview section for detailed information regarding the Change History Repository). To update the repository database, please follow the instructions below:
1. Locate the SQL script that updates the repository. It is called “Update Change History Repository.sql” and can be found at the download section on JNetDirect’s website and are also available under the Combine installation directory (for example, under the folder: C:\Program Files\JNetDirect\Combine\Repository\CombineChangeHistory).
2. Update the repository database on a SQL Server. To do it log on to the SQL server and to the Master database as an administrator (either as the sa user or as a domain administrator). Run the script “Update Change History Repository.sql”
The executed SQL script updates the database CombineChangeHistory and its schema (i.e., tables and stored procedures) to be compatible with the latest Combine version. The script can be run on a repository database of any Combine version since 4.0. It also correctly handles the case when it is run multiple times on the same database.
Note: By default, the name of the Change History Repository database is CombineChangeHistory. You can change this name (if you are using another database) by opening and editing the SQL script that updates the repository DB.
Note: Additional information regarding change history repository settings and configuration options is available in the Configuring Client Machines section.
Configuring Client Machines to Use the Change History Repository
After installing a Change History Repository database, all users and machines that wish to write to the repository and track package deployment results, should configure Combine with the location and credentials of the repository. Specifically, each user should go to Tools → Options → Packages → Change History, and provide the connection information for the SQL Server and database that hold the repository (see image below).
You can either select a server from the drop-down list, or alternatively enter a server name manually. If you need to specify a server port, you can enter it after the server name separating by comma. You should then enter the authentication type that will be used to register all the selected servers. Available authentication types are Windows Authentication and SQL Authentication, and Login name and password will be required if you are registering the servers using SQL Authentication. These credentials will be stored using strong encryption techniques to prevent others from viewing your credentials. Also you are able to modify all SQL server connection options using Advanced button.
If any of the settings are incorrect and Combine is configured to write to the Change History Repository to track deployments, then Combine will alert users of this fact.
In addition, the following configuration options are available to users working with a Change History repository:
– Store Extended Package Deployment Details: If this option is turned off then each time a user deploys a code package, only general package information (i.e., one row per package deployment) will be recorded in the Change History repository. However, when this option is checked, then Combine will store detailed DB change info (i.e., one row per each script and target DB pair) as well as the general package info in the repository. Additional information regarding general vs. detailed change info is available in the Repository Overview section.
– Test Connectivity to Change History Repository database: This option instructs Combine to test that a connection can be established to the Change History Repository database before the execution of a code package, when packages are deployed either from the user-interface or from the CpaExec command line utility. The authentication type and credentials used to connect to the database are those provided in the Options section shown in the image below.
Additional settings are also available under Tools → Options → Packages → Auto-Save Results and allow users to configure when and how Combine should write the deployment results to the Change History repository. For example, by setting the Auto-Save Package Results to Change History Repository, users can instruct Combine to always write to the repository, prompt and ask whether to track deployments, or never write to the change history database. When set to Prompt, each time users deploy packages from the user-interface they will be prompted to select whether to track execution results. For the CpaExec command line utility, users can activate the “ch” flag to write to the Change History repository.
Storing Only General Package Info in the Change History Repository
The Change History Repository enables users to write deployment results to a central repository database and then view and search the outcome of previously deployed code packages. Two sets of data are stored in the repository – general package deployment info and detailed database changes. General package information consists of a single row for each package deployment, whereas detailed changes track all changes made to target databases during the package deployment. The detailed info stores a single row for each script and each target database. Additional information regarding the records stored in the repository can be found in the Overview section.
Users can configure Combine on their client machines to either:
– Write only general package info to the Change History Repository
– Write both general package info and detailed database changes to the repository
To instruct Combine to write only general package info to the Change History Repository, users should go to Tools → Option → Packages → Change History. First, users must enter the location of the Change History Repository database and provide the credentials that will be used to connect and use (i.e., read from or write to) the repository. Then, users should uncheck the checkbox Store Extended Package Deployment Details and click the Apply button in the Options dialog.
If you choose to only store general deployment results, then Combine only writes a single row to the repository for each package deployment (you can still manually recover and populate the details from the general package info; for more info see the section titled Recovering Detailed DB Change Info from the General Package Info). In practice, this means that under the Change History tool (under Tools in the main menu) you will only see the general information relating to the execution of code packages. For example, if a code package contains 10 scripts and each script is executed against a Container with 5 target databases, then one row will be written to the repository. When viewing the deployment results as in the image below, all users that can read and search the repository will only see one row in the general info top grid for this deployment. This row contains the deployment start and end times, the name of the Environment on which the package was deployed, description of Environment Variable replacements (when applicable), the content of the Cre and the (wrapped) Cpa file, and other relevant fields (see the Overview section for complete details).
Advantages and Disadvantages. When choosing whether to only store general package info or to also record the detailed database changes, users should consider the following two factors:
1. After a package is executed, if Combine is configured to only store the general package deployment results, then only one row is written to the repository. On the other hand, if Combine also stores the detailed database changes, then one row will be written to the repository for each script and target database pair. For example, when Combine stores detailed info and a package contains 100 scripts and each script is executed on 10 target databases, then Combine will write the one row containing the general info plus 1000 rows containing details of DB changes. Therefore, writing database change details will take longer than only writing the general deployment info.
2. When using the Change History tool to view past deployment results, users can search the repository to find exact deployment details (e.g., all scripts that were deployed on a certain database, scripts that were executed using a certain SQL login, and so on). However, if you choose to only store the general deployment info, then searches are limited in accuracy and capabilities.
Related Topics. Combine can also be configured to prompt and ask users whether deployment results should be written to the repository before a package is executed. Alternatively, users can instruct Combine to always write the deployment results. To set these options, go to Tools → Options → Packages → Auto Save Results and select the desired option from the drop-down menu next to the Change History Repository entry (see image below). If you select Prompt, then Combine will prompt and ask you whether you wish to store deployment results in the repository before a package is executed. On the other hand, you can set this option to True to always store the deployment results. The last option (i.e., False) allows you to choose to not be prompted and not store any execution results to the repository.
Storing General Package Info and Detailed DB Changes in the Change History Repository
The Change History Repository enables users to write deployment results to a central repository database and then view and search the outcome of previously deployed code packages. Two sets of data are stored in the repository – general package deployment info and detailed database changes. General package information consists of a single row for each package deployment, whereas detailed changes track all changes made to target databases during the package deployment. The detailed info stores a single row for each script and each target database. Additional information regarding the records stored in the repository can be found in the Overview section.
Users can configure Combine on their client machines to either:
– Write only general package info to the Change History Repository
– Write both general package info and detailed database changes to the repository
To instruct Combine to write both general package info and detailed database changes, users should go to Tools → Option → Packages → Change History. First, users must enter the location of the Change History Repository database and provide the credentials that will be used to connect and use (i.e., read from or write to) the repository. Then, users should check the checkbox Store Extended Package Deployment Details and click the Apply button in the Options dialog.
If you choose to store both general package and detailed DB change info, then Combine writes one row to the repository database with the general information and an additional one row for each script and target database pair. For example, if a code package contains 10 scripts and each script is executed against a Container with 5 target databases, then Combine will write a total of 51 rows to the repository. When viewing the deployment results as in the image below, all users that can read and search the repository will only see one row in the general info top grid and 50 rows in the bottom (detailed) grid. The top grid shows the package deployment start and end times, the name of the Environment on which the package was deployed, description of Environment Variable replacements (when applicable), the content of the Cre and the (wrapped) Cpa file, and other relevant fields (see the Overview section for complete details). The bottom grid lists the script execution start and end times, the name and content of the script that was executed, the name of the target database and server, the login and authentication type used during execution, and more (see image below).
Advantages and Disadvantages. When choosing whether to only store general package info or to also record the detailed database changes, users should consider the following two factors:
1. After a package is executed, if Combine is configured to only store the general package deployment results, then only one row is written to the repository. On the other hand, if Combine also stores the detailed database changes, then one row will be written to the repository for each script and target database pair. For example, when Combine stores detailed info and a package contains 100 scripts and each script is executed on 10 target databases, then Combine will write the one row containing the general info plus 1000 rows containing details of DB changes. Therefore, writing database change details will take longer than only writing the general deployment info.
2. When using the Change History tool to view past deployment results, users can search the repository to find exact deployment details (e.g., all scripts that were deployed on a certain database, scripts that were executed using a certain SQL login, and so on). However, if you choose to only store the general deployment info, then searches are limited in accuracy and capabilities.
Related Topics. Combine can also be configured to prompt and ask users whether deployment results should be written to the repository before a package is executed. Alternatively, users can instruct Combine to always write the deployment results. To set these options, go to Tools → Options → Packages → Auto Save Results and select the desired option from the drop-down menu next to the Change History Repository entry (see image below). If you select Prompt, then Combine will prompt and ask you whether you wish to store deployment results in the repository before a package is executed. On the other hand, you can set this option to True to always store the deployment results. The last option (i.e., False) allows you to choose to not be prompted and not store any execution results to the repository.
Opening a Previously Deployed Package (Cpa) File from the Repository
When using a Change History Repository, users can retrieve the content of previously deployed code packages from the repository database. To do so, start the Change History tool from the main menu by selecting Tools → Change History. In this tool, enter the search conditions and click the Play button to show the general package info. Next, right-click the deployment row in the top (General Package Info) grid and select the option Open Package to open the content of the deployed package in the Package Explorer. Alternatively, you can also select the Save Package As option from the right-click menu to the save the (wrapped) code package to a cpa file, and then open the file in Combine by going to Open → Open Package.
Note: Users can only retrieve the content of code packages from the repository if they have read permissions in the repository database.
Opening a Package Results (Cre) File from the Repository
When using a Change History Repository, users can retrieve the Package Results (Cre) file that was generated in response to the execution of a code package. To open a Cre file from the repository, start the Change History tool from the main menu by selecting Tools → Change History. In this tool, enter the search conditions and click the Play button to show the general package info. Next, right-click the deployment row in the top (General Package Info) grid and select the option Open Results to open the Cre file for the deployed package in the editor. Alternatively, you can also select the Save Results As option from the right-click menu to the save the cre file, and then open the file in Combine by going to Open → Open File.
Note: Users can only retrieve the content of code packages from the repository if they have read permissions in the repository database.
Resuming Package Execution
When using a Change History Repository, users can resume the package deployment that has not been completely finished. To do so, start the Change History tool from the main menu by selecting Tools > Change History. In this tool, enter the search conditions, set the “Resumable Only” flag, and click the Play button to show the general package info. Next, select the desired deployment row in the top (General Package Info) grid, right-click it, and select the “Resume Package Execution” option to restart the deployment. Each failed or incompleted script will be executed against databases where it failed or was not executed in the previous deployment. A new deployment will be added to the Repository.
Note: Users can only retrieve the content of code packages from the repository if they have read permissions in the repository database.
Populating the Change History Repository from a Cre File
If you deploy a code package with Combine 3.0 or later, and save the Package Results (Cre) file, you can populate the Change History Repository directly from the cre file. This feature is extremely useful in two key scenarios:
1. If you deploy a code package and did not automatically write the deployment results to the Change History Repository, however now you wish to populate the repository with the deployment info and the cre file for that deployment is available.
2. If Combine is configured to only store general package info in the Change History Repository (i.e., only the general package info was stored in the repository for some previously deployed packages) and you now wish to also populate the detailed database changes in the repository.
In order to load the information from a cre file to the Change History Repository, you must first open the cre file in the editor. Then, hit the Save to Change History button on the top left hand side of the cre (see image). Next, Combine will prompt you and ask whether you wish to save the detailed DB change info. If you select No, then only the general package info will be written to the repository. However, if you select Yes then both the general package info and the detailed database change info will be written to the repository. Note that if the repository already contains the general package info (but not the detailed DB changes) for this deployment, then you can still select Yes in order to populate the detailed database change info. If the repository already contains both the general and detailed execution results, then Combine will not re-populate the repository and will notify you of this fact. To learn more about the differences between general package info vs. detailed DB changes, please refer to the Change History Overview section.
Recovering Detailed DB Change Info from General Package Info
Users that utilize a Change History Repository to store the execution results of code packages can configure Combine to only store general package and deployment information in the repository database. Alternatively, users can configure the application to store general info as well as details regarding all database changes. In addition, users can choose to store general info for some package deployments and detailed DB changes for other package executions (for additional information regarding the content of the repository, please refer to the Change History Overview section).
Assume that for some deployments, Combine stored the general package information in the Change History Repository and that a user now wishes to also populate the detailed database changes. This functionality is supported in Combine and can be achieved as follows:
1. Open the Change History tool from the main menu by selecting Tools → Change History.
2. Enter the search criteria for the package deployment and click the Play button to retrieve the deployment information in the General Package Details (top) grid.
3. In the top grid, right-click the row that contains the deployment information and select Open Results from the context menu.
4. A new window that displays the package results is now opened in the editor. In this window, refer to the top left hand side and click the option Save to Change History (see the section titled Populating the Change History Repository from a Cre File for more information).
5. When prompted, click Yes to instruct Combine to populate the detailed database changes section in the repository.
6. Close the editor window that contains the (cre) package results.
After performing steps 1-6 you can go back to the Change History tool and reload the data for the previously searched deployment, and then view the detailed database changes in the bottom grid.
Note: In order to perform the operations discussed above, a user must have sufficient permissions to read from and write to the Change History Repository.
Change History Tool – Viewing Deployment Results
The Change History tool enables users to view and search deployment results that were recorded in the Change History Repository database. In order to use the tool, a user must have sufficient permissions to read the content of the repository DB. For example, a user that is a member of the ChangeHistoryReadOnly SQL role will be able to view and search the Change History content, and this user-role is built-into the repository DB.
The Change History tool offers several search techniques to allow users to search the repository. The various search techniques are explained in the detail in the section titled How to Search the Change History Repository.
Change History Tool – Searching the Repository
The Change History tool offers several features that enable users to search deployment results of previously executed code packages. The two main search methodologies are now described below.
Searching the Change History Repository using built-in conditions
1. When starting the Change History tool, users can instruct Combine to retrieve the top-most X package deployments (where X is some number) or all package deployment results that meet the searched criteria. For example, the image below demonstrates how to retrieve the latest 3 deployment results that meet certain search criteria.
2. In additional user can choose to hide/show package deployment details. Details hiding can significantly speed up searching especially in case of huge deployments.
3. The date-range conditions enable users to only load deployments that started between given dates. For example, if a user searches for deployments between Jan 1st 2007 and Jan 1st 2008, then the Change History tool will only show general package information (in the top grid) for packages that their deployment-start-time is between these dates. Moreover, in the bottom grid that displays the detailed database changes, Combine will only show the database changes that their deployment-start-time is within the given date range.
4. Additional (up to three) search conditions can also be specified to enable more detailed searches. With these features, users can narrow their searches to only return package or detailed database changes information that meet the specified conditions. Explicitly, users can instruct Combine to only return information about packages that were deployment on a certain Environment or that contain a certain Environment Variable. On top, users can choose to only load detailed DB change info that meets certain conditions, such as scripts that contain a given SQL string, scripts that were executed by a given user name, change info that pertains to specific databases or servers, and so on.
Searching the Change History Repository using grid filters
The Change History tool also supports a second useful technique to search the deployment records. This technique is especially useful to search the detailed database changes. To use it, please follow the instructions below:
1. Load all the Change History data for the deployments you wish to search. For example, to search details of the last 3 deployments, load all last 3 package deployments and do not specify any of the Where conditions.
2. After the complete set of data is displayed in the Change History grids, select the Filter button in the grid (see image below) and set column filters in the grid to search for specific entries. Searched columns and search conditions can be specified either at the top grid (General Package Info), the bottom grid (Detailed DB Change Info), or both.
Tip: If you use the Filter functionality in the grids and later wish to remove all filters, simply click the Reset Layout button.
Change History Tool – Managing Columns in Grids
The Change History tool consists of two grids: The upper grid contains general package deployment information (i.e., one row for each package executed), whereas the lower grid displays detailed database change information (i.e., one row for each script and target database that were executed during the deployment of a code package). Each grids contain many different columns (see the Overview section for detailed column description) and the Change History tool provide simple means that enable users to hide or organize columns in accordance with user preferences.
Reordering columns in the grids – Columns in each grid can be reordered as you see fit. To do so, simply drag the header of the column that you wish to move and then drop it in the desired location. After reordering columns, Combine will remember the last grid order and will continue to display the grids in the newly selected order.
Hiding grid columns – A tool called Show Column Manager enables user to choose which columns should be displayed or hidden in the grids. In order to hide columns from the top grid, click anywhere in the top grid and then select the Show Column Manager, select the columns that you wish to hide, and then close the Show Column Manager tool by pressing the X at the top right corner of the tool. Similarly, to manage the columns of the bottom grid, click anywhere in the lower grid and then activate the Show Column Manager tool to configure the column-display settings for that grid. The Show Column Manager tool is shown in the image below. After hiding columns in either grid, Combine will remember the last settings used and will maintain those settings. To restore the grids to their original settings, users can click the Reset Layout button.
Data compression in the Change History Repository
The Change History Repository may be configured to use zip compression for large fields in Change History Repository database — CombineResultsStream & CombinePackageStream. Such compression reduces table size but may slightly affect performance. This option can be switched at any time and the new value will affect only subsequent deployments, no changes are made into the existing Change History Repository database. Combine transparently works with both compressed and non-compressed data.
To enable/disable compression users should go to Tools → Option → Packages → Change History and then check/uncheck the checkbox Compress package results data in Change History Repository database.
When this option is turned on, Combine compresses package and package execution result data using zip algorithm. Compressed data are stored in CombineResultsZipStream & CombinePackageZipStream fields in the Change History Repository database.
When this option is turned off (default), Combine does not use compression and package and package execution result data are written to CombineResultsStream & CombinePackageStream fields in a text form.
On data reading Combine automatically detects which form has been used and transparently decompress the data if needed.
When choosing whether to compress data or not, users should consider the following:
1. Compressing may significantly reduce (2-5 times) the amount of data recorded into the Change History Repository database. But Combine will spend some time to compress/decompress the data, thus when compression is turned on it may affect package execution time.
2. When using the Change History tool to view past deployment results, users can search the repository to find exact deployment details (e.g., all scripts that were deployed on a certain database, scripts that were executed using a certain SQL login, and so on). However, if you choose only store the general deployment then searches by the next criteria on compressed package data will be impossible:
-
SQL code like
-
Server name
-
Database name
-
SQL login name
-
Windows user name