Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transactional Replication on same server different database Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 9:54 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:28 AM
Points: 655, Visits: 3,917
Hi folks,

I imagine transactional replication is most often implemented between servers but I am trying to ascertain what benefits I can expect(if any) if I replicate to a different database on the same server.

The source database would be a medium busy, transactional, vendor provided database. Currently I have custom read-only code that is running against this database, basically reporting. The plan would be to redirect my code to the replicated database.

The issues I hope to improve are performance and some occasional deadlocking.

Can select statements contribute to deadlocking or is this only caused by inserts/updates?

Can I expect and performance improvements?

Thanks if you would like to share your thoughts.


Post #1451663
Posted Friday, May 10, 2013 1:18 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 2,832, Visits: 8,518
Select statements can absolutely affect performance.

Can you expect improvements ? That's hard to say without know the cause of the problems. If the problems are built into the application, or coming from queries and reports that need to use production, then they will still cause problems.



Post #1451725
Posted Friday, May 10, 2013 1:34 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:28 AM
Points: 655, Visits: 3,917
Ok let me refine my question. If I redirect select statements to the replicated database would the performance of the primary possibly be be improved. Sure at the server level CPU, memory and I/O are still being used.

I don't know much about the internals. If I issue two select statements against the same table does the second one have to wait for the first?

Post #1451727
Posted Friday, May 10, 2013 1:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 2,832, Visits: 8,518
Yes, you could quite possibly have improvements by directing queries to a replicated database. Less chance of blocking and generally less demand on the primary database.

Many people set up reporting databases for that reason. You can also replicate a subset of the data if that is acceptable so the reporting queries run faster. For instance maybe you only need previous 12 mionths of data, but production has 10 years. Just refresh reporting databas with previous 12 months.

Another possibility is to aggregate or otherwise manipulate data for reporting. You might replicate to reporting database, then run some jobs early morning to combine data into summary tables in the reporting db, then run reports off them, and ready for users when they get to the office. All kinds of options based on your business needs.



Post #1451735
Posted Friday, May 10, 2013 2:14 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:28 AM
Points: 655, Visits: 3,917
Ok, thank you sir, I'll continue to explore this. My primary goal is to minimize the potential impacts to the application using the primary database from my reporting and ad hoc querying. Replication latency of under a minute would be acceptable. I'm sure the ideal scenario is moving this to a different server or perhaps instance but I might not have that luxury.

I'd almost like to allocate server resources on a database by database level but to the best of my knowledge that is not possible. Thanks again.
Post #1451740
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse