database replication

I have one database(database1) in houston and the other database(database2) at Newjersy. SO if i wanted to replicate the data from the database1 to database2 how can I do that. I wanted to setup the replication to get the data into only one table. Infact both the tables are not identical in structure.



Thanks.There are several flavors of replication. Snapshot being the simplest, and which may work for you if the NJ data is read only. Snapshot rep is used mainly for bulk data movement, once a day, once a week type stuff. It is also used as a first step for the other rep types; transactional and merge replication.

Transaction rep, as it's name suggests, transfers data much more often. I don't think it's upon every db transaction but it's sure close to it.

Merge replication is when the folks in NJ need to update the data. This can get dicey.

BOL has very good info on replication.

Database Replication

we have setup a sql2005 server for reporting. we want to replicate the
transaction databases which used for live applications to that server such
that all reports will be generated in that server.
This reporting server is supposed read-only and with let's say 30 mins delay
from production data.
With this requirement, should we use the transactional replication or there
any method?
Thanks,
Ryan
Ryan,
transactional replication is often used for this type of reporting
requirement. You could also enhance the system by using the snapshot
committed isolation levels to maintain access while the distribution agent
is running. The main 'competitor' technology on SQL Server 2005 is database
mirroring with database snapshots. There's no detailed list of pros and
cons, but as I'm a replication guy I'll point out that mirroring doesn't
support FTI and you can't take back ups of snapshots
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul-
I have client who maintains a local SQL 2005 database which has
numerous bulk updates applied throughout the day. They wish to
replicate (most of) this data to their web host which is in another
city. They are trying to decide whether to use Transactional
Replication or once-per-day Merge Replication. (Concurrency is not a
big issue here).
What method would you recommend? What are the most important
considerations?
Thanks,
Paul
Paul Ibison wrote:
> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Merge is generally slower. If there are numerous updates to the same row,
then it can approach transactional times, but I have rarely seen cases of
someone claiming it to be faster. It's geared up for offline updates at teh
subscriber and conflict resolution, neither of which you'll need. I'd
definitely go with transactional.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks, Paul!
Does Transactional require an "always on" connection to the subscriber?
Paul Ibison wrote:
> Merge is generally slower. If there are numerous updates to the same row,
> then it can approach transactional times, but I have rarely seen cases of
> someone claiming it to be faster. It's geared up for offline updates at teh
> subscriber and conflict resolution, neither of which you'll need. I'd
> definitely go with transactional.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No - as long as there is a connection when the distribution agent is
scheduled to run you're ok (different for immediate updating subs but not
relevant in your case).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul, thanks a lot!
Do you know any limitation on using transactional replication? eg the min
delay time, can it perform query during the replication.
Aslo how's the overhead on resources of compare to mirroring? Is it require
many resources (eg. CPU and RAM) during the processing?
Regards,
Ryan
"Paul Ibison" wrote:

> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Ryan,
I've been using transactional replication at my current employer for years
and latency will always be determined by geographic region and equipment.
In my case I'm seeing less than 5 second latency, usually lower than 2, and
yes, of course you can run queries on the data that is being replicated.
Resources are minimal once replication is setup, during the initial
snapshot, the only issues you might run into are the locking of the tables
as they are processed for replication.
Adam P. Cassidy
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:D4AA35E2-CBA7-4052-93AB-37DF213D729D@.microsoft.com...[vbcol=seagreen]
> Paul, thanks a lot!
> Do you know any limitation on using transactional replication? eg the min
> delay time, can it perform query during the replication.
> Aslo how's the overhead on resources of compare to mirroring? Is it
> require
> many resources (eg. CPU and RAM) during the processing?
> Regards,
> Ryan
>
> "Paul Ibison" wrote:
|||Ryan,
I agree with Adam, but just to clarify, if you mean queries applied to the
publisher then there' s no issue, but if the query is to the subscriber, you
might experience the normal blocking issues. The new snapshot isolation
level can be of use here. I have no stats regarding the performance
comparison between database mirroring and replication as yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Yes agreed. Sorry for not clarifying. We replicate for Cognos reporting
and since non of the reports are require a committed state, we have all the
queries executed against the replicated data as read uncommitted and there
are no problems - definitely a point I should have made.
Adam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OhCAQbWFHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Ryan,
> I agree with Adam, but just to clarify, if you mean queries applied to the
> publisher then there' s no issue, but if the query is to the subscriber,
> you might experience the normal blocking issues. The new snapshot
> isolation level can be of use here. I have no stats regarding the
> performance comparison between database mirroring and replication as yet.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Database Replication

Hi everybody

Can I replicate a database onto a seperate instance of SQL? I need to
replicate a database to a stand-alone machine with no connectivety to
anything. Does anyone know where I can find info on this?

Thanks for any help.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1Perhaps in Books Online, under "Replication"? :-) And I guess you
don't really mean "no connectivity to anything", otherwise it would be
somewhat difficult to implement. Depending on what your goal is
(availability, disaster recovery, offline reporting etc.) you could
also look at log shipping, clustering, and of course backup/restore.

Replication is quite a specialized area, so if you have specific
questions about it, you'll probably get a better response in
microsoft.public.sqlserver.replication.

Simon|||Thanks Simon. I did check the Books Online ;-). All I need is our online
database on another stand-alone computer
that is not connected to any network (due to stupid security). I need this
for a whole bunch of testing on one of our apps. I think that the
backup/restore option would be better?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1|||If you have no network connection, you'll need to copy the database to
a physical medium (DVD, tape, USB drive) anyway, so backup/restore
would be the only real option (you could detach/attach, but that would
mean taking the source offline).

Also see this article:

http://support.microsoft.com/defaul...kb;en-us;314546

Simon