| Author |
Topic |
|
amsqlguy
Yak Posting Veteran
89 Posts |
 Posted - 2008-10-21 : 20:38:56
|
| Guys,I have OLTP database which is 60GB, currently reports also run against this database. I want to replicate this database and have reports to run against the replicated (slave) database. I believe this way there will less read lock transactionson the master database.Is there anything I need to look out for while setting replication in terms of performance and frequencyAny suggestions and inputs would help.Thanks |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-22 : 08:24:47
|
| Yes you can use transactional replication for it. How high is the transaction rate? what is network bandwidth between servers?Do you need full 60 GB replicated? |
 |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2008-10-22 : 09:56:05
|
| We didn't notice any performance impact in a similar situation, unless you're re-snapshotting during business hours. Use transactional for the busy tables, and snapshot for small static tables (country or state codes, lookup tables..)The frequency depends on your business needs for reporting. |
|
|
|
amsqlguy
Yak Posting Veteran
89 Posts |
Posted - 2008-10-22 : 22:09:49
|
| Thanks for your reply, I have setup transactional replication for selected database objects which are used reports.I have concerns about the instances - the master database is 60GB (this is the only database on the instance), currently1. Distributor and Publisher on the same instance2. Subscriber on a different instanceIdeally what is the recommended solution interms of settings up publisher, distributor and subscriber in sql instance what are the ramifications of setting up publisher, distributor and subscriber in the same instance Any suggestions and inputs would help.Thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-22 : 22:20:49
|
| There is no benefit if you are doing transactional replication in instances level as each instances will eat up resources of your server. |
|
|
|
amsqlguy
Yak Posting Veteran
89 Posts |
Posted - 2008-10-23 : 07:04:53
|
| Sorry trying to understand here..So having distributor, publisher and subscriber on the same instance doesnt hurt, I was thinking of having distributor on another instance (instance on different box).Is rhere any advantage of having distributor, publisher and subscriber on different Instance (basically instance on different box)Any suggestions and inputs would helpThanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-23 : 07:14:05
|
| Let me understand your question. You want to do replication with publisher and subscriber residing in different box, Am i right? You can have distributor run on publisher or subscriber. |
|
|
|
amsqlguy
Yak Posting Veteran
89 Posts |
Posted - 2008-10-23 : 09:51:39
|
| My intention is to find out interms of performance and load is it advisable to have distibutor, publisher and subscriber on the same instance for 60gb publisher database. Note that currently there is only one database on the instance. Thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-23 : 10:00:27
|
| First of all, you can't have all three(P,D,S) in same instance. You are not getting my point as instances and server are 2 different thing. You can have multiple instances in one server. |
|
|
|
amsqlguy
Yak Posting Veteran
89 Posts |
Posted - 2008-10-23 : 11:41:01
|
quote:
Originally posted by sodeep
First of all, you can't have all three(P,D,S) in same instance. You are not getting my point as instances and server are 2 different thing. You can have multiple instances in one server.
I understand instance and server are different entities, the two instances I am referring to are on different boxes.And hence the question what is best scenario1. Publisger and Distributor on one box with its own instance.2. Subscriber on one box with its own instanceThanks |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2008-10-23 : 12:17:57
|
| " ... the master database is 60GB ..."Huh !?!?!?edit.... you probably don't mean "MASTER", you mean the publisher database. |
|
|
|
amsqlguy
Yak Posting Veteran
89 Posts |
Posted - 2008-10-23 : 12:25:52
|
quote:
Originally posted by homebrew
" ... the master database is 60GB ..."Huh !?!?!?
i meant the publisher database. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-23 : 13:05:15
|
quote:
Originally posted by amsqlguy
quote:
Originally posted by sodeep
First of all, you can't have all three(P,D,S) in same instance. You are not getting my point as instances and server are 2 different thing. You can have multiple instances in one server.
I understand instance and server are different entities, the two instances I am referring to are on different boxes.And hence the question what is best scenario1. Publisger and Distributor on one box with its own instance.2. Subscriber on one box with its own instanceThanks
Looks good to me. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-10-23 : 19:01:23
|
| If your Publisher database is already hitting its limits you might want to let Distributor sit on the Subscriber instead of Publisher. What are the versions of publisher and subscriber?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
Setting replication for OLTP database