CCNA Pcd Migrate Data Questions

BigQuery Data Transfer Service for Teradata is the recommended service for migrating Teradata data to BigQuery. It handles schema and data transfer, but SQL scripts like BTEQ must be manually converted to BigQuery SQL as they are not automatically transformed.

DMS CDC requires that binary logs on the source are retained from the point of the full dump until cutover. If the binary logs are purged (e.g., by expiring logs or manual cleanup), the CDC phase cannot continue.

Shadow writes (dual-write) involve writing to both databases and comparing results. Comparison queries and load testing are validation techniques.

MySQL 5.7 allowed GROUP BY with columns not in the SELECT list by default (ONLY_FULL_GROUP_BY was off), but MySQL 8.0 enables it by default. This change can cause queries to fail if they rely on the old behavior.

MyISAM must be converted to InnoDB before migration. utf8 must be changed to utf8mb4 to support full Unicode. DMS continuous migration minimizes downtime.

MySQL 8.0 changes default authentication to caching_sha2_password, which may break application connections if the client driver does not support it. MyISAM tables should be converted to InnoDB for reliability and features like foreign keys. utf8 charset should be changed to utf8mb4 to avoid truncation of 4-byte characters. The caching_sha2_password plugin is default; if the app uses older connectors, they may need to set the default to mysql_native_password or update the connector.

Database Migration Service (DMS) supports continuous migration jobs that perform a full dump, then CDC (change data capture), allowing for near-zero-downtime cutover by promoting the Cloud SQL replica.

Option C is correct because exporting Snowflake data to Cloud Storage in Parquet format is an efficient, columnar format that reduces storage costs and speeds up loading into BigQuery. Parquet is natively supported by BigQuery and allows for schema inference, making the migration process seamless. This approach also supports incremental updates if needed, avoiding downtime by keeping the source operational during the export.

The source charset 'utf8' in MySQL is actually utf8mb3 (3-byte UTF-8). Cloud SQL MySQL 8.0 defaults to utf8mb4. To avoid data loss, the source tables should be converted to utf8mb4 before migration, or the target must be configured to accept utf8mb3 if compatible.

BigQuery Data Transfer Service supports scheduled transfers from Amazon S3 to BigQuery, handling incremental loads. It is the recommended service for this scenario.

PostgreSQL has SERIAL, sequences, and IDENTITY columns. Oracle sequences can be converted to PostgreSQL sequences or IDENTITY columns. SERIAL is a shorthand for creating a sequence on a column.

The recommended approach is to unload data from Redshift to Amazon S3 in Parquet or Avro format, then use BigQuery Data Transfer Service or load jobs to ingest from S3. Direct export from Redshift to BigQuery is not possible. Using a Cloud Storage intermediary would require copying from S3 to GCS, adding latency and cost.

Shadow writes, also known as dual-writes, allow the application to send the same write operations to both the old MySQL database and the new Cloud SQL database simultaneously. This technique enables real-time comparison of the results from both databases to verify that the migration is handling writes correctly without disrupting the existing production workload.

The BigQuery Migration Service (option E) provides a dedicated SQL translation capability that automatically converts Snowflake SQL dialect to BigQuery SQL, including schema, DDL, and DML statements. The BigQuery Data Transfer Service (option B) can be used to schedule and automate the transfer of data from Snowflake to BigQuery, which is a key part of the migration workflow. Together, these two services address both the SQL conversion and the data movement aspects of the migration.

Option C is correct because Cloud SQL requires InnoDB tables (MyISAM is not supported) and utf8mb4 charset for full Unicode support including emojis. By first converting the tables and charset on-premises, then using Database Migration Service (DMS) continuous migration, you achieve minimal downtime through ongoing replication.

The common pattern is to unload Redshift data to Amazon S3, then use Google Cloud Storage Transfer Service to move the files to Cloud Storage, and finally load into BigQuery.

BigQuery Data Transfer Service supports Redshift as a source and uses Amazon S3 as an intermediate staging location for the data transfer.

Database Migration Service (DMS) supports PostgreSQL migrations with continuous CDC for minimal downtime. It manages the full dump and incremental sync.

Flyway integrates well with Cloud SQL and CI/CD. The best practice is to use Flyway migrations to apply schema changes before or after data migration, ensuring version control and repeatability.

The recommended approach is to export Redshift data to S3, then use BigQuery Data Transfer Service for Redshift to load data directly into BigQuery, or use Cloud Storage as intermediate. The most efficient is to use the BigQuery Data Transfer Service which can read from S3 directly (via a transfer config) or use Cloud Storage as a staging area.

BigQuery Data Transfer Service for Teradata supports scheduled transfers. The correct setup is to configure a transfer config with a schedule (e.g., daily) and specify the dataset and table mappings. The service handles incremental loads based on a watermark column.

For a 500 GB database with a 2-hour downtime window, a one-time migration (offline) is sufficient because the dump and restore can complete within the window. Continuous migration is used for minimal downtime but adds complexity. Database Migration Service supports both one-time and continuous jobs.

BigQuery Data Transfer Service supports scheduled transfers from Teradata (and other sources) to BigQuery.

For microservices, each service should own its database. To maintain consistency across services, they should implement compensating transactions (sagas) rather than distributed transactions. Cloud Spanner can be used if strong consistency is required across global scales, but for microservices, the pattern is to use eventual consistency with sagas.

For a zero-downtime cutover, you must first stop writes to the source database, then promote the Cloud SQL replica. The error indicates writes are still happening; stopping them allows the CDC to catch up and then cutover.

Common Oracle to PostgreSQL mappings: NUMBER → NUMERIC, VARCHAR2 → VARCHAR, DATE → TIMESTAMP, CLOB → TEXT, RAW → BYTEA. Sequences and packages require different handling.

Enabling point-in-time recovery is important for data protection. Enforcing TLS ensures encrypted connections. These are common security and operational requirements.

PostgreSQL does not have a direct equivalent of Oracle packages. The recommended approach is to convert package procedures and functions to individual functions in a schema, and package variables to session-level variables or temporary tables. Ora2Pg can attempt conversion but often requires manual adjustments.

Flyway uses 'migrate' to apply pending migrations and 'validate' to verify the schema state. These are core commands in CI/CD pipelines.

Continuous migration with CDC enables cutover with minimal downtime. Promoting the replica is the final step to make Cloud SQL the primary with zero downtime.

Continuous migration job performs an initial full dump and then continuously replicates changes via CDC, allowing a near-zero downtime cutover by promoting the replica.

Migration planning includes assessing the source database to understand its characteristics and plan the migration strategy.

Liquibase and Flyway are industry-standard tools for version-controlled database schema migrations. They integrate with CI/CD pipelines and support PostgreSQL.

Oracle NUMBER maps to PostgreSQL NUMERIC (or DECIMAL), and VARCHAR2 maps to VARCHAR (or TEXT). The specified lengths are preserved.

Teradata uses a different syntax for temporal queries (e.g., CURRENT_DATE) and does not use backticks for identifiers. BigQuery SQL is based on GoogleSQL, which uses backticks for escaping reserved keywords and has different date functions.

Google's Database Migration Service (DMS) supports connecting to an on-premises Oracle database over the internet using Cloud SQL Auth Proxy. This method establishes an encrypted tunnel using the Cloud SQL Auth Proxy client, which authenticates via IAM and provides secure connectivity without requiring a public IP on the source database. It is the only supported connectivity method for internet-based migrations in DMS, enabling continuous migration with minimal downtime.

Option A is correct because running comparison queries (e.g., row counts, checksums) between the source Oracle database and the target Cloud SQL for PostgreSQL instance is a standard validation technique to ensure data consistency and completeness before cutover. Database Migration Service (DMS) handles continuous replication, but manual validation using queries like SELECT COUNT(*) or checksum functions (e.g., MD5 on concatenated columns) catches any discrepancies that might have been introduced during migration or replication lag.

The typical approach is to use BigQuery Data Transfer Service for Redshift (which can unload to S3 and then load into BigQuery), or manually export from Redshift to S3, transfer to GCS, and load into BigQuery. But the question specifies S3 as intermediate; using Transfer Service for Redshift is the managed option.

Shadow writes involve writing the same data to both the old and new databases simultaneously during the testing phase, then comparing the results to ensure consistency.

Option A is correct because Database Migration Service (DMS) supports continuous migration jobs that use PostgreSQL logical replication to keep the Cloud SQL replica synchronized with the on-premises source. When you are ready to cut over, you promote the Cloud SQL replica to a standalone primary, which applies any remaining changes and makes the database available with minimal downtime — typically only seconds for the final replication lag to clear.

DMS continuous migration requires archivelog mode for CDC, appropriate network connectivity (VPC peering or proxy), and a connection profile for the source.

Option A is correct because Cloud SQL Auth Proxy provides a secure way to connect to Cloud SQL from on-premises without requiring a public IP or firewall rules for the Cloud SQL instance. It uses a TLS tunnel to the Cloud SQL instance, which is ideal when the source database is behind a firewall and you need to establish outbound connectivity from the on-premises environment to Cloud SQL. This avoids exposing the Cloud SQL instance to the public internet.

Ora2Pg is an open-source tool that converts Oracle schemas and PL/SQL code to PostgreSQL-compatible format, including stored procedures and functions.

Exporting data from Snowflake to GCS (Google Cloud Storage) as files (e.g., Parquet, Avro, or CSV) and then loading them into BigQuery is the most appropriate method for a one-time batch transfer of historical data. This approach leverages Snowflake's native UNLOAD command to export data efficiently and BigQuery's batch load jobs to ingest the files, providing full control over the migration without requiring ongoing connectivity or complex tooling.

Snowflake and BigQuery have different SQL dialects. The best approach is to use a migration assessment tool to identify incompatible syntax, rewrite queries to BigQuery-compatible syntax (e.g., use STRING_AGG instead of LISTAGG), and test thoroughly. BigQuery does not support PIVOT natively; use conditional aggregation.

MySQL 8.0 removed the implicit sorting of GROUP BY that was present in MySQL 5.7. Applications relying on this behavior must explicitly use ORDER BY to guarantee sort order.

To test migration with production data shape, one approach is to take a snapshot of the source, restore it in a test environment, and then perform the migration to Cloud SQL. Alternatively, use shadow writes (dual-writes) to both old and new DBs in a test environment. Comparing query results and load testing are also part of validation.

Spanner does not support stored procedures; the recommended approach is to migrate business logic to the application layer using client libraries, which provides better scalability and maintainability.

Cloud SQL for MySQL 8.0 does not support MyISAM as the default storage engine; MyISAM tables must be converted to InnoDB. InnoDB provides ACID compliance, row-level locking, and crash recovery.

MyISAM tables must be converted to InnoDB before or during migration. The ALTER TABLE statement is the standard way to change the storage engine in MySQL.

DMS offers continuous migration jobs that perform a full dump followed by CDC, enabling near-zero-downtime cutover.

Cloud SQL Auth Proxy provides a secure tunnel and can be run on the source network to connect to Cloud SQL. VPC peering requires direct network connectivity, which may not be possible through a firewall.

Shadow writes allow testing by writing to both old and new DBs. Comparison queries validate data integrity. Load testing and rollback plan are also important but not testing approaches per se.

MySQL 8.0 has changed default character set to utf8mb4 and default collation to utf8mb4_0900_ai_ci. They should check: 1) Usage of utf8 vs utf8mb4 (utf8 is now an alias for utf8mb3). 2) The default collation change. 3) Any collation that is deprecated or removed in 8.0.

BigQuery SQL dialect is similar to standard SQL, but Snowflake-specific functions need to be converted. Using a converter tool or manual rewrite with testing is typical. There is no automated full conversion tool that guarantees correctness without review.

MySQL 8.0 defaults to strict SQL mode, which disables the MySQL 5.7 non-standard GROUP BY behavior (GROUP BY can select nonaggregated columns not in GROUP BY). In 8.0, this causes errors or different results unless the sql_mode includes ONLY_FULL_GROUP_BY.

Intermediate staging in S3, encrypting data in transit, and enabling automatic refresh are typical considerations for Redshift to BigQuery transfers using Data Transfer Service.

MyISAM tables are not supported on Cloud SQL for MySQL 8.0; they must be converted to InnoDB. InnoDB is the default engine and provides better performance, crash recovery, and concurrency.

Database Migration Service (DMS) with a continuous migration job enables ongoing Change Data Capture (CDC) replication from the source Oracle database to Cloud SQL for PostgreSQL. This approach keeps the target database synchronized with minimal downtime, allowing a controlled cutover when the migration is complete. It is the only option that satisfies the requirements of continuous sync and minimal downtime.

Common Redshift to BigQuery migration steps: unload data to Amazon S3, transfer to Google Cloud (Storage Transfer Service), then load into BigQuery. SQL dialect differences must be addressed.

Cloud Bigtable is a fully managed NoSQL wide-column database that is ideal for migrating from Cassandra. It provides high availability, low latency, and horizontal scalability.

Lift-and-shift means moving the database as-is to the cloud with minimal changes. Cloud SQL for SQL Server is compatible with on-premises SQL Server. The easiest method is to export a .bacpac file from the source and import it into Cloud SQL via the console or import/export feature.

MySQL's 'utf8' character set does not support 4-byte Unicode characters like emojis. Cloud SQL for MySQL supports 'utf8mb4', which is the correct charset for full Unicode support.

MySQL 8.0 defaults to strict SQL mode, which changes GROUP BY behavior. MySQL 5.7 may have had sql_mode with ONLY_FULL_GROUP_BY disabled. The solution is to either enable ONLY_FULL_GROUP_BY in the source or adjust queries to comply, or set the sql_mode in Cloud SQL to include ONLY_FULL_GROUP_BY if not already set, and fix the queries.