Log Shipping and maintenance plans

BACKGROUND:
I'm a developer who has just recently been given the additional
responsibility of DBA, we don't have the resources to hire a full-time DBA
yet but we can no longer afford to treat our database so casually. I am
trying to setup a configuration which involves
Replication, Mirroring and Log Shipping. We have a primary database SQL00
where all data entry happens, then we use replication to publish the data to
3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers for
the public web site. We do nightly full backups. We want to add mirroring,
log shipping and a maintenance plan. We will have a 5th server (SQL04) which
will act as a mirror for the primary and a 6th server (SQL05) which will be
the monitor for mirroring. Then we will use log shipping for reporting as
well as a set of "warm" standby servers.
As I am reading about log shipping I understand that I can only have one job
which will backup the transaction log for the primary database which will of
course be the log shipping job. I don't want to have to keep the entire
history of transaction log backups around so I want to do regular full and
differential backups.
QUESTION:
Because I want to do full and differential backups on a regular scheduled
basis I want to know if running a full or differential backup will
invalidate the transaction log backups created by the log shipping job and
cause the log shipping restore job to fail on the secondary. I am assuming
that I should be able to do this, but I still don't fully understand how all
the different backup types work together so I want to make sure that I'm
doing things right.
Thanks in advance for any help,
MarkThe short answer to your question is no, full and differential backups on
the primary database will not invalidate the transaction logs being shipped.
In a log shipping scenario, as soon as the standby server receives the log
backup, it restores it.
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> BACKGROUND:
> I'm a developer who has just recently been given the additional
> responsibility of DBA, we don't have the resources to hire a full-time DBA
> yet but we can no longer afford to treat our database so casually. I am
> trying to setup a configuration which involves
> Replication, Mirroring and Log Shipping. We have a primary database SQL00
> where all data entry happens, then we use replication to publish the data
> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers
> for the public web site. We do nightly full backups. We want to add
> mirroring, log shipping and a maintenance plan. We will have a 5th server
> (SQL04) which will act as a mirror for the primary and a 6th server
> (SQL05) which will be the monitor for mirroring. Then we will use log
> shipping for reporting as well as a set of "warm" standby servers.
> As I am reading about log shipping I understand that I can only have one
> job which will backup the transaction log for the primary database which
> will of course be the log shipping job. I don't want to have to keep the
> entire history of transaction log backups around so I want to do regular
> full and differential backups.
> QUESTION:
> Because I want to do full and differential backups on a regular scheduled
> basis I want to know if running a full or differential backup will
> invalidate the transaction log backups created by the log shipping job and
> cause the log shipping restore job to fail on the secondary. I am assuming
> that I should be able to do this, but I still don't fully understand how
> all the different backup types work together so I want to make sure that
> I'm doing things right.
> Thanks in advance for any help,
> Mark
>|||Mark Miller wrote:
> BACKGROUND:
> I'm a developer who has just recently been given the additional
> responsibility of DBA, we don't have the resources to hire a full-time DBA
> yet but we can no longer afford to treat our database so casually. I am
> trying to setup a configuration which involves
> Replication, Mirroring and Log Shipping. We have a primary database SQL00
> where all data entry happens, then we use replication to publish the data to
> 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers for
> the public web site. We do nightly full backups. We want to add mirroring,
> log shipping and a maintenance plan. We will have a 5th server (SQL04) which
> will act as a mirror for the primary and a 6th server (SQL05) which will be
> the monitor for mirroring. Then we will use log shipping for reporting as
> well as a set of "warm" standby servers.
> As I am reading about log shipping I understand that I can only have one job
> which will backup the transaction log for the primary database which will of
> course be the log shipping job. I don't want to have to keep the entire
> history of transaction log backups around so I want to do regular full and
> differential backups.
> QUESTION:
> Because I want to do full and differential backups on a regular scheduled
> basis I want to know if running a full or differential backup will
> invalidate the transaction log backups created by the log shipping job and
> cause the log shipping restore job to fail on the secondary. I am assuming
> that I should be able to do this, but I still don't fully understand how all
> the different backup types work together so I want to make sure that I'm
> doing things right.
> Thanks in advance for any help,
> Mark
>
Easy answer is "NO", running a full or diff backup does not disrupt the
transaction logs. That said however, since you already have replication
working, I question why you would want to switch to log-shipping for
your reporting needs. Replication is a much better option for a
reporting solution - log shipping makes the database unavailable for a
brief period while doing a restore.
You also state the desire to create a maintenance plan, I assume to
handle index fragmentation? You might be interested in this script:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, James.
You're right, I went and tested this by runing a series of backups and then
restoring them in differnt sequence.
FULL BACKUP #1
add table
LOG BACKUP #1
insert rows in new table
FULL BACKUP #2
insert more rows
LOG BACKUP #2
I tried
RESTORE FULL #1
RESTORE LOG #1
RESTORE LOG #2
and I also tried
RESTORE FULL #2
RESTORE LOG #2
It didn't matter which order I restored them in I always had all the data in
the end.
Thank you for your help.
Mark
"James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
> The short answer to your question is no, full and differential backups on
> the primary database will not invalidate the transaction logs being
> shipped. In a log shipping scenario, as soon as the standby server
> receives the log backup, it restores it.
> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually. I
>> am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database SQL00
>> where all data entry happens, then we use replication to publish the data
>> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting"
>> servers for the public web site. We do nightly full backups. We want to
>> add mirroring, log shipping and a maintenance plan. We will have a 5th
>> server (SQL04) which will act as a mirror for the primary and a 6th
>> server (SQL05) which will be the monitor for mirroring. Then we will use
>> log shipping for reporting as well as a set of "warm" standby servers.
>> As I am reading about log shipping I understand that I can only have one
>> job which will backup the transaction log for the primary database which
>> will of course be the log shipping job. I don't want to have to keep the
>> entire history of transaction log backups around so I want to do regular
>> full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular scheduled
>> basis I want to know if running a full or differential backup will
>> invalidate the transaction log backups created by the log shipping job
>> and cause the log shipping restore job to fail on the secondary. I am
>> assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want to
>> make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
>|||One quick caveat - differential backups (which you mentioned in the first
post) do throw a potential wrench into the works. A differential is always
tied to the last full backup. It is possible to setup a plan (or make an
"out-of-backup-scheme" full backup to restore to a test database or
something) that invalidates a number of differential backups. If you need to
have those type of full backups, you can use the WITH COPY_ONLY clause in
the full backup command. This tell 2005 (and it only works on 2005) to
ignore this full backup as part of the recorded backup chain - essentially
preserving the differential backups.
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:OFN6E7cqGHA.2452@.TK2MSFTNGP03.phx.gbl...
> Thanks, James.
> You're right, I went and tested this by runing a series of backups and
> then restoring them in differnt sequence.
> FULL BACKUP #1
> add table
> LOG BACKUP #1
> insert rows in new table
> FULL BACKUP #2
> insert more rows
> LOG BACKUP #2
> I tried
> RESTORE FULL #1
> RESTORE LOG #1
> RESTORE LOG #2
> and I also tried
> RESTORE FULL #2
> RESTORE LOG #2
> It didn't matter which order I restored them in I always had all the data
> in the end.
> Thank you for your help.
> Mark
> "James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
> message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
>> The short answer to your question is no, full and differential backups on
>> the primary database will not invalidate the transaction logs being
>> shipped. In a log shipping scenario, as soon as the standby server
>> receives the log backup, it restores it.
>> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
>> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually. I
>> am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database
>> SQL00 where all data entry happens, then we use replication to publish
>> the data to 3 other servers (SQL01, SQ02, SQL03) which serve as
>> "reporting" servers for the public web site. We do nightly full backups.
>> We want to add mirroring, log shipping and a maintenance plan. We will
>> have a 5th server (SQL04) which will act as a mirror for the primary and
>> a 6th server (SQL05) which will be the monitor for mirroring. Then we
>> will use log shipping for reporting as well as a set of "warm" standby
>> servers.
>> As I am reading about log shipping I understand that I can only have one
>> job which will backup the transaction log for the primary database which
>> will of course be the log shipping job. I don't want to have to keep the
>> entire history of transaction log backups around so I want to do regular
>> full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular
>> scheduled basis I want to know if running a full or differential backup
>> will invalidate the transaction log backups created by the log shipping
>> job and cause the log shipping restore job to fail on the secondary. I
>> am assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want to
>> make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
>>
>|||Thanks, your answer is what I was looking for. To satisfy curiosity and
answer your question the answer is this:
The database is growing very large and much of the data in the largest
tables may never be updated again, but I have to still provide access to it
on the site. I would like to use table partitioning on the reporting
servers, but because of budget constraints we cannot afford Enterprise
edition. So I am filtering the replication which I hope will give me a
"partition" for current data. For older "archive" data I want to use log
shipping which I can configure to run on a less frequent schedule. I will
have multiple secondaries which will be restored on different schedules so I
will always have 2 which are available for reporting.
As far as maintenance goes I meant that I need to make sure I have regular
backups which I can move offsite incase of disaster.
Thanks,
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e0P7v5cqGHA.4684@.TK2MSFTNGP05.phx.gbl...
> Mark Miller wrote:
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually. I
>> am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database SQL00
>> where all data entry happens, then we use replication to publish the data
>> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting"
>> servers for the public web site. We do nightly full backups. We want to
>> add mirroring, log shipping and a maintenance plan. We will have a 5th
>> server (SQL04) which will act as a mirror for the primary and a 6th
>> server (SQL05) which will be the monitor for mirroring. Then we will use
>> log shipping for reporting as well as a set of "warm" standby servers.
>> As I am reading about log shipping I understand that I can only have one
>> job which will backup the transaction log for the primary database which
>> will of course be the log shipping job. I don't want to have to keep the
>> entire history of transaction log backups around so I want to do regular
>> full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular scheduled
>> basis I want to know if running a full or differential backup will
>> invalidate the transaction log backups created by the log shipping job
>> and cause the log shipping restore job to fail on the secondary. I am
>> assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want to
>> make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
> Easy answer is "NO", running a full or diff backup does not disrupt the
> transaction logs. That said however, since you already have replication
> working, I question why you would want to switch to log-shipping for your
> reporting needs. Replication is a much better option for a reporting
> solution - log shipping makes the database unavailable for a brief period
> while doing a restore.
> You also state the desire to create a maintenance plan, I assume to handle
> index fragmentation? You might be interested in this script:
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks, that will help a lot with "ad hoc" backups!
Mark
"James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
message news:uPJUW$cqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> One quick caveat - differential backups (which you mentioned in the first
> post) do throw a potential wrench into the works. A differential is always
> tied to the last full backup. It is possible to setup a plan (or make an
> "out-of-backup-scheme" full backup to restore to a test database or
> something) that invalidates a number of differential backups. If you need
> to have those type of full backups, you can use the WITH COPY_ONLY clause
> in the full backup command. This tell 2005 (and it only works on 2005) to
> ignore this full backup as part of the recorded backup chain - essentially
> preserving the differential backups.
> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
> news:OFN6E7cqGHA.2452@.TK2MSFTNGP03.phx.gbl...
>> Thanks, James.
>> You're right, I went and tested this by runing a series of backups and
>> then restoring them in differnt sequence.
>> FULL BACKUP #1
>> add table
>> LOG BACKUP #1
>> insert rows in new table
>> FULL BACKUP #2
>> insert more rows
>> LOG BACKUP #2
>> I tried
>> RESTORE FULL #1
>> RESTORE LOG #1
>> RESTORE LOG #2
>> and I also tried
>> RESTORE FULL #2
>> RESTORE LOG #2
>> It didn't matter which order I restored them in I always had all the data
>> in the end.
>> Thank you for your help.
>> Mark
>> "James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
>> message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
>> The short answer to your question is no, full and differential backups
>> on the primary database will not invalidate the transaction logs being
>> shipped. In a log shipping scenario, as soon as the standby server
>> receives the log backup, it restores it.
>> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
>> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually.
>> I am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database
>> SQL00 where all data entry happens, then we use replication to publish
>> the data to 3 other servers (SQL01, SQ02, SQL03) which serve as
>> "reporting" servers for the public web site. We do nightly full
>> backups. We want to add mirroring, log shipping and a maintenance plan.
>> We will have a 5th server (SQL04) which will act as a mirror for the
>> primary and a 6th server (SQL05) which will be the monitor for
>> mirroring. Then we will use log shipping for reporting as well as a set
>> of "warm" standby servers.
>> As I am reading about log shipping I understand that I can only have
>> one job which will backup the transaction log for the primary database
>> which will of course be the log shipping job. I don't want to have to
>> keep the entire history of transaction log backups around so I want to
>> do regular full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular
>> scheduled basis I want to know if running a full or differential backup
>> will invalidate the transaction log backups created by the log shipping
>> job and cause the log shipping restore job to fail on the secondary. I
>> am assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want
>> to make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
>>
>>
>|||Mark Miller wrote:
> Thanks, your answer is what I was looking for. To satisfy curiosity and
> answer your question the answer is this:
> The database is growing very large and much of the data in the largest
> tables may never be updated again, but I have to still provide access to it
> on the site. I would like to use table partitioning on the reporting
> servers, but because of budget constraints we cannot afford Enterprise
> edition. So I am filtering the replication which I hope will give me a
> "partition" for current data. For older "archive" data I want to use log
> shipping which I can configure to run on a less frequent schedule. I will
> have multiple secondaries which will be restored on different schedules so I
> will always have 2 which are available for reporting.
> As far as maintenance goes I meant that I need to make sure I have regular
> backups which I can move offsite incase of disaster.
> Thanks,
> Mark
>
Hi Mark
You'll have to keep in mind that Log Shipping isn't available in SQL2000
Standard edition - you need Enterprise Edition for that.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen Persson (DK) wrote:
> Mark Miller wrote:
>> Thanks, your answer is what I was looking for. To satisfy curiosity
>> and answer your question the answer is this:
>> The database is growing very large and much of the data in the largest
>> tables may never be updated again, but I have to still provide access
>> to it on the site. I would like to use table partitioning on the
>> reporting servers, but because of budget constraints we cannot afford
>> Enterprise edition. So I am filtering the replication which I hope
>> will give me a "partition" for current data. For older "archive" data
>> I want to use log shipping which I can configure to run on a less
>> frequent schedule. I will have multiple secondaries which will be
>> restored on different schedules so I will always have 2 which are
>> available for reporting.
>> As far as maintenance goes I meant that I need to make sure I have
>> regular backups which I can move offsite incase of disaster.
>> Thanks,
>> Mark
> Hi Mark
> You'll have to keep in mind that Log Shipping isn't available in SQL2000
> Standard edition - you need Enterprise Edition for that.
>
Or write your own - it's not too difficult, and easier to troubleshoot...
Tracy McKibben
MCDBA
http://www.realsqlguy.com