SQL Server用Assembly还原数据库后遇到的一些奇怪问题和解决思路分享

SQL Server用Assembly还原数据库后遇到的一些奇怪问题和解决思路分享

直接开始：

有一次,我们开发团队需要把一个在测试环境运行正常的数据库，还原到另一台新的服务器上，这个数据库有点特殊，因为它里面包含了好几个用.NET语言（比如C#）写的程序集，这些程序集被注册到SQL Server里，实现了一些用纯SQL很难做到的功能，比如复杂的字符串处理或者调用外部API。

还原过程本身很顺利,使用SQL Server Management Studio的还原功能，或者执行RESTORE DATABASE命令，都成功完成了，数据库的状态显示为“正常”，我们以为万事大吉了，但接下来，奇怪的问题就一个接一个地冒出来了。

第一个奇怪问题：莫名其妙的“文件未找到”错误

当我们尝试调用一个依赖程序集的存储过程或函数时,SQL Server竟然报错了，错误信息大概意思是“无法找到程序集文件”或者“程序集‘MyAssembly’的加载失败”，这非常奇怪，因为我们在还原数据库时，明明已经选择了“覆盖现有数据库”，理论上数据库里的所有东西，包括程序集，都应该被完整地还原过来了。

解决思路：

1. 检查程序集依赖： 这是最常见的原因，我们用.NET写的程序集，有时候自己还会引用别的第三方程序集（DLL文件），在原来的服务器上，这些第三方DLL可能被放在服务器的某个特定文件夹（比如GAC全局程序集缓存）里，或者和主程序集在同一个目录，但当我们还原数据库时，SQL Server只会把注册在数据库内部的程序集二进制数据还原回来，而不会去管这些外部的文件依赖。

   • 怎么办？ 我们需要找出这个程序集引用了哪些外部DLL，可以在开发环境里，用像Visual Studio的“对象浏览器”或反编译工具（如ILSpy）打开这个程序集，查看它的引用列表，必须手动把这些缺失的DLL文件复制到新服务器上，并且使用CREATE ASSEMBLY语句（可能需要指定FROM ‘文件路径’），将它们也逐一注册到同一个目标数据库中，注册的顺序很重要，应该先注册被依赖的（比如那个第三方DLL），再注册依赖它的主程序集。

2. 权限问题（Security Permission）： SQL Server对运行.NET代码有严格的安全限制，在原来的服务器上，程序集可能是在某种较高的信任级别（比如EXTERNAL_ACCESS或UNSAFE）下创建的，但还原到新服务器后，程序集的信任级别可能被重置了，或者新服务器的默认安全策略更严格。

   • 怎么办？ 我们需要检查程序集当前的权限设置，可以查询系统视图sys.assemblies，查看permission_set_desc字段，如果它显示为SAFE，但我们的代码需要访问外部资源（文件、网络等），那就肯定会失败，这时，我们需要使用ALTER ASSEMBLY语句来重新设置权限级别，ALTER ASSEMBLY [MyAssembly] WITH PERMISSION_SET = EXTERNAL_ACCESS，注意，执行这个操作可能需要数据库的dbo权限，并且服务器配置了启用clr enabled选项（通过sp_configure设置）。

第二个奇怪问题：版本号对不上导致的“幽灵”错误

这个问题更隐蔽,还原后，调用程序集的某些功能似乎正常，但另一些功能却会报一些看不懂的异常，方法未实现”或“类型转换失败”，日志信息非常模糊，让人摸不着头脑。

解决思路：

1. 程序集版本不一致： 很有可能，在还原数据库的这段时间里，开发人员已经更新了.NET项目，重新生成了程序集，并只部署到了旧的测试服务器上，也就是说，数据库里注册的程序集二进制数据，是新版本的，当数据库被还原到新服务器后，如果有人不小心把旧版本的DLL文件又手动复制过去并尝试注册或更新，就会造成混乱，SQL Server可能会认为它们是同一个程序集（因为名称相同），但内部的代码结构可能已经变了。
   • 怎么办？ 要确保开发、测试、生产环境之间的程序集版本管理要清晰，在还原后如果遇到奇怪的运行时错误，可以对比一下程序集的版本号，可以通过SELECT name, version FROM sys.assemblies查看数据库中注册的版本，再检查一下服务器文件系统上DLL文件的版本是否一致，确保使用的是完全相同的构建版本。

第三个奇怪问题：服务器环境差异带来的“水土不服”

数据库还原成功了,程序集也看似正常加载了，但一运行就崩溃，错误可能指向操作系统层面的问题。

解决思路：

1. 位数不匹配（x86 vs x64）： 如果我们的.NET程序集被编译为特定的目标平台（比如x86），而新的SQL Server是运行在64位操作系统上，但程序集却要求32位环境，就可能出问题，虽然SQL Server CLR主机一般会处理这种差异，但在某些复杂场景下仍可能触发异常。

   • 怎么办？ 检查程序集的编译目标平台，理想情况下，用于SQL Server的程序集应该编译为“Any CPU”（任何CPU），如果不是，可能需要重新编译。

2. 数据库所有者（DBO）和登录映射问题： 程序集在创建时，会有一个所有者，如果程序集需要执行一些需要特定权限的操作，而新服务器上对应的数据库所有者（或者执行存储过程的登录账号）没有足够的权限，也会失败。

   • 怎么办？ 检查数据库的所有者是否正确，确保执行操作的登录账号有足够的权限访问程序集和其底层资源。

总结一下核心的解决思路流程：

当遇到用Assembly的数据库还原后出问题时,不要慌，可以按以下步骤排查：

1. 确认基础： 首先确保SQL Server的CLR集成功能是开启的（sp_configure 'clr enabled', 1）。
2. 检查错误信息： 仔细阅读错误消息，它通常会给出第一个线索，比如是“加载失败”还是“权限不足”。
3. 核对程序集列表和依赖： 使用sys.assemblies和sys.assembly_references系统视图，确认所有需要的程序集都已存在，并且它们之间的依赖关系是正确的、没有缺失的一环。
4. 验证权限设置： 确认每个程序集的PERMISSION_SET是否满足代码的运行要求（SAFE, EXTERNAL_ACCESS, 还是UNSAFE）。
5. 确保版本一致： 对比源环境和目标环境中的程序集版本，确保完全一致。
6. 审视环境配置： 考虑操作系统位数、数据库所有者、登录权限等服务器级的环境因素。

处理包含程序集的数据库还原,比处理纯SQL数据库要更小心，关键是要意识到，数据库还原的不仅仅是表和数据，还有这些“代码包”以及它们所依赖的整个运行环境，只有把这些隐性的依赖关系都理顺了，才能让程序集在新环境中顺利“安家落户”。