Load Microsoft SQL Server data into BigQuery

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

You can load data from Microsoft SQL Server to BigQuery usingthe BigQuery Data Transfer Service for Microsoft SQL Server connector. TheMicrosoft SQL Server connector supports data loads from Microsoft SQL Serverinstances hosted in on-premises environments and other cloud providers, such asCloud SQL, Amazon Web Services (AWS), or Microsoft Azure. With the BigQuery Data Transfer Service, youcan create on-demand and recurring data transfer jobs to transfer data from yourMicrosoft 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 aMicrosoft SQL Server database. Therefore, the number of simultaneoustransfer runs to a single Microsoft SQL Server database is alsolimited. Ensure that the number of concurrent transfer jobs is less than themaximum number of concurrent connections supported by theMicrosoft SQL Server database.
• Some Microsoft SQL Server data types might be mapped to theSTRINGtype in BigQuery to avoid data loss. For example, certainnumeric types in Microsoft SQL Server that don't have precision andscale defined might be mapped toSTRING in BigQuery. For moreinformation, seeData type mapping.

Data ingestion options

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

TLS configuration

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

• Encrypt data, and verify CA and hostname: This mode performs a fullvalidation of the server using TLS over the TCPS protocol. It encrypts alldata in transit and verifies that the database server's certificate is signedby a trusted Certificate Authority (CA). This mode also checks that thehostname you're connecting to exactly matches the Common Name (CN) or aSubject Alternative Name (SAN) on the server's certificate. This mode preventsattackers from using a valid certificate for a different domain to impersonateyour database server.
  • If your hostname does not match the certificate CN or SAN, the connectionfails. You must configure a DNS resolution to match the certificate or use adifferent 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 TLSover the TCPS protocol and verifies that the server's certificate is signed bya CA that the client trusts. However, this mode does not verify the server'shostname. This mode successfully connects as long as the certificate is validand issued by a trusted VA, regardless of whether the hostname in thecertificate matches the hostname you are connecting to.
  • Use this mode if you want to ensure that you are connecting to a serverwhose certificate is signed by a trusted CA, but the hostname is notverifiable or you don't have control over the hostname configuration.
• Encryption only: This mode encryptsall data transferred between the client and the server. It doesnot perform any certificate or hostname validation.
  • This mode provides some level of security by protecting data in transit, butit can be vulnerable to PITM attacks.
  • Use this mode if you need to ensure all data is encrypted but can't ordon't want to verify the server's identity. We recommend using this modewhen working with private VPCs.
• No encryption or verification: This mode does not encrypt any data anddoes not perform any certificate or hostname verification. All data is sent asplain text.
  • We don't recommend using this mode in an environment where sensitive data ishandled.
  • We only recommend using this mode for testing purposes on an isolatednetwork where security is not a concern.

Trusted Server Certificate (PEM)

If you are using either theEncrypt data, and verify CA and hostname mode ortheEncrypt data, and verify CA mode, then you can also provide one or morePEM-encoded certificates. These certificates are required in some scenarioswhere the BigQuery Data Transfer Service needs to verify the identity of yourdatabase server during the TLS connection:

• If you are using a certificate signed by a private CA within your organizationor a self-signed certificate, you must provide the full certificate chain orthe single self-signed certificate. This is required for certificates issuedby internal CAs of managed cloud provider services, such as the AmazonRelational 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 acertificate. The root certificates for these public CAs are pre-installed andtrusted by the BigQuery Data Transfer Service.

You can provide PEM-encoded certificates in theTrusted PEM Certificatefield when you create a Microsoft SQL Server transfer configuration, with thefollowing requirements:

• The certificate must be a valid PEM-encoded certificate chain.
• The certificate must be entirely correct. Any missing certificates in thechain or incorrect content causes the TLS connection to fail.
• For a single certificate, you can provide single, self-signed certificatefrom the database server.
• For a full certificate chain issued by a private CA, you must provide thefull chain of trust. This includes the certificate from the database serverand 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. Formore information, seeCreate a user with alogin.

BigQuery prerequisites

• Verify that you have completed all the actions that are required toenable the BigQuery Data Transfer Service.
• Create a BigQuery datasetto 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 theBigQuery Admin (roles/bigquery.admin) IAM role on your project. For more information about granting roles, seeManage 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 theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Network configuration

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

• Configure a connection to Google Cloud 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:

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

bqmk\--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 startabackfill run.

Data type mapping

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

Thejson andvector data types are only supported in Azure.

TheJSON datatype issupported in Azure SQL databases and Azure SQLmanaged instances configured with the always-up-to-date update policy. The JSONdata type is not supported in Azure SQL managed instancesconfigured with the Microsoft SQL Server 2022 update policy.

Microsoft SQL Server stores JSON asNVARCHAR(MAX), and not as a JSONtype. We recommend that you useCHECK (ISJSON(json_col) = 1) for validation,andJSON_VALUE() for querying.

The Microsoft SQL Server doesn't have vector support for thevector data type. We recommend that you store vectors as JSON arrays inNVARCHAR(MAX) and useJSON_VALUE() for extraction, with manualFLOATcalculations for similarity.

Troubleshoot

To troubleshoot issues for your data transfer, seeMicrosoft SQL Server transfer issues.

Pricing

There is no cost to transfer Microsoft SQL Server data intoBigQuery while this feature is inPreview.

What's next

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