Database Migration - On-Prem SQL Server 2014 to managed Cloud SQL (Postgres)

[dheerajpanyam]

What options do i have for a heterogeneous database migration - On-Prem SQL Server 2014 to GCP's managed Cloud SQL but Postgres?  I don't see Database migration service support SQL server as source database and not sure if it even supports on-prem.  Any OSS scripts / tools from GCP Professional Support that can be used here? Since it is not a like to like db migration couple of edge case scenarios need to be handled that are listed below. 

1) Is there a OSS script / tool  to map the column types? (e.g. varchar may not be supported)

3) Is there a way to verify data was migrated accurately?

[ms4446 · Accepted solution]

Migrating from an on-premises SQL Server 2014 to a  Cloud SQL Postgres instance on Google Cloud requires careful planning and execution due to significant differences in database structure, syntax (T-SQL vs. PL/pgSQL), data types (e.g., varchar vs. text), and functionalities (e.g., stored procedures, triggers). This process, known as a heterogeneous migration, involves three critical steps: assessment, schema and code conversion, and data migration and replication. Each of these steps is essential for ensuring a smooth and accurate transition to the new database environment.

Step 1: Assessment

The first step in the migration strategy is to assess the existing SQL Server database. This can be done manually, especially for smaller databases, or by using the Open Source Database Migration Assessment Tool (DMA). DMA analyzes your SQL Server database to identify migration complexity, estimate effort, and provide recommendations for suitable Google Cloud database targets. It also helps right-size your cloud resources and creates a phased migration plan. While DMA's recommendations are valuable, they should be carefully evaluated based on your specific requirements. More information on DMA can be found here.

Step 2: Schema and Code Conversion

Once the assessment is complete, the next step is to convert the database schema and code. The Ispirer Migration Tool is highly effective for this task, automating the conversion of database schema and code to PostgreSQL syntax. However, some manual intervention might be necessary for complex stored procedures or custom functions that cannot be directly translated. More details about the Ispirer Migration Tool can be found here.

Step 3: Data Migration and Replication

The final step is data migration and replication. Using Striim, you can handle this efficiently, starting with an initial load of your existing data. Striim then continuously replicates changes from SQL Server to Cloud SQL for PostgreSQL using Change Data Capture (CDC) in real-time, even after the initial load. It includes built-in validation mechanisms (e.g., checksum comparisons) to ensure data accuracy and allows for custom scripts for post-migration data integrity checks. More information about Striim can be found here.

By following this comprehensive approach—using assessment tools (manual or DMA), the Ispirer tool for schema and code conversion, and Striim for data migration and replication—you can successfully migrate your on-premises SQL Server 2014 database to a managed Cloud SQL for PostgreSQL instance on GCP. Remember, careful planning, the right tools, and thorough testing are key to a smooth and efficient migration.

[dheerajpanyam]

Thank you so much @ms4446 for the detailed step. One final question which is the last step is data validation to make sure data was migrated accurately. Let me read about Striim to see if it does this step also.

[ms4446]

Data validation encompasses a multi-faceted approach. First, automated validation tools provided by migration services like Striim for Google Cloud can be utilized. These often include checksum comparisons and other automated checks to ensure data accuracy during transfer. However, relying solely on automated tools may not be sufficient.

Therefore, custom scripts should be developed to perform additional data integrity checks. These scripts compare specific data values, record counts, and data types between the source and target databases, ensuring consistency and flagging any discrepancies. In addition, SQL queries can be used to compare essential metrics and summary statistics, such as sum, count, and average, between the original and migrated databases, further confirming data consistency.

To ensure that applications function as expected with the new database, thorough application testing in a staging environment using the migrated data is crucial. This process verifies that data-related operations produce the correct results and that the new system can support existing workflows. Furthermore, engaging end-users in User Acceptance Testing (UAT) can uncover issues that automated or scripted validation might have missed. End-user feedback provides valuable insights into the usability and functionality of the new system from a real-world perspective.

Finally, a review of audit trails and logs generated during the migration process can offer valuable insights into any anomalies or problems encountered. This allows for prompt identification and resolution of issues, ensuring a smoother transition to the new database.

By employing a comprehensive approach that includes automated validation, custom scripts, data consistency checks, application testing, user acceptance testing, and log review, organizations can ensure a seamless transition to their new database system. This thorough validation process not only guarantees the accuracy and reliability of the migrated data but also establishes a solid foundation for future business operations.