replication and logshipping

Hello
I read a post in the newsgroup about replication and logshipping. I wanted
to clarify further.
We have a warm-standby server to our production server. We use logshipping
to keep the stand-by in sync. Now we want to setup anothere server for
reporting as the current standby cannot serve as reporting server.(the
logshipping wither fails as there are users or the users need to be kicked
out of the stand-by server for every log restored). We thought of a few of
options.
a) use bcp to move data that we need for reporting in a batch.
b) Use linked servers to pull in data into the reporting servers
c) Tranactional replication
Our conditions are that the reporting server cannot have any downtime and
the data needs to be as close to real-time as possible (currently we are
running reports from the production nserver)
Have any of you tried any of the above?. if so can u share your expreiences.
Note: I have sql server experience. But have shunned away from replication
due to bad expreiences with 4.2, 6.0 and 6.5. So any help in this area is
appreciates.
techdummy
If your reporting server "cannot have any downtime and the data needs to be
as close to real-time as possible", transactional replication is your best
bet.
Since you are currently running these reports off your production server,
I'm guessing that your production server is pretty beefy and has ample
headroom. Given that, you can set a fairly low delay for transactional
replication so that updates get replicated quickly. <5minutes is pretty good
but can be lower (continuous if really necessary). Just make sure the server
and network doesn't get overwhelmed if you have lots of transactions going
through.
Of course, this is assuming you don't have some massive or a complex DB like
SAP or the likes of it.
BCP would work too but IMHO, it's quite a bit more work to script things
out, plug them into SQL Agent and monitor manually (sort of). At least with
replication, a lof of the setting up and monitoring is "relatively" easy so
long as you know what you're doing. Log Shipping won't work since you can't
read the standby database while LS is active.
joe.
"techdummy" <techdummy@.discussions.microsoft.com> wrote in message
news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
> Hello
> I read a post in the newsgroup about replication and logshipping. I
> wanted
> to clarify further.
> We have a warm-standby server to our production server. We use logshipping
> to keep the stand-by in sync. Now we want to setup anothere server for
> reporting as the current standby cannot serve as reporting server.(the
> logshipping wither fails as there are users or the users need to be kicked
> out of the stand-by server for every log restored). We thought of a few
> of
> options.
> a) use bcp to move data that we need for reporting in a batch.
> b) Use linked servers to pull in data into the reporting servers
> c) Tranactional replication
> Our conditions are that the reporting server cannot have any downtime and
> the data needs to be as close to real-time as possible (currently we are
> running reports from the production nserver)
>
> Have any of you tried any of the above?. if so can u share your
> expreiences.
>
> Note: I have sql server experience. But have shunned away from replication
> due to bad expreiences with 4.2, 6.0 and 6.5. So any help in this area is
> appreciates.
> --
> techdummy
|||Joe,
Thanks for ur response. I think replication would be agood choice as well.
However, will it work with Logshipping going on from the production server to
the standby?
techdummy
"Joe Yong" wrote:

> If your reporting server "cannot have any downtime and the data needs to be
> as close to real-time as possible", transactional replication is your best
> bet.
> Since you are currently running these reports off your production server,
> I'm guessing that your production server is pretty beefy and has ample
> headroom. Given that, you can set a fairly low delay for transactional
> replication so that updates get replicated quickly. <5minutes is pretty good
> but can be lower (continuous if really necessary). Just make sure the server
> and network doesn't get overwhelmed if you have lots of transactions going
> through.
> Of course, this is assuming you don't have some massive or a complex DB like
> SAP or the likes of it.
> BCP would work too but IMHO, it's quite a bit more work to script things
> out, plug them into SQL Agent and monitor manually (sort of). At least with
> replication, a lof of the setting up and monitoring is "relatively" easy so
> long as you know what you're doing. Log Shipping won't work since you can't
> read the standby database while LS is active.
>
> joe.
> "techdummy" <techdummy@.discussions.microsoft.com> wrote in message
> news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
>
>
|||We use replication for this. It works well. Latency can be as low as 10-60
seconds under high load (2500 tps).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"techdummy" <techdummy@.discussions.microsoft.com> wrote in message
news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
> Hello
> I read a post in the newsgroup about replication and logshipping. I
wanted
> to clarify further.
> We have a warm-standby server to our production server. We use logshipping
> to keep the stand-by in sync. Now we want to setup anothere server for
> reporting as the current standby cannot serve as reporting server.(the
> logshipping wither fails as there are users or the users need to be kicked
> out of the stand-by server for every log restored). We thought of a few
of
> options.
> a) use bcp to move data that we need for reporting in a batch.
> b) Use linked servers to pull in data into the reporting servers
> c) Tranactional replication
> Our conditions are that the reporting server cannot have any downtime and
> the data needs to be as close to real-time as possible (currently we are
> running reports from the production nserver)
>
> Have any of you tried any of the above?. if so can u share your
expreiences.
>
> Note: I have sql server experience. But have shunned away from replication
> due to bad expreiences with 4.2, 6.0 and 6.5. So any help in this area is
> appreciates.
> --
> techdummy
|||Hi
Are u using logshipping as well in parallel to replication from the same
source server?
techdummy
"Joe Yong" wrote:

> If your reporting server "cannot have any downtime and the data needs to be
> as close to real-time as possible", transactional replication is your best
> bet.
> Since you are currently running these reports off your production server,
> I'm guessing that your production server is pretty beefy and has ample
> headroom. Given that, you can set a fairly low delay for transactional
> replication so that updates get replicated quickly. <5minutes is pretty good
> but can be lower (continuous if really necessary). Just make sure the server
> and network doesn't get overwhelmed if you have lots of transactions going
> through.
> Of course, this is assuming you don't have some massive or a complex DB like
> SAP or the likes of it.
> BCP would work too but IMHO, it's quite a bit more work to script things
> out, plug them into SQL Agent and monitor manually (sort of). At least with
> replication, a lof of the setting up and monitoring is "relatively" easy so
> long as you know what you're doing. Log Shipping won't work since you can't
> read the standby database while LS is active.
>
> joe.
> "techdummy" <techdummy@.discussions.microsoft.com> wrote in message
> news:EB5628A9-E205-4F19-9A48-CF690629D890@.microsoft.com...
>
>

Replicating User Defined Data Types

Hey guys,
Thanks so much for this great newsgroup btw, helped me hugely through doing
replication via handheld units etc.
I'm running into a bit of a problem between server 2 server replication.
Both databases have user defined data types. All the tables using them are
fine, but once the snapshot is applied to the subscriber, and the replication
is hooked up to the publisher, all the data types are converted to their base
types (char 10 etc)
Is there any way to stop this? The subscriber needs to keep the user defined
data types.
I noticed in the publication wizard there is an option that says "Keep the
existing table unchanged" but the default value for this is "DROP the
existing table and re-create it" this is all under the Snapshot tab of the
article properties.
If I set it to "Keep the existing table unchanged" will that fix my problems
or is that not the proper fix for this issue?
Thanks and take care!
Simon,
on the article propertuies at the bottom there is the option to change user
defined datatypes to base types and by default this is enabled. Also, you
can add the user defined types themselves to a prescript.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)