SQL Server中Azure存储中的Blob列表 [英] List of blobs in Azure Storage from SQL Server

问题描述

我们当前正在使用Azure存储来备份我们的SQL Server数据库，并且我的任务是在数据库中重新创建Blob的列表.当前，我们使用BACKUP TO URL和存储在 sys.credentials 中的凭据.

We're currently using Azure storage to back up our SQL Server databases, and I've been tasked with recreating a list of the blobs in the database. Currently we use BACKUP TO URL with the credentials stored in sys.credentials.

是否可以直接从SQL Server获取存储帐户中的容器列表和/或从数据库中获取容器中的容器中的Blob列表?

Is it possible to get a list of containers in a storage account and/or get a list of blobs in a container straight from SQL Server with the saved credentials from the database?

推荐答案

是否可以直接从SQL Server获取存储帐户中的容器列表和/或从数据库中获取容器中的容器中的Blob列表?

Is it possible to get a list of containers in a storage account and/or get a list of blobs in a container straight from SQL Server with the saved credentials from the database?

正如David Makogon所说，如果您想从SQL Server中列出容器/blob，则可以尝试使用SQL CLR存储过程.我有一个工作示例来列出容器中的斑点，您可以参考它.

As David Makogon said, if you’d like to list containers/blobs from SQL Server, you could try to use SQL CLR stored procedure. And I have a working sample to list blobs from a container, you could refer to it.

SQL CLR StoredProcedure

SQL CLR StoredProcedure

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SqlStoredProcedure1 ()
{

    // Put your code here

    string StorageAccount = "myaccount";
    string StorageKey = "accountkey";

    string containername = "mycontainer";

    string requestMethod = "GET";
    string mxdate = "";
    string storageServiceVersion = "2014-02-14";

    HttpWebRequest req = (HttpWebRequest)WebRequest.Create(string.Format(CultureInfo.InvariantCulture,
            "https://{0}.blob.core.windows.net/{1}?restype=container&comp=list",
            StorageAccount,
            containername
            ));

    req.Method = requestMethod;

    //specify request header

    mxdate = DateTime.UtcNow.ToString("R");

    string canonicalizedHeaders = string.Format(
            "x-ms-date:{0}\nx-ms-version:{1}",
            mxdate,
            storageServiceVersion);

    string canonicalizedResource = string.Format("/{0}/{1}\ncomp:list\nrestype:container", StorageAccount, containername);

    string stringToSign = string.Format(
        "{0}\n\n\n\n\n\n\n\n\n\n\n\n{1}\n{2}",
        requestMethod,
        canonicalizedHeaders,
        canonicalizedResource);

    HMACSHA256 hmac = new HMACSHA256(Convert.FromBase64String(StorageKey));

    string signature = Convert.ToBase64String(hmac.ComputeHash(Encoding.UTF8.GetBytes(stringToSign)));

    String authorization = String.Format("{0} {1}:{2}",
        "SharedKey",
        StorageAccount,
        signature
        );
    string AuthorizationHeader = authorization;

    req.Headers.Add("Authorization", AuthorizationHeader);
    req.Headers.Add("x-ms-date", mxdate);
    req.Headers.Add("x-ms-version", storageServiceVersion);
    DataTable dt = new DataTable();
    using (HttpWebResponse response = (HttpWebResponse)req.GetResponse())
    {
        var stream = response.GetResponseStream();

        StreamReader reader = new StreamReader(stream);
        string content = reader.ReadToEnd();

        StringReader theReader = new StringReader(content);
        DataSet theDataSet = new DataSet();
        theDataSet.ReadXml(theReader);

        dt = theDataSet.Tables[2];
    }

    string blobs = "";

    foreach (DataRow item in dt.Rows)
    {
        blobs += item[0].ToString() + ";";
    }

    SqlContext.Pipe.Send(blobs);
}
}

默认情况下，Microsoft SQL Server中的公共语言运行时(CLR)集成功能是关闭的，必须启用才能使用SQL Server项目项.要启用CLR集成，请使用clrsp_configure存储过程的enabled选项.

The common language runtime (CLR) integration feature is off by default in Microsoft SQL Server and must be enabled in order to use SQL Server project items. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure.

您可以参考本教程，了解如何使用SQL CLR存储过程.

And you could refer to this tutorial to know how to use SQL CLR stored procedure.

这篇关于SQL Server中Azure存储中的Blob列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！