How dbForge Data Generator is involved in the DevOps process

Using test data is one of the most important Continuous Integration process steps in database development. The figure below shows the importance of generating test data for your CI.

You GET

Realistic test data

Modern data generation tools allow you to generate not just dummy data, but quite meaningful test data. Data such as personal names, streets names, email addresses, phone numbers, bank codes and much, much more can be easily generated.

Testing of new features

The development of new functionality often leads to the creation of new tables or modification of the existing ones. Therefore, in order to check the efficiency of the new functionality, you will need to fill these tables with data. You can do this quickly and easily using generation tools.

Independence from production data

Taking data for testing functionality from a real customer has many disadvantages and inconveniences. The main one is that customers are often reluctant to share data since they need to spend time on data masking or other measures to protect their confidential information.

You can also fill the database with data using replications, but this method will most likely require a production server load and a database administrator involvement. And most importantly, during business hours you will receive a refusal from the customer to take pictures since you will simply lock the tables. Yet, the CI process implies that it can be performed up to several times a day.

Generation tools allow you to generate an unlimited amount of realistic data. And if your functionality works properly with this data, then it will work properly on a customer's server as well.

Control of data amount

Generation tools allow you to fill the database from scratch, replace the existing data or add new data. Also, it is often the case that the customer is ready to share their data with you, but this data is not enough to fully test the performance of the new functionality. At this point, data generation tools will always come to assistance.

Various sets of data

Generation tools allow you to populate the same tables with different data sets. This is particularly beneficial when testing new functionality since it is quite possible that one generated sample can pass a set of tests, while the next - generated from the same process - will lead to their failure.

Easy automation

Generation tools allow you to quickly create one or more data generation projects for your database and include them in the CI process. When a project is created, logically meaningful generators for table columns are automatically assigned. Relations between tables, check constraints, and others are also taken into account.

You AVOID

Using sensitive personal data

Testing must not involve sensitive production data, legally protected at multiple levels by a number of privacy laws and regulations:

General Data Protection Regulation (GDPR), the most global privacy regulation concerning all EU residents
International standards, e.g. Payment Card Industry Data Security Standards (PCI DSS)
Country-specific and industry-specific acts and regulations, e.g. Health Insurance Portability and Accountability Act (HIPAA) in the US
Local acts and regulations, e.g. California Consumer Privacy Act (CCPA)

Data Generator delivers a variety of realistic, usable test data, which is not related to real people. Thus it allows you to stay compliant.

Data masking

As mentioned above, a customer can share real data for testing with a provision that some data will be masked. For example, bank codes, phone numbers, email addresses and more. This can make it difficult or completely impossible for you to test your functionality since testing requires a correct or real data set.

Asking your customers for data

It is often necessary to obtain data from a customer to test new functionality or upgrade. But each contact with the customer is another waste of time which you may lack if you need to meet the deadline for the product. In this case, you can save a lot of time using a data generator.

Wasting time on filling data

As you know, development of new functionality or testing of the existing one raises the problem of getting loads of test data quickly. It is possible to use import tools, but their usage imposes many restrictions. For instance, it is necessary to consider such factors as relations between tables and check constraints, which may lead to a big waste of time. Hopefully, modern generation tools can factor all this in.

Limited data

Situations such as the development of new features or the lack of real data from the customer, they are willing to share, can also be solved through data generation. You can always generate an unlimited amount of data.

A vital tool for data generation

dbForge Data Generator for SQL Server

One of the most effective ways to populate your database with test data during the CI process involves using a tool similar to dbForge Data Generator.

dbForge Data Generator is a tool that can populate SQL databases with test data using multiple generators for each column. It supports a full range of SQL data types and can recognize them in a loaded SQL database and provide real-world test data based on contained data types. It can generate randomized test data using the Random generator, various options, and the Regular expression generator, sequential data using the Incremental generator, and much more.

Creating a data generation project

01

To quickly create a test data generation project for your database, initiate the creation of a new data generation project

02

Then click Open in the New Data Generation wizard that opens

03

Next, configure the necessary settings in the new project window

04

Finally, save the project to the folder

Now, when the project for populating the database with data is ready, you can add it to the Source Control repository and use it in CI when you call the cmdlet to fill the database with test data. The generator project can be used in PowerShell cmdlets Invoke-DevartDatabaseTests and Invoke-DevartPopulate, as well as during the dbForge DevOps Automation for SQL Server - Populate for Azure DevOps step and the Generate test data section of the dbForge DevOps Automation for SQL Server - Test a database using the tSQLt step for the Jenkins/Bamboo/TeamCity project.

Invoking a project from a cmdlet

Invoke-DevartDatabaseTests -InputObject 'Server=MSSQLSERVER; Initial
Catalog=TestDB; User ID=sa'  -DataGeneratorProject
'D:\SourceDG\TestDB(MSSQLLocalDB).dgen' -InstalltSQLtFramework -RewriteReport
-UnInstalltSQLtFramework -IncludeTestData

$Connection = New-DevartSqlDatabaseConnection -Database TestDB -Server MSSQLSERVER -WindowsAuthentication true
$DataGeneratorProject = "D:\SourceDG\TestDB(MSSQLLocalDB).dgen"
Invoke-DevartPopulate -DataGeneratorProject $DataGeneratorProject -Connection $Connection

For more information on how to use cmdlets, see the Devart documentation center.

Conclusion

As mentioned above, you can create any number of data generation projects for the same database in order to test your functionality on different data sets. Indeed, different data sets allow you to test the functionality with the boundary condition data and cover other aspects of testing.

Learn about other dbForge tools involved in collaborative
database development and deployment