SQL Server BDC Hints and Tips: VDI Seeding for High Availability
Published Feb 08 2022 06:43 AM 1,604 Views
Microsoft

 

Moved from: bobsql.com

 

A high availability SQL Server BDC uses the Virtual Device Interface (VDI) to seed databases to secondary replicas. (https://docs.microsoft.com/en-us/sql/big-data-cluster/

 

BobDorr_0-1644331157817.png

 

deployment-high-availability?view=sql-server-ver15)

 

VDI History - https://docs.microsoft.com/en-us/archive/blogs/psssql/how-it-works-sql-server-vdi-vss-backup-resourc...

VDI was introduced to SQL Server allowing custom providers to backup/restore data to/from SQL Server like a file interface would.   The VDI interface is a client/server event driven model used for commands such as Read, Write, Flush, …  When the SQL Server backup would have called the Win32 WriteFile API backup instead calls the server instance, WriteCmd and the client instance accepts and processes the write event.   For example, the client might compress or encrypt the data block associated with the WriteCmd and store the data in a compressed file format.   Another example would be backup to Azure Storage using the rest interface to read and write data to/from Azure Blob Storage.

 

The introduction of VDI also allowed for the proper split mirroring and snapshot backups such as in Microsoft Data Protection manager (DPM.)  Prior to the introduction of the VDI feature the dbcc freeze/thaw TSQL commands were used to stop and start database writes/reads.   A utility would issue the dbcc freeze to stop the I/O activity for the database.  Once the freeze took effect the split mirror/snapshot operation took take place and then the dbcc thaw was issued to return the database to a fully operational state.   The flaw in the two-command approach was that you could issue the freeze and all the SQL Server workers got blocked waiting for a thaw.  When the utility attempted to issue the thaw command the command would be queued, and the database could not return to an operational state until SQL Server was restarted.

 

VDI eliminates this flaw because the freeze and thaw are part of the backup task operation.  During the backup, the VDI client is informed when I/O activity is frozen.  The VDI client completes the snapshot, responding to the freeze command when completed.  When the freeze command is complete (or cancelled) the backup task returns the database to a fully operation state (thaws I/O.)

The protocol for VDI is for an application to create and initialize a VDI client.  The client provides a unique device name that is used for communication with the SQL Server backup or restore command.  While the name can be any string it is commonly a Guid accommodating the uniqueness requirements. 

 

The application issues a backup or restore command with the VIRTUAL_DEVICE = ‘unique name’ syntax.   The SQL Server creates and initializes the VDI server object using the device name provided.   The device name is used to locate a shared memory region created during the VDI client initialization.  Using the shared memory region, the data and events are exchanged between the client and server VDI instance pairing.

 

Note: My colleague, Pat, enhanced the SQL Server VDI feature allowing VDI backup/restore utility support on Linux for SQL Server 2017 so you always have your choice in backup/restore storage providers on Linux and Windows.

 

Tip: On a Linux installation you can observe some of the shared memory usage by enumerating: /dev/shm

 

ContainedAG - VDI Seeding

To establish a secondary copy (replica) of a database the primary is backed up and restored on a secondary replica, seeded.  The restored secondary then transitions to the continuous recovery mode and applies log blocks shipped from the primary to synchronize changes.

 

On The primary

-          A seeding request is made for a database to be replicated to a secondary.

-          The seeding task uses a UCS channel (Service Broker transport for sending/receiving data) to/from the secondary to exchange the data.  As previously discussed, the VDI client may consume a Write command, compress and write the data to a file.   For seeding the write is directed across the UCS transport to the secondary target.

-          The seeding task creates a VDITask (new worker.)

-          The VdiTask creates and initializes an instance of the VDI client interface, listening on shared memory and the named events for a backup.  When events arrive, the events are sent to the secondary replica via the UCS transport.

-          The seeding task creates a Backup task which begins a BACKUP … to VIRTUAL_DEVICE command using the unique, device name created during VdiTask initialization.

-          The seeding task waits for the VdiTask and Backup task to complete.

-          The backup task creates the server side VDI interface and uses the event interface to write/stream the database backup to the listening VdiTask (client side of the VDI connection.)

 

BobDorr_0-1644331232576.png

 

On A Secondary

The secondary replica uses VDI in the reverse way of the primary to issue a RESTORE … from VIRTUAL_DEVICE command.   Leveraging the UCS transport to read the database from the primary to the secondary without requiring a file, UNC share or other intermediate storage.  

The primary’s VDITask client sends data to the secondary over the UCS network transport.  The secondary’s VDITask client uses the data to respond to the ReadCmd from the RESTORE command and when the BACKUP has completed the RESTORE has accepted the data and written the data to the database files. 

 

The replica has been seeded with the same data that exists on the primary and the secondary begins to accept and apply new log blocks keeping the database synchronized.  By using the UCS transport for seeding, seeding requires no additional disk space or file transfer activities.  The primary and secondary instances work in concert with each other to backup and restore (seed) the database and establish the database replica.

 

If you encounter seeding problems, check the SQL Server error log and the VDI log for additional information.

 

HotBackup

I wrote a VDI hot backup sample some 20 years ago to learn what was then the new VDI interface.  A few aspects of the interface have changed since I wrote the sample. The core of the sample is like how seeding occurs for the secondary replicas.

 

//

//            This example is designed use the VDI COM object to provide a HOT BACKUP facility

//

//            Use the Native SQLOLEDB provider to run the TSQL commands and VDI to handle the dump

//

//            (C) Microsoft

//

//            This source code is an intended supplement to the Microsoft SQL

//            Server online references and related electronic documentation.

//

//            This sample is for instructional purposes only.

//            Code contained herein is not intended to be used "as is" in real applications.

//

//            Developed by: rdorr July 1998

//

 

#define _UNICODE

#define UNICODE

 

#define _WIN32_DCOM

 

#include "windows.h"

#include "tchar.h"        

#include "stdio.h"

#include "stddef.h"                            //                   offsetof

 

 

#define THE_ERROR   _T("\n\n*** ERROR ENCOUNTERED ***\n\n")

 

 

//            COM needs

#include "objbase.h"

 

//            OLE DB Needs

#define DBINITCONSTANTS

#include "c:\mssql7\devtools\include\oledb.h"

#include "c:\mssql7\devtools\include\oledberr.h"

 

#include "c:\mssql7\devtools\include\sqloledb.H"                          //            SQL OLEDB

 

#pragma comment(lib, "c:\\mssql7\\devtools\\lib\\oledb.lib")

 

 

#include "c:\mssql7\devtools\include\vdi.h"       

#include "c:\mssql7\devtools\include\vdierror.h"  

#include "c:\mssql7\devtools\include\vdiguid.h"   

 

 

//

//                   Functions and Globals

typedef struct stCONN

{

       ICommandText *      pICommandText;

       TCHAR               strDB[513];

       TCHAR               strCmd[1024];

} CONN;

 

 

void   vReportHResult(IUnknown * pIUnknown, HRESULT hr, GUID iidError);

void   vDoBackup(char * gArgs[]);

 

int           iDoCmd(CONN * pConn);

 

HRESULT hrLogon(TCHAR * strServer, TCHAR * strDB, ICommandText ** pICommandText);

 

 

IMalloc *    pIMalloc     =      NULL;

 

 

 

 

//

//                   MAIN

int main(int iArgs, char * gArgs[])

{

       HRESULT             hr     =      E_FAIL;

      

       _tprintf(_T("\nCOMHotBackup - Version 1.00.000\n"));

 

       if(iArgs != 4)

       {

              _tprintf(_T("\nUsage:   COMHotBackup Server SourceDB DestDB"));

              return hr;

       }

 

 

       //

       //            The VDI objects are only FREE threaded so if you do not

       //            call CoInitializeEx you will get interface does not exist errors

       hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);

       if(SUCCEEDED(hr))

       {

              hr = CoGetMalloc(MEMCTX_TASK, &pIMalloc);

              if(SUCCEEDED(hr))

              {

                     vDoBackup(gArgs);

      

                     pIMalloc->Release();

                     pIMalloc = NULL;

              }

              else

              {

                     vReportHResult(NULL, hr, IID_NULL);

              }

             

 

              CoUninitialize();

       }

       else

       {

              vReportHResult(NULL, hr, IID_NULL);

       }

      

 

       _tprintf(_T("\n\nProcessing complete...\n"));

 

 

       return hr;

}

 

 

 

 

//

//            Handle the HRESULT

//

//                   At some point we can add a CLSID here because we could handle

//                   extended error information.  For now keep it simple.

void vReportHResult(IUnknown * pIUnknown, HRESULT hr, GUID iidError)

{

       DWORD               dwError                           =      GetLastError();

       bool                bErrorDisplayed            =      false;

       int                        iStored                           =      0;

       TCHAR *                    ptchrMsg                   =      NULL;

       IErrorInfo * pIErrorInfo                =      NULL;

       bool                bErrorInfoSupport   =      false;

      

 

 

       _tprintf(THE_ERROR _T("Facility: %ld, Severity: %ld, Code: %ld, HRESULT: 0x%X"),

                                                       HRESULT_FACILITY(hr),

                                                       HRESULT_SEVERITY(hr),                   

                                                       HRESULT_CODE(hr), hr);

 

 

       GetErrorInfo(0, &pIErrorInfo);

 

       //

       //            Do we have an object and does it support error information

       if(NULL != pIUnknown && IID_NULL != iidError)

       {

              ISupportErrorInfo * pISupport    =      NULL;

 

              if(SUCCEEDED(pIUnknown->QueryInterface(IID_ISupportErrorInfo, (LPVOID *) &pISupport)))

              {

                    

                     if(SUCCEEDED(pISupport->InterfaceSupportsErrorInfo(iidError)))

                     {

                           _tprintf(_T("\nObject supports error info"));

                           bErrorInfoSupport = true;

                     }

                     else

                     {

                           _tprintf(_T("\nObject does not support error info (2)"));

                     }

 

                     pISupport->Release();

                     pISupport = NULL;

              }

              else

              {

                     _tprintf(_T("\nObject does not support error info"));

              }

 

       }

 

       //            Process the errors / errors

       if(NULL != pIErrorInfo && true == bErrorInfoSupport)

       {

 

              IErrorRecords *     pIRecs =      NULL;

 

              if(SUCCEEDED(pIErrorInfo->QueryInterface(IID_IErrorRecords, (LPVOID *)&pIRecs)))

              {

 

                     ULONG  ulRecs =      0;

                     if(SUCCEEDED(pIRecs->GetRecordCount(&ulRecs)))

                     {

 

                           for(ULONG ulRec = 0; ulRec < ulRecs; ulRec++)

                           {

                                  IErrorInfo   *      pIErrorRec   =      NULL;

 

                                  if(SUCCEEDED(pIRecs->GetErrorInfo(ulRec, GetSystemDefaultLCID(), &pIErrorRec)))

                                  {

                                         BSTR   bstrDesc     =      NULL;

                                         BSTR   bstrSource   =      NULL;

 

                                         if(           SUCCEEDED(pIErrorRec->GetDescription(&bstrDesc))

                                                &&     SUCCEEDED(pIErrorRec->GetSource(&bstrSource)))

                                         {

                                                _tprintf(_T("\n\tDesc: %s\n\tSource: %s"), bstrDesc, bstrSource);

                                         }

                                         else

                                         {

                                                _tprintf(_T("\nAttempt to obtain standard error info failed."));

                                         }

 

                                         if(bstrDesc)

                                         {

                                                SysFreeString(bstrDesc);

                                                bstrDesc = NULL;

                                         }

                                        

                                         if(bstrSource)

                                         {

                                                SysFreeString(bstrSource);

                                                bstrSource = NULL;

                                         }

 

 

                                         //

                                         //            Can we get extended error info like state

                                         ISQLErrorInfo *            pISQLError   =      NULL;

                                         if(SUCCEEDED(pIRecs->GetCustomErrorObject(ulRec, IID_ISQLErrorInfo, (IUnknown **)&pISQLError))

                                                && NULL != pISQLError)    

                                         {

                                                long   lNum          =      0;

                                                BSTR   bstrState    =      NULL;

 

                                                if(SUCCEEDED(pISQLError->GetSQLInfo(&bstrState, &lNum)))

                                                {

                                                       _tprintf(_T("\n\tState: %s\n\tError: %ld"), bstrState, lNum);

 

                                                       SysFreeString(bstrState);

                                                       bstrState = NULL;

                                                }

 

                                                pISQLError->Release();

                                                pISQLError = NULL;

                                         }

                                         else

                                         {

                                                _tprintf(_T("\nUnable to obtain ISQLErrorInfo"));

                                         }

 

                                         pIErrorRec->Release();

                                         pIErrorRec = NULL;

                                  }

 

                           }

 

 

                     }

                     else

                     {

                           _tprintf(_T("\nUnable to obtain error record count"));

                     }

 

                     pIRecs->Release();

                     pIRecs = NULL;

              }

              else

              {

                     _tprintf(_T("\nUnable to obtain IID_IErrorRecords"));

              }

 

       }

 

       //            Free interaface

       if(pIErrorInfo)

       {

              pIErrorInfo->Release();

              pIErrorInfo = NULL;

       }

 

 

       //                   Try to specifically break out the HRESULT

       iStored = ::FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_ALLOCATE_BUFFER,

                                                               NULL,

                                                               hr,

                                                               GetSystemDefaultLangID(),

                                                               (LPTSTR) &ptchrMsg,

                                                               0,                

                                                               NULL);

 

 

       if(0 != iStored)

       {

              _tprintf(_T("\nHRESULT reported: 0x%X - %s"), hr, ptchrMsg);

      

              LocalFree(ptchrMsg);

              ptchrMsg = NULL;

             

              bErrorDisplayed = true;

       }

 

 

       if(false == bErrorDisplayed)

       {

              iStored = ::FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_ALLOCATE_BUFFER,

                                                                            NULL,

                                                                            HRESULT_CODE(hr),

                                                                            GetSystemDefaultLangID(),

                                                                            (LPTSTR) &ptchrMsg,

                                                                            0,                

                                                                            NULL);

 

 

                     if(0 != iStored)

                     {

                           _tprintf(_T("\nHRESULT reported: 0x%X - %s"), hr, ptchrMsg);

                    

                           LocalFree(ptchrMsg);

                           ptchrMsg = NULL;

                          

                           bErrorDisplayed = true;

                     }

 

 

 

       }

 

 

       if(false == bErrorDisplayed && 0 != dwError )

       {

 

              iStored = ::FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_ALLOCATE_BUFFER,

                                                                      NULL,

                                                                      dwError,

                                                                      GetSystemDefaultLangID(),

                                                                      (LPTSTR) &ptchrMsg,

                                                                      0,                

                                                                      NULL);

 

 

              if(0 != iStored)

              {

                     _tprintf(_T("\nGetLastError reported: %ld - %s"), dwError, ptchrMsg);

             

                     LocalFree(ptchrMsg);

                     ptchrMsg = NULL;

                     bErrorDisplayed = true;

              }

 

       }

 

      

       printf("\n");

 

};

 

 

 

//

//            Do the backup operation

void vDoBackup(char * gArgs[])

{

       HRESULT                    hr                         =      E_FAIL;

      

       //            Remenber Sphinx names are 255

       TCHAR         strServer[513]             =      _T("");

       CONN          connSrc;

       CONN          connDest;

      

 

       //

       //            Convert parameters from single byte to UNICODE

       _stprintf(strServer,              _T("%S"),    gArgs[1]);

       _stprintf(connSrc.strDB,   _T("%S;"),   gArgs[2]);

       _stprintf(connDest.strDB,  _T("%S;"),   gArgs[3]);

       _stprintf(connSrc.strCmd,  _T("BACKUP database %S  to   VIRTUAL_DEVICE = 'HotSrc' "),       gArgs[2]);

 

       //                   Assumes a data a log device like pubs..sysfiles for sample

       _stprintf(connDest.strCmd, _T( " RESTORE database %S from VIRTUAL_DEVICE = 'HotDest' ")

                                                       _T(    " with REPLACE, STANDBY = 'c:\\temp\\%S.standby.dat', ")

                                                       _T(    " MOVE '%S' to 'c:\\mssql7\\data\\%S.mdf', MOVE '%S_log' to 'c:\\mssql7\\data\\%S.ldf' "),

                                                              gArgs[3], gArgs[3], gArgs[2], gArgs[3], gArgs[2], gArgs[3]);

 

 

 

       //

       //            Establish the connections to the appropriate servers

       //            Remember the process can only run on the local server so

       //            to show a simulated hot backup without requiring

       //            file I/O we simply use 2 databases on the same server

       //

       //            Could get fancy with a COM object and a named pipe combonation

       //            to move from server to server

       hr = hrLogon(strServer, connSrc.strDB, &connSrc.pICommandText);

       if(SUCCEEDED(hr))

       {

 

              hr = hrLogon(strServer, connDest.strDB, &connDest.pICommandText);

              if(SUCCEEDED(hr))

              {

 

                     //

                     //            Now get the necessary COM Objects created to handle the VIRTUAL_DEVICE

                     //            Contained in the .\mssql7\binn\SQLVDI.DLL file

                     _tprintf(_T("\nSetting up the virtual devices."));

 

                     IClientVirtualDeviceSet *  pIVirtualDeviceSetSrc      =      NULL;

                     IClientVirtualDeviceSet *  pIVirtualDeviceSetDest     =      NULL;

 

                     hr = CoCreateInstance(     IID_IClientVirtualDeviceSet,

                                                              NULL,

                                                              CLSCTX_ALL,

                                                              IID_IClientVirtualDeviceSet,

                                                              (LPVOID *)&pIVirtualDeviceSetSrc);

 

                     if(SUCCEEDED(hr))

                     {

                           hr = CoCreateInstance(     IID_IClientVirtualDeviceSet,

                                                                                  NULL,

                                                                                  CLSCTX_ALL,

                                                                                  IID_IClientVirtualDeviceSet,

                                                                                  (LPVOID *)&pIVirtualDeviceSetDest);

 

                           if(SUCCEEDED(hr))

                           {

 

                                  //

                                  //            Create the actual device sets and then we have to wait for the

                                  //            SQL Server tp complete the configurations - single device

                                  VDConfig     cfgSrc;

                                  VDConfig     cfgDest;

 

                                  ZeroMemory(&cfgSrc, sizeof(VDConfig));

                                  ZeroMemory(&cfgDest, sizeof(VDConfig));

 

                                  cfgSrc.deviceCount  =

                                  cfgDest.deviceCount =      1;

                                 

                                  cfgSrc.features            =

                                  cfgDest.features    =      VDF_LikePipe;              //       Sequential mode

 

                                  cfgSrc.blockSize    =

                                  cfgDest.blockSize   =      8192;

 

                                  cfgSrc.serverTimeOut       =

                                  cfgDest.serverTimeOut      =      30000;

 

                                  cfgSrc.maxIODepth          =

                                  cfgDest.maxIODepth         =      100;                       //              Commands in the queue at a time

 

                                  hr = pIVirtualDeviceSetSrc->Create(_T("HotSrc"), &cfgSrc);

                                  if(SUCCEEDED(hr))

                                  {

                                         pIVirtualDeviceSetDest->Create(_T("HotDest"), &cfgDest);

                                         if(SUCCEEDED(hr))

                                         {

 

                                                //            Must execute the commands on new thread so we can wait on the VDCOnfig

                                                DWORD  dwThread     =      0;

 

                                                HANDLE hThreadsrc=CreateThread(NULL, 0, (LPTHREAD_START_ROUTINE) iDoCmd, &connSrc, 0, &dwThread);

                                                if(hThreadSrc)

                                                {

                                                      

                                                       HANDLE hThreadDest = CreateThread(NULL, 0, (LPTHREAD_START_ROUTINE) iDoCmd, &connDest, 0, &dwThread);

                                                       if(hThreadDest)

                                                       {

                                                      

                                                              //

                                                              //            Wait for the T-SQL to connect and configure us  (10 seconds)

                                                              _tprintf(_T("\nWaiting for config of dump command"));

                                                              hr = pIVirtualDeviceSetSrc->GetConfiguration(10000, &cfgSrc);

                                                              if(SUCCEEDED(hr))

                                                              {

 

                                                                     _tprintf(_T("\nWaiting for config of load command"));

                                                                     hr = pIVirtualDeviceSetDest->GetConfiguration(10000, &cfgDest);

                                                                     if(SUCCEEDED(hr))

                                                                     {

 

                                                                           IClientVirtualDevice *              pIDevSrc     =      NULL;

                                                                           IClientVirtualDevice *              pIDevDest    =      NULL;

 

                                                                           hr = pIVirtualDeviceSetSrc->OpenDevice(_T("HotSrc"), &pIDevSrc);

                                                                           if(SUCCEEDED(hr))

                                                                           {

                                                                                  hr = pIVirtualDeviceSetDest->OpenDevice(_T("HotDest"), &pIDevDest);

                                                                                  if(SUCCEEDED(hr))

                                                                                  {

 

                                                                                         //

                                                                                         //            Command processing loop

                                                                                         VDC_Command *   pCmd;

                                                                                         VDC_Command *   pCmdDest;

                                                                                         DWORD                dwBytes                    =      0;

                                                                                         DWORD                dwCompletion =      0;

 

                                                                                         while(SUCCEEDED(hr = pIDevSrc->GetCommand(INFINITE, &pCmd)))

                                                                                         {

                                                                                               

                                                                                                dwBytes = 0;

                                                                                                switch (pCmd->commandCode)

                                                                                                {

 

                                                                                                       //    

                                                                                                       case VDC_Write:

                                                                                                              {

 

                                                                                                                     //

                                                                                                                     //            Need to process commands from Dest to send buffer

                                                                                                                     //            as a read operation

                                                                                                                     bool   bExitLoop = false;

 

                                                                                                                     while(false == bExitLoop && SUCCEEDED(hr = pIDevDest->GetCommand(INFINITE, &pCmdDest)))

                                                                                                                     {

                                                                                                                           switch (pCmdDest->commandCode)

                                                                                                                           {

                                                                                                                                  //

                                                                                                                                  case VDC_Read:

                                                                                                                                         {

 

                                                                                                                                                //            Make sure we are all the same so memcpy works correctly

                                                                                                                                                if(cfgDest.blockSize != cfgSrc.blockSize || pCmd->size > pCmdDest->size )

                                                                                                                                                {

                                                                                                                                                       _tprintf(THE_ERROR _T("Block size mismatch problem"));

                                                                                                                                                       dwCompletion = ERROR_NOT_SUPPORTED;

                                                                                                                                                }

                                                                                                                                                else

                                                                                                                                                {

                                                                                 

                                                                                                                                                       memcpy(pCmdDest->buffer, pCmd->buffer, pCmd->size);

 

                                                                                                                                                       hr = pIDevDest->CompleteCommand (pCmdDest, dwCompletion, pCmd->size, 0);

                                                                                                                                                       if(SUCCEEDED(hr))

                                                                                                                                                              dwBytes = pCmd->size;

                                                                                                                                                       else

                                                                                                                                                              _tprintf(THE_ERROR _T("Write to device failure"));

 

                                                                                                                                                }

 

                                                                                                                                                bExitLoop = true;                 //            Let outer write complete

                                                                                                                                         }

                                                                                                                                         break;

 

 

                                                                                                                                  //

                                                                                                                                  case VDC_Flush:

                                                                                                                                  case VDC_ClearError:

                                                                                                                                         dwCompletion = ERROR_SUCCESS;

                                                                                                                                         hr = pIDevDest->CompleteCommand (pCmdDest, dwCompletion, 0, 0);

                                                                                                                                         break;

   

                                                                                                                                  //

                                                                                                                                  default:

                                                                                                                                         dwCompletion = ERROR_NOT_SUPPORTED;

                                                                                                                                         hr = pIDevDest->CompleteCommand (pCmdDest, dwCompletion, 0, 0);

                                                                                                                                         break;

 

                                                                                                                           }

 

 

                                                                                                                     }             //            End of loop for dest database

 

 

                                                                                                                     if(SUCCEEDED(hr))

                                                                                                                     {

                                                                                                                           if (dwBytes == pCmd->size )

                                                                                                                                  dwCompletion = ERROR_SUCCESS;

                                                                                                                           else

                                                                                                                                  dwCompletion = ERROR_DISK_FULL;

                                                                                                                     }

 

 

                                                                                                              }

                                                                                                              break;

 

                                                                                                       //

                                                                                                       case VDC_Flush:

                                                                                                              dwCompletion = ERROR_SUCCESS;

                                                                                                              break;

   

                                                                                                       //

                                                                                                       case VDC_ClearError:

                                                                                                              dwCompletion = ERROR_SUCCESS;

                                                                                                              break;

 

                                                                                                       //

                                                                                                       default:

                                                                                                              dwCompletion = ERROR_NOT_SUPPORTED;

                                                                                                              break;

 

                                                                                                }

 

                                                                                                hr = pIDevSrc->CompleteCommand (pCmd, dwCompletion, dwBytes, 0);

 

                                                                                         }             //     End of while loop

 

 

 

                                                                                         //

                                                                                         //            Check for an error code - could have been either side of the processing

                                                                                         if (hr != VD_E_CLOSE)

                                                                                         {

                                                                                                vReportHResult(pIDevSrc, hr, IID_IClientVirtualDevice);           

                                                                                                vReportHResult(pIDevDest, hr, IID_IClientVirtualDevice);          

                                                                                         }

                                                                                        

                                                                                         pIDevDest->Release();

                                                                                         pIDevDest = NULL;

 

                                                                                  }

                                                                                  else

                                                                                  {

                                                                                         vReportHResult(pIVirtualDeviceSetDest, hr, IID_IClientVirtualDeviceSet);       

                                                                                  }

                                                                                 

                                                                                  pIDevSrc->Release();

                                                                                  pIDevsrc=NULL;

                                                                           }

                                                                           else

                                                                           {

                                                                                  vReportHResult(pIVirtualDeviceSetSrc, hr, IID_IClientVirtualDeviceSet);        

                                                                           }

 

                                                                     }

                                                                     else

                                                                     {

                                                                            vReportHResult(pIVirtualDeviceSetDest, hr, IID_IClientVirtualDeviceSet);       

                                                                     }

 

 

                                                              }

                                                              else

                                                              {

                                                                     vReportHResult(pIVirtualDeviceSetSrc, hr, IID_IClientVirtualDeviceSet);        

                                                              }

 

 

 

                                                              WaitForSingleObject(hThreadDest, INFINITE);

                                                              CloseHandle(hThreadDest);

                                                       }

                                                       else

                                                       {

                                                              _tprintf(THE_ERROR _T("\nUnable to start LOAD thread"));

                                                       }

 

 

                                                       WaitForSingleObject(hThreadSrc, INFINITE);

                                                       CloseHandle(hThreadSrc);

 

                                                }

                                                else

                                                {

                                                       _tprintf(THE_ERROR _T("\nUnable to start DUMP thread"));

                                                }

 

 

 

                                         }

                                         else

                                         {

                                                vReportHResult(pIVirtualDeviceSetDest, hr, IID_IClientVirtualDeviceSet);

                                         }

 

                                  }

                                  else

                                  {

                                         vReportHResult(pIVirtualDeviceSetSrc, hr, IID_IClientVirtualDeviceSet);

                                  }

                    

                                  pIVirtualDeviceSetDest->Release();

                                  pIVirtualDeviceSetDest = NULL;

                           }

                           else

                           {

                                  vReportHResult(NULL, hr, IID_NULL);

                           }

 

                           pIVirtualDeviceSetSrc->Release();

                           pIVirtualDeviceSetsrc=NULL;

 

                     }

                     else

                     {

                           vReportHResult(NULL, hr, IID_NULL);

                     }

 

 

                     connDest.pICommandText->Release();

                     connDest.pICommandText = NULL;

              }

      

              connSrc.pICommandText->Release();

              connSrc.pICommandText = NULL;

       }

 

 

}

 

 

//

//            Logon to the SQL Server

HRESULT hrLogon(TCHAR * strServer, TCHAR * strDB, ICommandText ** pICommandText)

{

       HRESULT                           hr                   =      E_FAIL;

       IDBInitialize *            pIDBInit     =      NULL;

 

 

       _tprintf(_T("\nLogging into SERVER = %s, DATABASE=%s "), strServer, strDB);

             

       hr = CoCreateInstance(     CLSID_SQLOLEDB,

                                                NULL,              

                                                CLSCTX_ALL,        

                                                IID_IDBInitialize,

                                                (LPVOID *)&pIDBInit);

                    

       if(SUCCEEDED(hr))

       {

              IDBProperties *     pIProps       =      NULL;

 

              hr = pIDBInit->QueryInterface(IID_IDBProperties, (LPVOID *) &pIProps);

              if(SUCCEEDED(hr))

              {

                     DBPROP        propLogon[5];

                     DBPROPSET    gPropSet[1];

 

                    

                     gPropSet[0].guidPropertySet  = DBPROPSET_DBINIT;

                     gPropSet[0].cProperties  = 5;

                     gPropSet[0].rgProperties = propLogon;

 

 

                     VariantInit(&propLogon[0].vValue);

                     VariantInit(&propLogon[1].vValue);

                     VariantInit(&propLogon[2].vValue);

                     VariantInit(&propLogon[3].vValue);

                     VariantInit(&propLogon[4].vValue);

                    

                     propLogon[0].dwOptions =

                     propLogon[1].dwOptions =

                     propLogon[2].dwOptions =

                     propLogon[3].dwOptions =

                     propLogon[4].dwOptions = DBPROPOPTIONS_REQUIRED;

 

                     propLogon[0].colid =

                     propLogon[1].colid =

                     propLogon[2].colid =

                     propLogon[3].colid =

                     propLogon[4].colid =  DB_NULLID;

 

                     propLogon[0].dwPropertyID  = DBPROP_INIT_PROMPT;

                     propLogon[0].vValue.vt            = VT_I2;

                     propLogon[0].vValue.iVal   = DBPROMPT_PROMPT;

 

                     propLogon[1].dwPropertyID  = DBPROP_INIT_HWND;

                     propLogon[1].vValue.vt            = VT_I4;

                     propLogon[1].vValue.lVal   = (long) GetDesktopWindow();

 

                     propLogon[2].dwPropertyID  = DBPROP_INIT_DATASOURCE;

                     propLogon[2].vValue.vt            = VT_BSTR;

                     propLogon[2].vValue.bstrVal       = SysAllocString(strServer);            //       Server

                                        

                     propLogon[3].dwPropertyID  = DBPROP_AUTH_USERID;

                     propLogon[3].vValue.vt            = VT_BSTR;

                     propLogon[3].vValue.bstrVal       = SysAllocString(_T("sa"));

                    

                     propLogon[4].dwPropertyID  = DBPROP_INIT_TIMEOUT;

                     propLogon[4].vValue.vt            = VT_I4;

                     propLogon[4].vValue.lVal   = 20;                                                         //     20 second logon timeout

      

 

                     hr = pIProps->SetProperties(1, gPropSet);

                     if(SUCCEEDED(hr))

                     {

                           hr = pIDBInit->Initialize();

                           if(SUCCEEDED(hr))

                           {

                                  _tprintf(_T("Getting the Command object"));

                                 

                                  IDBCreateSession *  pICreateSession     =      NULL;

 

                                  hr = pIDBInit->QueryInterface(IID_IDBCreateSession, (LPVOID *) &pICreateSession);

                                  if(SUCCEEDED(hr))

                                  {

                                         IDBCreateCommand *  pIDBCreateCommand   =      NULL;

 

                                         hr = pICreateSession->CreateSession(NULL, IID_IDBCreateCommand, (IUnknown **)&pIDBCreateCommand);

                                         if(SUCCEEDED(hr))

                                         {

                                                hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, (IUnknown **) pICommandText);

                                                if(FAILED(hr))

                                                {

                                                       vReportHResult(pIDBCreateCommand, hr, IID_IDBCreateCommand);

                                                }

 

 

                                                pIDBCreateCommand->Release();

                                                pIDBCreateCommand = NULL;

                                         }

                                         else

                                         {

                                                vReportHResult(pICreateSession, hr, IID_IDBCreateSession);

                                         }

                                        

                                         pICreateSession->Release();

                                         pICreateSession = NULL;

                                  }

                                  else

                                  {

                                         vReportHResult(pICreateSession, hr, IID_IDBCreateSession);

                                  }

 

                           }

                           else

                           {

                                  vReportHResult(pIDBInit, hr, IID_IDBInitialize);

                           }

 

                     }

                     else

                     {

                           vReportHResult(pIProps, hr, IID_IDBProperties);

                     }

 

 

                     //            Free up the BSTR memory

                     SysFreeString(propLogon[2].vValue.bstrVal);

                     SysFreeString(propLogon[3].vValue.bstrVal);

                     SysFreeString(propLogon[5].vValue.bstrVal);

 

 

                     pIProps->Release();

                     pIProps = NULL;

              }

              else

              {

                     vReportHResult(pIDBInit, hr, IID_IDBInitialize);

              }

 

              pIDBInit->Release();

              pIDBInit = NULL;

       }

       else

       {

              vReportHResult(NULL, hr, IID_NULL);

       }

 

       return hr;

}

 

 

//

//                   Dump and Load threads

int iDoCmd(CONN * pConn)

{

       HRESULT       hr     =      E_FAIL;

 

       hr = pConn->pICommandText->SetCommandText(DBGUID_DBSQL, pConn->strCmd);

       if(SUCCEEDED(hr))

       {

              IRowset *    pIRowset     =      NULL;

              LONG          lNumRows     =      0;

 

              hr = pConn->pICommandText->Execute(NULL, IID_IRowset, NULL, &lNumRows, (IUnknown **)&pIRowset);

              if(FAILED(hr))

              {

                     _tprintf(_T("\nExecuting: %s"), pConn->strCmd);

 

                     vReportHResult(pConn->pICommandText, hr, IID_ICommandText);

              }

              else

              {

 

                     //            May not have returned rows

                     if(pIRowset)

                     {

                           pIRowset->Release();

                           pIRowset = NULL;

                     }

              }

 

 

       }

       else

       {

              vReportHResult(pConn->pICommandText, hr, IID_ICommandText);

       }

 

 

       return hr;

}

Co-Authors
Version history
Last update:
‎Feb 08 2022 06:42 AM
Updated by: