Terrible title, but we have had some instances of various SQL stored procs/cursors/SSIS packages that do something wrong that we cannot replicate. Is there any known data integrity issue that could potentially arise with SQL 2012 running on a VMWare machine with an old slow crummy SAN (V-Motioning between resource groups), and Veeam backups running their Explorer for Microsoft SQL Server?
Here are a few examples:
We have a SSIS package that takes a file and copies it to two servers and then imports into two separate databases and then runs a bunch of stored procedures to update the data. There is different amount of data in the affected tables between the two servers/databases, but the schemas and stored procs are the same. A few times, one of the records in one database was updated incorrectly (records matched on criteria that was not matching), but it was fine in the other database. The job runs overnight and the discrepancy was caught by another job right after so it was not a user issue.
There was a stored procedure that had a cursor that looked something like:
DECLARE @Groups VARCHAR(5);
DECLARE curG CURSOR FOR
SELECT ID FROM Group where SomeBitField = 1
FETCH NEXT FROM curG INTO @Groups
WHILE @@FETCH_STATUS = 0 BEGIN
--Do stuff WHERE Group = @Groups
And it proceeded to update a group where SomeBitField = 0. These fields only exist in the database, there is no front end for them, SomeBitField for that group has always been 0.
- We have an SSIS job that runs after business hours that has a series of SQL commands. One of them creates some records if they match a criteria and then sends an email if there were records created. The job ran and created the records and sent the email, but the records shouldn't have been created. Our IT team has a backup of the database from an hour before the job ran and the job ran against that and didn't create records, I have a backup file that I restored to a training environment and it didn't create the records, and when I run the queries on the server where the records were created, it doesn't flag any new records.
So it's a mix of SSIS jobs, cursors, stored procedures, and while it's rare, it's concerning. Any time that we have seen these, if the job/process is re-run in a backup or training environment, it never fails so we can't repeat the issue.
It's not the same tables affected and it's not every time, it seems sporadic. I'm concerned that there might be some issue with slow congested disk while Veeam is trying to "quiesce" the database for a backup and VMWare is VMotioning the server, and there's some sort of indexing issue or other thing that's happening and data is changing out of the normal running database?