patternsqlModerate
Benefits of making a sql server database as read-only?
Viewed 0 times
readsqldatabasebenefitsservermakingonly
Problem
I am using a reporting database, which for all practical purposes is a read-only database. It is created data and dynamic reports are generated and viewed on this database.
I was thinking of making this database read-only.
Some of things I want to ask regarding read-only databases are
I was thinking of making this database read-only.
Some of things I want to ask regarding read-only databases are
- Is only the data read-only? Can we still create indexes or views on this database?
- What are the performance benefits of using a read-only database? e.g. SELECT queries using shared locks, etc...
- Is there any disadvantage of using a read-only database?
- Are there any best practices to follow, while using read-only databases?
Solution
- You cannot make schema changes when the database is read only but you could put all your user tables on a new file group and mark that as read only.
- You can expect a modest performance benefit from absence of locking.
- On versions of SQL Server prior to 2012 statistics can't be auto created or updated on read only databases.
- Before making it read only you might as well remove all logical fragmentation and make page density as high as possible. Any non default
FILL_FACTORsettings will not be of benefit in a read only environment. Additionally create/update any statistics anticipated to be of use forSELECTqueries if on version
Context
StackExchange Database Administrators Q#8938, answer score: 17
Revisions (0)
No revisions yet.