Sunday, March 11, 2012

Can Log Shipping be used to ....

Hi,
I would like to find out if log shipping in SQL 2000 be used to create a
replica of database to be used to for data extraction on a regular basis.
Essentially, I am making a point-in-time snapshot of the databases so I can
use the second machine to generate reports without degrading performance of
the main server.
Thank you.Yes you can. But keep in mind Log Shipping is for Entire Database. You can
not be selective, restricting it a table or few tables. If this is what you
want Log Shipping is the right choice.
HTH
Satish Balusa
Corillian Corp.
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:uLJuU6e5DHA.1664@.TK2MSFTNGP11.phx.gbl...
quote:

> Hi,
> I would like to find out if log shipping in SQL 2000 be used to create a
> replica of database to be used to for data extraction on a regular basis.
> Essentially, I am making a point-in-time snapshot of the databases so I

can
quote:

> use the second machine to generate reports without degrading performance

of
quote:

> the main server.
> Thank you.
>
|||Yes, I am looking to replicate the whole databases. Is there any restriction
(Locks etc) to using the replicated database when a log shipping is setup? I
am trying to find out that once the database has been replicated, if I can
use either of the servers for the database at will without having to first
shutdown one or the other server.
Thank you.
"Satish Balusa" <sbalusa_nospam@.corillian.com> wrote in message
news:%23eb4mHf5DHA.504@.TK2MSFTNGP11.phx.gbl...
quote:

> Yes you can. But keep in mind Log Shipping is for Entire Database. You can
> not be selective, restricting it a table or few tables. If this is what

you
quote:

> want Log Shipping is the right choice.
> --
> HTH
> Satish Balusa
> Corillian Corp.
>
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:uLJuU6e5DHA.1664@.TK2MSFTNGP11.phx.gbl...
basis.[QUOTE]
> can
> of
>
|||How big is your database and how often are updates made
to it? I tried to set up something similar to what you
are trying and replicating everything was too much a load
on our bandwidth(ours is 100mb backbone, and we did not
have money for GB upgrade), something to think about...
quote:

>--Original Message--
>Yes, I am looking to replicate the whole databases. Is

there any restriction
quote:

>(Locks etc) to using the replicated database when a log

shipping is setup? I
quote:

>am trying to find out that once the database has been

replicated, if I can
quote:

>use either of the servers for the database at will

without having to first
quote:

>shutdown one or the other server.
>Thank you.
>"Satish Balusa" <sbalusa_nospam@.corillian.com> wrote in

message
quote:

>news:%23eb4mHf5DHA.504@.TK2MSFTNGP11.phx.gbl...
Entire Database. You can[QUOTE]
tables. If this is what[QUOTE]
>you
be used to create a[QUOTE]
extraction on a regular[QUOTE]
>basis.
the databases so I[QUOTE]
degrading performance[QUOTE]
>
>.
>
|||I have several databases at multiple sites. Each site will however have its
logged shipped locally. Databases can range anywhere from 5-30GB in size. I
will need these to be fully synched at least for an hour every day. I really
don't want to overload the network (also 100M). If this creates too much
network traffic I am thinking about create a dedicated network using spare
NICS and a crossover cable. Any comments?
Thank you.
"jeremy" <anonymous@.discussions.microsoft.com> wrote in message
news:638501c3e5fa$6e07b750$a401280a@.phx.gbl...[QUOTE]
> How big is your database and how often are updates made
> to it? I tried to set up something similar to what you
> are trying and replicating everything was too much a load
> on our bandwidth(ours is 100mb backbone, and we did not
> have money for GB upgrade), something to think about...
> there any restriction
> shipping is setup? I
> replicated, if I can
> without having to first
> message
> Entire Database. You can
> tables. If this is what
> be used to create a
> extraction on a regular
> the databases so I
> degrading performance|||It is also worth nothing that applying the logs may overwhelm the target
machine if it is not up to par.
James Hokes
"jeremy" <anonymous@.discussions.microsoft.com> wrote in message
news:638501c3e5fa$6e07b750$a401280a@.phx.gbl...[QUOTE]
> How big is your database and how often are updates made
> to it? I tried to set up something similar to what you
> are trying and replicating everything was too much a load
> on our bandwidth(ours is 100mb backbone, and we did not
> have money for GB upgrade), something to think about...
> there any restriction
> shipping is setup? I
> replicated, if I can
> without having to first
> message
> Entire Database. You can
> tables. If this is what
> be used to create a
> extraction on a regular
> the databases so I
> degrading performance|||Good point James.
It's also worth nothing that sometimes shipping differential backups instead
of logs can solve this problem. This is not exactly log shipping, but is
implemented very similarly.
Regards,
Greg Linwood
SQL Server MVP
"James Hokes" <noemail@.noway.com> wrote in message
news:elM$Sdi5DHA.2412@.TK2MSFTNGP11.phx.gbl...
quote:

> It is also worth nothing that applying the logs may overwhelm the target
> machine if it is not up to par.
> James Hokes
> "jeremy" <anonymous@.discussions.microsoft.com> wrote in message
> news:638501c3e5fa$6e07b750$a401280a@.phx.gbl...
>
|||I think is it beneficial to see alternatives where, just as you point out, G
reg. Log shipping, in
the strict meaning, is "designed" for fault tolerance. If we look at reporti
ng, we have other
options/variants:
Only ship the db backup every day (depending on how updated the info need to
be). Might not be
feasible if db is big, of course. Less complex than log shipping, IMO.
Log shipping, possibly with home-grown code. Note, however, that the target
database cannot be in
use during a restore. This applies to all restore operations.
Other alternatives, like diff backups.
Also, replication can be an option. It is more or less designed for this sce
nario.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uPGxHpk5DHA.1368@.TK2MSFTNGP10.phx.gbl...
quote:

> Good point James.
> It's also worth nothing that sometimes shipping differential backups inste
ad
> of logs can solve this problem. This is not exactly log shipping, but is
> implemented very similarly.
> Regards,
> Greg Linwood
> SQL Server MVP
> "James Hokes" <noemail@.noway.com> wrote in message
> news:elM$Sdi5DHA.2412@.TK2MSFTNGP11.phx.gbl...
>
|||Yep - and here's even one more! (c:
In 2000, I rolled a custom hybrid shipping solution for a busy 365x7
e-commerce site that uses combinations of .fbak, .diff & .tlog shipping to
effectively keep a lower spec'd machine in synch with a relatively bigger
server. The solution uses restore headeronly to compare lastlsn & only
restores if > the previous lsn. This allows .tlogs to fall behind if
necessary b/c the .diff or .fbak (eventually) catches up. It's been running
without a hitch on the smaller server for three years now. The tlogs do
often fall behind because the app (ISV supplied) uses "counter" tables
instead of identities which are updated at a ferocious pace. This causes the
tlog shipping to be considerably more expensive than .diff, but they still
want .tlogs shipped to close the recovery window.
Regards,
Greg Linwood
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujiYC2k5DHA.2416@.TK2MSFTNGP10.phx.gbl...
quote:

> I think is it beneficial to see alternatives where, just as you point out,

Greg. Log shipping, in
quote:

> the strict meaning, is "designed" for fault tolerance. If we look at

reporting, we have other
quote:

> options/variants:
> Only ship the db backup every day (depending on how updated the info need

to be). Might not be
quote:

> feasible if db is big, of course. Less complex than log shipping, IMO.
> Log shipping, possibly with home-grown code. Note, however, that the

target database cannot be in
quote:

> use during a restore. This applies to all restore operations.
> Other alternatives, like diff backups.
> Also, replication can be an option. It is more or less designed for this

scenario.
quote:

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=...ublic.sqlserver
quote:

>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uPGxHpk5DHA.1368@.TK2MSFTNGP10.phx.gbl...
instead[QUOTE]
target[QUOTE]
>
|||First, I will start with that my database was 70Gb, so
you may be small enough that it may not be a problem,
testing is really the only way to know. If you have the
means I definately recommend the dedicated network, this
will pull load away for network and save from any future
network load problems caused by the increase in your
database sizes.
quote:

>--Original Message--
>I have several databases at multiple sites. Each site

will however have its
quote:

>logged shipped locally. Databases can range anywhere

from 5-30GB in size. I
quote:

>will need these to be fully synched at least for an hour

every day. I really
quote:

>don't want to overload the network (also 100M). If this

creates too much
quote:

>network traffic I am thinking about create a dedicated

network using spare
quote:

>NICS and a crossover cable. Any comments?
>Thank you.
>"jeremy" <anonymous@.discussions.microsoft.com> wrote in

message
quote:

>news:638501c3e5fa$6e07b750$a401280a@.phx.gbl...
load[QUOTE]
about...[QUOTE]
log[QUOTE]
in[QUOTE]
message[QUOTE]
2000[QUOTE]
of[QUOTE]
>
>.
>

No comments:

Post a Comment