Netapp Sqlserver Snapshots

SQL Server backups using NetApp snapshots A few months ago I was introduced to NetApp technology. Although I was familiar with block virtualization I didn’t have a change put my hand on it. Recently I found that NetApp has a simulator, a piece of software that provides the core functionality of NetApp from block virtualization to creation snapshots and flexclones. I work for a company that provides hosting services for ERP systems. We support several kinds of platforms, the most popular being Windows/Oracle, Windows/SQL Server and UNIX/UNIX-like/Oracle. One of the most interesting features of NetApp is the snapshot, an image on disk of the state of files in a NetApp volume. The beauty of this feature is its potential to replace standard backups – maybe not all, but a big piece of it. Just imagine backing up your database several times a day without the hassle of setting Oracle in backup mode for a long time or managing tapes. NetApp provides tools that can manage snapshot backups of Oracle and SQL Server databases. The bad news is that these tools are not part of the NetApp simulator, the good news is that the snapshot functionality is. Oracle does not have much trouble integrating with NetApp snapshots. Basically you set the database (or tablespace) in backup mode, take the snapshot and return the database to normal mode. Restoring a complete Oracle database is a matter of restoring the snapshot and recovering the database; datafile recovery depends on the volume and LUN configuration in the NetApp filer but the procedure is the basically the same. Backing up a SQL Severer database is where things become interesting. SQL Server does not provide a function to put the database in backup mode like Oracle, at least not with standard tools. With one of the reasons of using snapshots for database backups being downtime prevention, and without access to NetApp SnapManager for SQL Server, I had to look at other options. A year ago I worked with a 3rd party backup solution to backup SQL Server databases from a mirror disk. Looking at SQL traces and other documentation I found that the backup tool used a special backup command for SQL Server - “BACKUP DATABASE…WITH SNAPSHOT” - in order to get consistent database backups. At that time I found that Microsoft provides an API to integrate backup solutions with SQL Server through VSS (Volume Shadow Service) called VDI (Virtual Backup Device Interface). The API specification is downloadable; it contains documentation, header files and (ok, thanks, Microsoft) sample programs. In a nutshell, VSS provides something called SQL writer which enables the capability of freezing (and thawing) SQL Server IO which in turn can be used with NetApp snapshots to take consistent backups; VDI provides the means to interact with VSS. After working some more options I was able to build a small executable (based on the examples from Microsoft’s VDI) to accomplish the freeze and thaw of SQL Server IO, something like the poor’s man SnapManager for SQL Server; I used MS Visual C++ Express Edition to build the executable.

The effect of the executable can be seen in the SQL Server log (see image below). The volume snapshot can be done in between.

Recovering the SQL Server database is done through VSS and the VDI API also. The small executable only supports restoring a complete database. A few changes to the code to add the “NORECOVERY” option to the “RESTORE DATABASE…WITH SNAPSHOT” command made the trick. Now I can recover from a snapshot and roll forward using “RESTORE LOG…” from Enterprise Manager or SQL client. The environment used for this test is shown below. It consist of a Linux server running NetApp simulator (ONTAP 7.3.1), a SQL Server 2005 database server running Windows Server 2003 with MS iSCSI initiator. CIFS was enabled in NetApp to create a share to store transaction log backups with thin provisioning (theme for other discussion).

The Oracle test was done in a similar configuration. Oracle was running in a Linux server. NFS was used to store archived redolog files.

If the IT infrastructure already has NetApp equipment and software then probably it would make more sense to buy SnapManager, but if budget is tight or there is no justification for it you can develop you own backup solution using snapshots.

Tip – I found that Windows does not handle well iSCSI disk operations so disconnect all disks before returning a snapshot and import them back using the Computer Management console when the snapshot is complete. Not to bias in favor of a platform but this was not a problem for the Linux server. Noe Hoyos is a SAP NetWeaver technologist working at DBSi, a hosting services provider. Noe has over 12 years of experience managing IT systems and 10 as a SAP Basis administrator. Noe can be contacted at [email protected]. *All product names used here are registered trademarks of the corresponding company.