Azure SQL server database read only copy

MrFlinstone 581

I have got an Azure SQL server database configured with the specific DTU based on my requirement. I would like to know if it is possible to have a read only database copy ? What I do today is create a database point in time snap and use this for reporting, I would like to have a near real time copy of the database. What is the most cost effectively way to achieve this ?

1 answer

Alberto Morillo 34,056 Reputation points MVP

2022-07-03T23:41:14.743+00:00

Business critical service tier provides one free-of-charge readable replica of the database that can be used to run read-only queries such as reports and analytics. Learn more about Business Critical tier here. Azure SQL DTU premium tiers have also the same feature of free readable replicas. You can enable this feature using the portal:

You can make changes to reports on the connection string with "ApplicationIntent=ReadOnly" parameter to make them connect to the read-only replicas.

If you are not able to pay for premiun tiers to get free access to a read replica, you can create a replica using Azure SQL Data Sync but you will have to pay for the replica database.
Please sign in to rate this answer.
MrFlinstone 581 Reputation points

2022-07-05T22:44:16.707+00:00

@Alberto Morillo Thanks for the proposed answer. I checked it out and you are right with the DTU premium tier, there are free readable replicas. However the DTU triples and so does the cost per month. Please advice on the cheapest way to achieve this. The database is quite and sized on a Standard S2 with 50 DTU's allocated. Is the the best configuration from a cost perspective ?

Alberto Morillo 34,056 Reputation points MVP

2022-07-06T02:34:34.62+00:00

Go with the last option I gave you, SQL Data Sync. Before setting up SQL Data Sync try to upgrade to S3 or S4, you will have a hub database and a member database. You will pay more than double the current price. Configure SQL Data SYnc with one-way sync from hub database to member database. Use the member database for reports and read-nly workloads.

MrFlinstone 581 Reputation points

2022-08-17T20:41:48.7+00:00

@Alberto Morillo
Hello.

I managed to get the scale out read only option working, however as documented the ApplicationIntent =ReadOnly needs to be specified, this is good for a reporting tool etc, however for users who connect via SSMS one can be sure that they will always forget to specify the read intent. I want a way whereby access to the database can be denied, and users can only use the read only replica. With the read scale out on premium tier, this cannot be done as access is still via the database server, the main change is the ApplicationIntent =ReadOnly flag.

The only other option i see is using data synch. The immediate issues I see with data synch are as follows.

All tables require a primary key.

What happens is the schema of the table on the primary database is altered, adding indexes, adding/editing constraints etc, will this break data synch ?

If a new table is added to the database, it needs to be added to the synch group.

Are there any other read only database options where a seperate database connection can be provided ?

Alberto Morillo 34,056 Reputation points MVP

2022-08-17T23:30:48.36+00:00

Does creating a job that kill users connected to the primary do the job? That job should only leave connected logins used by the appliation.

About SQL Data Sync, yes. Changes to the shema will break the data sync. More tables that need to be in sync should be part of a syn group, you can have more than one.

MrFlinstone 581 Reputation points

2022-08-18T22:14:19.367+00:00

This wouldn't work. The idea of killing off users.
Are there any other options ?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure SQL server database read only copy

1 answer

Your answer