Near-Continuous replication and database growth

VMware specific discussions

Near-Continuous replication and database growth

Veeam Logoby mlinders » Fri Aug 02, 2013 11:46 am

Hi all, just putting this one here to get some additional insights. It touches a mix of VMware and Veeam.

We just implemented a near-continuous replication solution for one of our clients and have noticed some things that I would have expected to show up in the manual. The setup is fairly simple, there is an HQ where all the production VM's are running. Besides that we have a DR site. On this site we have Veeam B&R running. We also have an install of Veeam B&R in the HQ site for proxy functionality and local backup if neccessary. Our VM's are located on SAN and everything is working prety fine.

Some important VM's (about 4) are replicated near-continuous from HQ to DR-Site. The replication jobs are initiated from our DR site which connects to the vCenter server in the HQ. What we noticed is that the vCenter database is quickly filling up with Events and Tasks rows in de table. We limited the retention for these events to 3 days, but even that was too large for the Express SQL database that was underneath. The problem is that vCenter tries to run a cleanup every six hours of Events and Tasks using a stored procedure. The massive amount of events and tasks would cause the vCenter database to fill up it's transaction log (DB mode is simple, but still uses Tlog's for long running queries). This would break the near-continous replication and causes Veeam temporary snapshots nested inside Veeam temporary snapshots. After changing the events and tasks retention to 1 day and truncating the tables with help of VMware support it seems stable.

We thought we were out of the woods and all was peachy again, unfortunatly we replicate to a vCenter server in te DR site. So this DR site is where the _replica VM's are residing with some restore points for each VM (28-points per VM). What we noticed is that one table in the vCenter database (VPX_INT_ARRAY) keeps growing steadily with 50.000 rows a day. I have been told by VMware that this table cannot be truncated and that it is used to keep VM information stored. We thought offcourse that it was due to the fact we were specifying the vCenter as target for our replica's, so we changed this to target the ESX(i) host instead of the vCenter server, but this changes nothing. vCenter still notices all the VM's being handled by the replication and starts growing again. The moment we stop replication, the table stops growing.

To make a long story short:
- Do you see this same massive growth in the vCenter database tables when using near-continuous replication? And how do you manage it?
- Have you come into the situation where events and tasks history (as low as 3 days) is causing transaction logs to fill up.

* It's a small environment with 3 hosts in the HQ and about 20 VM's)

# 00264477
mlinders
Veeam ProPartner
 
Posts: 26
Liked: 8 times
Joined: Sun Apr 22, 2012 10:42 pm
Location: the Netherlands
Full Name: Mark Linders

Re: Near-Continuous replication and database growth

Veeam Logoby vcocaud » Tue Sep 03, 2013 4:54 pm

Hi,

Same problem here.
Veeam 6.5, continuous replication of ~15VMs to same vCenter as source (different SAN).

vCenter Database is growing since 4GB limit (SQL 2005 Express) and vCenter crash ...

I've not found any solution for now.
vcocaud
Lurker
 
Posts: 1
Liked: never
Joined: Tue Sep 03, 2013 4:50 pm
Full Name: Valentin COCAUD

Re: Near-Continuous replication and database growth

Veeam Logoby CaptainFred » Fri Sep 06, 2013 8:57 pm

Hi,

We might use continuous replication in the future so when I read this I was a bit concerned. Forgive my ignorance but when you say "truncating the tables" do you mean using a SQL script to clear out entries? Or does this break everything?

Also for interest, how much data is changing at each replication run and how much is being transferred?
CaptainFred
Enthusiast
 
Posts: 88
Liked: 2 times
Joined: Wed Jul 31, 2013 12:05 pm
Full Name: Si

Re: Near-Continuous replication and database growth

Veeam Logoby Vitaliy S. » Sat Sep 07, 2013 4:19 pm

Valentin, if the vCenter Server generates too much data and outgrows the max size of the database, you might either set a smaller retention period to our vCenter Server database or migrate this database to SQL Server 2008/2012 Express, this will give you 10 GBs limit for the database.
Vitaliy S.
Veeam Software
 
Posts: 19558
Liked: 1102 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: Near-Continuous replication and database growth

Veeam Logoby mlinders » Sun Sep 08, 2013 11:34 pm

CaptainFred wrote:Hi,

We might use continuous replication in the future so when I read this I was a bit concerned. Forgive my ignorance but when you say "truncating the tables" do you mean using a SQL script to clear out entries? Or does this break everything?

Also for interest, how much data is changing at each replication run and how much is being transferred?


Hi CaptainFred,

- With Truncating the tables i mean SQL script to clear out entries. Basically deleting database records. Mostly these were Tasks and Events entries.
- VMWare has instructed me that VPXINTARRAY should not be truncated as this might possibly break things. This has been growing steadily to well over 3 million records.
- Veeam Replication finishes a run about every 5 minutes. That is including the create and commit snapshot operation, you might want to test impact on your operation first, since the snapshot commit every five minutes might be quite disruptive to Tier 1 applications like Exchange for instance. We see that as a few seconds freeze in Exchange responsiveness every 5 minutes. (SSD has much less freeze/stun then SAS, SATA, SAN, etc.) There is very little data being transfered over the line since CBT keeps track of what has changed and there isnt that much of a change in 5 minutes.

Hope this helps.
mlinders
Veeam ProPartner
 
Posts: 26
Liked: 8 times
Joined: Sun Apr 22, 2012 10:42 pm
Location: the Netherlands
Full Name: Mark Linders

Re: Near-Continuous replication and database growth

Veeam Logoby v.Eremin » Mon Sep 09, 2013 8:10 am

Additionally, it might be worth putting into use new parallel processing functionality that is supposed to reduce the time it takes to process Exchange VM and, therefore, lower the time of snapshot commit operation. Thanks.
v.Eremin
Veeam Software
 
Posts: 13266
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Near-Continuous replication and database growth

Veeam Logoby CaptainFred » Mon Sep 09, 2013 9:32 am

mlinders wrote:
CaptainFred wrote:Hi,

We might use continuous replication in the future so when I read this I was a bit concerned. Forgive my ignorance but when you say "truncating the tables" do you mean using a SQL script to clear out entries? Or does this break everything?

Also for interest, how much data is changing at each replication run and how much is being transferred?


Hi CaptainFred,

- With Truncating the tables i mean SQL script to clear out entries. Basically deleting database records. Mostly these were Tasks and Events entries.
- VMWare has instructed me that VPXINTARRAY should not be truncated as this might possibly break things. This has been growing steadily to well over 3 million records.
- Veeam Replication finishes a run about every 5 minutes. That is including the create and commit snapshot operation, you might want to test impact on your operation first, since the snapshot commit every five minutes might be quite disruptive to Tier 1 applications like Exchange for instance. We see that as a few seconds freeze in Exchange responsiveness every 5 minutes. (SSD has much less freeze/stun then SAS, SATA, SAN, etc.) There is very little data being transfered over the line since CBT keeps track of what has changed and there isnt that much of a change in 5 minutes.

Hope this helps.


Ah yes thanks

v.Eremin wrote:Additionally, it might be worth putting into use new parallel processing functionality that is supposed to reduce the time it takes to process Exchange VM and, therefore, lower the time of snapshot commit operation. Thanks.


What's that? How do we use it?
CaptainFred
Enthusiast
 
Posts: 88
Liked: 2 times
Joined: Wed Jul 31, 2013 12:05 pm
Full Name: Si

Re: Near-Continuous replication and database growth

Veeam Logoby v.Eremin » Mon Sep 09, 2013 9:36 am

With the introduction of this option, VB&R can process multiple VMs and VM disks in parallel, this’s supposed to reduce backup window and increase backup performance rate.

You should go to the Menu -> Options -> Advanced -> Enable parallel processing. Though, please be aware that this option is likely to be helpful if your Exchange server has more than one virtual disk and, also, if you have enough resource to perform more than one concurrent task.

More information regarding it can be found in the corresponding User Guide (p.476). Thanks.
v.Eremin
Veeam Software
 
Posts: 13266
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Near-Continuous replication and database growth

Veeam Logoby mlinders » Tue Oct 29, 2013 2:37 pm

Just got an update from VMware regarding VPX_INT_ARRAY, it seems the large number of records are due to huge number of orphan data records that exist in this table that are no longer referenced by any of their parent tables. Advice from VMware: Truncate the VPX_INT_ARRAY table, as vCenter will resync the info. We performed a truncate.

Veeam Backup & Replication will no longer replicate succesfully after truncating VPX_INT_ARRAY:
It looks like Veeam no longer knows the replica servers. We get errors like "The name <NAME>_replica already exists"

I restored the pre-truncate backup and Veeam starts replicating again without errors. This table has to do with mapping between Veeam and the replica virtual machines.
mlinders
Veeam ProPartner
 
Posts: 26
Liked: 8 times
Joined: Sun Apr 22, 2012 10:42 pm
Location: the Netherlands
Full Name: Mark Linders

Re: Near-Continuous replication and database growth

Veeam Logoby lars@norstat.no » Thu Oct 31, 2013 1:17 pm

Remember that SQL Express 2008 has the same 4 GB limit on database as 2005 version, but SQL 2012 has a 10GB limit. There is also a limit at 1 CPU socket OR 4 Cores so remember to choose 1 socket with 4 cores in systems settings for the Virtual machine used to run Vcenter and not the other way around. By far the biggest limitation on SQL Express is that it can only use 1GB of RAM.

Here is the script i used to clean out the old data from the VPX_INT_ARRAY table in the Vcenter database:

http://kb.vmware.com/selfservice/micros ... Id=2005333

After you have cleaned out the old entries run this script:

http://www.mssqltips.com/sqlservertip/1 ... databases/

This will rebuild all your tables and indexes, after you have done that, shrink the database and then it's VERY important to run the rebuild script again because otherwise your indexes will be fragmented. it will only take seconds but you need to take the database into single user mode for the first script, essentially taking the database down.

I also deleted all task logs and event logs from the database and set retention for 7 days, but i have heard people having to set it to one day when using Veeam.

In the end i reduced my Vcenter database from 50 GB to about 5 GB and everything is very fast now :-)
lars@norstat.no
Expert
 
Posts: 109
Liked: 14 times
Joined: Tue Nov 01, 2011 1:44 pm
Full Name: Lars Skjønberg

Re: Near-Continuous replication and database growth

Veeam Logoby mlinders » Thu Oct 31, 2013 1:41 pm

Hello Lars, we are using SQL 2008 R2 which has 10 GB limit. The KB from VMWare is about VPX_TEXT_ARRAY, not VPX_INT_ARRAY. Our database retention is already set for 1 day, because continuous replication floods the database with tasks and events. The problem is that the target vCenter server for replication jobs shows a steady growth of 10.000+ records per day in VPX_INT_ARRAY. The moment we stop replication, the growth stops. It's still under investigation by VMWare. I'll update this post as soon as we found out what is causing this.
mlinders
Veeam ProPartner
 
Posts: 26
Liked: 8 times
Joined: Sun Apr 22, 2012 10:42 pm
Location: the Netherlands
Full Name: Mark Linders

Re: Near-Continuous replication and database growth

Veeam Logoby lars@norstat.no » Thu Oct 31, 2013 2:25 pm

Of course, forgot about the R2 version :-)

Sorry for posting the wrong link, i'm sure i also managed to clean out the VPX_INT_ARRAY, but i will come back to you when i can find the solution i used. That said, my other tables where also quite big so cleaning out the VPX_TEXT_ARRAY also gave me quite a boost in performance.

Also i have only found a solution to how to clean it out, but i would also like to find out how to stop it from growing.
lars@norstat.no
Expert
 
Posts: 109
Liked: 14 times
Joined: Tue Nov 01, 2011 1:44 pm
Full Name: Lars Skjønberg

Re: Near-Continuous replication and database growth

Veeam Logoby mlinders » Wed Dec 18, 2013 10:41 am 2 people like this post

After some more testing and working with VMware we have come up with the following method for truncating VPX_INT_ARRAY.

* Disable any Veeam replication jobs you have running
* Log onto your vCenter server and stop the service "VMware VirtualCenter Server"
* Backup you vCenter Database (just to make sure, do not skip this step)

* Use the following T-SQL query on the vCenter database:
Code: Select all
truncate table vpx_int_array
select count(*) from VPX_INT_ARRAY

VPX_INT_ARRAY should return a value of: 0

* Log onto your vCenter server and start the service "VMware VirtualCenter Server"

If we would enable replication at this stage, the replication would fail with the following error:
Image

We need to perform a host resync to populate the VPX_INT_ARRAY with information from the vSphere hosts. To do this:
* Connect to your vCenter server using the vSphere client.
* For every Host in your environment click "Disconnect" and "Connect" right after. This will repopulate VPX_INT_ARRAY.

* You can verify this with the previous T-SQL Query:
Code: Select all
select count(*) from VPX_INT_ARRAY

VPX_INT_ARRAY should return a value higher than: 0

If we would enable replication after the resync, replication will continue without issue:
Image

This procedure will not stop your VPX_INT_ARRAY table from growing when using Continuous Replication, but it will give you the possibility to truncate the table when your SQL (Express) database is full due to the table size. Use at your own risk offcourse. We are still working with VMware to prevent the table from growing this large, as this is unexpected behaviour.
mlinders
Veeam ProPartner
 
Posts: 26
Liked: 8 times
Joined: Sun Apr 22, 2012 10:42 pm
Location: the Netherlands
Full Name: Mark Linders

Re: Near-Continuous replication and database growth

Veeam Logoby Gostev » Wed Dec 18, 2013 8:19 pm

Hi Lars, thank you very much for coming back and posting the detailed instructions for future readers. This has even caught an eye of our support folks ;)
Gostev
Veeam Software
 
Posts: 21390
Liked: 2349 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

Re: Near-Continuous replication and database growth

Veeam Logoby mlinders » Tue Feb 18, 2014 3:50 pm

Just received word back from VMware. Here is the official statement:

"The fix for this issue is currently in development for 5.1 Update 3 which is due for release approximation the end of this year. Due to the time frame I would like to suggest we archive this case. When 5.1 U3 is release can you upgrade your systems when possible. If the issue continues we can open a new case - referencing this one."

Regards,
Mark (That's not spelled Lars.... Gostev :wink: )
mlinders
Veeam ProPartner
 
Posts: 26
Liked: 8 times
Joined: Sun Apr 22, 2012 10:42 pm
Location: the Netherlands
Full Name: Mark Linders

Next

Return to VMware vSphere



Who is online

Users browsing this forum: No registered users and 14 guests