backup strategy for mirroring servers

Hi guys.
We have a DB system that is relatively small and transactions are not very
big, but very important, losing data will be very costly to the business.
I am assigned to port this system to SQL Server 2005 Sp1, we have decided to
implement the DB mirroring with the High Protection mode.
I have some questions regarding backup and restore.
Suppose we utilize 3 machines, A is the principal server, B is the mirroring
server, and C is the file server on which the backup files are stored.
My questions are:
1. Currently I have created 3 SQL Server Agent jobs to backup the principal
server, a) full backup once a day, b) differential backup once every 4 hours
,
c) transaction log back once every 15 minutes. The question is: should I
change the backup file (device) every day? Or I can use one backup
file(device) for all the backups day in and day out?
2. How do I backup the mirroring server? I think I can not do anything on
the mirroring server when it is in the Mirroring/Sync mode. And if I had the
same 3 agent jobs on the mirroring server, the jobs would fail? But what if
the principal server fails over, and mirroring server becomes the principal
server, do I have to create the backup agent jobs after failover?
3. When creating the mirroring server backup, can I reuse the same backup
file name(s) that I used on the principal server? or I better off storing th
e
backup file from the mirroring server on a different location?
4. Last question, not particular related to backup Should I store the
.MDF/.LDF file for the principal server and/or mirror server on machine C?
Thanks a lot!
WenbiaoSee comments inline below:
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
> Hi guys.
> We have a DB system that is relatively small and transactions are not very
> big, but very important, losing data will be very costly to the business.
> I am assigned to port this system to SQL Server 2005 Sp1, we have decided
to
> implement the DB mirroring with the High Protection mode.
> I have some questions regarding backup and restore.
> Suppose we utilize 3 machines, A is the principal server, B is the mirrori
ng
> server, and C is the file server on which the backup files are stored.
> My questions are:
> 1. Currently I have created 3 SQL Server Agent jobs to backup the principa
l
> server, a) full backup once a day, b) differential backup once every 4 hou
rs,
> c) transaction log back once every 15 minutes. The question is: should I
> change the backup file (device) every day? Or I can use one backup
> file(device) for all the backups day in and day out?
You have to decide this for yourself. You most probably want a few generatio
ns of the backups, and
whether to only have those on tape and also disk will influence this. I assu
me you are aware of the
INIT and NOINIT options.

> 2. How do I backup the mirroring server? I think I can not do anything on
> the mirroring server when it is in the Mirroring/Sync mode. And if I had t
he
> same 3 agent jobs on the mirroring server, the jobs would fail? But what i
f
> the principal server fails over, and mirroring server becomes the principa
l
> server, do I have to create the backup agent jobs after failover?
Run the same job on both servers. Have a preceeding jobstep which check the
mirroring catalog view
whether that server is primary or not. If not primary, exit with success, el
se do the backup.

> 3. When creating the mirroring server backup, can I reuse the same backup
> file name(s) that I used on the principal server? or I better off storing
the
> backup file from the mirroring server on a different location?
Basically same answer as 1. Logicaly, it doesn't matter from what machine th
e backup came. This
would work in faviour for using the same backup devices.

> 4. Last question, not particular related to backup Should I store the
> .MDF/.LDF file for the principal server and/or mirror server on machine C?
No, SQL Server doesn't support storing files on a mapped/UNC drive. Need to
be local, SAN or ISCSI.

> Thanks a lot!
> Wenbiao|||Tibor is correct that you cannot store database files on a UNC share,
however, you can store the backup files on a UNC share location.
Personally, I use a script to create a new backup file on a remote share for
each backup using a date and time stamp as part of the file name (just like
a DB maintenance plan). I have a separate job to clean out old backups
which makes it easy to adjust the retention time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ugBYISWwGHA.4972@.TK2MSFTNGP05.phx.gbl...
> See comments inline below:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
> news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
> You have to decide this for yourself. You most probably want a few
> generations of the backups, and whether to only have those on tape and
> also disk will influence this. I assume you are aware of the INIT and
> NOINIT options.
>
> Run the same job on both servers. Have a preceeding jobstep which check
> the mirroring catalog view whether that server is primary or not. If not
> primary, exit with success, else do the backup.
>
> Basically same answer as 1. Logicaly, it doesn't matter from what machine
> the backup came. This would work in faviour for using the same backup
> devices.
>
> No, SQL Server doesn't support storing files on a mapped/UNC drive. Need
> to be local, SAN or ISCSI.
>
>

backup strategy for mirroring servers

Hi guys.
We have a DB system that is relatively small and transactions are not very
big, but very important, losing data will be very costly to the business.
I am assigned to port this system to SQL Server 2005 Sp1, we have decided to
implement the DB mirroring with the High Protection mode.
I have some questions regarding backup and restore.
Suppose we utilize 3 machines, A is the principal server, B is the mirroring
server, and C is the file server on which the backup files are stored.
My questions are:
1. Currently I have created 3 SQL Server Agent jobs to backup the principal
server, a) full backup once a day, b) differential backup once every 4 hours,
c) transaction log back once every 15 minutes. The question is: should I
change the backup file (device) every day? Or I can use one backup
file(device) for all the backups day in and day out?
2. How do I backup the mirroring server? I think I can not do anything on
the mirroring server when it is in the Mirroring/Sync mode. And if I had the
same 3 agent jobs on the mirroring server, the jobs would fail? But what if
the principal server fails over, and mirroring server becomes the principal
server, do I have to create the backup agent jobs after failover?
3. When creating the mirroring server backup, can I reuse the same backup
file name(s) that I used on the principal server? or I better off storing the
backup file from the mirroring server on a different location?
4. Last question, not particular related to backup :) Should I store the
.MDF/.LDF file for the principal server and/or mirror server on machine C?
Thanks a lot!
WenbiaoSee comments inline below:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
> Hi guys.
> We have a DB system that is relatively small and transactions are not very
> big, but very important, losing data will be very costly to the business.
> I am assigned to port this system to SQL Server 2005 Sp1, we have decided to
> implement the DB mirroring with the High Protection mode.
> I have some questions regarding backup and restore.
> Suppose we utilize 3 machines, A is the principal server, B is the mirroring
> server, and C is the file server on which the backup files are stored.
> My questions are:
> 1. Currently I have created 3 SQL Server Agent jobs to backup the principal
> server, a) full backup once a day, b) differential backup once every 4 hours,
> c) transaction log back once every 15 minutes. The question is: should I
> change the backup file (device) every day? Or I can use one backup
> file(device) for all the backups day in and day out?
You have to decide this for yourself. You most probably want a few generations of the backups, and
whether to only have those on tape and also disk will influence this. I assume you are aware of the
INIT and NOINIT options.
> 2. How do I backup the mirroring server? I think I can not do anything on
> the mirroring server when it is in the Mirroring/Sync mode. And if I had the
> same 3 agent jobs on the mirroring server, the jobs would fail? But what if
> the principal server fails over, and mirroring server becomes the principal
> server, do I have to create the backup agent jobs after failover?
Run the same job on both servers. Have a preceeding jobstep which check the mirroring catalog view
whether that server is primary or not. If not primary, exit with success, else do the backup.
> 3. When creating the mirroring server backup, can I reuse the same backup
> file name(s) that I used on the principal server? or I better off storing the
> backup file from the mirroring server on a different location?
Basically same answer as 1. Logicaly, it doesn't matter from what machine the backup came. This
would work in faviour for using the same backup devices.
> 4. Last question, not particular related to backup :) Should I store the
> .MDF/.LDF file for the principal server and/or mirror server on machine C?
No, SQL Server doesn't support storing files on a mapped/UNC drive. Need to be local, SAN or ISCSI.
> Thanks a lot!
> Wenbiao|||Tibor is correct that you cannot store database files on a UNC share,
however, you can store the backup files on a UNC share location.
Personally, I use a script to create a new backup file on a remote share for
each backup using a date and time stamp as part of the file name (just like
a DB maintenance plan). I have a separate job to clean out old backups
which makes it easy to adjust the retention time.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ugBYISWwGHA.4972@.TK2MSFTNGP05.phx.gbl...
> See comments inline below:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
> news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
>> Hi guys.
>> We have a DB system that is relatively small and transactions are not
>> very
>> big, but very important, losing data will be very costly to the business.
>> I am assigned to port this system to SQL Server 2005 Sp1, we have decided
>> to
>> implement the DB mirroring with the High Protection mode.
>> I have some questions regarding backup and restore.
>> Suppose we utilize 3 machines, A is the principal server, B is the
>> mirroring
>> server, and C is the file server on which the backup files are stored.
>> My questions are:
>> 1. Currently I have created 3 SQL Server Agent jobs to backup the
>> principal
>> server, a) full backup once a day, b) differential backup once every 4
>> hours,
>> c) transaction log back once every 15 minutes. The question is: should I
>> change the backup file (device) every day? Or I can use one backup
>> file(device) for all the backups day in and day out?
> You have to decide this for yourself. You most probably want a few
> generations of the backups, and whether to only have those on tape and
> also disk will influence this. I assume you are aware of the INIT and
> NOINIT options.
>
>> 2. How do I backup the mirroring server? I think I can not do anything on
>> the mirroring server when it is in the Mirroring/Sync mode. And if I had
>> the
>> same 3 agent jobs on the mirroring server, the jobs would fail? But what
>> if
>> the principal server fails over, and mirroring server becomes the
>> principal
>> server, do I have to create the backup agent jobs after failover?
> Run the same job on both servers. Have a preceeding jobstep which check
> the mirroring catalog view whether that server is primary or not. If not
> primary, exit with success, else do the backup.
>
>> 3. When creating the mirroring server backup, can I reuse the same backup
>> file name(s) that I used on the principal server? or I better off storing
>> the
>> backup file from the mirroring server on a different location?
> Basically same answer as 1. Logicaly, it doesn't matter from what machine
> the backup came. This would work in faviour for using the same backup
> devices.
>
>> 4. Last question, not particular related to backup :) Should I store the
>> .MDF/.LDF file for the principal server and/or mirror server on machine
>> C?
> No, SQL Server doesn't support storing files on a mapped/UNC drive. Need
> to be local, SAN or ISCSI.
>
>> Thanks a lot!
>> Wenbiao
>

Backup strategy

I've recently inherited a position where I am responsible for the well-being
of some DBs.
2 (much) more important than others.
The current recovery model, from what I can tell, is to do a full db/log
backup overnight.
This .bak file is then written to tape as well as saved on the disk for 2
days.

Both these dbs are used fairly extensively 8-5pm and losing data would not
be good.
The db sizes are approx 5gb and 3gb.
This doesn't seem like the ideal situation to me. Everything I read tells
me... full backup periodically, differential nightly and transaction hourly.
Agreed?
If so then I have 2 questions:
1. Is the best way to do this via a maintenance plan or by scripting and
scheduling?
2. What, if any, overhead can be expected with regular transaction backups
during work hours?

A bit of a pointer to #1 would be appreciated also.
Thanks.Both the databases are quite small, so using full, differential and log
backups may be overkill - you might find that a full backup once or
twice a day with log backups every hour (or whatever) is fine. On the
other hand, if the databases are growing fast, then you may want to
allow for that from the beginning.

In my opinion, maintenance plans are useful for quickly getting backups
running in a new environment, but their functionality and flexibility
can be limited. So I would probably use them as a starting point while
I worked out what the longer term backup plan should be. For example, I
don't believe that maintenance plans support differential backups, so
if you need them then you would have to create your own jobs anyway.
See "Reducing Recovery Time" in Books Online for details of using
different backup types together.

The overhead of a transaction log backup is likely to be minimal, but
as always, it's best to test it yourself in your own environment. It
would depend on the number of transactions, the disk layout and the
destination for the backups etc. But even if there is some slight
impact, it's probably a small price to pay for having the backups
available in the case of an outage.

Simon|||Jay,

The only thing I use the maintenance jobs for are two (2) tasks.
1. checking the consistency of the database (usually at 11pm on Sat)
2. doing the reindex of the database (usally at 1 am on Fri)

The rest of the backups I script manually. I create a backup device for
each database.
Job #1 - Full Backup of the database (usually at 6pm each night)
Job #2 - Transaction log backup every hour from 7am to 5pm.
Job #3 - Full backup of the database to a separate device at 12 noon (not
all databases)

Oscar...

"Jay" <jazemail@.gmail.com> wrote in message
news:Hqepe.5753$U4.811596@.news.xtra.co.nz...
> I've recently inherited a position where I am responsible for the
well-being
> of some DBs.
> 2 (much) more important than others.
> The current recovery model, from what I can tell, is to do a full db/log
> backup overnight.
> This .bak file is then written to tape as well as saved on the disk for 2
> days.
> Both these dbs are used fairly extensively 8-5pm and losing data would not
> be good.
> The db sizes are approx 5gb and 3gb.
> This doesn't seem like the ideal situation to me. Everything I read tells
> me... full backup periodically, differential nightly and transaction
hourly.
> Agreed?
> If so then I have 2 questions:
> 1. Is the best way to do this via a maintenance plan or by scripting and
> scheduling?
> 2. What, if any, overhead can be expected with regular transaction backups
> during work hours?
> A bit of a pointer to #1 would be appreciated also.
> Thanks.|||"Oscar Santiesteban Jr." <oscarsantiesteban@.worldnet.att.net> wrote in
message news:Qgtpe.912601$w62.11314@.bgtnsc05-news.ops.worldnet.att.net...
> Jay,
> The only thing I use the maintenance jobs for are two (2) tasks.
> 1. checking the consistency of the database (usually at 11pm on Sat)
> 2. doing the reindex of the database (usally at 1 am on Fri)

Forgive me if I don't follow your practise. Those hours are the only hours
I'm *not* thinking about DBs :)
I've learned that the transaction log is written to 2 HDD (raid) so it isn't
as bad as I thought.
As a precaution I now back up the transaction log to a USB drive each
lunchtime.

Cheers
Jay

> The rest of the backups I script manually. I create a backup device for
> each database.
> Job #1 - Full Backup of the database (usually at 6pm each night)
> Job #2 - Transaction log backup every hour from 7am to 5pm.
> Job #3 - Full backup of the database to a separate device at 12 noon (not
> all databases)
> Oscar...
> "Jay" <jazemail@.gmail.com> wrote in message
> news:Hqepe.5753$U4.811596@.news.xtra.co.nz...
>> I've recently inherited a position where I am responsible for the
> well-being
>> of some DBs.
>> 2 (much) more important than others.
>> The current recovery model, from what I can tell, is to do a full db/log
>> backup overnight.
>> This .bak file is then written to tape as well as saved on the disk for 2
>> days.
>>
>> Both these dbs are used fairly extensively 8-5pm and losing data would
>> not
>> be good.
>> The db sizes are approx 5gb and 3gb.
>> This doesn't seem like the ideal situation to me. Everything I read tells
>> me... full backup periodically, differential nightly and transaction
> hourly.
>> Agreed?
>> If so then I have 2 questions:
>> 1. Is the best way to do this via a maintenance plan or by scripting and
>> scheduling?
>> 2. What, if any, overhead can be expected with regular transaction
>> backups
>> during work hours?
>>
>> A bit of a pointer to #1 would be appreciated also.
>> Thanks.
>>
>>|||"Jay" <jazemail@.gmail.com> wrote in message
news:Hqepe.5753$U4.811596@.news.xtra.co.nz...
> I've recently inherited a position where I am responsible for the
well-being
> of some DBs.
> 2 (much) more important than others.
> The current recovery model, from what I can tell, is to do a full db/log
> backup overnight.
> This .bak file is then written to tape as well as saved on the disk for 2
> days.
> Both these dbs are used fairly extensively 8-5pm and losing data would not
> be good.
> The db sizes are approx 5gb and 3gb.
> This doesn't seem like the ideal situation to me. Everything I read tells
> me... full backup periodically, differential nightly and transaction
hourly.
> Agreed?
> If so then I have 2 questions:
> 1. Is the best way to do this via a maintenance plan or by scripting and
> scheduling?

For this I'd use a simple maintenance plan to back up your logs every X
minutes.

> 2. What, if any, overhead can be expected with regular transaction backups
> during work hours?

VERY little.

We run our transaction log backups every 20 minutes and notice no impact.

(in fact, ironically in some cases, running them MORE often can be better
since they'll be smaller when you do run them.)

> A bit of a pointer to #1 would be appreciated also.
> Thanks.