An Overview of Continuous Integration of SQL Server Database

Continuous integration (CI) is the practice of integrating the isolated databases as soon as they are pushed into an ordinary source control repository. During the early stage of the continuous integration concept, the rule of thumb followed by database admins was of daily integration. However, lately, it is widespread that constant integration is done many times a day. Each change passes through a well-structured testing plan, which runs automatically as and when the changes are detected in the repository. If the test runs successfully, then these changes get automatically merged based on the build script.

Understanding “continuous” “integration”

Concept of “continuous”

The term “Continuous” in CI refers to the concept that this process, once started, will never end. However, in the actual scenario, this is not always the case. The only thing which runs continuously is the source control monitoring repository for any changes. The rest of the processes are repeatable and runs automatically as soon as a change is detected, i.e.,

The processes as:

• Build
• Loading test data
• Running test
• Documenting, and
• Deployment is continual.

What “integration” does?

In continuous integration of CI, “integration” actually refers to the concept that once the database change is committed, the CI server gets triggered, pulls the latest repository state, and integrates the committed DB changes with production. This will create a database building process and load it with test data to perform different testing types. As soon as the tests are completed, “integration” gets initiated, and it builds the database that will get deployed.

3 significant benefits of continuous integration on SQL Server DB

1. Agility

Unlike the conventional testing approach where you had to wait for the QA team to accomplish the testing before any release, continuous integration on the SQL server database provides real-time feedback well ahead in time very often. This will allow developers to be more agile in the bug fixing and avoid getting is carried over for days, weeks, or months as accumulated defects.

2. Efficiency

As CI automates the repeatable processes described above, i.e., building, loading of test data, test execution, and deployment, human effort, time is taken, the scope of manual errors, etc. are eradicated throughout the build process.

3. Quality

With continuous integration, you have a solid database that can pass all the tests, without which it may not get deployed to the QA. This will offer optimum confidence to the developers and DBAs that the database deployed to the QA will always be free of any critical errors at any given time.

Requirements to initiate continuous integration

1) An SCM (Source control management) system

A reliable source control management system is an essential component of CI. There aremany SCM systems available like:

• Subversion (SVN)
• Team Foundation Server (TFS)
• Git
• Mercurial and,
• Perforce etc.

By placing the database under the SCM, you can establish a very systematic approach to development. Maintaining a complete history of the changes made to the database, SCM empowers the developers to revert any of the changesets if a rollback is needed. As RemoteDBA.com points out, having an SQL database under the SCM is a prerequisite to implementing CI.

2) CI Server

This is the server where all the automation testing is done, and the building and deployment are managed. A continuous integration server is also connected to the source control repository to watch for any changes. When any change to the database is detected, the CI server will check the repository and a database will be built from the latest version. It will also put some test data and run the automated tests and inform the developers about the test results.

3) A reliable build and deploy process

When the tests are run and passed, a build system is triggered by using the tested codes to build a database and deploy it to the QA. For this, you need to ensure that a reliable build and deploy process is in place through the sample data sets and the trial automation tests.

4) Automation

As we had seen above, from the very first step to monitor the changes on the source control repository through testing, test reporting, generating feedback to the developers, to the final build and deployment, CI may not require any human intervention and must run automatically. This entire process needs to occur instantly and spontaneously for each change made in the database or committed to the repository. However, the building can also be scheduled on time, like once daily or at different times of the day. However, practice, for optimization, is to be set to run automated for each change committed.

Continuous Integration workflow

To create an on-demand CI, the very first step is to identify all the processes involved, which have to be automated. These processes can be in different areas of building, testing, and deployment of databases.

Say, for example, a primary requirement may be to create a delta script of the recent schema changes in the database to go to QA. Next, it may run unit tests on the changed objects and new objects on the stored procedures. Sometimes, the requirement may be to replicate the static production data as Place names, Zip codes, or so. The synthetic production data, i.e., test, may also be needed to test the database.

Read also Why Data Integration is an Absolute Must for Your Business

Next, you should create a process to get all these processes in a repeatable fashion and let them run based on a particular schedule in a sequence. There are various tools asApexSQL Diff), ApexSQL Generate for synthetic DB scripting etc. to be use for this purpose.

Finally, you have to create a job for combining these processes and scheduling with running them in a proper sequence. As an ideal output of this, it should be able to return code testing for success or failure in one click or through the scheduled process.

Overall, continuous integration or CI of SQL Server Database makes the entire development, testing, and database management more real-time and effective for the DevOps teams at different layers.