Update: Google Stackdriver is now Google Cloud Logging and Google Cloud Monitoring. BindPlane will continue to integrate and support both of these products.
For some time now, Google Cloud Platform (GCP) has been supporting enterprises that have Windows workloads. In 2017, GCP announced their official support for SQL Server using virtual machine (VM) images. There are certain aspects—like sustained use discounts or custom VMs—in GCP that make it suitable to run your SQL Server workloads.
Even though SQL Server is not yet supported as a managed service, GCP is still good for hosting SQL Server workloads. Windows images in GCP support SQL Server Always On Availability Groups as well as integration with Volume Shadow Copy Service (VSS) for disk snapshots. Google’s documentation is a good source of information about how to make your move to GCP more efficient.
In this post, you’ll learn more about why GCP is a good option for SQL Server. I’ll also share recommendations and further reads for topics like performance, cost, security, reliability, and storage. So let’s dive in to learn more about what you’ll need to consider when running SQL Server on GCP.
Because you use VMs when running SQL Server on GCP, let me start by talking about Google’s VM features. First, in GCP you have sustained use discounts without having to configure anything. When an instance runs for more than seven or eight days per month, you’ll see a discount price on your bill at the end of the month. You also extend security when using GCP’s identity access management (IAM), firewall rules, or private connections through VPN tunnels or direct peering.
In GCP, you have predefined machine types with a fixed combination of CPU and memory. For example, the type n1-standard-4 has four vCPUs and 15GB of memory. But maybe these types of resources don’t fit your needs. Perhaps you need more memory or more CPU. If that’s the case, you’ll need to use custom machine types. Yes, you can customize a VM with a configuration that fits your needs. If you do this, GCP will charge by the amount of memory and CPU you choose.
When you create a VM, you have to choose an image. To do this, you’d simply type “SQL” in the search box, and the complete list of SQL Server images will appear. GCP currently officially supports the following versions of SQL Server:
If you happen to have the software assurance program from Microsoft, then you can bring your own license (BYOL) to Google and save money. If your strategy to migrate to GCP is “shift and lift,” Google has a self-service migration service. You could also work with some partners to help you on this journey.
Google has useful resources on how to improve the performance of SQL Server in VMs. At the Next ’17 conference, Google’s Feng Min spent some time talking about the improvements Google has made on VMs for Windows workloads. Min talked specifically about Hyper-V enlightenments that allow VMs to perform better. Min also mentioned adding support for other Windows drivers that will improve the networking and disk throughput.
Even though Google has made Windows VMs perform better, there are specific configurations that you need to consider for SQL Server. For example, Google recommends having two disks: an ephemeral local disk and a persistent disk. The ephemeral is to configure the Windows Pagefile and the TempDB—this allows you to offload the read/write operations. You’ll use the ephemeral disk for staging data and backups. The second disk is a persistent one, which is for the database and log files. By default, the more CPU and space a persistent disk has, the better the performance is.
Google has pretty good documentation on how the SQL Server tuning is when running in a cloud environment like GCP. You might still need to apply other configurations based on your needs, but I think it’s great that Google is working on improving the performance of Windows workloads in the cloud.
If you want to do a deep dive into the topic of performance, you can read Google’s paper about how to tune the VM based on the needs of your workloads. In the future, maybe GCP ends up including SQL Server as a managed service. In the meantime, you have Google’s recommendations and support to run your SQL Server workloads in GCP. You should also be sure you have a solid strategy for analyzing and monitoring performance. Google Stackdriver is a logical choice, that now supports SQL Server workload monitoring using Blue Medora Bindplane.
Storage and Backup
You could continue using SQL Server Management Studio for backup, your PowerShell scripts, or another tool you’ve used previously. But as long as you’re using GCP, why not use their native services for backup? You could save backups on Google Cloud Storage. Or, thanks to the VSS integration, you could take incremental snapshots for the disks where you’re storing data.
In a cloud environment like GCP, you could synchronize all the transaction log files to a bucket in cloud storage. Then, you could spin up a new SQL Server instance (only when you needed to) and restore the data based on the transaction log that has been synchronized. This approach will keep your costs low because you’ll just be taking differential backups and recovering only when you need to instead of having to replicate the data to another instance in passive mode actively. I recommend you read this complete guide from Google on how to implement this approach and you apply the techniques they discuss.
As a general good practice for backups, make sure you’re not only taking backups but that you’re also ensuring that the recovery process works. Do it regularly—you don’t know when you’re going to need to restore data. It would be unfortunate to find out, just when you most need it, that backups stopped working at some point or that data has been corrupted. To avoid this problem, practice the backup and restore process regularly.
Reliability and Availability
By using VMs on Google, you have an uptime of 99.99 percent, which is a good number to have as a base. Then, it’s about how you plan before anything happens inside the VM or when a traffic spike comes. Depending on your needs, you might need to have another instance in another region.
SQL Server has a feature called Always On, which is an alternative to database mirroring for high-availability and disaster-recovery solutions. Always On is supported in GCP, but you need to enable it before starting to use it. There’s also a one-click option in Google’s marketplace so that you don’t have to configure Always On. Then, there’s a beta configuration to have failover cluster instances in GCP.
GCP supports highly-available architectures for SQL Server, but you might need to add a few additional steps when provisioning the cluster.
Finally, let’s talk about how much it will cost to run your SQL Server workloads on GCP. When you sign up for GCP, you have a free trial credit of $300. You might not be able to have an instance running during the full month with this budget, but you’ll definitely be able to try out for yourself how the experience and the performance in GCP is.
And, as I mentioned before, GCP supports BYOL in case you’re already paying a license for SQL Server. That means you’ll get a reduced cost in GCP. I know I also talked about the sustained use discount in a previous section, but I just wanted to remind you about it. If you run an instance for the full month, you’ll get a discount.
Here’s a list of how much it costs to run each version of SQL Server on GCP:
In addition to that, you’ll need to sum the pricing for storage, memory, and any other resources you’ll deploy in GCP. But to make things easier, Google has a pricing calculator that you can use to estimate the costs of running a SQL Server on GCP.
GCP Is a Good Option for SQL Server
I don’t know about you, but I’m seeing that GCP’s adoption is increasing more and more every day. Some companies are thinking about migrating to GCP, either from their on-premises infrastructure or from another cloud vendor. For some companies, multi-cloud workloads is a must. It’s good to know (and see) that GCP is adding more features like SQL Server for the enterprise. I like that GCP has excellent resources to learn more about their services. It looks like they’re willing to help people improve performance—not just with SQL Server, but for other workloads too.
Now that you’re aware of GCP support for SQL Server, go and give it a try. You can start with the free trial credit. Whether you’re thinking about migrating to GCP or considering it for your multi-cloud strategy, GCP is a good option for SQL Server databases.
This post was written by Christian Meléndez. Christian is a technologist that started as a software developer and has more recently become a cloud architect focused on implementing continuous delivery pipelines with applications in several flavors, including .NET, Node.js, and Java, often using Docker containers.