INF: Implementing DUMP and LOAD to PIPE Devices

• Microsoft SQL Server, version 6.0

SUMMARY

Microsoft SQL Server 6.0 has added new functionality that allows DUMP and LOAD commands to be performed on a named pipe device. There is also a sample program included with the Programmer's Toolkit (PTK) to demonstrate using PIPE devices. This article explains some of steps required to correctly use PIPE devices.

MORE INFORMATION

The NMPSDMP.EXE sample program and code NMPSDMP.C that comes with the DB-Library development files demonstrates using a PIPE dump device having the client program write the PIPE DUMP output to a file, and read the PIPE LOAD input from the same file. There are several key points that allow this program to function correctly:

- The first data read from the DUMP pipe should always be a single 2K page,

  which is the DUMP header. This page should always be written to the LOAD
  pipe as a single 2K page.

  because SQL Server uses an EXTENT-based algorithm when DUMPing that does
  not allow extents to be split up between buffers.

  always a full LOAD buffer size, except for the last write. Writes to the
  load pipe should always be a full load buffer size, except for the first
  write (the DUMP header) and the last write (which may or may not align on
  a full buffer, depending on the DUMP size).

  dynamically configurable via the "backup buffer size" parameter and can
  be calculated with the formula:

     <backup buffer size> * <30 pages per buffer> * <2K per page>

  Having this value dynamically configurable can be useful when performance
  testing an application to find the best balance of buffer size and number
  of striped dump devices. Your application should be configurable for the
  number of devices, because the optimal number may be dependent on the
  number of processors the computer has.