Cloud SQL/ Create SQL instance

gcloud sql instances create [INSTANCE_NAME] --database-version=SQLSERVER_2017_STANDARD --cpu=[NUMBER_CPUS] --memory=[MEMORY_SIZE] --root-password=[INSERT-PASSWORD-HERE]

Connect to your instance

• Private IP: Requires additional APIs and permissions, which may require your system admin. visit the Networking page to change.
• Public IP: Authorize a network or use Cloud SQL Proxy to connect.

Cloud SQL/ Cloud SQL Auth proxy

Cloud SQL Auth proxy can listen on any port, it only creates outgoing connections to the Cloud SQL instance on port.

Download Cloud SQL Auth proxy.

Creates outgoing connections to the Cloud SQL instance on port 1433:

cloud_sql_proxy -instances=[Instance-connection-name]=tcp:1433 

Configuring the user account

gcloud sql users set-password sqlserver --instance=[INSTANCE_NAME] --prompt-for-password

Restoring and Backing up

Due to permission from GCP (Ref: https://cloud.google.com/sql/docs/sqlserver/users), we use bucket for the import. It’s required roles and permissions.

gsutil iam ch serviceAccount:[email protected]:legacyBucketWriter,objectViewer gs://ia-tdc-data-1

gcloud sql instances describe catdcdbt1
gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:W gs://[BUCKET_NAME]
gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:R gs://[BUCKET_NAME]/[IMPORT_FILE_NAME]

Import:

gcloud sql import bak [INSTANCE_NAME] gs://[BUCKET_NAME]/[FILE_NAME] 
--database=[DATABASE_NAME]

Export:

gcloud sql export bak [INSTANCE_NAME] gs://[BUCKET_NAME]/[FILE_NAME] 
--database =[DATABASE_NAME]

Example

We have created SQL Instance named catdcdbt1, set the password password when the instance was created.:

Then the command is below:

"C:\namle\cloud_sql_proxy.exe" -instances="tdc-shared-services:us-central1:catdcdbt1"=tcp:1433

Setting the password for user sqlserver:

gcloud sql users set-password sqlserver --instance=tdcdb-t1 --prompt-for-password

Creating User, Setting Pwd:

gcloud sql users create [USER_NAME] --instance=[INSTANCE_NAME] --password=[PASSWORD]

or

gcloud sql users set-password sqlserver --instance=[INSTANCE_NAME] --prompt-for-password

Connect to instance using SQL Server Management Studio:

• For Server Type, enter Database Engine.
• For Server Name, enter 127.0.0.1 as the IP address of your SQL Server instance.
• For Authentication, enter SQL Server Authentication.
• For Login, enter default user is sqlserver.
• For Password, enter the password used when the instance was created/ or the new pwd updated.

Restoring and Backing up

To restore a bak from bucket gs://nas1-bucket/db_ex_stg.bak to the SQL Instance, set the role Write and Read, then, run the import command.

gcloud sql instances describe catdcdbt1
gsutil acl ch -u "[email protected]":W gs://nas1-bucket
gsutil acl ch -u "[email protected]":R gs://nas1-bucket/db_ex_stg.bak

Then perform the restore from bucket:

gcloud sql import bak catdcdbt1 gs://nas1-bucket/db_ex_stg.bak --database=Exchange_Staging

19 Nov 2021

Last modified on October 10th, 2024 at 1:07 pm

gcloud GCP