Cloud SQL & Cloud Spanner for Relational Databases

Introduction to Managed Database Services in GCP

Google Cloud offers two powerful solutions for relational databases:

• Cloud SQL: Fully managed MySQL, PostgreSQL, and SQL Server
• Cloud Spanner: Horizontally scalable relational database with global consistency

Cloud SQL: The Easy Path to Managed Databases

Cloud SQL handles routine database administration while providing:

Key Features

• Automated backups and point-in-time recovery
• Seamless vertical scaling
• High availability configurations
• Read replicas for performance

Creating a Cloud SQL Instance

Using the Google Cloud Console or gcloud CLI:

gcloud sql instances create my-mysql-db \
    --database-version=MYSQL_8_0 \
    --tier=db-n1-standard-2 \
    --region=us-central1 \
    --root-password=mysecretpassword

Connecting from Java Applications

Use standard JDBC to connect to Cloud SQL:

// MySQL connection example
String jdbcUrl = "jdbc:mysql:///my-db?cloudSqlInstance=my-project:us-central1:my-mysql-db&socketFactory=com.google.cloud.sql.mysql.SocketFactory";
String username = "root";
String password = "mysecretpassword";

try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("CREATE TABLE products (id INT, name VARCHAR(255))");
    System.out.println("Table created successfully");
} catch (SQLException e) {
    e.printStackTrace();
}

Don't forget to add the Cloud SQL JDBC Socket Factory dependency:

<!-- Maven dependency -->
<dependency>
  <groupId>com.google.cloud.sql</groupId>
  <artifactId>mysql-socket-factory-connector-j-8</artifactId>
  <version>1.4.4</version>
</dependency>

Backup Strategies

Cloud SQL provides automated and on-demand backups:

// Create an on-demand backup using Admin API
import com.google.cloud.sql.admin.v1.CloudSQLClient;
import com.google.cloud.sql.admin.v1.BackupRun;

CloudSQLClient client = CloudSQLClient.create();
BackupRun backup = client.backupRuns()
    .insert("my-project", "my-mysql-db")
    .execute();
System.out.println("Backup started: " + backup.getId());

Best practices:

• Enable automated backups (retained for 7+ days)
• Test restore procedures regularly
• Consider exporting to Cloud Storage for long-term retention

Read Replicas for Scaling

Improve read performance with replicas:

# Create a read replica
gcloud sql instances create my-replica \
    --master-instance-name=my-mysql-db \
    --region=us-west1

Java applications can distribute read traffic:

// Load balancing reads between primary and replicas
String readOnlyUrl = "jdbc:mysql:///my-replica?cloudSqlInstance=my-project:us-west1:my-replica&socketFactory=com.google.cloud.sql.mysql.SocketFactory";

// Use for read-only operations
try (Connection readConn = DriverManager.getConnection(readOnlyUrl, username, password)) {
    // Execute queries
}

Cloud Spanner: Global Scale Relational Database

When you need horizontal scaling with strong consistency:

// Cloud Spanner Java example
import com.google.cloud.spanner.*;

DatabaseClient client = SpannerOptions.getDefaultInstance()
    .getService()
    .getDatabaseClient(DatabaseId.of("my-project", "my-instance", "my-db"));

client.readWriteTransaction().run(transaction -> {
    String sql = "INSERT INTO Users (UserId, Name, Email) VALUES (?, ?, ?)";
    transaction.executeUpdate(Statement.newBuilder(sql)
        .bind("p1").to("user1")
        .bind("p2").to("John Doe")
        .bind("p3").to("john@example.com")
        .build());
    return null;
});

Key advantages:

• Global distribution with strong consistency
• Automatic sharding
• 99.999% availability SLA

Choosing the Right Service

	Cloud SQL	Cloud Spanner
Best for	Traditional RDBMS workloads	Globally distributed apps
Scale	Vertical	Horizontal
Pricing	Lower cost	Premium for global scale

For most applications starting out, Cloud SQL provides the best balance of familiarity and managed convenience. As your scale grows globally, Cloud Spanner offers unparalleled scalability while maintaining relational semantics.