How to get the most from Cloud SQL for SQL Server

At Google Cloud, we believe moving to the cloud shouldn’t have to mean starting over from scratch. That’s why we’re on a mission to give you choices for how you run your enterprise workloads, including migrating and modernizing your Windows workloads. 

In 2019, we launched Cloud SQL for Microsoft SQL Server so you can bring your existing, on-premises SQL Server databases and applications with you to the cloud. Our fully managed relational service, Cloud SQL, is an essential part of how we help enterprises  focus on innovation, not only on infrastructure. 

In this post, we’ll explore some best practices for leveraging Cloud SQL for SQL Server, so you can better understand when and how to utilize our SQL Server managed offering. 

We’ll cover:

• Provisioning Cloud SQL for SQL Server

• Connecting your data to your analytics 

• Ensuring your data is secure

• Understanding high availability

If you’re looking for other database solutions for your data, read more about Google Cloud’s managed database services. 

As you start setting up your deployments, there are several key considerations you should keep in mind: 

1. Provisioning your SQL Server instance

We offer the same standardized machine types for Cloud SQL for SQL Server as PostgreSQL and MySQL, allowing you to take advantage of the full breadth and capability of the resources for instances up to 96 vCPU cores, 624 GB of RAM, and 30 terabyte SSD. 

One unique benefit is that Cloud SQL for SQL Server only runs on SSD—there’s no HDD option. We’ve found that there’s increased resiliency and fewer issues with offering a single option. You can initiate the creation of a machine or modify an existing instance the same way you would any other Cloud SQL instance using the console, gcloud commands, or our API.  

To seed your instance with data, Cloud SQL lets you import native backup (BAK) files so you can see your data offline. If you’d like to bring in data actively with minimal disruption, choose transactional replication to set up Cloud SQL as a subscriber.

Once your Cloud SQL instance is running, you can set additional parameters and settings. For example, we recommend autoscaling your storage instead of pre-provisioning all the storage you need. Cloud SQL allows you to enable an automatic storage increase setting on the disk, so you don’t have to worry about having the correct allocation for project growth in the future. 

You can also use database flags for many SP_configure settings, including adjusting SQL Server parameters, adjusting options, and configuring and tuning an instance. This also includes setting a collation type to define the default sorting rules, case, and accent sensitivity for your databases. 

To get the most from your high availability (HA) configurations, and take full advantage of Cloud SQL’s 99.95% service-level agreement (SLA), select a regional availability and configure maintenance windows based on the best times to make any changes. We do our best to minimize disruptions by scheduling maintenance as quickly and as infrequently as possible, but our main priority is ensuring our service is secure and highly available. 

We get a lot of questions about the best way to utilize automatic backups for disaster recovery or restoring to other instances in other clouds or on-premises. By default, automatic backups run daily at the time you set. These are only storage snapshots of the persistent disk, which have no impact on Cloud SQL performance as it doesn’t leverage the database engine. For more frequent backups, set up manual backups using APIs or gcloud commands. However, you’ll need to manage the retention of those backups yourself, so we suggest leveraging manual backups in conjunction with automatic ones.

2. Understanding high availability configurations

In simple terms, the high availability configuration provides data redundancy. If a zone or instance becomes unavailable, your data will still be available to clients. How does this work? 

A Cloud SQL instance configured for HA (also known as a regional instance), is located in a primary and secondary zone and contains both a primary instance and a standby instance. Unlike SQL Server replication, Cloud SQL uses regional persistent disks (RePDs) to reduce downtime. 

Using synchronous replication to each zone’s persistent disk, all writes to your primary instance are synced to the standby instance. If a primary instance is unresponsive for approximately 60 seconds or a zone fails, the HA configuration switches over to the standby instance under the same IP and keeps your data available to applications. 

Another advantage is that high availability, or regional instances, only incur cost for a single license for the active resource. If you’d like to learn more, read about licensing pricing here. 

3. Keeping security top of mind

At Google Cloud, ensuring security continues to be a top priority. That’s why we offer several cloud-wide platform features and differentiated security capabilities that ensure all of our products and services, including Cloud SQL, are as consistent and secure as possible. 

Google Cloud encrypts all your at-rest data by default. Data in transit is encrypted when data moves outside of Google’s network, but might not always be encrypted by default within. You can use SSL/TLS certificates to keep data secure when connecting to an instance using its public IP, and there are also additional security measures you can apply. You can also use customer-managed encryption keys (CMEK) as part of Cloud Key Management, allowing you to add your own cryptographic keys for data at rest in Cloud SQL. 

You have three connectivity options in Cloud SQL: 

• Private IP—This is the easiest and most secure way to connect and access your Cloud SQL instance in your SQL Server database. You can set this as part of your VPC or peer-to-VPC networks.
  

• Public IP (with Cloud SQL Proxy)—If you’re coming from a different environment or cloud and need to use a public IP, we recommend using Cloud SQL Proxy whenever possible. Cloud SQL Proxy manages your SSL connectivity and settings without requiring you to authorize other networks.
  

• Public IP—If you prefer manual management options, we offer public IP addresses for your Cloud SQL instance. However, we strongly recommend following security best practices to avoid additional risk and exposure to threats.

Our final tip deals with login credentials: Cloud SQL for SQL Server provides a default SQL Server user to help ensure the service’s integrity and security. If you would like to grant additional privileges beyond what is issued by default, you can  use explicit syntax. You can also create more SQL Server users if you prefer to manage data access that way. 

4. Transforming your SQL Server data into valuable insights

One of the most common requests we hear from our SQL Server customers is that they want to use analytics services, such as SQL Server Recording Studio, Analytics Studio, and Integration Services.  

To help customers use their preferred services, we recommend running them separately in Compute Engine and then connecting them to your Cloud SQL instance. Your native tools, such as Query Optimizer or other Microsoft products can also be adopted for use in Cloud SQL by connecting them directly in your instance.

Cloud SQL also lets you bring your data into other services in Google Cloud’s robust analytics ecosystem if you want to modernize your stack. For instance, a standard JDBC connection can join common services like Dataflow or Cloud Data Fusion, letting you create more complex pipelines for data transformation and data analytics purposes.

To learn more about best practices for Cloud SQL for SQL Server check out the documentation here.