Log Shipping with HOLD

I had an interview question the other day I don't understand. He asked about
types of replication and when you would use them. One of the options was "log
shipping with hold" and why?
I can't think of a good reason to do this, as with that type of replication,
you would want the replicant/subscriber to be as close as possible to the
primary/publisher. Otherwise, why not just use snapshot?
Thoughts please.JayKon wrote:
> I had an interview question the other day I don't understand. He asked about
> types of replication and when you would use them. One of the options was "log
> shipping with hold" and why?
> I can't think of a good reason to do this, as with that type of replication,
> you would want the replicant/subscriber to be as close as possible to the
> primary/publisher. Otherwise, why not just use snapshot?
> Thoughts please.
For a standby or "disaster recovery" server, having a delay in the
restore process can help protect from logical disasters, i.e. somebody
nukes a million rows in a table by accident. You have time to prevent
that mistake from reaching your standby machine in case you need it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||My first reaction is "DOH!", that makes sense. But after that, I wonder if
thats really such a good idea. After all, you can still do a point-in-time
restore, or even restore to a seperate DB and manually extract the rows to
recover.
Anyway, thanks Tracy, I should have thought of that.
"Tracy McKibben" wrote:
> JayKon wrote:
> > I had an interview question the other day I don't understand. He asked about
> > types of replication and when you would use them. One of the options was "log
> > shipping with hold" and why?
> >
> > I can't think of a good reason to do this, as with that type of replication,
> > you would want the replicant/subscriber to be as close as possible to the
> > primary/publisher. Otherwise, why not just use snapshot?
> >
> > Thoughts please.
> For a standby or "disaster recovery" server, having a delay in the
> restore process can help protect from logical disasters, i.e. somebody
> nukes a million rows in a table by accident. You have time to prevent
> that mistake from reaching your standby machine in case you need it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:B04C4FDD-89F3-468E-8626-B335D964D61F@.microsoft.com...
> My first reaction is "DOH!", that makes sense. But after that, I wonder if
> thats really such a good idea. After all, you can still do a point-in-time
> restore, or even restore to a seperate DB and manually extract the rows to
> recover.
>
Also,
Using Secondary Servers for Query Processing
http://msdn2.microsoft.com/en-us/library/ms189572.aspx
David|||JayKon wrote:
> My first reaction is "DOH!", that makes sense. But after that, I wonder if
> thats really such a good idea. After all, you can still do a point-in-time
> restore, or even restore to a seperate DB and manually extract the rows to
> recover.
>
All true, but if you're in a low- or no-downtime environment, doing
weekly full/hourly log backups, with a two-hour delay in log shipping,
if something happens on Thursday, will it take longer to restore the
full and 4 days of logs, or two hours of logs (up to the failure)?
It's just another tool, another way of protecting yourself...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David, the question was not about uses of replication, but rather the "with
hold" option.
"David Browne" wrote:
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:B04C4FDD-89F3-468E-8626-B335D964D61F@.microsoft.com...
> > My first reaction is "DOH!", that makes sense. But after that, I wonder if
> > thats really such a good idea. After all, you can still do a point-in-time
> > restore, or even restore to a seperate DB and manually extract the rows to
> > recover.
> >
> Also,
> Using Secondary Servers for Query Processing
> http://msdn2.microsoft.com/en-us/library/ms189572.aspx
>
> David
>|||"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:6BA5BF29-8848-46C4-9C8A-A238AAB4E5E9@.microsoft.com...
> David, the question was not about uses of replication, but rather the
> "with
> hold" option.
>
My point, perhaps not really explained, was that the secondary database is
available for querying only while you are not restoring logs. If, for
instance, you only restore logs at the end of the day, you can use the log
shipping secondary server for point-in-time reporting.
Davidsql

Log shipping question.

Hi all,
I have a question about the scheduling of different backup types under log
shipping. Let's say I have two servers, ServerA and ServerB, and there's
one database called DatabaseA that exists on both servers. Imagine that I
have it setup so that a full backup is generated every day at midnight,
differential backups every hour, and a transaction log every 15 minutes.
These are copied over to ServerB, and restored into the database as they
come in. Thus, my database on ServerB is (at most) about 15 minutes behind.
Question: is there any advantage, given this scenario, to doing the full
backup every night versus once a week? For that matter, why even do the
differentials every hour? What if I changed it to do the full backup once a
week, differentials once a day, and kept generating the transaction logs
every 15 minutes?
It seems to me that, so long as I keep the transaction logs in order and
restore them as they are deposited on ServerB, my data would be just as
up-to-date as if I did the full and differentials more often -- plus, I'd be
generating less network traffic, which is a bonus in my scenario.
I'd appreciate any thoughts or observations ...
Thanks for the help!
WadeWade (wwegner23NOEMAILhotmail.com) writes:
> I have a question about the scheduling of different backup types under
> log shipping. Let's say I have two servers, ServerA and ServerB, and
> there's one database called DatabaseA that exists on both servers.
> Imagine that I have it setup so that a full backup is generated every
> day at midnight, differential backups every hour, and a transaction log
> every 15 minutes. These are copied over to ServerB, and restored into
> the database as they come in. Thus, my database on ServerB is (at most)
> about 15 minutes behind.
> Question: is there any advantage, given this scenario, to doing the full
> backup every night versus once a week? For that matter, why even do the
> differentials every hour? What if I changed it to do the full backup
> once a week, differentials once a day, and kept generating the
> transaction logs every 15 minutes?
Others have more experience of setting backup schedules than I have, but I
would keep the nightly full backup and the log backups, but skip the
differential backups.
One thing of interest here is how far ServerA and ServerB are from each
other. Are they on different sides of town, or in the same computer room?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi
You did not state your DB size, but running differentials every hours seems
a bit much to me.
Most people use the:
Full one per week
Differential once per day
Transaction log every 15 minutes.
For a restore, you need your last full backup, the most recent differential
and the transaction logs after the differential to the point in time.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:ON3vTRNrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a question about the scheduling of different backup types under log
> shipping. Let's say I have two servers, ServerA and ServerB, and there's
> one database called DatabaseA that exists on both servers. Imagine that I
> have it setup so that a full backup is generated every day at midnight,
> differential backups every hour, and a transaction log every 15 minutes.
> These are copied over to ServerB, and restored into the database as they
> come in. Thus, my database on ServerB is (at most) about 15 minutes
> behind.
> Question: is there any advantage, given this scenario, to doing the full
> backup every night versus once a week? For that matter, why even do the
> differentials every hour? What if I changed it to do the full backup once
> a week, differentials once a day, and kept generating the transaction logs
> every 15 minutes?
> It seems to me that, so long as I keep the transaction logs in order and
> restore them as they are deposited on ServerB, my data would be just as
> up-to-date as if I did the full and differentials more often -- plus, I'd
> be generating less network traffic, which is a bonus in my scenario.
> I'd appreciate any thoughts or observations ...
> Thanks for the help!
> Wade
>|||Thanks, Mike.
> You did not state your DB size, but running differentials every hours
> seems a bit much to me.
The sizes are all over the place -- 20 MBytes to 2.7 GBytes. Obviously, if
there's not really a reason to do the fulls more often (which is what it
appears like ...), then the less frequenct the backups occur the better.
> Most people use the:
> Full one per week
> Differential once per day
> Transaction log every 15 minutes.
Yes, exactly what I stated.
> For a restore, you need your last full backup, the most recent
> differential and the transaction logs after the differential to the point
> in time.
... thanks.
Wade|||> Others have more experience of setting backup schedules than I have, but I
> would keep the nightly full backup and the log backups, but skip the
> differential backups.
I've thought of that too ... but, it seems like having the diffs is a good
catch, because if something happens and the LSN #'s get out of sequence with
the transaction logs (which has happened to me in testing, although I think
I have that resolved) the differential backups can usually clean it up.
> One thing of interest here is how far ServerA and ServerB are from each
> other. Are they on different sides of town, or in the same computer room?
Denver, CO --> Reston, VA. We have a 1 megabit pipe that we're replicating
data over. Perhaps I should have added a bit more of the real world into
the question. :)
Thanks,
Wade|||Wade (wwegner23NOEMAILhotmail.com) writes:
>> One thing of interest here is how far ServerA and ServerB are from each
>> other. Are they on different sides of town, or in the same computer room?
> Denver, CO --> Reston, VA. We have a 1 megabit pipe that we're
> replicating data over. Perhaps I should have added a bit more of the
> real world into the question. :)
That's good. It has to be pretty bug disaster that wipes out both.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||God forbid.
So ... kind sounds like there's no reason to backup anymore than the
following
Full: 1 / week
Diff: 1 / day
Tran: Every 15 minutes
Sound good?
Thanks!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C2811C71153Yazorman@.127.0.0.1...
> Wade (wwegner23NOEMAILhotmail.com) writes:
>> One thing of interest here is how far ServerA and ServerB are from each
>> other. Are they on different sides of town, or in the same computer
>> room?
>> Denver, CO --> Reston, VA. We have a 1 megabit pipe that we're
>> replicating data over. Perhaps I should have added a bit more of the
>> real world into the question. :)
> That's good. It has to be pretty bug disaster that wipes out both.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>

Log shipping question.

Hi all,
I have a question about the scheduling of different backup types under log
shipping. Let's say I have two servers, ServerA and ServerB, and there's
one database called DatabaseA that exists on both servers. Imagine that I
have it setup so that a full backup is generated every day at midnight,
differential backups every hour, and a transaction log every 15 minutes.
These are copied over to ServerB, and restored into the database as they
come in. Thus, my database on ServerB is (at most) about 15 minutes behind.
Question: is there any advantage, given this scenario, to doing the full
backup every night versus once a week? For that matter, why even do the
differentials every hour? What if I changed it to do the full backup once a
week, differentials once a day, and kept generating the transaction logs
every 15 minutes?
It seems to me that, so long as I keep the transaction logs in order and
restore them as they are deposited on ServerB, my data would be just as
up-to-date as if I did the full and differentials more often -- plus, I'd be
generating less network traffic, which is a bonus in my scenario.
I'd appreciate any thoughts or observations ...
Thanks for the help!
Wade
Wade (wwegner23NOEMAILhotmail.com) writes:
> I have a question about the scheduling of different backup types under
> log shipping. Let's say I have two servers, ServerA and ServerB, and
> there's one database called DatabaseA that exists on both servers.
> Imagine that I have it setup so that a full backup is generated every
> day at midnight, differential backups every hour, and a transaction log
> every 15 minutes. These are copied over to ServerB, and restored into
> the database as they come in. Thus, my database on ServerB is (at most)
> about 15 minutes behind.
> Question: is there any advantage, given this scenario, to doing the full
> backup every night versus once a week? For that matter, why even do the
> differentials every hour? What if I changed it to do the full backup
> once a week, differentials once a day, and kept generating the
> transaction logs every 15 minutes?
Others have more experience of setting backup schedules than I have, but I
would keep the nightly full backup and the log backups, but skip the
differential backups.
One thing of interest here is how far ServerA and ServerB are from each
other. Are they on different sides of town, or in the same computer room?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Hi
You did not state your DB size, but running differentials every hours seems
a bit much to me.
Most people use the:
Full one per week
Differential once per day
Transaction log every 15 minutes.
For a restore, you need your last full backup, the most recent differential
and the transaction logs after the differential to the point in time.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:ON3vTRNrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a question about the scheduling of different backup types under log
> shipping. Let's say I have two servers, ServerA and ServerB, and there's
> one database called DatabaseA that exists on both servers. Imagine that I
> have it setup so that a full backup is generated every day at midnight,
> differential backups every hour, and a transaction log every 15 minutes.
> These are copied over to ServerB, and restored into the database as they
> come in. Thus, my database on ServerB is (at most) about 15 minutes
> behind.
> Question: is there any advantage, given this scenario, to doing the full
> backup every night versus once a week? For that matter, why even do the
> differentials every hour? What if I changed it to do the full backup once
> a week, differentials once a day, and kept generating the transaction logs
> every 15 minutes?
> It seems to me that, so long as I keep the transaction logs in order and
> restore them as they are deposited on ServerB, my data would be just as
> up-to-date as if I did the full and differentials more often -- plus, I'd
> be generating less network traffic, which is a bonus in my scenario.
> I'd appreciate any thoughts or observations ...
> Thanks for the help!
> Wade
>
|||Thanks, Mike.

> You did not state your DB size, but running differentials every hours
> seems a bit much to me.
The sizes are all over the place -- 20 MBytes to 2.7 GBytes. Obviously, if
there's not really a reason to do the fulls more often (which is what it
appears like ...), then the less frequenct the backups occur the better.

> Most people use the:
> Full one per week
> Differential once per day
> Transaction log every 15 minutes.
Yes, exactly what I stated.

> For a restore, you need your last full backup, the most recent
> differential and the transaction logs after the differential to the point
> in time.
... thanks.
Wade
|||> Others have more experience of setting backup schedules than I have, but I
> would keep the nightly full backup and the log backups, but skip the
> differential backups.
I've thought of that too ... but, it seems like having the diffs is a good
catch, because if something happens and the LSN #'s get out of sequence with
the transaction logs (which has happened to me in testing, although I think
I have that resolved) the differential backups can usually clean it up.

> One thing of interest here is how far ServerA and ServerB are from each
> other. Are they on different sides of town, or in the same computer room?
Denver, CO --> Reston, VA. We have a 1 megabit pipe that we're replicating
data over. Perhaps I should have added a bit more of the real world into
the question.
Thanks,
Wade
|||Wade (wwegner23NOEMAILhotmail.com) writes:
> Denver, CO --> Reston, VA. We have a 1 megabit pipe that we're
> replicating data over. Perhaps I should have added a bit more of the
> real world into the question.
That's good. It has to be pretty bug disaster that wipes out both.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||God forbid.
So ... kind sounds like there's no reason to backup anymore than the
following
Full: 1 / week
Diff: 1 / day
Tran: Every 15 minutes
Sound good?
Thanks!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C2811C71153Yazorman@.127.0.0.1...
> Wade (wwegner23NOEMAILhotmail.com) writes:
> That's good. It has to be pretty bug disaster that wipes out both.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>

Log shipping question.

Hi all,
I have a question about the scheduling of different backup types under log
shipping. Let's say I have two servers, ServerA and ServerB, and there's
one database called DatabaseA that exists on both servers. Imagine that I
have it setup so that a full backup is generated every day at midnight,
differential backups every hour, and a transaction log every 15 minutes.
These are copied over to ServerB, and restored into the database as they
come in. Thus, my database on ServerB is (at most) about 15 minutes behind.
Question: is there any advantage, given this scenario, to doing the full
backup every night versus once a week? For that matter, why even do the
differentials every hour? What if I changed it to do the full backup once a
week, differentials once a day, and kept generating the transaction logs
every 15 minutes?
It seems to me that, so long as I keep the transaction logs in order and
restore them as they are deposited on ServerB, my data would be just as
up-to-date as if I did the full and differentials more often -- plus, I'd be
generating less network traffic, which is a bonus in my scenario.
I'd appreciate any thoughts or observations ...
Thanks for the help!
WadeWade (wwegner23NOEMAILhotmail.com) writes:
> I have a question about the scheduling of different backup types under
> log shipping. Let's say I have two servers, ServerA and ServerB, and
> there's one database called DatabaseA that exists on both servers.
> Imagine that I have it setup so that a full backup is generated every
> day at midnight, differential backups every hour, and a transaction log
> every 15 minutes. These are copied over to ServerB, and restored into
> the database as they come in. Thus, my database on ServerB is (at most)
> about 15 minutes behind.
> Question: is there any advantage, given this scenario, to doing the full
> backup every night versus once a week? For that matter, why even do the
> differentials every hour? What if I changed it to do the full backup
> once a week, differentials once a day, and kept generating the
> transaction logs every 15 minutes?
Others have more experience of setting backup schedules than I have, but I
would keep the nightly full backup and the log backups, but skip the
differential backups.
One thing of interest here is how far ServerA and ServerB are from each
other. Are they on different sides of town, or in the same computer room?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
You did not state your DB size, but running differentials every hours seems
a bit much to me.
Most people use the:
Full one per week
Differential once per day
Transaction log every 15 minutes.
For a restore, you need your last full backup, the most recent differential
and the transaction logs after the differential to the point in time.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:ON3vTRNrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a question about the scheduling of different backup types under log
> shipping. Let's say I have two servers, ServerA and ServerB, and there's
> one database called DatabaseA that exists on both servers. Imagine that I
> have it setup so that a full backup is generated every day at midnight,
> differential backups every hour, and a transaction log every 15 minutes.
> These are copied over to ServerB, and restored into the database as they
> come in. Thus, my database on ServerB is (at most) about 15 minutes
> behind.
> Question: is there any advantage, given this scenario, to doing the full
> backup every night versus once a week? For that matter, why even do the
> differentials every hour? What if I changed it to do the full backup once
> a week, differentials once a day, and kept generating the transaction logs
> every 15 minutes?
> It seems to me that, so long as I keep the transaction logs in order and
> restore them as they are deposited on ServerB, my data would be just as
> up-to-date as if I did the full and differentials more often -- plus, I'd
> be generating less network traffic, which is a bonus in my scenario.
> I'd appreciate any thoughts or observations ...
> Thanks for the help!
> Wade
>|||Thanks, Mike.

> You did not state your DB size, but running differentials every hours
> seems a bit much to me.
The sizes are all over the place -- 20 MBytes to 2.7 GBytes. Obviously, if
there's not really a reason to do the fulls more often (which is what it
appears like ...), then the less frequenct the backups occur the better.

> Most people use the:
> Full one per week
> Differential once per day
> Transaction log every 15 minutes.
Yes, exactly what I stated.

> For a restore, you need your last full backup, the most recent
> differential and the transaction logs after the differential to the point
> in time.
... thanks.
Wade|||> Others have more experience of setting backup schedules than I have, but I
> would keep the nightly full backup and the log backups, but skip the
> differential backups.
I've thought of that too ... but, it seems like having the diffs is a good
catch, because if something happens and the LSN #'s get out of sequence with
the transaction logs (which has happened to me in testing, although I think
I have that resolved) the differential backups can usually clean it up.

> One thing of interest here is how far ServerA and ServerB are from each
> other. Are they on different sides of town, or in the same computer room?
Denver, CO --> Reston, VA. We have a 1 megabit pipe that we're replicating
data over. Perhaps I should have added a bit more of the real world into
the question.
Thanks,
Wade|||Wade (wwegner23NOEMAILhotmail.com) writes:
> Denver, CO --> Reston, VA. We have a 1 megabit pipe that we're
> replicating data over. Perhaps I should have added a bit more of the
> real world into the question.
That's good. It has to be pretty bug disaster that wipes out both.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||God forbid.
So ... kind sounds like there's no reason to backup anymore than the
following
Full: 1 / week
Diff: 1 / day
Tran: Every 15 minutes
Sound good?
Thanks!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C2811C71153Yazorman@.127.0.0.1...
> Wade (wwegner23NOEMAILhotmail.com) writes:
> That's good. It has to be pretty bug disaster that wipes out both.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>sql