{"id":495,"date":"2012-03-04T22:47:18","date_gmt":"2012-03-04T21:47:18","guid":{"rendered":"http:\/\/www.bussink.ch\/?p=495"},"modified":"2018-05-23T18:00:48","modified_gmt":"2018-05-23T16:00:48","slug":"creating-a-maintenance-plan-for-sql-server-2008-r2-for-vcenterupdatemgrvcloud","status":"publish","type":"post","link":"https:\/\/www.bussink.ch\/?p=495","title":{"rendered":"Creating a Maintenance Plan for SQL Server 2008 R2 for vCenter\/UpdateMgr\/vCloud"},"content":{"rendered":"

I shall start by saying that I’m by no way a Database Administrator, but over the years I have picked up some knowledge and I have talked to a few guys that have more Knowledge on the topic to learn small tips & tricks. I have created in previous posts how to quickly create a vCenter Database using Transact-SQL scripts<\/a>, and how to create a vCloud Director database using Transact-SQL script<\/a>. It this small article, I will just resume how to create some Maintenance Plans to ensure that your vCenter\/UpdateManager\/vCloudDirector databases are backed up. I’m not using the Full Recovery model in SQL Server 2008 R2 for my lab and my clients, so these maintenance jobs should be fine. I believe that if you have a large enough environment that is critical to your day-to-day operations, you should use the Full Recovery model, but you would then also have a real Database Administrator onsite that could manage, nurture and keep your databases in proper running condition.<\/p>\n

I have seem my share of transaction log databases for VMware vCenter go haywire, such that the Roll-Up jobs are not running anymore (Check your History Log) and the transaction log databases explodes. My personal worse situation was last year at a client that didn’t check their database and the transaction log database run out of storage on the disk when it passed the 90GB. There are procedures on the VMware Knowledge Base on how to compact and roll-up these huge transaction database, but it takes a lot of time. In most cases, we cut out losses and just purge the transactions logs.<\/p>\n

Coming back to my Maintenance plan. We will create to sets of database maintenance plans, one for the System Dababases and one for the User databases. I need to thank my friend Eric Krejci<\/a> for showing me how to separate the two maintenance plans.<\/p>\n

System Maintenance Plan<\/strong><\/p>\n

We need to connect to our database server using the SQL Server Management Studio program. And from the Management folder, select the Maintenance Plan and start the Wizard.<\/p>\n

\"\"<\/a>

Start Maintenance Plan Wizard<\/p><\/div>\n

The System databases is comprised of the Master, Model, MSDB and TempDB databases. These database don’t change much, but I will select to make a Twice Weekly maintenance and Backup Plan. Please note that the MSDB database contains all the Stored Procedures for your vCenter & Update Manager database.<\/p>\n

\"\"<\/a>

Define Maintenace Plan<\/p><\/div>\n

And let’s Schedule the Plan for two runs per week on Tuesday evening and Friday evening.<\/p>\n

\"\"<\/a>

Job Schedule Properties<\/p><\/div>\n

You can select any other pattern that you wish.I for one also use VMware Data Recovery 2.0 for making daily backup of my virtual machines, so I make sure that my\u00a0 VMware Data Recovery schedule does not run on my databases between 23:00 and 01:00.<\/p>\n

Now we can select the various Maintenance Tasks we want to run.<\/p>\n

\"\"<\/a>

Select Maintenance Tasks<\/p><\/div>\n

I have selected<\/p>\n