Google Cloud SQL Server: Set up, Connection Guide, and Pricing

Discover the step-by-step process of setting up Google Cloud SQL server.

In today’s data-driven world, efficient data management is crucial for businesses.
Google Cloud Platform (GCP) offers a robust solution by allowing you to set up and use SQL Server, a powerful relational database management system.
Learn about its advantages, High Availability options, pricing, and get answers to frequently asked questions.

Advantages of Cloud SQL

Cloud SQL is fully managed Relational Database service offering by google cloud platform. It allows you to configure your needs and do not worry about managing the database.

• Scalability : Cloud SQL allows you to scale your SQL Server databases easily as your data requirements grow. You can increase or decrease resources based on demand.
• High Availability: Cloud SQL provides automatic failover and backups, ensuring your data is protected and available even in the event of hardware failures (99.95 % SLA).
• Managed Service: Google takes care of routine database management tasks like patching, backups, and updates, allowing you to focus on your applications and data.
• Security: GCP’s robust security measures, including encryption at rest and in transit, ensure the safety of your data.
• Global Reach : With multiple regions and availability zones, you can deploy SQL Server instances closer to your users, reducing latency and improving performance.

Setting Up SQL Server on GCP: Step-by-Step Guide

Follow these steps to set up SQL Server on Google Cloud Platform

Create a GCP Account

If you don’t have one, you can opt-in for a free trial. Refer our tutorial Google Cloud Free Credits – Google Cloud Tutorials.

This will be a hands-on demonstration and you can follow along.

Enable Cloud SQL API

In your project, enable the Cloud SQL API to begin using the service.

Log into google cloud console https://console.cloud.google.com/ using your cloud account and search “cloud sql ” in Google cloud search box.

Create a SQL Server GCP Instance

Navigate to the Cloud SQL Console and create a new instance, selecting SQL Server as the database type.

Set instance ID, password, and choose the desired configuration options, including machine type and storage.

Enter a valid instance Id name and select password that would be used to connect to SQL Server instance.

Select your required SQL server Database version.

If you are new to cloud database set up, select Deployment options for SQL server database.

Selecting the correct deployment option as per your requirement would prefill database configuration form with the best suitable options value.

gcp allows you to deploy cloud database in single region or multiple zones depending on your desired availability options.

In case of multi zone option select primary region and secondary region for new database

You cannot create global cloud SQL database in google cloud platform.

Choose from the most commonly used shapes or choose a custom shape. Make sure your selection has enough memory to hold your largest table.

It also allows you to disable multi-threading depending on the machine configuration.

Select Storage type and storage capacity. SSD storage provides high performance.

10 – 65,536 GB. Higher capacity improves performance, up to the limits set by the machine type. Capacity can’t be decreased later.

Enable automatic storage to increase for highly available database servers.

Database advance configuration allows you to configure data encryptions options.

By default, it uses google managed encryptions to encrypt your data.

To learn more about data encryptions refer Google Cloud Storage Encryption – Cloud KMS – Google Cloud Tutorials .

Automated backups options help to protect data loss in case of failures.

Configure automated daily backups and saved it in single region or multiple regions.

Enable Delete protection for critical database workloads.

Select prefer window for Database maintenance activities.

Maintenance typically only takes place once every few months, and requires your instance to be restarted while updates are made, which disrupts service briefly.

Review configuration and click on create instance.

Connect to the SQL Server GCP Instance

create a new Database in our cloud SQL instance.

Navigate to cloud SQL instance and under Database tab click on create new Database.

In this tutorial we will connect to cloud SQL database using Microsoft SQL server Management studio

Refer this link for free download SQL Server Downloads | Microsoft.

First step to connect to Cloud SQL is to enable Cloud SQL Admin API

Search cloud SQL Admin API in google cloud console and enable in API for your Project.

Install gcloud CLI

The gcloud CLI uses the Admin API to access Cloud SQL, so you must Enable the Admin API before using the gcloud CLI to access Cloud SQL.

In Windows PowerShell, run the following command to initialize the gcloud CLI

gcloud init

Run the following command to authenticate the gcloud CLI

  gcloud auth login
  

Select google cloud account and authenticate it using valid credentials.

Download and install the Cloud SQL Auth Proxy

Using the Cloud SQL Auth Proxy is the recommended method for connecting to a Cloud SQL instance. The Cloud SQL Auth Proxy

It works with both public and private IP endpoints.

Run the Cloud SQL Auth Proxy by using a bash shell command prompt (or by using Windows PowerShell)

./cloud-sql-proxy INSTANCE_CONNECTION_NAME

Navigate to folder where cloud proxy is installed and execute above command.

Copy Paste connection name from overview tab of cloud SQL.

Log into SSMS, select Connect Object Explorer from the File menu.

For Server Type, select Database Engine.

In Server Name, enter 127.0.0.1 as the IP address..

For Authentication, select SQL Server Authentication.

Enter sqlserver for username.

Enter password created at time of instance creation.

On successful log in it would list all the Databases created in cloud is cloud instance.

Create a new Employee table using below SQL query

create table employee
(
  
    EmployeeId int,
    EmployeeName varchar(256)
      
)

Add 2 new rows in the Employee Table using below query

insert into employee (EmployeeId,EmployeeName)
VALUES('1' ,'Anmol')

insert into employee (EmployeeId,EmployeeName)
VALUES('2' ,'Gaurav')


select * from employee

Learn more about SQL queries here SQL – Full Stack Tutorials Hub

Cloud SQL Server Pricing

You can create an account to evaluate how Cloud SQL performs in real-world scenarios. New users will get up to $400 in free credits to spend on Cloud SQL to run, test, and deploy workloads. You won’t be charged until you upgrade.

Cloud SQL pricing is designed to be straightforward, transparent, and pay-as-you-go. The pricing model comprises several components, each contributing to the overall cost of using the service. The primary components that influence Cloud SQL pricing include:

1. Instance Type: Cloud SQL supports various database engines such as MySQL, PostgreSQL, and SQL Server. Each engine has different performance levels, and the pricing varies accordingly. There are usually multiple instance types available for each engine, ranging from basic to high-performance configurations.
2. Instance Size: Instances come in different sizes, indicating the amount of CPU, RAM, and storage provided. Larger instances with more resources will generally cost more than smaller ones.
3. Storage: The amount of storage allocated to your instance is a significant cost factor. Cloud SQL offers different storage options, including Standard Storage and SSD Storage. The choice of storage type and the amount you allocate will impact your costs.
4. Backup and Storage Snapshot Costs: Cloud SQL provides automated backups and the ability to take manual snapshots of your database. While automated backups are included in the service cost, storage charges for additional backup copies and snapshots can contribute to costs.
5. Network Usage: Data transferred in and out of your Cloud SQL instance is subject to network usage charges. This includes both incoming and outgoing data.
6. Region and Availability: The physical location of your Cloud SQL instance (region) and its availability configuration (high availability, failover replicas, etc.) can affect pricing. Some regions might have higher costs due to factors like demand and infrastructure availability.

For detailed information refer Pricing  |  Cloud SQL: Relational Database Service  |  Google Cloud

Several factors can influence the overall costs of using Cloud SQL:

1. Database Size and Traffic: The size of your database and the amount of traffic it handles directly impact costs. Larger databases and high traffic volumes may require larger instances with more resources, contributing to higher expenses.
2. Storage Growth: As your data grows over time, you might need to increase your storage allocation. Monitoring storage usage and planning for growth can help manage costs effectively.
3. Backup and Snapshot Frequency: Taking frequent backups and snapshots can lead to increased storage costs. Evaluate your backup and snapshot strategy to balance data protection needs with expenses.
4. Instance Availability: Using features like high availability and failover replicas enhances database reliability but can add to costs. Consider the level of availability your application requires.

To optimize your Cloud SQL expenses:

1. Rightsize Instances: Choose the instance type and size that align with your performance requirements. Avoid overprovisioning resources.
2. Monitor and Tune: Regularly monitor your instance’s performance metrics and tune your database queries and configurations to ensure efficient resource utilization.
3. Backup and Snapshot Strategy: Evaluate your backup and snapshot frequency. Delete unnecessary backups and snapshots to manage storage costs.
4. Use Regional Replicas Wisely: Instead of scaling up a single instance, consider using read replicas in different regions to distribute read traffic and reduce the load on the primary instance.
5. Consider Reserved Instances: If you have long-term usage plans, reserved instances can provide cost savings.