Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drsloat
Starting Member
45 Posts |
 Posted - 2006-09-22 : 11:08:27
|
| I have never used Sql Server replication and would like to ask a few general questions to make sure i am following the right path. I want to move data off a production sql server, onto a different machine in order to run queries for analysis and decision support. The data will be read only once it is off of production. There is a fast network between the machines. The data can be stale (but fresher is better).In the past I might have done this with a dts package to copy data. This seems somewhat analogous to Snapshot replication. Are there any big differences or advantages to snapshot replication over a fairly simple dts package?What are the performance implications of transactional replication on the Publishing server? I'd like the replicated data to be as up to date as possible without degrading performance on the publisher. Is there a better setup than trasactional replication, with the distributor different from the publisher?Thanks for any input. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-22 : 13:15:40
|
| Yes you want transactional replication. We've been using it for a few years for our reporting environment. We haven't noticed any performance issues on the publisher, subscriber, or or distributor. It is recommended that your distributor be on a different SQL instance.Tara Kizer |
 |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-09-24 : 10:21:13
|
| We use replication sql transactional....Its good ...you do need primary keys on all the tables.....otherwise your have to use merge ...with the merge it will put its own column on to the tables.Its pretty easy to set up aswell...however i did have an error last week on our dev box...i set up transactional then deleted the entire replication re added it ....and it kept thinking i choose merge....eventually it worked.But apart from that its good. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-25 : 10:15:15
|
| Well you should have primary keys on every table anyway unless it's a staging table, so that part is moot.Tara Kizer |
|
|
|
|
|
|
Sql Server 2000 Replication Configuration