将大容量（varbinary）数据迁移到Azure [英] Migrating large volume (varbinary) data to Azure

问题描述

此日志已按照建议提出我的Microsoft  Azure Communications Manager ||  Azure门户团队聊天。

This log has been raised as suggested my a Microsoft Azure Communications Manager || Azure Portal Team Chat.

为生产部署创建我的应用程序数据库。

背景：数据库只包含少量表，但有些表有VARCHAR（MAX）和VARBINARY（MAX）列类型。 
对于varbinary列表，一行包含例如96,014KB的MP4文件。 
当加载到SQL Server for windows时，当使用LEN（）函数选择它时，该行返回98,317,734。

Back ground: the database contains only a small number of tables, however there are some with VARCHAR(MAX) and VARBINARY(MAX) column types.  In the case of the varbinary column table, a row contains for example a 96,014KB MP4 file.  When loaded into SQL Server for windows, this row returns 98,317,734 when LEN() function is used to select it.

最初，我正在执行数据加载使用Microsoft SQL Server Management Studio（SSMS）中的"任务+生成脚本"功能。

Initially, I was performing the data load using the Tasks+Generate Scripts feature in Microsoft SQL Server Management Studio (SSMS).

数据库中没有多少表，所以在我开始处理之前，这样做会很好IMAGES表。

There are not many tables in the database, so this would have worked out fine until I started to process the IMAGES table.

PK：IMAGE_RN INT非空

PK: IMAGE_RN INT Not null

FK：RESPN_RN INT非空

FK: RESPN_RN INT Not null

FILE_NAME VARCHAR （40）非空

FILE_NAME VARCHAR(40) Not null

IMAGE VARBINARY（MAX）不为空

IMAGE VARBINARY(MAX) Not null

当我尝试生成包含脚本的内容时SSMS崩溃我怀疑是内存问题此表的数据。 
有关内容的说明，请参阅上面的背景信息。

SSMS crashes with what I suspect are memory issues when trying to generate scripts that include the data for this table.  See background above for explanation of contents.

通常，您可以在上面显示的用户界面中单击表格单元格（错误）以获取有关错误的更多信息。 
但是，SSMS在这一点上非常沮丧，需要重新启动。

Usually, you can click on the table cell (Error) in the UI shown above to get more information on the Error.  However, SSMS is pretty upset at this point and needs a restart.

然后我读了一篇关于导出数据层应用程序的网络文章，所以我的重点是转移到它作为迁移方法。

I then read a web article on Export Data-tier application and so my focus then moved to that as a migration method.

SSMS：任务+导出数据层应用程序

SSMS: Tasks+Export Data Tier Application

因此，我为整个数据库生成了一个bacpac文件。 <跨度>&NBSP; 导出成功。 
但是，当我尝试使用连接到Azure实例中的主数据库的SSMS进行部署时，它会崩溃。

So, I produced a bacpac file for the whole database.  The export was successful.  However, when I tried to deploy it with SSMS connected to the master database in my Azure instance, it crashes.

单击结果错误对于错误中的任何行，会产生详细消息：

Clicking on the Result error for any of the rows in error produces a detailed message:

Microsoft.SqlServer.Dac：抛出了类型为"System.OutOfMemoryException"的异常。 （mscorlib）

下一步是使用导出数据层应用程序和高级选项卡从导出中省略IMAGES表。 
成功导入了我的数据库，所以我现在唯一的问题是如何将IMAGES表放入我的Azure数据库。

Next step was to use Export Data-tier application and the advanced tab to omit the IMAGES table from the export.  That imported my database successfully, so my only problem now was how to get the IMAGES table into my Azure database.

最后，我决定最好采取查看推荐的Azure迁移方法。 
我下载并安装了数据迁移助手（DMA）。

So finally, I decided I better take a look at the recommended method of Azure migration.  I downloaded and installed Data Migration Assistant (DMA).

据我所知，有两个功能：

As I understand it, there are two features:

1. 评估 - 我的数据库和Azure是否存在任何不兼容性？
2. 迁移 - 我的数据库实际迁移到Azure

如下所示，该工具的评估部分确实报告了跟踪标记周围的不兼容性。 
我真的不确定跟踪标志是什么，我当然不记得在我的数据库中安装/打开它们。 
我不清楚这种情况。  这是一个警告或报告的失败，无法通过迁移过程本身解决吗？ 
我的意思是，跟踪标志只是因为迁移而被关闭了吗？

As you can see below, the assessment part of the tool did report an incompatibility around trace flags.  I am really not sure what trace flags are and I certainly do not recall installing / turning them on in my database.  The status of this is unclear to me.  Is this a warning or a reported failure that cannot be resolved by the migration process itself?  I mean, can trace flags just simply be turned off as a result of the migration?

此时UI不会阻止用户继续尝试迁移/导出他们的数据库，但也许它应该？ 
下一个问题是DMA现在不会接受服务器详细信息来执行迁移。 
这些服务器详细信息仅在之前成功用于执行评估。 服务器是本地Sql Server 2014实例，其名称与计算机名称相同。  目前，DMA不是由于这些问题我可以用于迁移的
路由。

At this point the UI does not prevent the user from continuing to attempt to migrate / export their database, but perhaps it should?  The next problem is that DMA will not now accept the Server details to perform the migration.  These are the exact same server details it successfully used to perform the assessment only moments earlier.  The server is a Local Sql Server 2014 instance with the same name as the machine name. Currently, DMA is not a route I can use to migrate due to these problems.

问题摘要

1. 我无法将我的IMAGES表中的数据迁移到Azure。
2. 我没有自动方法可以轻松地将完整的数据库传输到Azure（包括我的IMAGES表）。 / li>

1. I have no way to migrate the data in my IMAGES table to Azure.
2. I have no automated method that easily transfers a complete database to Azure (which includes my IMAGES table).

SSMS关于包装盒版本信息：

SSMS About box version information:

选择@@版本

推荐答案

嗨Andrew，

Hi Andrew,

请更新
SSMS ，因为您至少需要以下版本才能将.backpac导入Azure SQL数据库：

Please update your installation of SSMS, as you will need the following version as a minimum to import the .backpac to Azure SQL Database:

v.17.9.1

v.18.0  现在是GA所以v17.x下载可能无法继续使用。

And v.18.0 which is now GA so v17.x download may not be available going forward.

请尝试t他用v18导入。因为您目前安装的MDAC版本是：  Microsoft数据访问组件（MDAC）
10.0.17134.1

Please try the import with v18. as the MDAC version you currently have installed is: Microsoft Data Access Components (MDAC) 10.0.17134.1

这篇关于将大容量（varbinary）数据迁移到Azure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！