Shulou(Shulou.com)06/01 Report--

SQL Compare is a tool for comparing and synchronizing SQL Server database structures. More than 150000 database administrators, developers, and testers now use it. SQL Compare automates the process of allocating the database when testing the local database, temporarily storing or activating the database on the remote server.

Our team develops real-world applications for business organizations; mainly Georgian banks. These applications are based on the .net-Windows-Forms application of MS SQL Server as a database. They have a lot of business logic that is contained in database routines, such as stored procedures, functions, views, and SQL CLR.

Not surprisingly, given the nature of the customer's business, we do not have access to the customer database or data for development, testing, or deployment. Under TFS source code control, we only have to develop the database and its manual test data. Developers work on their own copies of the database, each with its own sample data, and they use Redgate SQL Source Control to commit development changes. Then we use the SQL Compare command line to automate database deployment. In this article, I'll explain how to achieve this goal, give examples of how to compare two revisions of databases in the same or different branches, and generate deployment scripts.

Manage database in source code control

Our database source code control and branching strategy is simple. We have the latest code base in Trunk; the entire application is there, including the database part of it. All new features and bug fixes are initially done in Trunk. Each branch we create is only a copy of Trunk, so it represents the full point-in-time state of the code base. After applying some changes and checking in the Trunk, we can merge them into any of these branches as needed. Typically, this is to fix reported errors, but when it is important to our customers, we also need to be able to merge small functional changes. For example, not every customer can afford to deploy each version, so the version they deploy is usually three or four versions behind. However, they still need us to deploy emergency fixes for the current version, occasionally using some "proprietary" features.

So how does all this work when we develop software? Let's call it "under-source-control-application" (USCAPP for short). We have the latest code base in USCAPP_Trunk and provide several distributions called v241, v242, and so on under the TFS branch.

All changes made directly or through merging can be done through the normal TFS check-in of Trunk and its branches. Each time you check in, TFS creates something called a changeset, which has a unique reference number. A changeset represents a snapshot of the entire code base in source control. Like any other source control system, TFS can generate the point-in-time state of the code base for any given changeset number for any revision.

Of course, for all TFS projects in the collection (including their branches), the TFS changeset number is global, and the TFS changeset number is incremented each time the project collection is checked in. For us, this means that USCAPP_Trunk and all its branches v241, v242, and so on share the same, global, growing changeset number.

Developers make changes, everyone works on their own dedicated database, and checks in changes through SQL Source Control, which update the code in USCAPP_Trunk. As needed, we merge the required changesets into other branches and create new changesets in those branches. Therefore, assume that the latest version is v245, and we know that customer A has deployed v242 to the production environment. The customer is not yet able to upgrade to the latest version, but an additional upgrade script has been deployed to fix some bugs and make some minor improvements. In other words, customer An is running a very specific version of v242, which we can convert to a TFS changeset number that uniquely identifies the point-in-time status of the code base of its deployed branch v242.

Use the SQL Compare command line to automatically generate a change script

Our goal is to automate the process of generating a synchronous SQL script that covers all changes that have occurred since the last release of the script.

Suppose customer A has deployed branch v242 and the release of the database is marked with a human-readable version number 2.4.2.0, which corresponds to changeset number 87300, that is, it is the global changeset number of the code base released when changeset 87300 is the latest version.

A month later, we have made changes in the database, and now the current number of change sets in TFS is 88100. Now we are going to generate a script that contains each change made that month, so upgrade the v2.4.2.0 of the database to the state represented by changeset number 88100, which we call v2.4.2.1.

To do this, we need to retrieve two point-in-time states of the database from TFS, one representing the source database (unchanged) and the other representing the target database (we are upgrading). Therefore, for customer A, changeset 88100 represents the source and 87300 represents the target. We need to compare the two states to find out the differences, and then generate a script to synchronize the target so that its state is the same as the source. For any database objects that exist in both databases but are different, you must change the definition of the object in the target to match its definition in the source. Any objects that exist in the source but not in the destination should be created, and any objects that exist in the destination but not in the source should be deleted.

The good news is that we don't have to do this manually. This feature is supported on both the SQL Compare GUI and SQL Compare command lines. We want to automate this process, so we use the command line and pass the appropriate parameters to the command line to generate a synchronization script. We also need to carefully document the script to upgrade the 2.4.2.0 version of the database to v2.4.2.1. Of course, we also need some protective measures here. One of these is a check, which will stop running this script on any database that is not v2.4.2.0. I won't demonstrate here, but in the end, I'll discuss these requirements in more detail.

Compare two revisions in the same branch

First, I'll describe how we release a script called "Fix," which is mainly used to deploy bug fixes and minor improvements. The major version remains the same.

We do this using the SQL Compare command line, passing a XML parameter file (argfile) that contains the values of all the necessary command line switches that instruct SQL Compare how to perform the comparison. Alternatively, you can specify each switch to the command line, or the "splat" parameter in PowerShell.

In this case, the only parameter that needs to be passed to SQL Compare is the qualified file name of XML Argfile, called "shared.xml"

"% programfiles (x86)%\ Red Gate\ SQL Compare 13\ sqlcompare" / Argfile: "shared.xml"

The contents of the argfile should be filled in exactly as described in the online documentation on the SQL Compare command line. This is a real example:

88100 87300 NoDeploymentLogging,IgnoretSQLt,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,CaseSensitiveObjectDefinition,ObjectExistenceChecks,DropAndCreateInsteadofAlter,ForceColumnOrder,DoNotOutputCommentHeader,IgnoreUsersPermissionsAndRoleMemberships Command Line\ SourceControlAddress v242.xml Command Line\ Filters\ Shared.scpf Interactive Command Line\ Output\ Shared.html Command Line\ Output\ Shared.sql

Argfile contains five command line switches that we use to define the desired behavior. The / Sourcecontrol1 and / Sourcecontrol2 switches specify our source and destination, which is a folder of the source control script, in which case the versions are 88100 and 87300, respectively.

8810087300

The switch contains a complete file path for the XML file, SourceControlAddress v242.xml. This file is shown below and contains the source code control address of the database for branch v242:

Http://tfs:8080/tfs/projects $/ USCAPP/Branches/v242/Database/Schema

This is the address from which SQL Compare should recover the 87300 and 88100 changesets. When the command-line version of SQL Compare is executed, it restores these changesets to a "script folder" (as of this writing, to a folder in Windows Temp) and compares 88100 as the source and 87300 as the target to generate the final upgrade script.

Compare databases in two different branches

The process we use to release all the new features that have been done in Trunk is slightly different from the bug fix version, but the main concepts remain the same. Also in this case, we must compare the two different states of the database schema. Even if their "source of truth" exists as a version of TFS source control, they are exported to folders as what Redgate calls "script folders". They can then be compared as two database schemas. In this case, the difference is that instead of comparing the two revisions (or point-in-time states) represented by the changeset in one TFS branch, we compare them between the two branches that now represent the version.

Step by step: the process starts by creating a new branch from the Trunk branch and assigning it an appropriate name. For example, if v2.4.2 is the final release of the USCAPP application, we have created a branch called v242 when we released that version. Now that we have made more changes to Trunk, logically we will release v2.4.3, so our new branch will be called v243, and from then on it will be an exact copy of the Trunk branch in terms of what it contains.

Now we should compare the two changesets of the two separate branches. The changeset we use for comparison must be the changeset of the new v243 branch just created and the changeset corresponding to the newly released deployment script of the previous branch v242 that customer A has applied. This comparison will reveal changes that occurred only on the database of Trunk, which were missing from the database of the previous branch v242.

To do this, we need to specify one source control folder location instead of two, one for the TFS branch that contains the source / ScriptsFolderXML1 and the other for the branch that contains the target / ScriptsFolderXML2. We use the SQL Compare reserved keyword "HEAD" to specify the latest source control change set for the source branch we want. The generated Argfile is as follows:

HEAD 88100 NoDeploymentLogging,IgnoretSQLt,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,CaseSensitiveObjectDefinition,ObjectExistenceChecks,DropAndCreateInsteadofAlter,ForceColumnOrder,DoNotOutputCommentHeader,IgnoreUsersPermissionsAndRoleMemberships Command Line\ SourceControlAddress v243.xml Command Line\ SourceControlAddress v242.xml Command Line\ Filters\ Shared.scpf Interactive Command Line\ Output\ Shared.html Command Line\ Output\ Shared.sql

This is the source control script location of the target XML file (SourceControlAddress v242.xml):

Http://tfs:8080/tfs/projects $/ USCAPP/Branches/v242/Database/Schema

This is source code one (SourceControlAddress v243.xml):

Http://tfs:8080/tfs/projects $/ USCAPP/Branches/v243/Database/Schema

Once again, we use only the address of Argfile as the only argument to invoke the SQL Compare command line:

"% programfiles (x86)%\ Red Gate\ SQL Compare 13\ sqlcompare" / Argfile: "shared.xml"

After the SQL Compare command line finishes its work, in the "Shared.sql" file, we have an upgrade script that we can run on the target database to upgrade to the latest major version.

Further requirements

In real life, we always need to carefully examine the automatically generated scripts and add checks and controls to ensure that, for example, all necessary upgrade scripts are applied to the expected database version in the correct order. We also need to make a small number of additions and customizations to SQL Compare's automatically generated deployment scripts, such as handling data insertion or adding header information to each script (copyright information, contact information, etc.) when creating the script. Or append some dynamically generated SQL scripts to the end of each automatically generated script to identify the customer.

Many of these goals can be achieved by using custom migration scripts to modify SQL Compare deployments, although we actually encounter some difficulties, such as slowing down SQL Source Control or pre-and post-deployment scripts.

For us, another factor to consider is that SQL Compare pre-and post-deployment scripts are static, while our requirements are dynamically generated scripts. Instead, we built a simple, lightweight tool in Visual Studio that allows developers to make small dynamic additions and customizations to SQL Compare scripts.

Another complexity that I avoid delving into here is that for each of our customers, our source control trunk combines all the shared logic of the customer database with small routines that contain the organization's proprietary custom code. In this article, I demonstrated how to use the SQL Compare command line to deploy the database structure and code common to all customers. Although this process is basically the same as a customer-specific routine, some minor adjustments are needed to ensure that unique functionality is always deployed only to that customer's production database. and no customer can see the logic written specifically for another customer. I will describe how we can achieve this goal in the next article!

Conclusion

Our experience is that Redgate Source Control and SQL Compare can work together and play a big role in automating the script generation process. SQL Compare allows very fine-grained control over how it extracts scripts from Git or TFS source control, saving us a lot of manual scripting. We will see more possibilities for automatically generating corresponding rollback (downgrade) scripts as well as upgrade scripts. It just needs to reverse the changesets we use for the source and target and start the SQL Compare command line! It is a multi-functional tool.

This is the end of this tutorial. I hope it will be helpful to you.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.