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...
> 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, 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...
> 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...
> > 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.
> >
> >
>|||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...
>--Original Message--
>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...
>> 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...
>> > 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.
>> >
>> >
>>
>
>.
>|||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...
> 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...
> >--Original Message--
> >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...
> >> 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...
> >> > 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.
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >|||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...
> 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...
> >--Original Message--
> >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...
> >> 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...
> >> > 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.
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >|||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...
> 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...
> > 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...
> > >--Original Message--
> > >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...
> > >> 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...
> > >> > 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.
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> > >.
> > >
>|||I think is it beneficial to see alternatives where, just as you point out, Greg. Log shipping, in
the strict meaning, is "designed" for fault tolerance. If we look at reporting, 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 scenario.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uPGxHpk5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> 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...
> > 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...
> > > 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...
> > > >--Original Message--
> > > >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...
> > > >> 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...
> > > >> > 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.
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||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...
> I think is it beneficial to see alternatives where, just as you point out,
Greg. Log shipping, in
> the strict meaning, is "designed" for fault tolerance. If we look at
reporting, 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
scenario.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uPGxHpk5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> > 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...
> > > 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...
> > > > 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...
> > > > >--Original Message--
> > > > >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...
> > > > >> 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...
> > > > >> > 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.
> > > > >> >
> > > > >> >
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||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.
>--Original Message--
>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...
>> 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...
>> >--Original Message--
>> >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...
>> >> 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...
>> >> > 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.
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Thank you everyone for detailed replies...
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:uLJuU6e5DHA.1664@.TK2MSFTNGP11.phx.gbl...
> 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.
>|||Greg,
LOL. Looks like we both did the same Freudian slip...
"...worth nothing..."
<g>
James Hokes
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uPGxHpk5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> 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...
> > 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...
> > > 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...
> > > >--Original Message--
> > > >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...
> > > >> 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...
> > > >> > 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.
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||lol - I'm just gettting used to using these new computer thingys... <g>
Regards,
Greg Linwood
SQL Server MVP
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:ubRlaup5DHA.2136@.TK2MSFTNGP12.phx.gbl...
> Greg,
> LOL. Looks like we both did the same Freudian slip...
> "...worth nothing..."
> <g>
> James Hokes
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uPGxHpk5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> > 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...
> > > 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...
> > > > 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...
> > > > >--Original Message--
> > > > >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...
> > > > >> 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...
> > > > >> > 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.
> > > > >> >
> > > > >> >
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||I am trying to something of the sort here. I want to log ship to a standby
server and then replicate from that server to other databases. Is this
possible? has anyone tried it?
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:uStJhmp5DHA.2524@.TK2MSFTNGP11.phx.gbl...
> Thank you everyone for detailed replies...
>
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:uLJuU6e5DHA.1664@.TK2MSFTNGP11.phx.gbl...
> > 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.
> >
> >
>|||Interesting... Do you wait a while before applying the tlog backups? Just in case there will come a
later db or diff backup which will make them un-needed...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message news:ekyK1$k5DHA.632@.TK2MSFTNGP12.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...
> > I think is it beneficial to see alternatives where, just as you point out,
> Greg. Log shipping, in
> > the strict meaning, is "designed" for fault tolerance. If we look at
> reporting, 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
> scenario.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> > news:uPGxHpk5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> > > 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...
> > > > 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...
> > > > > 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...
> > > > > >--Original Message--
> > > > > >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...
> > > > > >> 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...
> > > > > >> > 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.
> > > > > >> >
> > > > > >> >
> > > > > >>
> > > > > >>
> > > > > >
> > > > > >
> > > > > >.
> > > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||There are many options to setting up an environment for reporting
needs.
If it's just for quantification you can setup DTS to send only
selective columns.
If money is not an object HW clustering with two instances (very fast
and super redundancy but costly).
Log shipping; plain old DUMP/LOAD using customized program (very
little over head) so no use for Pub/Sub/Dis or Ent version for LOG
shipping.
Or what others have recommended above.
From personal experience working for the world's largest Hosting
company (not anymore) I have seen and done all above setups and all
three solutions worked good for its need. Largest LOG shipping setup
was for a marketing company that generated 40-80mb of LOGS every 10
minutes on SQL7/NT4 on Compaq 7000 (old) on 100MB network. Problem
with LOG shipping is disciplining users not to stay connect to the
STANDBY DB because it will break the synch. Properly setup SQL server
can take some real beating so LOG shipping should not break or create
any problem on older HW.|||No, there's a control table that records what fbak, diff & tlogs are
available, what their lsns are etc & they're just applied in "best" order
(ie a diff if it's there will be restored in favour of a series of tlogs).
But if a tlog or diff get lost, then future diff or tlogs can accommodate
the loss of the other.
Regards,
Greg Linwood
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ubPtMtx5DHA.1052@.TK2MSFTNGP12.phx.gbl...
> Interesting... Do you wait a while before applying the tlog backups? Just
in case there will come a
> later db or diff backup which will make them un-needed...
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:ekyK1$k5DHA.632@.TK2MSFTNGP12.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...
> > > I think is it beneficial to see alternatives where, just as you point
out,
> > Greg. Log shipping, in
> > > the strict meaning, is "designed" for fault tolerance. If we look at
> > reporting, 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
> > scenario.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> > > news:uPGxHpk5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> > > > 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...
> > > > > 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...
> > > > > > 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...
> > > > > > >--Original Message--
> > > > > > >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...
> > > > > > >> 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...
> > > > > > >> > 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.
> > > > > > >> >
> > > > > > >> >
> > > > > > >>
> > > > > > >>
> > > > > > >
> > > > > > >
> > > > > > >.
> > > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||A standby database is basically a Read-Only database that has the ability too have transaction logs applied to it, so it can be kept upto speed (so to speak). I do not think you can setup replication on a read-only database, there are various tables within the database itself that define replication, which are not accessable while in read-only mode.
Also keep in mind, that any access to the standby (log shipped) database will cause the application of any log files to hold until all access has been terminated. So in otherwords nobody can be accessing the database while logs are being applied. So you can cause the standby to go out of sync for the period of time the database is in use, and until the logs are applied fully again.
Just some thoughts I had.
Chuck Spencer|||Thanks for the info, Greg. I was partly curious because I wrote the log shipping code in Db Maint
and it is always interesting to see how other implementations look like :-).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:ORCuAgP6DHA.2168@.TK2MSFTNGP12.phx.gbl...
> No, there's a control table that records what fbak, diff & tlogs are
> available, what their lsns are etc & they're just applied in "best" order
> (ie a diff if it's there will be restored in favour of a series of tlogs).
> But if a tlog or diff get lost, then future diff or tlogs can accommodate
> the loss of the other.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ubPtMtx5DHA.1052@.TK2MSFTNGP12.phx.gbl...
> > Interesting... Do you wait a while before applying the tlog backups? Just
> in case there will come a
> > later db or diff backup which will make them un-needed...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:ekyK1$k5DHA.632@.TK2MSFTNGP12.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...
> > > > I think is it beneficial to see alternatives where, just as you point
> out,
> > > Greg. Log shipping, in
> > > > the strict meaning, is "designed" for fault tolerance. If we look at
> > > reporting, 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
> > > scenario.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> > > > news:uPGxHpk5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> > > > > 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...
> > > > > > 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...
> > > > > > > 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...
> > > > > > > >--Original Message--
> > > > > > > >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...
> > > > > > > >> 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...
> > > > > > > >> > 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.
> > > > > > > >> >
> > > > > > > >> >
> > > > > > > >>
> > > > > > > >>
> > > > > > > >
> > > > > > > >
> > > > > > > >.
> > > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>