CODEWITHSUNDEEP
sql-server
c00ke
c00ke

Reputation: 2315

Readonly Database Query?

I have read quite a bit about the ReadOnly databases.

Therefore I was wondering if there would be any benefit in having a readonly database, used heavily to read and query data, and a separate write database used for all the CRUD work when the write database doesn't utilize locking.

Upvotes: 0

Views: 3152

Answers (5)

Jin Thakur
Jin Thakur

Reputation: 2773

This is an architectural Question and comes under Resource Planning .

  • You should plan your load http/SQL call/transactions
  • define how many hits you need who is your audience
  • How many concurrent sql db hits
  • Caching
  • SQL Concurrency Model
  • and what is stress level

So the Architecture you are defining is known as Transactional Database and reporting database When to build a separate reporting database?

So I worked for Department of Education and we have 100TB data of 5 billion students teachers parents etc. So we did SQL Clustering and Replication. So our data is replicated every 5 min and from replicated database we run our monthly weekly and tons of reports without impacting our Transaction db. Again if your company cannot afford multiple database you can do it in same db Like startup companies do.Or if audience is not critical just save $$ LOL It is matter of $$$ you have to build your Infrastructure As somebody already mentioned why it’s needed 1. When transaction performance is critical. 2. When it's hard to get a maintenance window on the transactional app. 3. If reporting needs to correlate results not only from this app, but from other application silos. 4. If the reports need to support trending or other types of reporting that are best suited for a star schema/Business Intelligence environment. 5. If the reports are long running. 6. If the transactional app is on an expensive hardware resource (cluster, mainframe, etc.) 7. If you need to do data cleansing/extract-transform-load operations on the transactional data (e.g., state names to canonical state abbreviations).

Upvotes: 0

HardCode
HardCode

Reputation: 6756

If your application is restricting the SQL statements being executed against the database via known stored procedures, reports, etc., don't worry about a read-only copy. After all, a database is intended for simultaneous reading, writing, and updating. You will, after all, test your system and make sure your SELECT queries don't lock tables so other users can't INSERT/UPDATE/DELETE.

However, if you are providing an ah hoc method of accessing the data (i.e. MS Access with linked tables), then you definitely want to have a read-only database. Otherwise, users could write ad hoc queries such as SELECT * FROM TheBiggestTableEveryoneUses, potentially creating deadlocks with other users trying to INSERT/UPDATE/DELETE records in that table.

Upvotes: 0

E.J. Brennan
E.J. Brennan

Reputation: 46839

There would probably be some benefit, especially if it offloads your adhoc queries and reporting needs from the R/W database. The biggest challenge will be to keep them in sync, but if an overnight process is good enough(i.e. intra-day changes don't need to be included in the read-only DB), then generally having two databases works out really well, especially if the databases are on separate physical machines.

Edit: As a real world example, years ago at a client of mine they were having a hard time getting usable adhoc reports out of their production database (SQL Server), running on very powerful machine that costs upwards of $50,000 just for the hardware.

I talked them into setting up a read-only database, with de-normalized r/o data, and setup SQL server running on a desktop machine (cost around $2500 for the hardware) and the $2500 off-the-shelf desktop PC blew away by a magnitude of 10X the performance of massive compaq quad processing server with gobs of ram and gobs of disk trying to serve the OLTP, batch processing and reporting needs of the unit.

Needless to say the users were very happy, and despite initial resistance from the IT folks, in the end in freed up valuable cycles on the "production" machine and the model was emulated elsewhere.

Upvotes: 3

AnthonyWJones
AnthonyWJones

Reputation: 189437

I don't think there is a great deal of benefit to be had from a readonly Relational database.

Perhaps what you are thinking of is an OLAP where data is heavily denomormalised into multidimensional matrices. Have a look at SQL servers Analysis services.

Upvotes: 0

Matthew Farwell
Matthew Farwell

Reputation: 61695

The benefits of a readonly database are clear: there is no table locking, nobody is changing the data while you're trying to read it.

However, you do have a sychronization problem, you need to update the read only database with the changes, say overnight.

You can also have the two databases on separate servers, to further improve the performance.

Upvotes: 0

Related Questions