sql-server - 增加 SQL VDI(虚拟设备接口)SQL 备份到 Azure Blob 返回的缓冲区大小
问题描述
我们基于此代码项目创建了一个项目并升级到 Visual Studio 2017。但我们修改了 C# DotNet 部分以备份到 Azure Block Blob。它需要是一个 Block Blob,以便稍后添加加密。CPP DotNet 代码将缓冲区传递到 64KB 缓冲区中的 C# 部分。C# 部分在上传到 Azure 之前将多个 64KB 块合并为一个 50MB 大块。由于 Azure 块 Blob 中的块数限制约为 50K,因此需要合并缓冲区。上传为 64KB 块将使我们很快超过大型数据库的限制。
这一切都像一个魅力。然而,它非常缓慢:备份 80GB 数据库的时间比BACKUP TO URL
Management Studio 中的同等数据库长约 3 倍。
这是从 C# 传递到 CPP VDI 子系统的命令:
BACKUP DATABASE [bench1] TO VIRTUAL_DEVICE='<deviceGUID>' WITH FORMAT, COMPRESSION
性能拖累似乎在于将 64KB 块合并为 50MB 块。我的问题:有没有办法强制 VDI 子系统返回超过 64KB 的缓冲区,还是“内置在酱汁中”?
这是非常精简的 VDIDotNet.CPP 代码。为简洁起见,大多数错误处理已被删除。
#include "vdi.h"
#include "vdierror.h"
#include "vdiguid.h"
using namespace System;
using namespace System::Data;
using namespace System::Data::Odbc;
using namespace System::Globalization;
using namespace System::IO;
using namespace System::Runtime::InteropServices;
using namespace System::Threading;
using namespace System::Reflection;
namespace VdiDotNet {
public ref class VdiEngine
{
private: Void SqlServerConnection_InfoMessage(Object^ sender, OdbcInfoMessageEventArgs^ e)
{
VdiDotNet::InfoMessageEventArgs^ i = gcnew VdiDotNet::InfoMessageEventArgs(e->Message);
InfoMessageReceived(this, i);
}
private: Void ThreadFunc(Object^ data)
{
try
{
String^ connString = "Driver={SQL Server Native Client 11.0};Server=(local);Trusted_Connection=Yes;";
//Create and configure an ODBC connection to the local SQL Server
OdbcConnection^ SqlServerConnection = gcnew OdbcConnection(connString);
SqlServerConnection->InfoMessage += gcnew OdbcInfoMessageEventHandler(this, &VdiDotNet::VdiEngine::SqlServerConnection_InfoMessage);
//Create and configure the command to be issued to SQL Server
OdbcCommand^ SqlServerCommand = gcnew OdbcCommand(data->ToString(), SqlServerConnection);
SqlServerCommand->CommandType = CommandType::Text;
SqlServerCommand->CommandTimeout = 0;
//Notify the user of the command issued
CommandIssued(this, gcnew CommandIssuedEventArgs(data->ToString()));
//Open the connection
SqlServerConnection->Open();
//Execute the command
SqlServerCommand->ExecuteNonQuery();
}
catch (Exception ^ex)
{
LogException(ex);
throw gcnew ApplicationException(ex->Message);
}
}
private: static Void ExecuteDataTransfer (IClientVirtualDevice* vd, Stream^ s)
{
VDC_Command * cmd;
DWORD completionCode;
DWORD bytesTransferred;
HRESULT hr;
while (SUCCEEDED(hr = vd->GetCommand(INFINITE, &cmd)))
{
array<System::Byte>^ arr = gcnew array<System::Byte>(cmd->size);
bytesTransferred = 0;
switch (cmd->commandCode)
{
case VDC_Read:
// ... stuff ...
case VDC_Write:
//Copy the data from the cmd object to a CLR array
Marshal::Copy((IntPtr)cmd->buffer, arr, 0, cmd->size);
//Write the data to the stream
s->Write(arr, 0, cmd->size);
//Set the number of bytes transferred
bytesTransferred = cmd->size;
//Set the completion code
completionCode = ERROR_SUCCESS;
break;
case VDC_Flush:
//Flush the stream
s->Flush();
//Set the completion code
completionCode = ERROR_SUCCESS;
break;
case VDC_ClearError:
//Set the completion code
completionCode = ERROR_SUCCESS;
break;
default:
//Set the completion code
completionCode = ERROR_NOT_SUPPORTED;
break;
}
//Complete the command
hr = vd->CompleteCommand(cmd, completionCode, bytesTransferred, 0);
}
}
public: Void ExecuteCommand(System::String^ command, Stream^ commandStream)
{
try
{
//Initialize COM
HRESULT hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);
//Get an interface to the virtual device set
IClientVirtualDeviceSet2* vds = NULL;
hr = CoCreateInstance(CLSID_MSSQL_ClientVirtualDeviceSet, NULL, CLSCTX_INPROC_SERVER, IID_IClientVirtualDeviceSet2, (void**)&vds);
//Configure the device configuration
VDConfig config = { 0 };
vds->GetConfiguration(INFINITE, &config);
config.deviceCount = 1; //The number of virtual devices to create
//Create a name for the device using a GUID
String^ DeviceName = System::Guid::NewGuid().ToString()->ToUpper(gcnew CultureInfo("en-US"));
WCHAR wVdsName[37] = { 0 };
Marshal::Copy(DeviceName->ToCharArray(), 0, (IntPtr)wVdsName, DeviceName->Length);
//Create the virtual device set
hr = vds->CreateEx(NULL, wVdsName, &config);
//Format the command
command = String::Format(gcnew CultureInfo("en-US"), command, DeviceName);
//Create and execute a new thread to execute the command
Thread^ OdbcThread = gcnew Thread(gcnew ParameterizedThreadStart(this, &VdiDotNet::VdiEngine::ThreadFunc));
OdbcThread->Start(command);
//Configure the virtual device set
hr = vds->GetConfiguration(INFINITE, &config);
//Open the one device on the device set
IClientVirtualDevice* vd = NULL;
hr = vds->OpenDevice(wVdsName, &vd);
//Execute the data transfer
ExecuteDataTransfer(vd, commandStream);
//Wait for the thread that issued the backup / restore command to SQL Server to complete.
OdbcThread->Join();
}
catch (Exception ^ex)
{
LogException(ex);
throw gcnew ApplicationException(ex->Message);
}
}
};
}
这是非常精简的 VDI.H
#include "rpc.h"
#include "rpcndr.h"
#include "windows.h"
#include "ole2.h"
#pragma pack(8)
struct VDConfig
{
unsigned long deviceCount;
unsigned long features;
unsigned long prefixZoneSize;
unsigned long alignment;
unsigned long softFileMarkBlockSize;
unsigned long EOMWarningSize;
unsigned long serverTimeOut;
unsigned long blockSize;
unsigned long maxIODepth;
unsigned long maxTransferSize;
unsigned long bufferAreaSize;
} ;
enum VDCommands
{ VDC_Read = 1,
VDC_Write = ( VDC_Read + 1 ) ,
VDC_ClearError = ( VDC_Write + 1 ) ,
VDC_Rewind = ( VDC_ClearError + 1 ) ,
VDC_WriteMark = ( VDC_Rewind + 1 ) ,
VDC_SkipMarks = ( VDC_WriteMark + 1 ) ,
VDC_SkipBlocks = ( VDC_SkipMarks + 1 ) ,
VDC_Load = ( VDC_SkipBlocks + 1 ) ,
VDC_GetPosition = ( VDC_Load + 1 ) ,
VDC_SetPosition = ( VDC_GetPosition + 1 ) ,
VDC_Discard = ( VDC_SetPosition + 1 ) ,
VDC_Flush = ( VDC_Discard + 1 ) ,
VDC_Snapshot = ( VDC_Flush + 1 ) ,
VDC_MountSnapshot = ( VDC_Snapshot + 1 ) ,
VDC_PrepareToFreeze = ( VDC_MountSnapshot + 1 ) ,
VDC_FileInfoBegin = ( VDC_PrepareToFreeze + 1 ) ,
VDC_FileInfoEnd = ( VDC_FileInfoBegin + 1 )
} ;
struct VDC_Command
{
DWORD commandCode;
DWORD size;
DWORDLONG position;
BYTE *buffer;
} ;
感谢您的任何建议。
解决方案
我终于弄明白了。发布答案以防其他人遇到类似问题
我MAXTRANSFERSIZE=4194304
在 BACKUP 命令上使用了(最大值),例如:
BACKUP DATABASE [bench1] TO VIRTUAL_DEVICE='<deviceGUID>' WITH FORMAT, COMPRESSION,
MAXTRANSFERSIZE=4194304
之后,SQLVDI 以每块 4MB 的形式传递回缓冲区。
推荐阅读
- ansible - 如何调试缓慢的“ansible”(和“ansible-plabook”)?
- c - 如何从文件中读取特定单词?
- python - Django 3 运行服务器错误在 django 3 上配置不正确
- java - MapView 与 Firebase Android
- r - 我正在更新和修改现有的 Shiny 代码以适合我的项目,但它会在 if: 参数长度为零时创建错误
- python-3.x - WX:主窗口失去焦点事件
- python - 我如何比较python代码中序列中的奇数?
- ruby-on-rails - 截断文本而不截断 HTML before_save 回调
- php - Centos7 上的 PHP-FPM 与 Directadmin -> 通过 httpd-alias 的 url 未解析为 php
- python - 从 Python 向外部应用程序提供多个输入