Q143294

• 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.



• Data from a DUMP pipe will frequently not be a full dump buffer in size because SQL Server uses an EXTENT-based algorithm when DUMPing that does not allow extents to be split up between buffers.



• In the sample program, writes to the LOAD pipe after the 2K header are 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).



• The size of the buffer that the server will use for the DUMP pipe is 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.

Additional query words: sql6 dump load pipe dblib

Keywords : kbusage
Issue type : kbhowto
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600