locked
DB Design help with Replication involved: MVPs please help RRS feed

  • Question

  • I have to re-design a system which currently uses transactional replication in SQL Server 2005. The source database would be used by tools such as informatica to load data and then  transaction replication would be used to replicate the data every 15 mins to the target database which is in another instance. The target database would be used by users to generate report.

    Now the problem happens when users try generating report using target database while replication is running.The reason is simple. Replication needs exclsuive lock to insert into target database where as the users generating report request for shared lock through their select queries.Since these locks are not compatable with each other the process gets blocked and replication is delayed.

    One of the possible solution what I am guessing is to use READ_COMMITTED_SNAPSHOT for all the select queries fired by users while generating report.Please let me know if this approach looks good.I went through the msdn article

    http://msdn.microsoft.com/en-us/library/ms188277(v=SQL.90).aspx

    and it looks good and defines the positive and negative of snapshot isolation level.

    Please let me know if this will help to solve the problem.Are there any other cost such as contention in GAM or SGAM in tempdb due to snapshot isolation level.

    Many thanks in advance.

    Regards,

    Zainu

     

    Monday, February 14, 2011 7:27 PM

Answers

  • Hi Zainu,

    If you have heavily workloads running on the publication database, the transactions within 15 minutes would be a little bit over. Therefore, when transactions replated to subscription database in each 15 minutes interval, it would be cost more contention while generating reports. To work around this issue, you may configure Log Reader Agent as well as Distribution Agent to run continuously.

    Also, you could choose "Row Versioning-based Isolation Levels" to reduce overheads on subscription database. In this case, the tempdb will be more used such as space, please make sure your tempdb well configured. You could test these configurations and decide in which way you could get more benefits.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, February 21, 2011 8:34 AM