Data Protection Software

Hi gang

We are trying to improve the lives of our MSSQL DBAs.

This is the situation: We have MS SQL DB servers running on physical machines with their disks on IBM FlashSystems. Right now they are doing 2 things regularly: 1) regular backups (DBs and logfiles) into a (NetApp-)file share and 2) once per day and additional fullbackup of specific production  DBs into a (NetApp-)file share that then get copied via a second server onto a different (NetApp-)file share which then is loaded into a third MS SQL DB server for testing purposes. Since the the third server resides in a testing network he is not allowed to directly access the DB dump from the first server (which is in the production network). Now the DBs were getting bigger over time (approx. 6TB now) and the allowed time window stays the same. This process also puts strain on the servers and the network so now the guys are lokking for help from us (the storage team).

We proposed to use SCDM to "clone" the DBs inside the FlashSystem which, if I'm not completely wrong, should be easily possible (either with the thrid server connecting directly to the same (production) FlashSystem or we do a replicate onto the testing FlashSystem. But before we will do a test setup of this the MS SQL DBAs aksed us their only important question: Will this process keep the LSN (not sure what that means - the guys call it "backup chain") of their 'normal' backups intact?

Any input regarding that question?

Thanks and Cheers

Peter

Hi Peter,

Interesting solution, thanks for sharing.

There are several ways to think about when talking about backups (or data protections).

• Is the dump area protected from changes (Write Once Read Many), so that as soon as the backup dump has been created, it can't be changed, deleted, and will expired using centralized policies.
• Is the backup dump protected from unauthorized access

Here you can use several different solutions, will show one example.

Spictera SPFS can be used to mount the IBM Spectrum Protect storage as a drive-letter on your windows server, and use this as a dump area for your backups.

SPFS is a WORM based file system, so as soon as the dump is created, it can't be changed or deleted (deletion can be enabled if needed), the retention of the dump files are configured using centralized policy based management from the IBM Spectrum Protect server.

SPFS uses the IBM Spectrum Protect Client API, which makes it more easy to mount the same drive-letter on other servers, such an in your case, mounting the dump disk on the test server.

see https://www.ibm.com/partnerworld/bpdirectory/partner/a2woh%20%20%20/spictera/solution/56435/spfs---a-filesystem-for-spectrum-protect-2.4/en/

Protecting larger sized databases (in your case 6TB) might be better with snapshot techniques.

The issues with snapshot techniques are:

• the number of snapshots might not be sufficient for your retention policy and restore time objectives
  • Eg, hourly snapshots kept for 30 days would require 24h * 30 days = 720 snapshots
• the snapshots writes changes on other media, but are dependent on the origin/current media
  • if the origin/current media loses too many disks, than one can loose not only the origin/current media, but perhaps all its snapshots
• the snapshots might be LUN based (hard disk drive)
  • which requires that the data is stored on the intended disks
• the retention of snapshot might be done via API to the protected servers?
  • the server might have too much authorization to the storage, so that it can delete data not intended? such as it's own snapshots, or worse other systems snapshots
• the detection of which LUN disks involved in the snapshots might be complex
  • requires access to the database, to detect which paths data is located on
  • needs to translate path to filesystem
  • needs to translate filesystem to LUN disks
• How to deal with the transactional data?
  • Some databases requires that the transactional data is taken after the snapshot
• Restoring a snapshot to same or new server might result in LUN conflicts
  • What if the LUN disks are included in a volume group? does this require import ?

Are there other ways?

Some databases has a way to protect the transactional data automatically. For those databases Spictera SPFS can be a good fit, as the transactional data will be protected up to last transactional log file.

Applications and/or databases can be protected using snapshot techniques, whether it is hardware based or software based.

Spictera Snapshot is an example of a software based snapshot technique that uses the build in OS snapshot techniques, and stores the snapshots on the IBM Spectrum Protect backup server (separated from the origin/current disks).

https://www.ibm.com/partnerworld/bpdirectory/partner/a2woh%20%20%20/spictera/solution/56927/spictera-snapshot--instant-recovery-for-spectrum-protect-2.4/en/

The benefit with software based snapshots, is that

• it will be no LUN conflicts
• the snapshots are not dependent on the origin/current
• the snapshots are off host located
• the number of snapshots (retentions) are controlled using centralized management from the IBM Spectrum Protect server (unlimited number of snapshots)
• the restore can be done to same or new server on same or new location using same storage or new storage, whether it is different software RAID configurations (RAID5 to RAID6) or the targeted storage has a different size
• the snapshots are write protected, only used to restore the data to the targeted storage
• nearly instant restore (the data can be used while restores are performed in background from the snapshot)
  • this can be a benefit compared to traditional backup/restore, as one can browse the data while restore is performed, to select the part of interest
• the snapshots can be mounted for read only browsing

Hope you enjoy reading, and that you find a solution for your requirements.

Regards Tomas Dalebjörk

Hi Peter, 

The truncation of logs is where the log sequence is managed by SQL to shrink the logs on the production disk. You have to decide which backup process will truncate the logs. CDM truncates the logs if you choose to have a copy of the log files with the database snapshot. This will interfere with the normal backup you mention in your description. It really comes down to the recovery procedure and the granularity of that recovery.  But you should definitely try it out.

https://www.ibm.com/docs/en/scdm/2.2.17?topic=jobs-creating-backup-job-definition-sql

IBM Spectrum Copy Data Management automatically truncates post log backups of databases that it backs up. If database logs are not backed up with IBM Spectrum Copy Data Management, logs are not truncated by IBM Spectrum Copy Data Management and must be managed separately.

Her is some guidance on the Instant Seeding Prerequisites and Limitations: https://www.ibm.com/support/pages/node/6586608

You can try the functionality with the 30 days trial version of CDM. You find the link  here: https://www.ibm.com/products/spectrum-copy-data-management

It is quite easy to install. 

Also consider adding the safeguarded copies in the flash system for cyber resiliency if your production system supports it, came with 8.4.2.  CDM can initiate consistent safeguarded snapshots to prevent overwriting of the copies after they are made.

https://www.ibm.com/docs/en/flashsystem-7x00/8.4.x?topic=overview-safeguarded-copy-function

Hi Peter.

My fifty cents.

Take a look at this video showing CDM functionality on SQL Servers.

Spectrum Copy Data Management 226 SQL Server 2016 -->  https://www.youtube.com/watch?v=ZrQ3Kji4OLk

In this video Sean Perry is doing something very similar to what you mentioned. Despite this video is based on SQL servers running in VMs, CDM can do the same with physical machines and Flashsystem.

Hope it helps.

Nicolas Perez de Arenaza

GIUX S.A.

<ytd-app guide-refresh="" darker-dark-theme=""><yt-guide-manager id="guide-service" class="style-scope ytd-app"> </yt-guide-manager> <yt-mdx-manager class="style-scope ytd-app"></yt-mdx-manager> <yt-playlist-manager class="style-scope ytd-app"></yt-playlist-manager> <yt-hotkey-manager class="style-scope ytd-app"></yt-hotkey-manager> <yt-history-manager id="historyManager" class="style-scope ytd-app"></yt-history-manager> </ytd-app>