Chapter 9 - Developing: Databases - Unit Testing the Migration

Article
12/05/2007

Introduction and Goals

The final task to be performed in the Developing Phase is to test the database. Because everything except the core database design has been transformed, it is very important to thoroughly test every aspect of the Microsoft® SQL Server™ database before release to the production environment. Testing should cover the hardware, the SQL Server installation, security, database objects, data, and performance.

The descriptions of the testing process for databases and applications is divided into smaller pieces and spread out between the Developing and Stabilizing Phases. Testing for integrity and functionality of the migrated database is covered in this chapter. Additional application-based testing of the database is performed as part of testing the application. Performance can be verified only through the benchmarking and piloting that is performed in the Stabilizing Phase.

Objectives of Testing

The following areas should be tested:

Physical architecture of the database. Have the data files, transaction log, tablespaces, and other items that comprise the database been created correctly? Has the SQL Server instance been properly configured?
Security. Have the logins and users been correctly created? Have the appropriate permissions been assigned? Is the database physically secure?
Logical architecture of the database. Have the tables, views, stored procedures, triggers, and other database objects been created successfully?
Data. Have the contents of the tables been transferred correctly? Do tables contain the correct numbers of rows? Is the data valid?
Functionality. Do the stored procedures, triggers, views, and other items comprising Transact-SQL code operate in the same manner as the original Oracle objects?
Performance. Does response time and throughput meet requirements and match user expectations?

The Testing Process

The database is tested in three different stages of the migration:

Developing Phase — Database

In this stage, the database is unit tested for all the components that have been migrated, which includes the database architecture, the schema, the users, and the data. This type of testing is covered in the rest of this section.
Developing Phase — Application

In this phase, the database is tested with respect to the application support. This primarily tests that all the objects required by the application are present and perform as expected. The application-related testing is covered in the application development Chapters 11 through 17.
Stabilizing Phase

In the Stabilizing Phase, more thorough tests are performed for integration, performance, stress, and scalability. This phase is described in Chapter 18.

This section describes the testing process that is followed during the Developing Phase for the database. The testing is interspersed with the steps in the migration of the database. The procedure for migration and testing is illustrated in the flowchart in Figure 9.1.

Figure 9.1 Data migration test procedure

Figure 9.1 Data migration test procedure

The tasks on the left half, namely, migrating the architecture, migrating the schema, migrating the users, and migrating the data, are part of the database migration and are covered in chapters 5 through 8. The success of these tasks is validated as database integrity testing and data validation. Details regarding these two types of testing appear under the following headings.

Test Database Integrity

After the database schema has been created in the target database, an integrity test should be performed before starting the data migration. This should cover the physical database structure, the schema, and the schema objects.

There is no tool available for comparing the integrity and validity of the SQL Server objects as compared to the original Oracle objects. Unit testing has to rely on the experience and expertise of the database designers or administrators who are performing the mapping. However, a true unit test would be to load data from the source objects and run SQL with identical functionality in the two environments. Any discrepancies should be only as a known (explainable) consequence of the migration.

The following tasks are performed to ensure database integrity:

Unit test the schema objects. The type and number of each object in the original Oracle database has to be compared to that in the SQL Server database. Queries can be written in the two databases (Oracle and SQL Server) to produce a count of each type of object. However, the counts in the two solutions may not match because there is no one-to-one equivalency for every object.
Verify that data elements have been created as per the mapping. The proper transformation of the data type and domain values of the columns — as well as any constraints, such as NULL and CHECK constraint on the columns — have to be verified before performing the data migration. A representative set of data from the original Oracle database can be used for this purpose.
Verify that all constraints are in place. For each table having a parent-child relationship, referential integrity is checked.
Functions, triggers, and stored procedures have been accurately transformed. Stored procedures, triggers, and views can be tested by using an identical set of representative data in the source and migrated databases. Execution of test cases against these objects should produce identical results.
Verify the data access. Additional indexes may be present in SQL Server to offset features such as fast full index scans in Oracle that can use the non-leading columns of indexes. It should be verified that all objects in the original Oracle database have been successfully migrated to SQL Server. A complete set of T-SQL statements should be obtained from the application development team and each of them verified for optimal data access paths.

If the application has been migrated, running a few frequently used business transactions would ensure whether the schema has been successfully migrated. After the correctness of the database structure and its objects is guaranteed, data migration can proceed.

Test Security

The security mechanisms available with SQL Server and Windows are significantly different from those used by UNIX and Oracle. The difference in the authentication mechanisms is especially important because it affects the user login process. For example, the requirements of password management functionality, such as aging, locking, and password strength will require that Windows authentication be used in place of database authentication. The system privileges available in the two databases differ significantly. You must verify that only authenticated, authorized users have access to the objects in the database. You must also verify that Oracle users and roles have been correctly mapped to SQL Server users and roles, and that all objects in the database have the appropriate access rights granted to them.

Validate Data

The existence of indexes and constraints during data migration adversely affects the performance of the data migration. The firing of triggers can corrupt the data and produce undesirable effects. Therefore, these objects have to be disabled or dropped. A negative aspect of these actions is that data is not checked for correctness or completeness during the migration and has to be validated after the migration. No special plans are needed for validating data integrity. The database itself checks the integrity of the data when the constraints are enabled using the WITH CHECK clause. For more information on check constraints in SQL Server, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp.

The two types of tests that have to be performed to validate the data are discussed in the "Validating the Data Migration" section in Chapter 8.

Note One of the common risks involved in data migration is the lack of a proper or complete set of constraints in the database. In many applications, the constraints are built into the application. In such cases, the development team will have to be involved in constructing SQL-based tests to verify data integrity by identifying such rules in the application.

Validate the Migration

After data migration is complete, tests have to be performed on the database as a whole. Testing should cover the database architecture, database objects, data, and users. In addition, the database connectivity, security, and performance have to be tested. For information on client connectivity to the database, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_client_5er7.asp. A discussion of the security architecture in Oracle and SQL Server is available in Appendix A: "SQL Server for Oracle Professionals." Appendix B: "Getting the Best out of SQL Server 2000 and Windows" contains several references on SQL Server performance.

While in smaller databases the data migration validation can be exhaustive, in very large databases, some simpler tests (such as counts using various groupings) may be used. The following checks are recommended:

Develop group functions based on type of data.

For example, a business-related check can be implemented by calculating the sum of the balances in all accounts.
Develop group functions based on time.
Check for record counts.
Use the application to compare the summary reports.
Check for ad-hoc control totals.

For example, after ledger data of a financial application has been migrated, adding up the account numbers in that ledger has no business validity. However, this ad-hoc control total could reveal if all the data in the row has been migrated successfully.

The SQL Server Migration Assistant offers a Migration Tester tool that verifies the migrated objects (procedures, functions, and views) by generating its own set of test data. For more information on the Migration Tester and to download it refer to https://www.microsoft.com/sql/migration. The beta version of this tool is available as of the date of publishing this solution. Version 1.0 of the tool is slated to be available in June 2005.

A test that validates the entire migration is the running of production reports and application with production-quality data. The same reports should be executed against the original database and the migrated database using a snapshot (or copy) of production data. Performance and other vital database statistics, such as cache performance and locking, can be gathered.