SQL Server backups in Vsphere server

[Cathy]

SQL Server has always been quite good in providing its own backup facilities
and admins have always been taught to religiously setup backups in case
there is any data loss.

I have on two different occasions in two different companies reviewed their
backup strategies and it would seem that there is a very easy duplication
(if not more) of backups being made.

The thing is most dba's seem to query with their clients, how much needs to
be backed up and how regular backups need to be. They then setup a backup
regime, normally to hard disk, to compliment such a regime. (lets assume 30
days, which could easily mean a full backup weekly and a differential backup
daily plus log files backups to allow for point in time recovery - which I
seem to have observed to almost be a defacto standard)

However nowadays, the infrastructure team have upgraded their hardware into
a vsphere environment with all the benifits that this brings and have
managed to implement an amazing backup regime where all virtual server are
backed up fully on the weekend and incremental backups taken every
weeknight.

My observations.
1. It seems to me that if I restored one instance of a SQL Server in a
Vsphere environment, say for 30 days ago, that restored server will
effectively have 30 days worth of SQL backups on it for 30 days previous.
2. The same could be said for any day I restore to, which should mean, I
have 30 X 30 = 900 backups I could possibly go back to. This seems a mere
waste of resources and overkill
3. Should my vsphere server fail (assuming complete failure) today, and I
restore back to last nights vsphere server, chances are I probably do't have
the log file from today to restore to a given point in time. (I guess the
log file needs to be stored on a different physical drive which in a cloud
of vsphere computing, I often wonder how one knows if it is really a
different physical drive, or should I therefore put it on a different
physical vsphere host?)

My solution
I am therefore thinking the right solution is
Set all databases to Simple and don't bother setting up any backups. It will
boost performance
In the most unlikely event of a failure (since vsphere is almost unbreakable
with multiplr raid arrays and the works), simply restore the vsphere to the
previous night, and lose the data that was changed or advise the people that
changed the data to re-enter the data. (I understand that this might not be
a very plausable solution in say a banking arena, but re-considering my
observations above, I really struggle to justify more than this)

I would like to see others comments on this (I know some will argue against
having SQL running in Vsphere, but I do not wish to get involved in that
argument, I am looking to make it work in a vsphere environment)

TIA for any comments
C

[Erland Sommarskog]

Cathy (Ca...@Nospam.com) writes:
> My solution
> I am therefore thinking the right solution is
> Set all databases to Simple and don't bother setting up any backups. It
> will boost performance In the most unlikely event of a failure (since
> vsphere is almost unbreakable with multiplr raid arrays and the works),
> simply restore the vsphere to the previous night, and lose the data that
> was changed or advise the people that changed the data to re-enter the
> data. (I understand that this might not be a very plausable solution in
> say a banking arena, but re-considering my observations above, I really
> struggle to justify more than this)

If you can afford to lose everything since the last full backup, you can
run in simple recovery, VSphere or not. I don't know what your organisation
you are in, but I doubt that all system owners would accept that solution.

I don't know exactly how VSphere does it, but backing up a database is more
complex than just copying the MDF a file at a certain point. The risk is
that when you restore the Vsphere image, that you get back transactionally
inconsistent and corrupt database. There is certainly reason to test that
the restore works as expected.


--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Cathy]

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message

> If you can afford to lose everything since the last full backup, you can
> run in simple recovery, VSphere or not. I don't know what your
> organisation
> you are in, but I doubt that all system owners would accept that solution.

I think therein lies the crux of the matter. In my humble view many Admins
seem to default to FULL backups with log file backups without considering
what the actual requirement are.

In many cases I would argue that where systems are only utilised
occasionally, say for example a bulliten board on a website which normally
has one posting a week, but has the potential of having say 10 postings or
more in one day.

Depending on the content of the Bulliten board, I would argue that even if I
lost the database on the day that 10 postings were made due to technical
errors, then I could easily do with losing the 10 postings, restoring to the
previous night and issuing a sincere apology on the website for the loss of
posts and asking anybody who posted to re-post their posts.

If on the other hand, I was taking online payments, I could see there is a
distinct argument for taking regular log file backups. However at the same
time, I would be worried that if there is a complete failure of the system,
I would not have the log files stored since the last full backup to tape,
and therefore would implement some way of the system recording all
transactions elsewhere by means of perhaps emailing each financial
transaction or I guess ensuring that such a transaction is recorded on a
remote system to allow me to at least contact all users if there is a system
failure and re-assure them that their transaction is being dealt with
regardless of the system being temporarily unavailable, or the loss of
transaction data once system is restored from tape.

In a banking environment, or on a website like ebay, it is much more
important to have the ability to restore to a specific point in time.

Any thoughts or further debate on this will be appreciated.

> I don't know exactly how VSphere does it, but backing up a database is
> more
> complex than just copying the MDF a file at a certain point. The risk is
> that when you restore the Vsphere image, that you get back transactionally
> inconsistent and corrupt database. There is certainly reason to test that
> the restore works as expected.

Of course its always worth testing to ensure a restore is possible and I
have certainly done this. In my case, since system availability is only
required during office hours, I can afford to shutdown the database and
entire server at midnight. Then have Vsphere backup the server in a shutdown
state and restart the server again.

Since the server is shut down normally, the log file is closed and MDF file
safe to backup. I do actually do a normal backup although I do wonder why,
as simply restoring the Virtuual server should be sufficient.

Out of interest, I originally did this for a Sharepoint server farm
environment for a company intranet. One SQL Server with two Web servers and
one application server all built in VM environment. In this instance we used
powershell to shut down all 4 virtual servers at midnight, then do backups
of the servers in shutdown state and restart the servers after backup.

Sharepoint does offer further backup solutions within the sharepoint, so it
seems silly to have the shrepoint guy run his backups, the sql guy run his
backups and the VM Admin backup, effectively creating three backups every
night.

regards
C

[Erland Sommarskog]

Cathy (Ca...@Nospam.com) writes:
> I think therein lies the crux of the matter. In my humble view many Admins
> seem to default to FULL backups with log file backups without considering
> what the actual requirement are.

The sad truth is that many DBA has a backup strategy, but no restore
strategy.

If you see your IT Department as a company within your company that
offers services to your customers, you should have SLA (Service Level
Agreements) with RPO (Recovery Point Objective) and RTO (Recovery Time
Objective). That is, in case of a failure, how much data do you accept
to lose? How much downtime can you accept?

Backups is only one piece in the puzzle. For critical systems, a restore
is not likely to fit in to the RTO, so you need HA solutions like
mirroring or availability groups.

But since you offer a services - and charge for it - it is up to the
customer. If a department thinks their system is very important that
data loss is out of the question and RTO in case of a total failure
is one hour, they will have to pay through the nose.

> Of course its always worth testing to ensure a restore is possible and I
> have certainly done this. In my case, since system availability is only
> required during office hours, I can afford to shutdown the database and
> entire server at midnight. Then have Vsphere backup the server in a
> shutdown state and restart the server again.

Yes, if the VM is shutdown and SQL Server is shutdown cleanly, I think a
VSphere backup should be fine.

I made my point, because I'm afraid many people take backups, but they
never test their backups, and then one day there is a disaster, they find
that something is broken - or that they simply do not understand the
routines.

I once helped one of our customers when their disk went belly-up. My plan
was to restore to a point in time some half an hour before the complete
failure, but I found that the backup jobs were set up so that one
piece of the log backup was dropped. Thankfully, the failure happend
on a Saturday morning, so the hours lost were not critical - but if
the failure had happened on a Wednesday 21:00, they would have been
in very big problems. (And given the accelerating corruption going
on, it was probably just as well that I could not apply the transaction
log.)