February 2, 2011 at 2:10 pm
Hi
We have transaction replication from Primary to couple read-only servers. These read-only servers are used mainly for reporting. So, couple of questions:
1. I have read somewhere that if a DB is mostly read-only, set it to be in that mode, so that the locks dont happen. Does this increase the performance?
2. since transactional replication is happening to the subscriber servers (which are read-only), can i set the DB to read-only mode? If I did that, how will transactional replication update the DB because it is set to read-only mode?
Thanks.
February 2, 2011 at 8:58 pm
I can't answer your first question - never measured performance of writeable Vs read only databases.
On you second question, you cannot replicate to a read only database. If you think about it - how can you make changes to a read only database. Replication simply issues insert/update/ delete statements to update a subscriber in pretty much the same way that you would. If the database is read only, these statements will fail.
February 2, 2011 at 9:05 pm
aMSDeveloper (2/2/2011)
HiWe have transaction replication from Primary to couple read-only servers. These read-only servers are used mainly for reporting. So, couple of questions:
1. I have read somewhere that if a DB is mostly read-only, set it to be in that mode, so that the locks dont happen. Does this increase the performance?
2. since transactional replication is happening to the subscriber servers (which are read-only), can i set the DB to read-only mode? If I did that, how will transactional replication update the DB because it is set to read-only mode?
Thanks.
1. what do you mean by locks? locks on a table? even if your db is readonly and you have queries running against it, the table lock can still occur. to prevent locks, use the nolock hint in your select statement
2. you can not set the subscriber's db to read-only.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply