Read-only replicas using database mirror, snapshots and synonyms

Database mirroring can be used as a load balancing mechanism to divert all reader applications to a different instance provided that applications are modified to take advantage of the feature.

Database mirror
Database should be in FULL recovery model for mirroring to be configured. Once mirror is configured, transactions happening at the source will be transmitted to destination using synchronous/asynchronus mode.

Database snapshots
Snapshots are read-only copies of a database and can be created on a schedule (once every 15 minutes/30 minutes etc). Snapshots need to be created at the mirror destination SQL instance.

Synonyms
Once snapshots are created, they can be used like any other database using snapshot name but applications will not be able to automatically benefit from this because original database name will be different from snapshot name.

We can create a database (Ex: master_readonly) for exclusively holding synonyms for various objects of all the databases pointing to the respective database snapshots on mirror destination SQL instance. These synonyms can be recreated as and when new snapshot is generated. This will not affect the reader queries running on  mirror destination SQL instance.

A new database schema needs to be created under  master_readonly database for each mirrored database and synonyms for various database objects could be created inside that database schema. Ex: If we have 10 databases that are being mirrored, 10 database schemas with various database names should be created under master_readonly database at destination SQL instance.

Conclusion

Summing up together, a SQL agent job could be scheduled on mirror destination SQL instance to periodically create new database snapshots, recreate synonyms to point to latest snapshots and cleanup older snapshots.

However, applications and underlying SQL code should be modified to take advantage of this setup. Application specific database views, stored procedures and user defined functions should be modified as well to follow the new pattern of master_readonly.database.object name. Following this model will seggregate reader applications from writer applications and provides for load-balancing, reduced contention, scalability.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s