SQL Snapshot issues

[elecdave]

We are running a single SQL 2014 Server that is hosting databases for Dynamics GP. We have a lot of batch processing happening during the day whether it's posting purchase orders, payroll, invoices...etc. Meanwhile, the SQL Server running on a VMWare VM running Windows 2012R2.

Needless to say, we purchased a Nimble SAN and virtualized about 4 years ago, Shortly after the implementation, we noticed that our GP app would lose connections to temp tables intermittently. After documenting the times when these errors occured and then reviewing SQL logs, it was determined that the issue was related to Nimble taking application aware snapshots. During this time, the SQL Log would show "I/O Freeze" when taking the snapshots.

Our fix at the time was to implement native SQL DB and Log backups. However, we recently implemented Veeam this year. We tried enabling Application Aware on our SQL server with Veeam backups, and we started seeing the same issues on our SQL server. Now that we have spent a lot of money on implementing Veeam with local backups, Cloud backups and replication, I am not satisfied that we are meeting our objectives of improved RPO and RTO, because we cannot take application aware backups of SQL. Because of the inability to take application aware backups, I cannot take snapshots during normal business hours. So, I have to rely on Veeam Backups in the evening, to capture the SQL backups and all other changes on the VM. The SQL backups are a bottleneck, because of the amount of data being backed up each day (500+Gigs). We only store two days worth of SQL Backups on that local volume. Any suggestions?

[wishr]

Hi David,

I/O freeze you mentioned may be related to a situation when you use two or more tools for backing up SQL consistently. Could you please make sure that this is not the root cause?

Thanks,
Fedor

[foggy]

Hi David, just to clarify, are you using backup from storage snapshots through integration with Nimble storage? What if you try to create a VM snapshot manually? Generally speaking, this behavior is not something unusual, especially in case of a heavily loaded server.

[elecdave]

Fedor, we thought this was the case too regarding multiple tools backuping up SQL. We did have Nimble Snapshots running 24 hours, and we were also backing up to tape at night. We did have issues from time to time where the VSS provider would hang on the backup program. We then stopped running storage snapshots in the evenings when tape backups would run. When we went with Veeam, I am pretty certain that we turned off Nimble Storage snapshots while trying to take Veeam backups. We eventually had the same issues while Veeam was freezing the I/O.

[elecdave]

Foggy, our Nimble Storage snapshots are already syncing with VMWare VSphere. I never thought to take a VMWare snapshot manually. At this point, I probably should expect this type of behavior with our SQL Server and Dynamic GP. However, when the errors did occur, it affected several users at a time. We almost always had to close the app ungracefully using Task Manager.

[wishr]

Hi David,

Thank you for the comments.

Could you please give Foggy's suggestion a try. I'm really expecting a similar I/O freeze to be observed during manual snapshot creation (based on your inputs) and in this case, it would be a good idea to investigate the Dynamics GP environment with responsible parties.

Please keep us posted. Thank you.