William
Hoping you can advise me on best way to handle this.
My client doesn’t want to use SQL backups. Instead,
they are using VM and a third party backup software by choice.
Without a DBA on staff, they would
prefer to deal with SQL the less the better.
They do not plan on doing any transaction logs and are
not concerned with restore from point of failure.
...
Goal would be to not have transaction logs or somehow
keep those logs small.
Colleague
Here's how I replied.
So, the first question to ask a
client who doesn't want transaction log backups is, "why?"
The transaction log is not
something to be afraid of. Backing up the transaction log is a simple process
and clears out the log. Backup the log at regular intervals (somewhere between
15 and 60min is typical) and there is no problems. Then, you get all the
advantages of point-in-time recovery, so if you have a 5:01pm disaster, you
could restore all the way to 5:00pm, for example. Plus, full recovery mode is
required for most SQL-based HA/DR scenarios.
Using a third party backup software is fine, but
they must have purchased the SQL plugin. This is usually an extra cost add-on for whatever backup license they bought. You CANNOT backup an .mdf file with a file system backup and expect that to work. SQL MUST BACKUP SQL.
That
SQL plugin does exactly that for the third party software - sends commands to SQL to perform a
backup. It's fine if they don't want to use sql maintenance plans or jobs to
perform the backups, but they must use the sql plugin for any 3rd party
enterprise backup software.
With a database in full
recovery mode, you must perform full and transaction log backups, or the log
will grow.
Typically, you do a nightly full and hourly tran log backups during
business hours. The reason people get scared/confused/frustrated with
transaction logs is that they don't realize you are supposed to back up the log
(which empties it) regularly. As in, hourly at most. If they want the log to
stay small, back it up every 15 minutes. BackupExec can do that.
If they are really stubborn
about not wanting to use all the DR capability that they paid for, putting the
database in simple mode is fine. Just full sql backups are needed then. In simple
mode, transactions aren't stored in the log after they commit. A transaction
commits, there's a checkpoint, and then it is wiped from the log. A simple
recovery mode database will have its transaction log grow to the size of the
biggest single concurrent set of transactions at any given time.
Never truncate a log file.
Perform a log file backup, which will empty the file. The file may still be
large though it is empty, in this case if you absolutely need the space back,
you can shrink the log file. Never shrink a data or log file on a schedule. It
creates fragmentation which hurts performance.
Let me know if you have any
questions!
-William
