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

In this issue, the editor will bring you about how SQL Compare uses the SQL comparison command line to customize deployment from source code control. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

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.

Giorgi Abashidze explains how his team uses a two-phase deployment process with the SQL Compare command line and some SQL synonyms to automate custom deployment for each of its customers, while maintaining only one branch for each version in source control.

In my previous article, from using SQL to compare command-line source control to databases, I explained how my team used the SQL comparison command to act as our customers automate database deployment without having to access the actual staging or production database, which is impossible. I showed how to use the SQL Compare command line to deploy database metadata and code common to all customers by accessing only our development database (included under TFS source control).

However, in addition to this shared code, each of our customers must be provided with a unique variant of each software version to provide them with custom routines that meet their different business and compliance requirements. Each of our customers needs some of these routines, and of course, this proprietary feature should always be deployed only to that customer's production database: it is critical that no customer can see the logic written specifically for another customer.

How can we achieve this goal? The traditional view seems to be that in each release, we treat each customer-specific variant of the database version as a separate branch. However, this may increase the complexity of the build. With some creativity, we can avoid this and instead create a branch for each new release that we can use to maintain and deploy all shared logic for each of our customers, as well as logic dedicated to an unlimited number of customers.

In this article, I'll show you how to use the SQL Compare command line, synonyms, and some ingenuity to do all this.

How do we store customer-specific routines in a single database

So, in order to get closer to the way our development database is built, we assume that our Trunk combines all our logic. Suppose we have three customers, Cust1,Cust2 and Cust3.

We also have some code, the SQL stored procedure, called the SQL stored procedure loan.calculate_effective_rate, which calculates the real interest rate paid on the loan over a period of time based on an algorithm called the "effective interest rate." This is the same code for all customers, but one day the customer Cust1 asked us to change their version of the algorithm, which means we now need to maintain and deploy two different versions of the "efficient" process.

First, we need effective_rate to implement an alternative algorithm for the process as required by Cust1 (from here, I'll use this abbreviated form of its name). We have only one development database, called Trunk, and since the name of each routine in any routine must be unique, this means that we must have a naming strategy to distinguish between these variants.

The way we do this is:

Rename the effective_rate process to effective_rate_default to create a default computing implementation for our customers.

Create a new procedure called effective_rate_cust1. In terms of parameters and parameter types, it should have the same signature as the old signature. This must be deployed only to the Cust1 organization.

This means that all customers' application code must now call the effective_rate_default procedure, except for the Cust1 that must be called, except effective_rate_cust1. However, we do not want to make any changes to any customer's calling code. After all, the purpose of the program has not changed, but we now have multiple ways to perform the same operation (to calculate the effective interest rate on the loan). Instead, we use synonyms to refer to any action with multiple implementations. In other words, the caller code never calls the exact implementation directly, but instead invokes the operation (synonym for SQL).

So, in this case, we will create a SQL synonym and deploy it to each customer, which has the name that effective_rate references the effective_rate_default procedure. Of course, the only synonym for Cust1 must refer not to the default procedure, but to the Cust1 variant. However, as I have already mentioned, we maintain only one database in source control, while in Trunk (and any release branch), each synonym always references only the default implementation of the relevant operation. So how can we achieve this goal?

The answer is a two-phase deployment process. The first phase deploys the effective_rate_default process to each customer and effective_rate references synonyms for the process. The second phase simply deploys the procedure to Cust1, then removes synonyms from the Cust1 database and creates a new referenced procedure.

Effective_rate_cust1effective_rateeffective_rate_cust1

By following this approach, based on SQL synonyms for each customer and the two-phase deployment process, we can include the required specialized routines in a development database, so we can still provide code to our customers, taking into account different business and compliance requirements.

Let's see how to implement this two-phase deployment process using the SQL Compare command line.

2-phase deployment using SQL comparison

We will expand slightly in the simplified deployment example and assume that three customers (Cust1,Cust2 and Cust3) need to customize variants of the "efficient" algorithm.

The first phase of the deployment process generates a deployment script that is delivered to each customer database and run on each customer database. This will upgrade all generic database routines (that is, no routines whose names end with a customer alias) to the same version.

The second phase generates zero or more customer-specific synchronization scripts that only create or update SQL routines specific to that customer, which means that the name of any routine ends with the user's alias, in which case cust1,cust2 or cust3. It will also delete and recreate any synonyms, in this case effective_rate synonyms, so that everyone is always bound to the correct base implementation.

To meet these requirements, we execute the SQL comparison command line once for each synchronization script we need.

Phase 1: generate regular deployment scripts

In the first phase of the deployment process, we execute the SQL Compare CL only once and pass in all the parameters indicating which databases to compare and how to compare by providing a XML argfile named "shared.xml":

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

I explained the basics of this Argfile in the previous article, but it is important to first compare the two just-in-time versions of the database, the new version and the previous version, directly from the source control location. , followed by a reference to the corresponding filter file (shared.scpf) This filter file uses the filter expression shown below to exclude any customer-specific version of the schema object (in this case, any version that ends in Cust1,Cust2 or Cust3):

(@ NAME NOT LIKE'% [_] cust1') AND (@ NAME NOT LIKE'% [_] cust2') AND (@ NAME NOT LIKE'% [_] cust3')

Of course, if your customer aliases all follow the standard pattern (as in this simplified example), you can use a more generic filter, such as (@ NAME NOT LIKE'% [_] cust [0-9]'). However, all of our real customer names are different, so this pattern matching is not possible.

As a result, SQL Compare will generate a SQL synchronization script that will only create the effective_rate_default stored procedure, then delete the effective_rate synonym, and then recreate it so that it references the default procedure (CREATE SYNONYM effective_rate FOR effective_rate_default).

Phase 2: generate customer-specific deployment scripts

In fact, for each customer, this phase consists of two parts:

SQL Compare automatically generates a script that creates or modifies the required customer-specific routines

We "reset" all synonyms in each customer database so that they reference the correct underlying implementation (stored procedures or functions, etc.). To do this, we "inject" the required code into the end of each automatically generated custom script

We execute SQL Compare CL again for each customer who needs custom code, simply switching argfile each time to instruct SQL Compare to contain only objects whose names end with that customer's alias. We store a list of all customer aliases in the development database.

"% programfiles (x86)%\ Red Gate\ SQL comparison 13\ sqlcompare" / Argfile: "cust1.xml"% programfiles (x86)%\ Red Gate\ SQL comparison 13\ sqlcompare "/ Argfile:" cust2.xml "% programfiles (x86)%\ Red Gate\ SQL comparison 13\ sqlcompare" / Argfile: "cust3.xml"

The content of each argfile is almost the same as that of the shared.xml file, except that each customer-specific argfile contains a reference to that customer's filter file (for example, Cust1.scpf), which instructs SQL Compare CL to use the following expression to detect only changes specific to that customer

(@ NAME LIKE'% [_] cust1')

When comparing runs (for example, for Cust1), SQL Compare generates a deployment script that creates, modifies, or deletes any objects in the trunk branch that * _ cust1 represents the installed version of Cust1, so it will be synchronized with the latest version in source control. In this case, it will create an effective_rate_cust1 stored procedure.

However, the deployment script automatically generated by SQL Compare for each customer does not replace the current effective_rate synonym (created by running the generic script in phase 1) with a synonym that references the effective_rate_cust1 stored procedure, because the synonym always references the default implementation for each major version (v241jor v242, etc.) in Trunk or any single branch.

Therefore, every time SQL Compare automatically generates a customer-specific synchronization script, we need to modify it to "reset" any synonyms in the script so that it references a customer-specific implementation of the relevant operation, or reverts to the default action if a custom variant is no longer needed.

We cannot use standard SQL Compare post-deployment scripts for this, first of all, because the tool currently does not support them when comparing directly to source control locations. In any case, because of our simple "one branch per release" strategy, we cannot dynamically generate post-deployment scripts for each customer, nor can we change synonyms in each script to reference the correct customer implementation. The only way to make it work is to use a more complex build of one branch per customer variant per version, scripting each branch into a folder, and then adding a post-deployment script to it to reset synonyms for this.

However, we prefer to use simpler source control methods, so what should we do? When SQL Compare writes a customer-specific synchronization file to the directory of our choice (specified by the parameter in the out customer's XML argfile), we will open it using a local tool and add a line to the end of the automatically generated code to execute the stored procedure we wrote called switch_synonyms_to_customer. This process accepts parameters for the organization's alias (synonyms should be bound to this parameter), then iterates through all SQL synonyms, deletes them one by one, and then recreates them with the appropriate name of the underlying object referenced by the synonym. Default value or dedicated routine (if required by a specific customer).

Therefore, for cust1:

EXEC altasoft.switch_synonyms_to_customer @ alias = 'cust1'

For cust2:

EXEC altasoft.switch_synonyms_to_customer @ alias = 'cust2'

Wait...

Run the deployment for each customer

We provide each customer with regular deployment scripts for deploying all common objects, and, if necessary, we provide each customer with additional custom deployment scripts for their custom logic, which will also correctly reset all synonyms. They must always run regular scripts first and not run their custom scripts until they are finished.

If you are developing database applications for many different customers, your customers' needs will begin to diverge, and a single deployment will not meet your needs. After all, local taxes and laws, as well as different business practices, will determine how customers calculate certain financial values.

In order to provide all the variations in the required logic only to the customers who need it, the SQL Compare command line can do 99% of the work. It generates a generic synchronization script to deploy any changes from release to release that each customer needs, and then generates a separate synchronization script file for each customer with "special" requirements.

By using synonyms to represent each required business operation and "manually" reset them at the end of each customer-specific synchronization script so that they always refer to the correct implementation of this operation, we avoid any changes to the caller.

In each release, it is possible to create some new logic for the customer, or update or delete existing custom logic. If some actions no longer require a custom implementation, they are deleted, so the customer returns to the default implementation of the operation.

This is how SQL Compare is customized from source control using the SQL comparison command line. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.