Video Screencast Help

BE2010: Best Practice for a virtual MS SQL 2005 Server

Created: 26 Feb 2010 • Updated: 22 May 2010 | 7 comments
This issue has been solved. See solution.

Hi

I have a Windows Server 2008 R2 x64 running SQL Server 2005 on a Virtual Machine. The databases on that server are using the Full Recovery Model, which means that I need to perform Full DB Backups as well as Log Backups and truncate the Logs at some point.

I've tried the VMware Application GRT for SQL Servers which works smoothly for Full Backups. However, the Log files don't get truncated after the Backups, since no Log Backup Job has been run.

My question is: How do you backup your full recovery model DBs on a virtual MS SQL Server? Do you use the Windows Agent and treat the server like a physical machine? Or do you have a cronjob running on the SQL Server itsself to perform a Log-Backup? Or is there even a possibility to do a Log Backup Job via the VMware Agent? (I couldn't find any...)

Before the VMware Agent, I ran full backups at nights and a Log Backup during lunch break...

Thanks for your inputs,

fiNal

Comments 7 CommentsJump to latest comment

teiva-boy's picture

 I would suggest running a separate job perhaps in the policy to just run a LOG backup.  Or truncate it via SQL commands via scheduled tasks/triggers in SQL

There is an online portal, save yourself the long hold times. Create ticket online, then call in with ticket # in hand :-) http://mysupport.symantec.com "We backup data to restore, we don't backup data just to back it up."

BernyR's picture

 Hi final,
SQL Server doesn't know or care if's it's on a virtual machine or physical machine.  Just treat it as if it were any other database server.

Berny

fiNal's picture

@teiva-boy: this job unfortunately fails with this error:

Final error: 0xe0000363 - The Backup Exec SQL Agent was not used to create the last full, differential, or log backup of this database. You must use the SQL Agent to run a full backup before you can run a differential backup or transaction log backup.
Final error category: Resource Errors
For additional information regarding this error refer to link V-79-57344-867

It seems like VMware AppGRT does not use the Windows Remote Agent to Backup the DBs. Therefore, a "normal" Log Job doesn't run through.

@BernyR: I'm very much aware of that. Do you suggest then that I should not use the VMware Agent for my SQL Server and rather back it up as if it was a physical machine?

Dev T's picture

Hello,

For SQL Database Backups we will need a license for SQL Agent.

Its a best practice to have a "Support Contract" with Symantec...
 

BEsymc's picture

OK, with AVVI agent, you will not be able to run log backup. Also, in order to run a log backup outside AVVI agent, you will need to perform a full backup using sql agent. AVVI backup is not recognized as a sql agent backup by SQL Agent. Hence, the limitation. So, the only solution,  is to perform a full backup of sql database using sql agent, followed by log backup. If you do an AVVI backup, you might have to perform a full backup using SQL Agent again, so that log backup succeeds. Currently, this is the default behaviour of AVVI Agent in Backup Exec 2010.

Please mark it a solution, if this is useful.
Thanks

SOLUTION
fiNal's picture

Okay ... that is a serious design flaw in my opinion ... it makes an otherwise nice feature completely useless.

I have now set up a cronjob which backups and truncates the logs via sqlcmd ... the created .bak file is then backuped by BE with a very small file job during the lunch break. At nights, I'm still running a full backup via AVVI.

teiva-boy's picture

Please refer to the Admin Guide.

The guide, within the chapter about AVVI, says otherwise to the "solution."

Refer to pg 1346 and 1347 

There is an online portal, save yourself the long hold times. Create ticket online, then call in with ticket # in hand :-) http://mysupport.symantec.com "We backup data to restore, we don't backup data just to back it up."