This tutorial shows you how to create and deploy a pipeline that continuously replicates changed data from a Microsoft SQL Server database to a BigQuery table.
In this tutorial, you:
- Set up your Microsoft SQL Server database to enable replication.
- Create and run a Cloud Data Fusion Replication job.
- View the results in BigQuery.
This tutorial uses billable components of Google Cloud, including:
When Replication runs, you are charged for the Dataproc cluster and you incur processing costs for BigQuery. To optimize these costs, we strongly recommend that you use BigQuery flat rate pricing.
Before you begin
- Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.
Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.
- Enable the Cloud Data Fusion, BigQuery, and Cloud Storage APIs.
- Create a SQL Server instance.
- Download the AdventureWorks2017 database and load the data into your SQL Server instance.
- Create a private Cloud Data Fusion instance in version 6.3.0 or above. When you set up the instance:
- Enable Virtual Private Cloud peering.
- Enable Replication by clicking Add Accelerators and selecting Replication.
If you use an existing instance, or if you don't see Replication in the Cloud Data Fusion menu menu, see Upgrade to enable Replication.
Enable replication in your SQL Server database
Create and run a Cloud Data Fusion Replication job
Upload the JDBC driver
Download the SQL Server JDBC driver to your local machine.
In the Cloud Data Fusion UI, upload the JDBC driver. Use these values to configure the JDBC driver:
- In the Name field, enter
- In the Version field, keep the default.
- In the Class Name field, enter
- In the Name field, enter
Create the pipeline
In the Cloud Data Fusion UI, click the menu menu and navigate to the Replication page.
On the Create new replication job page, specify a Replication job Name.
Configure the source:
- Select Microsoft SQL Server as the source.
- For Host, enter the hostname of the SQL Server to read from.
- For Port, enter the port to use to connect to the SQL Server:
- For JDBC Plugin Name, select
sqlserver(or the name you specified when you configured the JDBC driver).
- For Database name, enter
- In the Credentials section, enter your username and password to access the SQL Server.
If the connection is successful, a list of AdventureWorks2017 tables is displayed. For this tutorial, select all of the tables and events to replicate (including Insert, Update, and Delete events).
Configure the target:
- Select the BigQuery target.
- The Project ID and Service Account Key are automatically detected. Keep the default values.
- (Optional) In the Advanced section, you can configure the staging bucket's name and location, the load interval, the staging table prefix, and the behavior when tables or databases are dropped.
(Optional) Configure the advanced properties. For this tutorial, you can accept the default settings.
On the Review assessment page, click View mappings by any of the tables to get an assessment of schema issues, missing features, or connectivity issues that might occur during replication. If any issues occur, they must be resolved before you can proceed. For this tutorial, if any of the tables have issues, go back to the step where you selected tables and instead select a table or event (Inserts, Updates, or Deletes) without issues.
Review the summary Replication job details, and then click Deploy Replication job.
Start the pipeline
From the Replication job details page:
The Replication job transitions from Provisioning to Starting to Running state. In the running state, the Replication job loads an initial snapshot of the table data that you selected (for example, the People table) into BigQuery. In this state, the state of the People table is listed as Snapshotting. After the initial snapshot is loaded into BigQuery, any changes made to the People table are replicated to BigQuery, and the state of the table is listed as Replicating.
Monitor the pipeline
You can start and stop the Replication job, review its configuration and logs, and monitor your Replication job.
You can monitor Replication job activities from the Replication job details page.
From the Replication page, click the desired Replication job Name.
View the results in BigQuery
The Replication job creates a replicated dataset and table in BigQuery, with names inherited from the corresponding SQL Server database and table names.
Open BigQuery in the Cloud Console.
In the left panel, click on your project name to expand a list of datasets.
adventureworks2017dataset, and then select a table to view.
For more information, see the BigQuery documentation.
To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.
After you've finished the tutorial, clean up the resources you created on Google Cloud so they won't take up quota and you won't be billed for them in the future. The following sections describe how to delete or turn off these resources.
Delete the VM instance
- In the Cloud Console, go to the VM instances page.
- Select the checkbox for the instance that you want to delete.
- To delete the instance, click More actions, click Delete, and then follow the instructions.
Delete the Cloud Data Fusion instance
Delete the project
The easiest way to eliminate billing is to delete the project that you created for the tutorial.
To delete the project:
- In the Cloud Console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.