SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactional Replication on same server different database


Transactional Replication on same server different database

Author
Message
Chrissy321
Chrissy321
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1512 Visits: 4743
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.
homebrew01
homebrew01
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6257 Visits: 9177
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.



Chrissy321
Chrissy321
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1512 Visits: 4743
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?
homebrew01
homebrew01
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6257 Visits: 9177
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.



Chrissy321
Chrissy321
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1512 Visits: 4743
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search