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...
>
>

Replication - SQL Server 2000 - Service Pack 4

Hi,
I am considering upgrading to SQL Server service pack 4 and would just like
to clarify.
In the readme file for SP 4 if find the following:
“Note If an instance of SQL Server is not part of a replication topology,
you can back up a user database and restore it on any other release of SQL
Server 2000.”
Does this mean that I will not be able to restore a user database from a
instance running SP3 to a instance running sp 4 if it is part of a
replication topology? And if this is the case is there a workaround? Like a
script that upgrades the user databases to sp 4?
Scenario: Two environments: testing and production. I upgrade test to SP4
and then restore user databases from production (sp3). Replication is used
both in test and production, so the restored user databases that are involved
in replication will not longer work, because they are from an instance that
is sp3? Right?
Many Thanks,
Nina
You cannot restore a backup of a replicated database from a different version
of SQL Server 2000. This is because replication updates user databases
(rather than just system databases) when the service pack is applied, and a
restored SP3 database may have replication metadata that is incompatible with
SP4 replication. User databases participating in a replication topology must
be online and accessible during upgrade to SP4 so that updates can be made.
I'm not sure if there is a separate script just to update user databases to
SP4, it may be part of the overall replication upgrade scripts. At any rate,
the recommended process is to let SP4 Setup perform the upgrade.
Glenn Gailey
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nina" wrote:

> Hi,
> I am considering upgrading to SQL Server service pack 4 and would just like
> to clarify.
> In the readme file for SP 4 if find the following:
> “Note If an instance of SQL Server is not part of a replication topology,
> you can back up a user database and restore it on any other release of SQL
> Server 2000.”
> Does this mean that I will not be able to restore a user database from a
> instance running SP3 to a instance running sp 4 if it is part of a
> replication topology? And if this is the case is there a workaround? Like a
> script that upgrades the user databases to sp 4?
> Scenario: Two environments: testing and production. I upgrade test to SP4
> and then restore user databases from production (sp3). Replication is used
> both in test and production, so the restored user databases that are involved
> in replication will not longer work, because they are from an instance that
> is sp3? Right?
> Many Thanks,
> Nina
>