Best practices for importing and exporting

The following are best practices to consider when importing andexporting data:

• Don't use Cloud Storage Requester Pays buckets
• Compress data to reduce cost.
• Reduce long-running import and export processes
• Use the bcp utility for importing and exporting data
• Use bulk insert for importing data
• Use SqlPackage for importing and exporting data
• Use striped import and export
• Verify the imported database

Don't use Cloud Storage Requester Pays buckets

You cannot use a Cloud Storage bucket that hasRequester Pays enabled for imports and exportsfrom Cloud SQL.

Compress data to reduce cost

Cloud SQL supports importing and exporting both compressed anduncompressed files. Compression can save significant storage space onCloud Storage and reduce your storage costs, especially when you areexporting large instances.

Reduce long-running import and export processes

Imports into Cloud SQL and exports out of Cloud SQL can take a long time to complete, depending on the size of the data being processed. This can have the following impacts:

• You can't stop a long-running Cloud SQL instance operation.
• You can perform only one import or export operation at a time for each instance, and a long-running import or export blocks other operations, such as daily automated backups.

You can decrease the amount of time it takes to complete each operation by using the Cloud SQL import or export functionality with smaller batches of data.

For whole database migrations, you generally should use BAK files rather than SQL files for imports. Generally, importing from a SQL file takes much longer than importing from a BAK file.

Use SqlPackage for importing and exporting data

You can import and export data in Cloud SQLby usingSqlPackage.It enables you to export a SQLdatabase, including database schema and user data, to a BACPAC file (.bacpac) and toimport the schema and table data from a BACPAC file into a new user database.

SqlPackage uses your credentials to connect to SQL Server to performdatabase imports and exports. It makes migrations available for all Cloud SQL users.To perform import and export operations, you must have the following:

• A workstation that is connected to your instance, where you can run SqlPackage.To learn more about connectivity options, seeAbout connection options.

• SqlPackage installed on your system. To learn more about downloading andinstalling SqlPackage,see theMicrosoft documentation.

• Credentials set up to access your instance. To learn more about setting upcredentials,seeHow to authenticate to Cloud SQL.

Examples

c:\ProgramFiles\MicrosoftSQLServer\160\DAC\bin>SqlPackage/Action:Import/tsn:myTargetServer/tdn:AdventureWorks2017/tu:myUsername/sf:mySourceFile/TargetTrustServerCertificate:True/tp:myPassword

c:\ProgramFiles\MicrosoftSQLServer\160\DAC\bin>SqlPackage/Action:Export/TargetFile:"myTargetFile"/ssn:mySourceServer/su:myUsername/sdn:AdventureWorks2017/SourceTrustServerCertificate:True/sp:myPassword

Use the bcp utility for importing and exporting data

Another option to import and export data in Cloud SQLis using the bulk copy program (bcp) utility. By using the bcp utility, you can export data from a SQL Server database into a data file and import data from a data file into a SQL Server database.The bcp utility uses your credentials to connect to SQL Server to performdatabase imports and exports. It makes transfers available for all Cloud SQL users.To perform import and export operations, you must have the following:

• A workstation where you can run the bcp utility, and that has connectivity toyour Cloud SQL instance.To learn more about connectivity options, seeAbout connection options.

• The bcp utility installed on your system. To learn more about downloading andinstalling bcp,see theMicrosoft documentation.

• Credentials set up to access your instance. To learn more about setting upcredentials,seeHow to authenticate to Cloud SQL.

Examples

bcpPerson.Personin"person.csv"-dAdventureWorks2017-UmyLoginID-SmyServer

bcpPerson.Personout"person.dat"-UmyLoginID-SmyServer-dAdventureWorks2017

Use bulk insert for importing data

Bulk insert lets you import data into your Cloud SQL for SQL Server database from a file stored in Cloud Storage.

This section describes the following:

• Required roles and permissions
• Considerations when using bulk insert
• Perform bulk insert
• View the imported data
• Disable bulk insert

Required roles and permissions

To configure bulk insert, you need the following:

• TheCONTROL permission on the database where you want to import the data.

• AnHMAC access key and a secret mapped to anIAM account with the following permissions:

  • storage.buckets.get
  • storage.objects.create andstorage.multipartUploads.create for writing error logs and examples of bad data.

  Alternatively, you can also use following roles:

  • Storage Object Viewer
  • Storage Object Creator for writing error logs and examples of bad data.

To use bulk insert, you need the following:

• TheEXECUTE permission on themsdb.dbo.gcloudsql_bulk_insert stored procedure.Cloud SQL creates the stored procedure after bulk insert is enabled on the instance.Cloud SQL grants theEXECUTE permission to thesqlserver admin account by default.
• TheINSERT permission on the object where you want to import the data.

For more information on creating users for bulk insert, seeCreate and manage users.

Considerations when using bulk insert

This section has recommendations for handling security, performance, and reliability on instances while using bulk insert.

Security

Cloud SQL encrypts and stores the HMAC access key and secret in an instance as adatabase scoped credential.Their values cannot be accessed after they are saved. You can delete the key and secret from an instance bydropping the database scoped credential using a T-SQL command.If you take any backup while the key and secret are stored on the instance, then that backup wouldcontain that key and secret. You can also render the key invalid bydeactivating and deleting the HMAC key.

The following operations can inadvertently transfer the access key and secret and make them available:

• Cloning the instance: the key and secret are available on the cloned instance.
• Creating a read replica: the key and secret are available on the created read replica.
• Restoring from a backup: the key and secret are available on the instance restored from a backup.

We recommend that you drop the key and secret from the target instance after performing these operations.

Bulk insert can write data that it can't parse to a file stored in a Cloud Storage bucket.If you want to protect data that bulk insert has access to, then configureVPC service controls.

Performance

We recommend doing the following to mitigate performance impacts while using bulk insert:

• Test and set an appropriate value for@batchsizebecause by default, all data is imported in a single batch.
• For large inserts, disable indexes temporarily to speed up data insertion.
• If possible, use the@tablock option because this can reduce contention and increase data load performance.
• Use the@ordercolumnsjson parameter to specify data sorted in the order of the clustered index. This helps with better instance performance.

Reliability

We recommend doing the following to mitigate impact on instance reliability while using bulk insert:

• If a failure occurs and@batchsize is used, this can lead to partially loaded data.You might need to manually clean up this data on your instance.
• Use the@errorfile option to keep a log of errors andexamples of bad data detected during the load process. This makes it easier to identify rows that have failed to load.

Perform bulk insert

You can perform the bulk insert operation using the following stored procedure:

msdb.dbo.gcloudsql_bulk_insert

For more information, seeStored procedure for using bulk insert.

Example: Import data from a file in Cloud Storage and specify an error file

1. Enable bulk insert

To enable bulk insert on your instance, enable thecloud sql enable bulk insert flag.

gcloudsqlinstancespatchINSTANCE_NAME--database-flags="cloud sql enable bulk insert"=on

ReplaceINSTANCE_NAME with the name of the instancethat you want to use for bulk insert.

For more information, seeconfigure database flags.

After you enable this flag on your instance, Cloud SQL installs the bulk insert stored procedure on your instanceand gives thesqlserver admin account permissions to execute.

2. Create an HMAC key

You require an HMAC key to access your Cloud Storage bucket. We recommend that youcreate an HMAC key for a service accountand grant the service account permissions to the buckets that you want to use for bulk insert.For more information and security considerations, seeConsiderations when using bulk insert.

3. Create sample data to import

1. Using a text editor, create a file with ANSI or UTF-16 encoding that has the following sample data. Save the file in your Cloud Storage bucket and name it asbulkinsert.bcp, for example.

   1,Elijah,Johnson,1962-03-212,Anya,Smith,1982-01-153,Daniel,Jones,1990-05-21

2. Create a format file using the following sample data. Save the file in your Cloud Storage bucket and name it asbulkinsert.fmt, for example.For more information about XML and non-XML format files in SQL Server,seeCreate a Format File.

   13.041SQLCHAR07","1PersonID""2SQLCHAR025","2FirstNameSQL_Latin1_General_CP1_CI_AS3SQLCHAR030","3LastNameSQL_Latin1_General_CP1_CI_AS4SQLCHAR011"\r\n"4BirthDate""

4. Execute the stored procedure

1. Connect to your instance using thesqlserver user and create a sample database and table for bulk insert.

   USEMASTERGO-- create test databaseDROPDATABASEIFEXISTSbulktestCREATEDATABASEbulktestGO-- create table to insertUSEbulktest;GOCREATETABLEdbo.myfirstimport(PersonIDsmallint,FirstNamevarchar(25),LastNamevarchar(30),BirthDateDate);

2. Create adatabase master key, adatabase scoped credential, and anexternal data source. Set the identity asS3 Access Key.

   -- create master keyCREATEMASTERKEYENCRYPTIONBYPASSWORD='YourStrongPassword1';-- create database scoped credentialCREATEDATABASESCOPEDCREDENTIALGCSCredentialWITHIDENTITY='S3 Access Key',SECRET='<Access key>:<Secret>';--create external data sourceCREATEEXTERNALDATASOURCEGCSStorageWITH(TYPE=BLOB_STORAGE,LOCATION='s3://storage.googleapis.com/bulk-insert-demo/',CREDENTIAL=GCSCredential);CREATEEXTERNALDATASOURCEGCSStorageErrorWITH(TYPE=BLOB_STORAGE,LOCATION='s3://storage.googleapis.com/bulk-insert-demo/',CREDENTIAL=GCSCredential);

3. Execute the bulk insert stored procedure to import the sample data.

   EXECmsdb.dbo.gcloudsql_bulk_insert@database='bulktest',@schema='dbo',@object='myfirstimport',@file='s3://storage.googleapis.com/bulk-insert-demo/bulkinsert.bcp',@formatfile='s3://storage.googleapis.com/bulk-insert-demo/bulkinsert.fmt',@fieldquote='"',@formatfiledatasource='GCSStorage',@ROWTERMINATOR='0x0A',@fieldterminator=',',@datasource='GCSStorage',@errorfiledatasource='GCSStorageError',@errorfile='s3://storage.googleapis.com/oom-data/bulkinsert/bulkinsert_sampleimport.log',@ordercolumnsjson='[{"name": "PersonID","order": " asc "},{"name": "BirthDate","order": "asc"}]'

View the imported data

You can view the imported data by using one of the following methods:

• Run the following query:

  SELECT*FROMdbo.myfirstimport

• Cloud SQL adds a record of this procedure to the SQL error log. You can viewthis inCloud Logging. You can also view this in the SQL error log dataonSQL Server Management Studio (SSMS).

Disable bulk insert

To disable bulk insert, remove thecloud sql enable bulk insert flag:

gcloudsqlinstancespatchINSTANCE_NAME--database-flags="cloudsql enable bulk insert"=off

ReplaceINSTANCE_NAME with the name of the instance where you want to remove bulk insert.

Alternatively, you can run the following command to clear all database flags:

gcloudsqlinstancespatchINSTANCE_NAME--clear-database-flags

ReplaceINSTANCE_NAME with the name of the instance where you want to remove bulk insert.

Use striped import and export

When you perform a striped import or export, you reduce the time it takes forthe operation to complete, and enable databases larger than 5 TB to be importedand exported. For more information, seeExport and import using BAK files.

Verify the imported database

After an import operation is complete, connect to your database and run theappropriate database commands to make sure the contents are correct. Forexample,connect andlist the databases, tables, and specific entries.

Known limitations

For a list of known limitations, seeIssues with importing and exporting data.

Automating export operations

Although Cloud SQL doesn't provide a built-in way to automate databaseexports, you can build your own automation tool using several Google Cloudcomponents. To learn more, see this tutorial.

Troubleshooting

Troubleshooting import operations

Troubleshooting export operations

What's next

• Learn how to import and export data using BAK files.
• Learn how to import data using SQL dump files.
• Learn how to enable automatic backups.
• Learn how to restore from backups.