Backup Strategy

My client has about 2TB data, are there any recommended
backup strategy? Does SAN / NAS helps?Hi,
The best method for 2 TB database will be,
1. Perform a Diffrential Backup daily once
2. Perform a Transaction Log backup every 30 Minutes (U can change based on
daily growth)
Since the volume is very huge ideal way to keep the backup devices will be
either SAN or NAS.
Thanks
Hari
MCDBA
"Peter Wong" <anonymous@.discussions.microsoft.com> wrote in message
news:0a8d01c3d9a9$d97e0140$a101280a@.phx.gbl...
> My client has about 2TB data, are there any recommended
> backup strategy? Does SAN / NAS helps?|||You might also take a look at filegroup backups, which are intended for very
large databases. These are doc'd in books on line... You might have to move
data around into new filegroups to implement this, but it would allow you to
back up part of the database each night...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter Wong" <anonymous@.discussions.microsoft.com> wrote in message
news:0a8d01c3d9a9$d97e0140$a101280a@.phx.gbl...
> My client has about 2TB data, are there any recommended
> backup strategy? Does SAN / NAS helps?sql

Backup Strategy

My client has about 2TB data, are there any recommended
backup strategy? Does SAN / NAS helps?Hi,
The best method for 2 TB database will be,
1. Perform a Diffrential Backup daily once
2. Perform a Transaction Log backup every 30 Minutes (U can change based on
daily growth)
Since the volume is very huge ideal way to keep the backup devices will be
either SAN or NAS.
Thanks
Hari
MCDBA
"Peter Wong" <anonymous@.discussions.microsoft.com> wrote in message
news:0a8d01c3d9a9$d97e0140$a101280a@.phx.gbl...

quote:

---

> My client has about 2TB data, are there any recommended
> backup strategy? Does SAN / NAS helps?

---

|||You might also take a look at filegroup backups, which are intended for very
large databases. These are doc'd in books on line... You might have to move
data around into new filegroups to implement this, but it would allow you to
back up part of the database each night...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter Wong" <anonymous@.discussions.microsoft.com> wrote in message
news:0a8d01c3d9a9$d97e0140$a101280a@.phx.gbl...

quote:

---

> My client has about 2TB data, are there any recommended
> backup strategy? Does SAN / NAS helps?

---

sql

Backup Software

We are using SAN for the SQL Server 2000.
Someone mentions that since SQL Server is using cache during operation, when
we do the sanpshot of the SAN, the cache value has to be written to SAN. Is
it correct, what is the exact steps for this mechanism ?
Thanks"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:u0bsYJ5iGHA.4884@.TK2MSFTNGP03.phx.gbl...
> We are using SAN for the SQL Server 2000.
> Someone mentions that since SQL Server is using cache during operation,
> when we do the sanpshot of the SAN, the cache value has to be written to
> SAN. Is it correct,
Yes, that is correct. In effect the backup software requests that the
databse flush it's dirty pages to disk and prepare for the snapshot, then to
suspend writes while the snapshot is taken.

>what is the exact steps for this mechanism ?
VSS the Windows Volume Shadow Copy Service.
How Volume Shadow Copy Service Works
http://technet2.microsoft.com/Windo...3.mspx?mfr=true
That's how it should work. If your SAN software doesn't use VSS then you
should not use it to snapshot SQL Server databases.
David|||Dear David,
Thank you for your prompt reply.
Can you elaborate more about dirty pages ? Why they have to be flushed to
the disk ?
Rob
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ekh0FS5iGHA.4884@.TK2MSFTNGP03.phx.gbl...
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:u0bsYJ5iGHA.4884@.TK2MSFTNGP03.phx.gbl...
> Yes, that is correct. In effect the backup software requests that the
> databse flush it's dirty pages to disk and prepare for the snapshot, then
> to suspend writes while the snapshot is taken.
>
> VSS the Windows Volume Shadow Copy Service.
> How Volume Shadow Copy Service Works
> http://technet2.microsoft.com/Windo...3.mspx?mfr=true
>
> That's how it should work. If your SAN software doesn't use VSS then you
> should not use it to snapshot SQL Server databases.
> David
>|||"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:ei3PhW5iGHA.4512@.TK2MSFTNGP02.phx.gbl...
> Dear David,
> Thank you for your prompt reply.
> Can you elaborate more about dirty pages ? Why they have to be flushed to
> the disk ?
>
Dirty pages contain changes that a user has made to the database, but which
haven't been written to the data files yet. These changes have been
commited in the transaction logs, and they are real, complete, durable
transactions. If you don't flush these pages to disk before taking a
snapshot then you would loose data when restoring the database.
David

Backup Software

We are using SAN for the SQL Server 2000.
Someone mentions that since SQL Server is using cache during operation, when
we do the sanpshot of the SAN, the cache value has to be written to SAN. Is
it correct, what is the exact steps for this mechanism ?
Thanks"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:u0bsYJ5iGHA.4884@.TK2MSFTNGP03.phx.gbl...
> We are using SAN for the SQL Server 2000.
> Someone mentions that since SQL Server is using cache during operation,
> when we do the sanpshot of the SAN, the cache value has to be written to
> SAN. Is it correct,
Yes, that is correct. In effect the backup software requests that the
databse flush it's dirty pages to disk and prepare for the snapshot, then to
suspend writes while the snapshot is taken.
>what is the exact steps for this mechanism ?
VSS the Windows Volume Shadow Copy Service.
How Volume Shadow Copy Service Works
http://technet2.microsoft.com/WindowsServer/en/Library/2b0d2457-b7d8-42c3-b6c9-59c145b7765f1033.mspx?mfr=true
That's how it should work. If your SAN software doesn't use VSS then you
should not use it to snapshot SQL Server databases.
David|||Dear David,
Thank you for your prompt reply.
Can you elaborate more about dirty pages ? Why they have to be flushed to
the disk ?
Rob
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ekh0FS5iGHA.4884@.TK2MSFTNGP03.phx.gbl...
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:u0bsYJ5iGHA.4884@.TK2MSFTNGP03.phx.gbl...
>> We are using SAN for the SQL Server 2000.
>> Someone mentions that since SQL Server is using cache during operation,
>> when we do the sanpshot of the SAN, the cache value has to be written to
>> SAN. Is it correct,
> Yes, that is correct. In effect the backup software requests that the
> databse flush it's dirty pages to disk and prepare for the snapshot, then
> to suspend writes while the snapshot is taken.
>>what is the exact steps for this mechanism ?
> VSS the Windows Volume Shadow Copy Service.
> How Volume Shadow Copy Service Works
> http://technet2.microsoft.com/WindowsServer/en/Library/2b0d2457-b7d8-42c3-b6c9-59c145b7765f1033.mspx?mfr=true
>
> That's how it should work. If your SAN software doesn't use VSS then you
> should not use it to snapshot SQL Server databases.
> David
>|||"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:ei3PhW5iGHA.4512@.TK2MSFTNGP02.phx.gbl...
> Dear David,
> Thank you for your prompt reply.
> Can you elaborate more about dirty pages ? Why they have to be flushed to
> the disk ?
>
Dirty pages contain changes that a user has made to the database, but which
haven't been written to the data files yet. These changes have been
commited in the transaction logs, and they are real, complete, durable
transactions. If you don't flush these pages to disk before taking a
snapshot then you would loose data when restoring the database.
David

Backup query ?

Im currently planning to move a Sql Server Database from one Server to another due to SAN errors on the current server.

What I need to know is...

Im currently restoring from last nights backup.. I then plan to apply all the transaction logs from today and tomorrow.
(Up until downtime)

If the database back's up between now and tomorrow will I loose data at the points between the back and the next transaction log backup ??

Im not truncating the transaction log at backup but I just wanted to make sure that ive got this straight before I start the work.

Thanks.You will not loose data between the full backup and the next transactional log backup. That is, of course, everything was done correctly.

Backup of SAN databases

I'm looking for some "best practices" help from the SAN gurus out there.
We're looking to move some of our databases to an EMC SAN. Currently we run
full backups every night and tran logs every 15 minutes, which are then used
by logshipping to restore to warm standby servers.
After moving to the SAN and a clustered environment, the logshipping system
will be dropped. Since the only other reason for backups is DR, there's some
dispute as to whether SQL backups will even be necessary going forward.
We're being told that the SnapView software we're getting with the SAN will
be able to create clones of the production LUNs, from which we can then back
up to tape or restore back to live if/when necessary.
As a long-time DBA it goes against my grain to turn off SQL backups and put
the databases in simple recovery mode <g>. Is that generally how things are
done on a SAN? I should add that I fully understand the "point-in-time"
issue, that is if we take a new clone every night of the live LUNs we may
lose up to 24 hours of transactions if we have to re-sync back. Other than
this issue, are there good or bad sides to SnapView as our primary SQL
backup strategy?
Thanks
Randy Rabin
Hi
No, no, no.
We have a massive SAN environment comprising of EMC and Hitachi, and even
though we use EMC's SRDF to have data moved to DR in real time, we still do
Full backups daily and Log dumps every 15 minutes. I trust EMC to store the
data, but once EMC wants to be the only form of backup I have, I start to
worry. The also have bugs. SnapView and SRDF will faithfully transfer the
corruption that your DB could have to the backup copy as it knows no better.
If you run simple recovery mode, how can you do point in time restores? Most
DB outages are caused by human error like dropping tables, or deleting too
many rows from a table. What it an application error destroys data and then
you get asked, we need a restore up to 10:28 this morning?
Clones of LUNs are not feasible to be run every 15 minutes. Put the dumps on
different LUNS to the data and log. Even an EMC engineer can mess up a
configuration and then you have no good backup.
A SAN is a storage mechanism and not a replacement for good backups.
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/
"Randy Rabin" <randyr@.channeladvisor.com> wrote in message
news:e2B9Pp$WFHA.2080@.TK2MSFTNGP15.phx.gbl...
> I'm looking for some "best practices" help from the SAN gurus out there.
> We're looking to move some of our databases to an EMC SAN. Currently we
> run
> full backups every night and tran logs every 15 minutes, which are then
> used
> by logshipping to restore to warm standby servers.
> After moving to the SAN and a clustered environment, the logshipping
> system
> will be dropped. Since the only other reason for backups is DR, there's
> some
> dispute as to whether SQL backups will even be necessary going forward.
> We're being told that the SnapView software we're getting with the SAN
> will
> be able to create clones of the production LUNs, from which we can then
> back
> up to tape or restore back to live if/when necessary.
> As a long-time DBA it goes against my grain to turn off SQL backups and
> put
> the databases in simple recovery mode <g>. Is that generally how things
> are
> done on a SAN? I should add that I fully understand the "point-in-time"
> issue, that is if we take a new clone every night of the live LUNs we may
> lose up to 24 hours of transactions if we have to re-sync back. Other than
> this issue, are there good or bad sides to SnapView as our primary SQL
> backup strategy?
> Thanks
> Randy Rabin
>

Backup Jobs

I am backing up my SQL Server databases to VDM (SAN) but all my backups are
taking very long some times 12 hours and always failing with the following
error.
failed: 64(The specified network name is no longer available.) [SQLSTATE
42000] (Error 3202) A nonrecoverable I/O error occurred on file
Any help will be appreciated.
Thanks!Get your network and SAN people involved, because the problem is something
that they will have to solve. (If you are also the network and SAN person,
the problem is probably not in your SQL Server.)
This is most likely a bottleneck in either the network layers or the SAN
disk or disk controller throughput. I suspect that at the lower levels of
network and/or disk I/O there are a lot of retries happening, until finally
one of them just cannot recover in time. Then your backup fails because it
can no longer see that the file to which it was backing up.
How much data are you backing up? How big is the largest database? How
long would you expect it to take? You are probably having this problem on
your biggest databases, which put the greatest backup load on your
landscape, and the little databases are having no problems.
RLF
<msnews.microsoft.com> wrote in message
news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
> I am backing up my SQL Server databases to VDM (SAN) but all my backups
> are taking very long some times 12 hours and always failing with the
> following error.
> failed: 64(The specified network name is no longer available.) [SQLSTATE
> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
> Any help will be appreciated.
> Thanks!
>|||Are you watching the network traffic and CPU on both your server and the
SAN?
I recommend you do.
Also, if you backup locally, to disk, how long does it take?
<msnews.microsoft.com> wrote in message
news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
> I am backing up my SQL Server databases to VDM (SAN) but all my backups
> are taking very long some times 12 hours and always failing with the
> following error.
> failed: 64(The specified network name is no longer available.) [SQLSTATE
> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
> Any help will be appreciated.
> Thanks!
>|||What is the best way to do that ?
"Jay" <spam@.nospam.org> wrote in message
news:%23AeAq2EGIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Are you watching the network traffic and CPU on both your server and the
> SAN?
> I recommend you do.
> Also, if you backup locally, to disk, how long does it take?
>
> <msnews.microsoft.com> wrote in message
> news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I am backing up my SQL Server databases to VDM (SAN) but all my backups
>> are taking very long some times 12 hours and always failing with the
>> following error.
>> failed: 64(The specified network name is no longer available.) [SQLSTATE
>> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
>> Any help will be appreciated.
>> Thanks!
>|||I am not a SAN or network specialist, although I work with them, so I cannot
tell you exactly what counters to watch. But you should be able to estimate
the throughput that you should get and compare it to what is actually
happening.
For example, if you expect to backup 150 GB an hour, but the 150 GB backup
fails after 12 hours, then something is obviously wrong. Jay asked 'How
long does the backup run to local disk?' Like my questions, it was an
effort to find out how much your backup landscape is differing from what you
would expect.
RLF
<omranu@.Gmail.com> wrote in message
news:OBjXImYGIHA.5544@.TK2MSFTNGP02.phx.gbl...
> What is the best way to do that ?
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23AeAq2EGIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Are you watching the network traffic and CPU on both your server and the
>> SAN?
>> I recommend you do.
>> Also, if you backup locally, to disk, how long does it take?
>>
>> <msnews.microsoft.com> wrote in message
>> news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I am backing up my SQL Server databases to VDM (SAN) but all my backups
>> are taking very long some times 12 hours and always failing with the
>> following error.
>> failed: 64(The specified network name is no longer available.) [SQLSTATE
>> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
>> Any help will be appreciated.
>> Thanks!
>>
>|||Are you asking how to monitor the network traffic, or how to backup to disk?
The simplest way to monitor network traffic is to watch the Network tab of
the Task Manager. I was, however, refering to the Performance Monitor
available under "Control Panel->Administrative Tools"..
The simplest way to backup to disk is a right-click on the database name in
EM, of MS.
<omranu@.Gmail.com> wrote in message
news:OBjXImYGIHA.5544@.TK2MSFTNGP02.phx.gbl...
> What is the best way to do that ?
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23AeAq2EGIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Are you watching the network traffic and CPU on both your server and the
>> SAN?
>> I recommend you do.
>> Also, if you backup locally, to disk, how long does it take?
>>
>> <msnews.microsoft.com> wrote in message
>> news:u%23CF2Z$FIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I am backing up my SQL Server databases to VDM (SAN) but all my backups
>> are taking very long some times 12 hours and always failing with the
>> following error.
>> failed: 64(The specified network name is no longer available.) [SQLSTATE
>> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
>> Any help will be appreciated.
>> Thanks!
>>
>|||Did you ever find the root cause of this? I am struggling with the same
issue. Windows 2003 x64 / SQL 2005 with SAN backend databases. DB access is
very fast on the attached SAN drives however the SQL backups are way, way
slow. As soon as the job leaves the fiber fabric and moves through the IP
stack and out to the VDM the jobs drag. This is a gig connected box and I am
seeing rates between 5-12MB/s, which is horrible. Staying with in the fiber
frabric is great, but moving out into the IP cloud is horrible. I have tried
all sorts of stuff yet nothing is showing me the problem. The VDM links to a
DataMover that is barely under load; tons of free memory, tons of CPU head
room. I have confirmed with the NE that the edge switches are not under
load, there are no errors on the port, and that the pipes feeding the VDM are
way, way underutilized: 2GB pipe with no more than 40MB/s throughput when the
job is running.
Is there any known issue with 2003 x32 or x64 SP2, SQL2005 and data movement
through a NAS VDM?
~ Jeffrey
"msnews.microsoft.com" wrote:
> I am backing up my SQL Server databases to VDM (SAN) but all my backups are
> taking very long some times 12 hours and always failing with the following
> error.
> failed: 64(The specified network name is no longer available.) [SQLSTATE
> 42000] (Error 3202) A nonrecoverable I/O error occurred on file
> Any help will be appreciated.
> Thanks!
>
>