Going Cloud with SQL Server: Google Cloud Options

Published in

Google Cloud - Community

8 min readMay 2, 2022

This blog is co-authored and PoC’ed by Pop Periyanesh (pop.dbig@gmail.com)

Hi folks! This is all about moving SQL Server to cloud using GCP options, contesting one over the other and a rather lengthy one at that :) please stick till the end for an interesting verdict and engaging next steps…

Going cloud, migrating to cloud, data on cloud and integration to cloud, cloud presence — these are the words we hear these days when it comes to data management or processing applications. This simply means that the organisation is moving its digital assets wholly or partially to the cloud. It could also mean migrating from one cloud to another. A lot of benefits with “going cloud”, to name a few:

Eliminates operational overhead
Far superior flexibility and agility (because of ease of deployment, no upfront cost, no infrastructure maintenance / cost, affordability)
Cost-effective (pay only for what you use)
Scalability
Monitoring and more

This blog contains concepts on hosting of SQL Server on GCP. The following solutions are designed focusing on scalability, migration effort and ease of maintenance.

Ways to host the SQL Server on the cloud

SQL Server in Compute Engine
Cloud SQL for SQL Server
Using Docker Container / Persistent storage

1. SQL Server in Compute Engine

Deployment and configuration are similar to traditional on premise database infrastructure
Vertically scaling, Persistent disk / predefined machines, load balancing (if we are going to use multi instance database design) can be utilized by designing this way

VM Minimum Specifications Required

Memory : 4GB
Processor : x64, 2.0GHz
Storage : Min 6GB

Server failure is a possibility due to below reasons
SLA for Compute 99.99%(multiple zone) uptime and 99.5% on single instance
Poorly designed VM (by personal choice of configuration) may lead to poor SQL Server performance
Pre migration cost and design planning is crucial
Compute engine instance prices vary with configurations, if we don’t know the detailed design at the time of configuration, we may end up paying more
Also under — utilisation / over — utilisation of resources is a possibility

POC — SQL Server in Compute Engine

Steps:

GCP console> Navigation menu> Compute Engine> VM instance> Create instance
We can pick either Linux based/Windows boot image for our purpose since SQL Server works in both OS
Desktop friendly windows VM could be used if we want to RDP to the VM and maintain the SQL Server via GUI tools
On the boot menu we can choose from a variety of OS including (but not limited to),
Ubuntu
Windows Server (license to be purchased separately)
SQL Server on windows server (license to be purchased separately)

Create the VM (for POC we have picked Linux based Ubuntu(18.04), since it is free)
Once a VM is created, SSH into the VM and update the package manager then install the SQL Server
The following set of command has to be run on VM to install SQL Server:

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

sudo add-apt-repository “$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"

sudo apt-get update → Update the package manager

sudo apt-get install -y mssql-server → Install SQL Server

sudo /opt/mssql/bin/mssql-conf setup → Configure SQL Server

Firewall has to be configured to allow incoming connection on the SQL Server port(default port — 1433) of the VM instance

Note: We can add firewall rule to a particular VM by using Network tags

Now we can connect to SQL Server using public IP and SQL user credentials in SSMS and other tools
Database creation, deployment of DAC file, database backup are all tested and worked without any issue

Note: DAC file is Data-tier AppliCation which is a compressed file of the entire Database Model including objects like tables, views, instance objects, user logins etc.

SQL Server on GCP VM instance connected through client machine using SSMS

Use cases:

This type of SQL Server deployment is suggested when the database management team is used to conventional ways of DB management, and DBAs have not adopted the cloud ecosystem yet
This is the easiest way where we could get GUI based server management tools (windows VM)

Best practices:

This method is not recommended if you are just creating a temporary server for Dev/Testing environments. Please note that this option comes with relatively high operational overhead
For the development/testing environment, we suggest going with Linux OS, SQL Express/SQL Container image on Docker. This way we can reduce the licensing cost
Make sure you have the entire design including technical details like volume, timeline, transactions etc. planned out before this option is configured

2. Cloud SQL for SQL Server

Cloud SQL for SQL Server is a managed database service that helps you set up, maintain, manage, and administer your SQL Server databases on Google Cloud.

Cloud SQL is a fully managed SQL Server from GCP
Also, CloudSQL is the Google recommended best practice as opposed to deployment of SQL Server in the compute engine because it really portrays the benefits of migrating to cloud and reduces any management of the database
In under 5 minutes, we can create Cloud SQL and migrate existing Database, auto scaling is also available
Add up to 96 processor cores and more than 624 GB of RAM and 64 TB of storage possible

Steps:

GCP console>Navigation Menu>SQL>Create Instance
Choose SQL Server version and other configuration as required

SQL instances can be chosen from predefined Developer/Production instance configuration

Also, SQL instances compute size and memory are customisable

After creating the instance we can connect to the SQL Server through generated public IP

Note:

By default CloudSQL blocks the connections from external IPs, so we have to add client machine or web application IP into the authorised network
Please make sure to add IPv4 and the PUBLIC ip, private IPv4 will throw an error
There are some exceptions to authorising public networks, please refer to this product documentation for the same: https://cloud.google.com/sql/docs/mysql/authorize-networks#limitations
We can authenticate users by using built-in database authentication

Once the configuration is complete, we can connect to CloudSQL Server just like our on premise SQL Servers
Connections can be authorised by

Cloud SQL Auth proxy and Cloud SQL connector libraries for Java and Python
Self-managed SSL/TLS certificates
Authorised networks

SQL connection can be made using sqlcmd, SSMS, Azure data studio, OLEDB etc.
The Access Control can be done for limiting access to database on 2 levels — Instance Level and Database Level

Instance Level is access authorisation for any client from which you are connecting to Cloud SQL
Database Level is access to data in your instance using Server-Level-Roles

Data residency, localisation and privacy refer to the requirements on your data about the physical location of data and local regulations. Cloud SQL helps you meet the challenges of data residency in 3 ways:

Storing data: Configuring where your data is stored (using region configuration)
Encrypting data: Controlling where your encryption keys are stored (using Customer-Managed Encryption Keys)
Accessing data: Controlling access to your data (using Access Approval, Transparency and Key Access Justification methods)

Database creation using scripts, DAC package and backup file can be done in Cloud SQL instances
Some basic SQL Server features like database creation/deletion, database backup, user management can be done in the GCP console itself, but not recommended, there are many SQL Server administrator tools to choose from

Best Practices:

Determine the capacity of data load before you configuring and deploying SQL Server
When possible use smaller cloud instances than using large monolithic instance
Watch the number of database tables for impact on instance response time
Use Connection Pooling for efficient resource management
Several small transactions are better than one large transaction

3. Using Docker Container

Microsoft has an official docker image of SQL Server for Linux based container readily available in Docker Hub
We can pull that image and straight away launch the SQL Server instance
We can also backup the existing databases and create a docker image with those backups, so all the containers created would have the same data replicated without any extra sync and migration
Container storage is not persistent so we have to use Container volumes / external storage (persistent disk) for the databases else we have to design automated backup / restore of databases

Using the same external storage for different SQL Server instances can also be done theoretically. Which can make SQL Server more fail proof and can scale horizontally thus minimise the cloud spend
We can also connect to SQL Server outside the container by various methods (SSMS, sqlcmd, VS code and other data analytics tools) with minimal work

Requirements (linux container)

Memory : 2 GB
File System : XFS or EXT4
Disk space : 6 GB
Processor speed : 2 GHz
Processor cores : 2 cores
Processor type : x64-compatible only

By orchestrating the container using GKE and using scalable persistent storage, we could design a fail-resistant, highly available SQL Server on Google cloud

The above image shows two different SQL containers using the same container volume (a persistent storage) but different ports (1436/1437), so we can also load balance the SQL Server using GKE

Drawbacks observed with Docker containers:

Theoretically we could create multiple SQL Servers within minutes with the same configurations in network storage or local storage but SQL database file and SQL Server instance are mapped 1:1 by design, so spanning multiple instances in the same storage doesn’t scale a Database horizontally
We could replicate a containerised SQL Server instance (not real time but snapshot based) along with their data in different machines (even in Google Cloud Compute engine) easily, we have to configure the port settings and firewall rules for each machine in order to access remotely
Compared to CloudSQL, operational overhead is high and operational cost is unpredictable

The Verdict

Of the 3 options PoC’ed in this blog, Cloud SQL proved to be the simplest, least-effort, fastest and reliable approach and with its 99.95% Uptime Percentage SLO, flexible component-wise pricing structure (1. CPU and Memory, 2. Storage and Networking, 3. Licensing), it is highly scalable for capacity up to 64 TB of storage available, with the ability to automatically increase storage size as needed!!

What’s next?

Why don’t you go back to the options above, try for yourself and feel free to reach out / comment if you have thoughts / suggestions?

As a follow up, give these a follow up read for some more fun:

Cloud SQL Libraries and Samples: https://cloud.google.com/sql/docs/sqlserver/admin-api/libraries
Deploying Multi Regional Disaster Recovery with Google Cloud https://cloud.google.com/architecture/deploying-microsoft-sql-server-multi-regional-disaster-recovery
Reach us at:
https://www.linkedin.com/in/abiramisukumaran
https://www.linkedin.com/in/kppnesh