SQL Server BDC Hints and Tips: VDI Seeding for High Availability

SQL Server BDC Hints and Tips: VDI Seeding for High Availability

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/deployment-high-availability?view=sql-server-ver15)​​

high-availability-ag-bdc

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

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

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(“nn*** ERROR ENCOUNTERED ***nn”)

//  COM needs

#include​​ “objbase.h”

//  OLE DB Needs

#define DBINITCONSTANTS

#include “c:mssql7devtoolsincludeoledb.h”

#include “c:mssql7devtoolsincludeoledberr.h”

#include “c:mssql7devtoolsincludesqloledb.H”    //  SQL OLEDB

#pragma comment(lib, “c:mssql7devtoolsliboledb.lib”)

#include “c:mssql7devtoolsincludevdi.h”  ​​ ​​ ​​ ​​ ​​ ​​​​

#include “c:mssql7devtoolsincludevdierror.h”  ​​​​

#include “c:mssql7devtoolsincludevdiguid.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.000n”));

 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(“nnProcessing 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(“ntDesc: %sntSource: %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(“ntState: %sntError: %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:mssql7data%S.mdf’, MOVE ‘%S_log’ to ‘c:mssql7data%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 .mssql7binnSQLVDI.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;

}

-​​ Bob Dorr

How It Works, SQL Server BDC Hints and Tips Backup/Restorecontainedagseedingvdi

Rating
( No ratings yet )