- Completeness check
- Data validation
- Application functionality test
- Performance testing
Completeness Check
Completeness check involves ensuring that all database objects and dependencies have migrated successfully. This includes tables, stored procedures, functions, views, triggers, indexes, constraints, and so forth.
Microsoft Azure provides tools such as the ‘Data Migration Assistant’ (DMA) that can be helpful in performing completeness checks. For instance, DMA provides an assessment report with a list of potential issues that might affect database functionality post migration.
Remember the command
USE Your_Database_Name;
GO
EXEC sp_MSforeachtable @command1='PRINT ''?''', @command2='DBCC CHECKIDENT (''?'')'
This command retrieves a list of all tables and checks if the identity values are consistent.
Data Validation
Data validation ensures that all the data in the source database has been migrated successfully to the target database on Azure SQL.
A simple strategy is to perform record counts on all tables in the source and target databases and compare the results. If the counts match, it’s likely that the data has been migrated correctly. This can be done using a simple SQL statement like
SELECT COUNT(*) FROM Your_Table_Name
Another strategy is to sample data before and after migration using SQL Server Integration Services (SSIS) or PowerBI and compare results.
Application Functionality Test
Once the migration is complete, it’s important to test all applications and services that interact with the database to ensure they are functioning correctly. This should include checks on the application’s business functions, reports, and any other database-connected services.
A good practice is to have a functional specification document defining the expected behaviour of all the business functions. The functionality can then be cross-checked against this document to ensure all elements work as expected.
Performance Testing
After migration, the performance of the new Azure SQL database should be tested to ensure it meets service level agreements (SLAs) and provides suitable response times for end users.
The performance of various database operations like read, write, update, and delete operations should be tested, using tools like HammerDB, which is an open-source SQL database load testing and benchmarking tool.
By closely monitoring the system’s performance, one can identify and address any potential bottlenecks or performance issues early, hence ensuring a smooth performance post migration.
Conclusion
Migration to Azure SQL represents a major undertaking for any organization, and therefore, thorough post-migration validation is important to ensure a successful transition. While the checks mentioned above are a good starting point, each organization should create its own detailed validation process based on its unique requirements and constraints.
Practice Test
True or False: After migrating, there is no need to validate if the applications are connected and fully functional.
- True
- False
Answer: False
Explanation: Post migration validations are necessary to ensure that all applications are correctly configured and operational.
True or False: Post migration validation involves checking both data integrity and performance issues.
- True
- False
Answer: True
Explanation: This is a part of the post migration validation process to ensure that both the data and the performance of the database application are as expected after the migration.
Which of the following are considered in post migration validations? (Multiple Select)
- a) Check data integrity
- b) Checking performance issues
- c) Troubleshoot connectivity problems
- d) Set up Azure SQL
Answer: a, b, c
Explanation: Post migration validation includes checking data integrity, performance issues, and troubleshooting any connectivity problems. Setting up Azure SQL is part of the migration process, not validation.
True or False: Post migration validation does not involve resolving any potential connectivity issues.
- True
- False
Answer: False
Explanation: Part of post migration validation is resolving potential connectivity issues to ensure all connections to the newly migrated Azure SQL Database are working as expected.
What is penultimate step in the Azure SQL Database Migration Checklist?
- a) Begin the migration
- b) Perform post migration validation
- c) Troubleshoot connectivity problems
- d) None of the above
Answer: b. Perform post migration validation
Explanation: After migrating the database to Azure SQL Database, it’s crucial to perform one last step to ensure that everything is functioning normally – post migration validation.
True or False: In the post-migration phase, the DBA should ensure that Database Mail has been setup correctly.
- True
- False
Answer: True
Explanation: After migration, things like Database Mail might have some settings that didn’t carry over and need to be checked.
Post migration validation is the process of:
- a) Checking if all data got transferred
- b) Checking if the new system is stable
- c) All of the above
- d) None of the above
Answer: c. All of the above
Explanation: Post migration validation is the process of ensuring integrity and functionality of the migrated data and system.
True or False: You should not bother checking if all the SQL jobs are running as expected post migration.
- True
- False
Answer: False
Explanation: One of the post migration checks is to make sure all SQL jobs are functioning as expected.
In a successful post data migration, which of the following should be accomplished? (Multiple Select)
- a) System should be stable
- b) All data are accurately transformed
- c) There are no compatibility issues with tools and software
- d) Improved performance of system
Answer: a, b, c
Explanation: The key results of successful post data migration should include a stable system, accurately transformed data, and no compatibility issues.
True or False: Rewriting queries is part of post migration validation.
- True
- False
Answer: False
Explanation: Rewriting queries comes under the optimization of the database, not under post validation process. The validation process ensures that everything is working correctly after migration.
Which of the following is not part of post migration validation?
- a) Checking data integrity
- b) Checking if system is stable
- c) Rewriting queries
- d) Ensuring there are no compatibility issues
Answer: c. Rewriting queries
Explanation: Post migration validation includes checking data integrity, performance issues, and compatibility issues. Rewriting queries comes under optimization of the database.
Interview Questions
What is post-migration validation in Azure SQL Database?
Post-migration validation is the process of verifying the success of data migration from a source database to Azure SQL Database. It includes checking data integrity, performance, and functionalities of the migrated database against the source database.
How can you validate the data integrity after migration?
You can validate data integrity by running specific scripts or queries to compare the data in the source database and the migrated Azure SQL Database.
What tool can you use to compare schema and data between Azure SQL Database and the source database?
You can use SQL Server Data Tools (SSDT), specifically the Schema Compare and Data Compare features, to compare schema and data between Azure SQL Database and the source database.
What is the purpose of running the Database Experimentation Assistant (DEA) post-migration?
Running the Database Experimentation Assistant (DEA) post-migration helps evaluate a target SQL Server’s performance by comparing performance traces from the source and target servers. This can identify potential compatibility issues, performance enhancements, or regressions.
How can you validate the performance of Azure SQL Database after migration?
You can validate performance by monitoring various metrics such as CPU usage, number of database connections, and input/output operations per second (IOPS) using Azure Monitor and Azure SQL Analytics.
What role does Azure SQL Analytics play in post-migration validation?
Azure SQL Analytics provides an in-depth analysis of the performance data, which helps identify potential bottlenecks, trends, and performance anomalies. This helps to ensure the Azure SQL Database is operating as expected after migration.
Why is it important to validate functional correctness after migration?
It’s vital to ensure that all the features and functionalities of the application connected to the database work as expected in the new environment, to ensure a seamless transition for the end-users.
What are some of the common checks performed during post-migration validation?
Common checks include schema and data validation (such as data types, integrity, volume, and correctness), performance validation, functional correctness, and validation of security settings.
How can you validate security and compliance post-migration?
You can validate security and compliance settings by reviewing the configurations and settings for firewalls, virtual networks, and threat detection policies in the Azure SQL Database.
Can post-migration validation activities impact the live operations of the database?
No, Post-migration validation activities do not impact the live operations, however, it’s best to carry them out during non-peak hours to minimize any potential impact on performance.
What is the purpose of re-pointing applications to the new database post-migration?
Re-pointing applications to the new database ensures that applications start using the Azure SQL Database instead of the old database, which then becomes obsolete.
How can SQL Server Management Studio be used in the post-migration validation process?
SQL Server Management Studio can be used to connect to the Azure SQL Database to perform schema validation, execute queries to validate data, and evaluate the performance and metrics of the Azure SQL Database.
What metrics are important to validate during the performance check post-migration?
The important metrics to validate include CPU usage, DTUs (Database Transaction Units) utilization, number of connections, IOPS, and latency.
What should be the next step if inconsistencies or errors are found during post-migration validation?
In case of inconsistencies or errors, steps should be taken to investigate the root cause, resolve it and rerun the validation checks to confirm the successful migration.
What should be the condition of the source database during post-migration validation?
The source database should be kept intact and untouched until the post-migration validation is fully completed and passed.
extutorials.com