Exclude database from Read Replica?

[cbuckley]

Is it possible to mark databases for exclusion in the read replica? We occasionally backup/restore databases to a new name for prod fixes, feature POC, etc. We do not need those databases to be replicated. I attempted to remove them from the availability group, but permissions do not allow that.

Using Sql Server 2019 Enterprise

Thanks for any assistance.

[ms4446 · Accepted solution]

Unfortunately, there isn't a direct method to exclude specific databases from replication in Cloud SQL for SQL Server. This is primarily due to how replication is managed within the service. Here's a closer look at the limitations and some potential strategies you might consider:

Replication Behavior in Cloud SQL:

• Instance-Level Replication: Cloud SQL replicates data at the instance level, meaning all databases within an instance are replicated to its read replicas. This model does not support database-level or more granular exclusions directly.

Potential Workarounds:

Given the constraints of Cloud SQL, here are some strategies that might help manage or mitigate the replication of temporary databases:

1. Use Separate Instances for Temporary Databases:

   • Approach: Host temporary databases that you do not wish to replicate on a separate Cloud SQL instance.

   • Benefits: Completely isolates temporary databases from the replication process.

   • Considerations: Increases management overhead and costs due to additional instances.

2. Review Your Database Management Practices:

   • Approach: Adjust how and when temporary databases are created to minimize their impact on replication. For example, consider whether it's possible to use alternative approaches for testing or staging that do not involve creating multiple temporary databases in your production instance.

   • Benefits: Reduces the replication overhead and the need for manual interventions.

   • Considerations: May require changes to existing workflows or processes.

Important Considerations:

• Service Limitations: The capabilities and limitations of Google Cloud SQL's replication features are subject to change. Always refer to the latest documentation for the most current information.

• Cost and Complexity: Separate instances increase costs and management complexity but provide a clear separation of environments.

• Compliance and Best Practices: Ensure that any chosen strategy aligns with your organization's compliance requirements and data management best practices.

Given the current capabilities of Cloud SQL for SQL Server, using separate instances for databases that should not be replicated is the most straightforward and effective workaround. This approach avoids the complexities and potential issues associated with trying to manipulate replication in unsupported ways.