Enterprise database workloads are the backbone of many of your applications and ecosystems. Also, guaranteed availability is critical when choosing a cloud provider.

Many enterprises built their mission-critical applications on Microsoft SQL Server 2008, and it’s common still to run into older versions of SQL Server as you’re working toward modernizing your on-prem environments.

According to Business insider, 60% of Microsoft users still use SQL Server 2008, which reached its end of life in July 2019. This provides the opportunity for many of you to find a place to host your SQL Server 2008 instances on newer technology with less operational burden. 

We’re announcing that Cloud SQL for SQL Server is generally available globally. This means that Cloud SQL now helps you keep your SQL Server workloads running by providing a 99.95% uptime service-level agreement (SLA), which is consistent with the other Cloud SQL database engines.

Cloud SQL for SQL Server is fully managed and compatible with SQL Server 2017. Now you can migrate your critical production SQL Server workloads to Google Cloud and rely on the service’s stability and reliability. 

We hear from enterprise companies how important the ability to migrate to Cloud SQL for SQL Server is to their larger goals of infrastructure modernization and a multi-cloud strategy. On-premises applications like HR, finance, and payroll often depend on these legacy databases to keep running.

Customers often cite the challenge of wanting to maintain compatibility with these existing systems and datasets, while also streamlining deployments and scale-out at a fraction of the overhead. Migrating these instances to Cloud SQL for SQL Server can save costs and maintenance time and improve efficiency and speed. 

Getting started migrating SQL Server 2008

The migration for Microsoft SQL Server 2008 to Cloud SQL for SQL Server can be achieved in a simple five steps. For details, check out the full migration guide: SQL Server 2008 R2 server to Cloud SQL for SQL Server. 

1. Create a Cloud SQL for SQL Server instance

gcloud beta sql instances create target  \
    --database-version=SQLSERVER_2017_ENTERPRISE \
    --cpu=2 \
    --memory=5GB \
    --root-password=sqlserver12@ \
    --zone=us-central1-f

2. Create a Cloud Storage bucket

  gsutil mb -b off -l US "gs://bucket-name"

3. Back up your Microsoft SQL Server 2008 database

osql -E -Q “BACKUP DATABASE db-name TO DISK=’c:\backup\db-name.bak'”

4. Import the database into Cloud SQL for SQL Server

gcloud beta sql import bak target \
    gs://bucket-namedb-name.bak \
    --database db-name

5. Validate the imported data

/opt/mssql-tools/bin/sqlcmd -U sqlserver -S 127.0.0.1 -Q “query-string”

If you’re working with newer versions of SQL Server, check out the SQL Server 2017 to Cloud SQL for SQL Server migration guide.

Since the launch of Cloud SQL for SQL Server, we’ve heard your feedback and have continued to improve the performance and durability of the service. We expect to continue our rapid pace of innovation and feature releases to meet our customers’ needs and address feedback. Cloud SQL for SQL Server has proven itself as a key component when migrating existing enterprise applications and infrastructure.

We’re continuing to rapidly improve Cloud SQL for SQL Server to meet all of your cloud database needs. Stay tuned for features in development that can help with Active Directory integration, online migrations, and more options for replicas and machine types. 

We are thrilled that Gartner has positioned Google as a Leader for the second year in a row in the 2021 Gartner® Magic Quadrant™ for Cloud Database Management Systems (DBMS).

We believe the report evaluated Google’s unified capabilities across both transactional and analytical use cases, and showcases innovation progress in areas like data management consistency, high speed processing and ingestion, security, elasticity, advanced analytics, and more. 

With the recent announcement of Dataplex, organizations can centrally manage, monitor, and govern their data across data lakes, data warehouses, and data marts with consistent controls.  Solutions like BigQuery ML provide a “built-in” approach for advanced analytics capabilities and Analytics Hub offer the infrastructure customers need to share data analytics solutions securely and at scale in ways never before achieved. For example, over a seven-day period in April, more than 3,000 different organizations shared over 200 petabytes of data using BigQuery. 

Research shows 90% of organizations have a multicloud strategy, which is why we have invested in a cross-cloud data analytics solution for Google Cloud, AWS, and Azure with BigQuery Omni. Additionally, our progress with Anthos and our Distributed Cloud this past year further advance our ability to support multi and hybrid cloud scenarios. To gain a competitive advantage using data, organizations need a data platform that transcends transactional and analytical workloads and can be run with the highest level of reliability, availability and security. Cloud Spanner, our globally distributed relational database has redefined the scale, global consistency, and availability of Online Transaction Processing (OLTP) systems. Spanner processes over 1 billion requests per second at peak, and has been battle-tested with some of the most-demanding applications, including Google services such as Search, YouTube, Gmail, Maps, and Payments. And what’s unique about our core services Spanner and BigQuery, is that they leverage common infrastructure such as our highly durable distributed file system (Colossus), our large-scale cluster management system (Borg), and Jupiter, our high-performance networking infrastructure, enabling features such as federation between Spanner and BigQuery.

We remain focused on the integration of Google Trends, Maps, Search, Ads, and have increased industry domain expertise in areas such as retail, financial services, healthcare, and gaming. We’re continuing to develop industry white papers such as this one – How to develop Global Multiplayer Games using Cloud Spanner – and we’re proud of the work the team has done to create and share industry and horizontal architecture patterns built from industry leaders to serve as solution accelerators for customer use cases. 

Innovation momentum continues with a unified and open data cloud

We continue to innovate across our data cloud portfolio, especially with the innovations we announced at Google Cloud NEXT’21. BigQuery Omni is now available for AWS and Azure, supporting cross-cloud analytics for customers. We’ve added additional capabilities for enterprise data management and governance with Dataplex, which recently went GA. We’ve made migrations to Cloud SQL easier and faster with the Database Migration Service. More than 85% of all migrations are underway in under an hour, with the majority of customers migrating their databases from other clouds. We are embracing openness with Spanner by adding a PostgreSQL interface, allowing enterprises to take advantage of Spanner’s unmatched global scale, 99.999% availability, and strong consistency using skills and tools from the popular PostgreSQL ecosystem. We are also automating data processing with Spark on Google Cloud, which enables developers to spend less time on infrastructure management and more time on data science, modeling, and delivering business value. Finally, we announced Google Earth Engine on Google Cloud, allowing customers to integrate Earth Engine with BigQuery, Google Cloud’s ML technologies, and the Google Maps Platform. 

With these innovations, enterprises like PayPal, Deutsche Bank, and Equifax use Google Cloud to solve for their end-to-end data lifecycle use cases. Organizations like Telefonica use Google Cloud to deliver new customer experiences. Telefónica has transformed every aspect of how they store, share, and analyze data while doubling processing power and lowering costs.

We continue to support an open ecosystem of data partners including Informatica, Tableau, MongoDB, Neo4j, C3.ai, and Databricks, giving customers the flexibility of choice to build their data clouds without being locked into a single approach. 

We are honored to be a Leader in the 2021 Gartner Magic Quadrant for Cloud Database Management Systems (DBMS), and look forward to continuing to innovate and partner with you on your digital transformation journey. 

Download the complimentary 2021 Gartner Magic Quadrant for Cloud Database Management Systems report.

Learn more about how organizations are building their data clouds with Google Cloud solutions. 

^{Gartner, Magic Quadrant for Cloud Database Management Systems, Henry Cook, Merv Adrian, Rick Greenwald, Adam Ronthal, Philip Russom, 14 December 2021.}

^{Gartner and Magic Quadrant are registered trademarks of Gartner, Inc. and/or its affiliates in the U.S. and internationally and are used herein with permission. All rights reserved.}

^{Gartner does not endorse any vendor, product or service depicted in its research publications and does not advise technology users to select only those vendors with the highest ratings or other designation. Gartner research publications consist of the opinions of Gartner’s Research & Advisory organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose.}

^{This graphic was published by Gartner, Inc. as part of a larger research document and should be evaluated in the context of the entire document. The Gartner document is available upon request from Google Cloud.}

Editor’s note: Streak makes a CRM add-on for Gmail, and recently adopted Cloud Spanner to take advantage of its scalability and SQL capabilities to implement a graph data model. Read on to learn about their decision, what they love about the system, and the ways in which it still needs work.]  

Streak is a customer relationship management (CRM) tool built directly into Gmail. It is used for sales, marketing, hiring, and just about anything else you can think of.

We built it because out of the box, email is actually a really crummy team sharing system. By adding a layer of organization on top of email, Streak lets you add email threads directly into its spreadsheet view, making it useful as a workflow tool with capabilities including task creation, email template management, and easy data entry.

Streak has been integrated with G Suite (originally Google Apps For Your Domain) since its inception so when choosing a cloud, it made sense to colocate our server stack with Google Cloud.

Likewise, Streak was on Google App Engine from the start, and we slowly added other GCP services as the offerings improved or as our use cases became more complex.

In addition to App Engine, we use Google Kubernetes Engine to run a bunch of our compute workload, including both application servers and our offline processes like indexers and task queue consumers.

We use Cloud Dataflow for both streaming event processing and logs ETL, and BigQuery for all of our analytics queries. We use Cloud Pub/Sub for interacting with the Gmail watch API, as well as Stackdriver (logging, tracing, monitoring, errors) and OpenCensus to dig into any operational issues as they arise.

Then, on the database front, we recently started using Cloud Spanner, Google Cloud’s scalable relational database service. Before that, we stored most of our business data in Cloud Datastore, Google Cloud’s NoSQL document database.

Partially, that was historical, since Cloud Datastore was GCP’s only managed database when we wrote the Streak backend. And we’ve been very happy with how easy Cloud Datastore is to maintain. Between Google App Engine and Cloud Datastore, we’ve never had to have an explicit infrastructure on-call rotation.

But as more users rely on Streak to collaborate with larger and larger teams, we were feeling the pain of not having a fully relational database. We found ourselves having to manually join data in our application, which increased application latency and increased the time developers spent coding workarounds and debugging that complexity.

We found we needed two things out of our database: a scalable relational store and a graph store that could power next-gen Streak features. At the same time, we wanted a single database that could handle both use cases and wouldn’t increase our operational burden. This meant finding a managed service to give us more query flexibility, so we decided to give Cloud Spanner a try.

Of course, we didn’t want to migrate our existing stack to a new data platform without first testing it out (never a smart strategy). But since most of our existing data model required transactional updates with other entities, pulling out a single entity to test was challenging. We did have a feature in our pipeline that necessitated a graph data store and that was removed from our other data: our email metadata indexing system.

How your client software handles email metadata indexing can make or break the useability of a system.  Think about how many times somebody forgets to reply-all or that you receive a forwarded thread with thirty emails in reverse-chronological order. Within our own inboxes, we rely on Gmail’s UI to nicely organize email threads, but that organization breaks down when working with a team or across organizational boundaries.

We decided to fix that in the Streak product by organizing metadata (i.e., headers but not message content) from users’ email by using Cloud Spanner as a graph database. Using a graph database lets us answer questions like “What are all the emails on this thread in the inboxes of everybody on my team?” and “Who on my team has previously talked with the organization that this prospect works at?”

In our model, the nodes of the graph are either an email message, a person (email address) or a company (a domain). Then we have four different types of “edges”— properties by which nodes in a graph connect to one another:

1. Message to message (thread): messages that are on the same thread have an edge between them. The reason we do this is because we want to show users a list of threads to answer their questions, not messages, so we need to be able to get the spanning set of messages.
2. Message to message (same RFC id): A core value proposition of Streak is being able to see the “unified” version of a thread that shows each person on a team’s version of the email thread. To make sure we are getting each user’s version of a thread when we issue a query, there needs to be an edge between a message in the queryer’s inbox and the same message in their team’s inbox. In case you’re curious, Streak uses the RFC message id to determine that two messages across inboxes are actually the same.
3. Email address to message: a message has an edge to an email address if it was either the from, to, cc, or bcc on the message. This edge is crucial for queries that start with: “Show me all threads between this person and our team.”

Domain to message: a message has an edge to a domain if the domain is present in any of the from, to, cc, or bcc addresses on the message. This edge is similarly used for queries that start with “Show me all threads between this company and our team.”

Using Cloud Spanner’s distributed SQL capabilities and scalability to build a graph database also let us answer the important follow-up question: “Which threads have I been granted permission to view?” And while a lot of these questions could be answered per-user by a traditional relational database, scale limitations have to be taken into consideration, especially as we plan for 10x or more data volume growth as both our user base grows and as their inboxes accumulate more emails. A graph database model is simply a better fit for Streak’s collaboration model with many-to-many mappings between users and teams, and will allow us to query the data in any number of configurations, without worrying about scale limitations or having to manually shard a relational database. Cloud Spanner gives us queryability and scalability.

Taking the Cloud Spanner plunge

With so many advantages to it, we went ahead and began building out our metadata system with Cloud Spanner as a back-end.

Adopting Cloud Spanner has been great. Here are some of the high points:

1. The fast distributed queries and transactions are absolutely real. We have global indexes across our entire dataset and we haven’t had to spend very much time at all thinking about co-locating data. In particular, we only use interleaved tables for values that would be repeated fields in Cloud Datastore, and that hasn’t been a problem for us yet.
2. We haven’t had any reliability problems whatsoever, despite averaging 20K writes/sec in steady state.
3. Once we optimized our queries on realistic data, Cloud Spanner scaled up in a surprisingly predictable way. You need to run queries after you’ve populated data, do the explain to figure out how the query planner is executing the query, and add indexes/modify queries to make sure they’re performant.
4. Compared to the hoops some traditional relational databases make you jump through, Cloud Spanner’s online schema changes and index builds are magical. There is no downtime for these operations.

Overall, the experience has been encouraging, and we’re planning to move 20 TB of existing data in Cloud Datastore to Cloud Spanner as well. We built out an ORM library for Java on top of Cloud Spanner called Ratchet and are testing a framework for dual-writing entities to both Cloud Datastore and Cloud Spanner to support the rest of the migration. We now store about 40 TB of email metadata in Cloud Spanner, which makes us a large user of Cloud Spanner.

In short, if you’re starting to outgrow your NoSQL database, and want to move to a managed SQL database, give Cloud Spanner a try. You definitely want to model out your costs and try out a proof of concept, both to see how it works on your workload and to get familiar with the quirks of the system. But you don’t need to spend much time worrying about the reliability of the product: it’s there.

At Precognitive, we were able to start with a blank technology slate to support our fraud detection software products. When we started building the initial version of our platform in 2017, we had some decisions to make: What coding language to use? What cloud infrastructure provider to choose? What database to use? The majority of the decisions were straightforward, but we struggled to decide upon a database. We had plenty of collective experience with relational databases, but not with a wide-column database like Cloud Bigtable—which we knew we’d need to scale our behavior and device workloads. At launch, our products were supported by a self-managed database, but we quickly migrated to Cloud Bigtable, and we love it.  

To efficiently support our bursty, real-time fraud detection workloads, we needed a cloud database that could satisfy the following key requirements:

• Stability to keep up with increased adoption of our products
• Intelligent scaling that avoids bottlenecks
• Native integrations with BigQuery and Cloud Dataproc
• Managed services that free up our engineers’ time to work on our products

Adding Cloud Bigtable as our performance database

As we scaled our services and added customers, our data collection services for our Device Intelligence and Behavioral Analytics products were seeing thousands of events per second. Cloud Bigtable provided a stable managed database that could handle the volume we were receiving during peak hours. We weren’t always able to handle this scale, as an early version of our product utilized a self-managed database.

Every month, two or three engineers spent hours managing the database instances. Whenever the instances crashed, it would cost at least one engineer a day or two of productivity attempting to restore the instances and recovering any data from our backup database. Managing this database internally was taking precious time away from product development.

We circled back to Cloud Bigtable. After two weeks of R&D, we decided to switch the Device Intelligence and Behavioral Analytics services to Cloud Bigtable.

Cloud Bigtable solved our scaling issues. Cloud Bigtable had been attractive to us from the start because it was fully managed, and offered regional replication and other features we were lacking in our own managed instances. Cloud Bigtable provides horizontal scaling and automatically rebalances row keys (equivalent to a shard key) over time to prevent “hot” nodes. In addition, Cloud Bigtable provides a connector to BigQuery and Cloud Dataproc that allows us to analyze the terabytes of data we are processing and use that data for unsupervised machine learning.

The perks of using Cloud Bigtable

After the migration to Cloud Bigtable, we noticed a number of additional benefits: improved I/O performance, a significant cost reduction, and a sizable decrease in hours spent on database maintenance.

We measured some of our typical metrics before and after implementing Cloud Bigtable. Our request latency dropped by about 30 ms on average (to sub-10 ms) for API requests. Prior to the change, we were seeing latencies of 40+ ms on average. This latency drop on our Behavioral Analytics and Device Intelligence products allowed us to trim about an additional 10 to 15 ms off our average response time across all dependent services.

Before we moved to Cloud Bigtable, we had to scale our database instances every time a new customer was onboarded. We were over-scaling in an attempt to avoid constantly resizing our database servers. By sunsetting our self-managed database and switching to Cloud Bigtable, we cut database infrastructure costs by approximately 35% and can now scale as needed, with a couple of clicks, during onboarding.

We have spent zero hours managing a Cloud Bigtable database since launch, and we put the time we are saving every month toward product development.

Moving forward with Cloud Bigtable

As an engineering team, we love working with Cloud Bigtable. We are not only seeing improved developer experience and reduced latency, which keeps the engineers happy, but also reduced costs, which keeps the business happy. We’re able to build more product, too, with the time we’ve saved by switching to Cloud Bigtable. Stay tuned to our engineering blog for more on the lessons we’ve learned and our contributions to the wider Cloud Bigtable community.