Load Microsoft SQL Server data into BigQuery

You can load data from Microsoft SQL Server to BigQuery using the BigQuery Data Transfer Service for Microsoft SQL Server connector. The Microsoft SQL Server connector supports data loads from Microsoft SQL Server instances hosted in on-premises environments and other cloud providers, such as Cloud SQL, Amazon Web Services (AWS), or Microsoft Azure. With the BigQuery Data Transfer Service, you can create on-demand and recurring data transfer jobs to transfer data from your Microsoft SQL Server instance into BigQuery.

Limitations

Microsoft SQL Server data transfer jobs are subject to the following limitations:

• There is a limited number of simultaneous connections to a Microsoft SQL Server database. Therefore, the number of simultaneous transfer runs to a single Microsoft SQL Server database is also limited. Ensure that the number of concurrent transfer jobs is less than the maximum number of concurrent connections supported by the Microsoft SQL Server database.
• Some Microsoft SQL Server data types might be mapped to the STRING type in BigQuery to avoid data loss. For example, certain numeric types in Microsoft SQL Server that don't have precision and scale defined might be mapped to STRING in BigQuery. For more information, see Data type mapping.

Data ingestion options

The following section provides information about the data ingestion options when you set up a Microsoft SQL Server data transfer.

TLS configuration

The Microsoft SQL Server connector supports the configuration for transport level security (TLS) to encrypt your data transfers into BigQuery. The Microsoft SQL Server connector supports the following TLS configurations:

• Encrypt data, and verify CA and hostname: This mode performs a full validation of the server using TLS over the TCPS protocol. It encrypts all data in transit and verifies that the database server's certificate is signed by a trusted Certificate Authority (CA). This mode also checks that the hostname you're connecting to exactly matches the Common Name (CN) or a Subject Alternative Name (SAN) on the server's certificate. This mode prevents attackers from using a valid certificate for a different domain to impersonate your database server.
  • If your hostname does not match the certificate CN or SAN, the connection fails. You must configure a DNS resolution to match the certificate or use a different security mode.
  • Use this mode for the most secure option to prevent person-in-the-middle (PITM) attacks.
• Encrypt data, and verify CA only: This mode encrypts all data using TLS over the TCPS protocol and verifies that the server's certificate is signed by a CA that the client trusts. However, this mode does not verify the server's hostname. This mode successfully connects as long as the certificate is valid and issued by a trusted VA, regardless of whether the hostname in the certificate matches the hostname you are connecting to.
  • Use this mode if you want to ensure that you are connecting to a server whose certificate is signed by a trusted CA, but the hostname is not verifiable or you don't have control over the hostname configuration.
• Encryption only: This mode encrypts all data transferred between the client and the server. It does not perform any certificate or hostname validation.
  • This mode provides some level of security by protecting data in transit, but it can be vulnerable to PITM attacks.
  • Use this mode if you need to ensure all data is encrypted but can't or don't want to verify the server's identity. We recommend using this mode when working with private VPCs.
• No encryption or verification: This mode does not encrypt any data and does not perform any certificate or hostname verification. All data is sent as plain text.
  • We don't recommend using this mode in an environment where sensitive data is handled.
  • We only recommend using this mode for testing purposes on an isolated network where security is not a concern.

Trusted Server Certificate (PEM)

If you are using either the Encrypt data, and verify CA and hostname mode or the Encrypt data, and verify CA mode, then you can also provide one or more PEM-encoded certificates. These certificates are required in some scenarios where the BigQuery Data Transfer Service needs to verify the identity of your database server during the TLS connection:

• If you are using a certificate signed by a private CA within your organization or a self-signed certificate, you must provide the full certificate chain or the single self-signed certificate. This is required for certificates issued by internal CAs of managed cloud provider services, such as the Amazon Relational Database Service (RDS).
• If your database server certificate is signed by a public CA (for example, Let's Encrypt, DigiCert, or GlobalSign), you don't need to provide a certificate. The root certificates for these public CAs are pre-installed and trusted by the BigQuery Data Transfer Service.

You can provide PEM-encoded certificates in the Trusted PEM Certificate field when you create a Microsoft SQL Server transfer configuration, with the following requirements:

• The certificate must be a valid PEM-encoded certificate chain.
• The certificate must be entirely correct. Any missing certificates in the chain or incorrect content causes the TLS connection to fail.
• For a single certificate, you can provide single, self-signed certificate from the database server.
• For a full certificate chain issued by a private CA, you must provide the full chain of trust. This includes the certificate from the database server and any intermediate and root CA certificates.

Before you begin

Before you can schedule a Microsoft SQL Server data transfer, you must meet the following prerequisites.

Microsoft SQL Server prerequisites

You must have created a user account in the Microsoft SQL Server database. For more information, see Create a user with a login.

BigQuery prerequisites

• Verify that you have completed all the actions that are required to enable the BigQuery Data Transfer Service.
• Create a BigQuery dataset to store your data.

Required roles

To get the permissions that you need to create a Microsoft SQL Server data transfer, ask your administrator to grant you the BigQuery Admin (roles/bigquery.admin) IAM role on your project. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to create a Microsoft SQL Server data transfer. To see the exact permissions that are required, expand the Required permissions section:

You might also be able to get these permissions with custom roles or other predefined roles.

Network configuration

You must set up specific network configurations when a public IP address isn't available for the Microsoft SQL Server database connection. For more information, see the following sections:

• Configure a connection to Cloud de Confiance instance
• Configure a connection to AWS
• Configure a connection to Azure

Set up a Microsoft SQL Server data transfer

Select one of the following options:

bq mk \
    --transfer_config \
    --project_id=PROJECT_ID \
    --data_source=DATA_SOURCE \
    --display_name=DISPLAY_NAME \
    --target_dataset=DATASET \
    --params='PARAMETERS'

bq mk \
    --transfer_config
    --target_dataset=mydataset
    --data_source=sqlserver
    --display_name='My Transfer'
    --params='{"assets":["db1/dbo/Department","db1/dbo/Employees"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":"1520",
        "connector.networkAttachment":"projects/dev-project1/regions/us-central1/networkattachments/na1",
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

To manually run a data transfer outside of your regular schedule, you can start a backfill run.

Data type mapping

The following table maps Microsoft SQL Server data types to the corresponding BigQuery data types:

The json and vector data types are only supported in Azure.

The JSON data type is supported in Azure SQL databases and Azure SQL managed instances configured with the always-up-to-date update policy. The JSON data type is not supported in Azure SQL managed instances configured with the Microsoft SQL Server 2022 update policy.

Microsoft SQL Server stores JSON as NVARCHAR(MAX), and not as a JSON type. We recommend that you use CHECK (ISJSON(json_col) = 1) for validation, and JSON_VALUE() for querying.

The Microsoft SQL Server doesn't have vector support for the vector data type. We recommend that you store vectors as JSON arrays in NVARCHAR(MAX) and use JSON_VALUE() for extraction, with manual FLOAT calculations for similarity.

Troubleshoot

To troubleshoot issues for your data transfer, see Microsoft SQL Server transfer issues.

Pricing

There is no cost to transfer Microsoft SQL Server data into BigQuery while this feature is in Preview.

What's next

• For an overview of the BigQuery Data Transfer Service, see What is BigQuery Data Transfer Service?.
• For information on using transfers, including getting information about a transfer configuration, listing transfer configurations, and viewing a transfer's run history, see Manage transfers.