Oracle 到 SQL Server 和 SQL Server 到 Oracle 迁移指南 - Pt。 3

已发表: 2022-03-11

本系列的第一部分和第二部分讨论了 Oracle 数据库和 Microsoft SQL Server 在事务实现方面的差异，以及由此产生的转换陷阱，以及一些常用的语法元素。

最后一部分将介绍 Oracle 读取一致性的概念以及如何将基于此概念的体系结构转换为 Microsoft SQL Server 版本。 它还将解决同义词的使用（以及如何不使用它们）以及变更控制过程在管理数据库环境中的作用。

SQL Server 中的 Oracle 读取一致性及其等效项

Oracle 读一致性是对单个 SQL 语句返回的所有数据都来自同一个奇异时间点的保证。

这意味着如果您在 12:01:02.345 发出SELECT语句并在返回结果集之前运行了 5 分钟，那么截至 12:01:02.345 已提交到数据库中的所有数据（并且只有数据）都会成功进入你的回报集。 在数据库处理您的语句的那 5 分钟内，您的返回集不会添加任何新数据，也不会有任何更新，也不会看到任何删除。

Oracle 架构通过在内部对数据的每次更改加时间戳并从两个源构建结果集来实现读取一致性：永久数据文件和撤消段（或“回滚段”，直到版本 10g 才知道）。

为了支持它，应该保留撤消信息。 如果它被覆盖，则会导致臭名昭著的ORA-01555: snapshot too old错误。

撇开撤销段管理——以及如何导航ORA-01555: snapshot too old错误——让我们看看读取一致性对 Oracle 中任何实际实现的影响。 此外，它应该如何在 SQL Server 中进行镜像，这与其他 RDBMS 实现的情况一样，除了 PostgreSQL 的可能和合格的例外 - 不支持它？

关键是Oracle读写不会互相阻塞。 这也意味着您的长时间运行的查询返回集可能没有最新数据。

非阻塞读写是 Oracle 的一个优势，它会影响事务范围。

但是读取一致性也意味着你没有数据的最新状态。 在某些情况下它非常好（例如为特定时间生成报告），它可能会在其他情况下产生重大问题。

没有最新的——甚至是“肮脏的”或未提交的——数据可能很关键：经典场景是酒店房间预订系统。

考虑以下用例：您有两个同时接受房间预订订单的客户服务代理。 如何确保房间不会超额预订？

在 SQL Server 中，您可以启动显式事务并从可用房间的列表（可以是表或视图）中SELECT一条记录。 只要此事务未关闭（通过COMMIT或ROLLBACK ），没有人可以获得您选择的相同房间记录。 这可以防止重复预订，但也会让每个其他座席都按顺序等待对方一次完成一个预订请求。

在 Oracle 中，您可以通过针对匹配搜索条件的记录发出SELECT ... FOR UPDATE语句来获得相同的结果。

注意：存在更好的解决方案，例如设置一个临时标志来标记房间“正在考虑中”，而不是盲目地锁定对它的访问。 但这些是架构解决方案，而不是语言选项。

结论：Oracle 读一致性并不是“全好”或“全坏”，而是平台的一个重要属性，需要很好地理解，对跨平台代码迁移至关重要。

Oracle 和 Microsoft SQL Server 中的公共（和私有）同义词

“公共同义词是邪恶的。” 这不完全是我个人的发现，但我一直接受它作为福音，直到我的一天、一周和一年被公共同义词拯救。

在许多数据库环境中——我会说我有机会使用的所有 Oracle 环境，但没有一个是我设计的——对每个对象使用CREATE PUBLIC SYNONYM是例行公事，因为“我们一直都是这样做的”。

在这些环境中，公共同义词只有一个功能：允许在不指定所有者的情况下引用对象。 这是公开同义词的一个深思熟虑的原因。

然而，Oracle 公共同义词可能非常有用，如果正确且有理由地实施和管理，它可以为团队带来显着超过其所有缺点的生产力优势。 是的，我说的是“团队生产力”。 但是怎么做？ 为此，我们需要了解名称解析在 Oracle 中是如何工作的。

当 Oracle 解析器查找名称（非保留关键字）时，它会尝试按以下顺序将其与现有数据库对象匹配：

以 my_object 作为输入开始的流程图。发出会话的当前模式是否有一个名为 my_object 的对象？如果是这样，我们就完成了。如果没有，发出会话的当前模式是否有一个名为 my_object 的私有同义词？如果是这样，我们将同义词解析为一个对象，我们就完成了。如果没有，是否有一个名为 my_object 的公共同义词？如果是这样，解决它，我们就完成了。如果没有，请查找具有此名称的架构。如果我们找到一个，我们就完成了。如果不是，则引发错误。

注意：引发的错误将是ORA-00942: table or view does not exist ，或PLS-00201: identifier 'my_object' must be declared 。

在这个名称解析顺序中，很容易看出，当开发人员在自己的模式中工作时，任何与公共同义词同名的本地对象都会隐藏这个公共同义词。 （注意：Oracle 18c 实现了“仅登录”模式类型，此讨论不适用于它。）

扩展团队的公共同义词：Oracle 变更控制

现在让我们看看一个由 100 名开发人员组成的假设团队，他们在同一个数据库上工作（这是我所经历的）。 此外，让我们假设他们都在他们的个人工作站上本地工作并独立进行非数据库构建，所有这些都链接到同一个数据库开发环境。 非数据库代码（无论是 C#、Java、C++、Python 还是其他任何东西）中的代码合并解析将在更改控制签入时完成，并将在下一次代码构建时生效。 但是数据库表、代码和数据在持续开发过程中需要多次来回更改。 每个开发者独立执行此操作，并立即生效。

为此，所有数据库对象都在一个通用应用程序模式中创建。 这是应用程序引用的模式。 每个开发者：

  • 使用他们的个人用户帐户/模式连接到数据库
  • 总是从一个空的个人架构开始
  • 如上所述，仅通过名称解析到公共同义词来引用公共模式

当开发人员需要对数据库进行任何更改（创建或更改表、更改过程代码，甚至修改一组数据以支持某些测试场景）时，他们会在其个人模式中创建对象的副本。 他们通过使用DESCRIBE命令获取 DDL 代码并在本地运行它来做到这一点。

从这一刻起，此开发人员的代码将看到对象和数据的本地版本，其他任何人都看不到（也不会影响）。 开发完成后，将修改后的数据库代码签入源代码管理，并解决冲突。 然后，最终代码（和数据，如果需要）在通用模式中实现。

在此之后，整个开发团队可以再次看到相同的数据库。 刚刚交付代码的开发人员会从他/她的个人模式中删除所有对象，并准备好接受新任务。

这种为多个开发人员促进独立并行工作的能力是公共同义词的主要好处——这一重要性怎么强调都不为过。 然而，在实践中，我继续看到团队在 Oracle 实施中创建公共同义词“只是因为我们总是这样做”。 相比之下，在使用 SQL Server 的团队中，我不认为创建公共同义词已成为一种常见做法。 该功能存在但不经常使用。

在 SQL Server 中，用户的当前默认架构是在用户配置中定义的，如果您具有“更改用户”权限，则可以随时更改。 可以实施与上述 Oracle 完全相同的方法。 但是，如果不使用此方法，则不应复制公共同义词。

由于默认情况下 Microsoft SQL Server 不会将新用户帐户与其自己的架构相关联（如 Oracle 所做的那样），因此该关联应该是标准“创建用户”脚本的一部分。

下面是一个创建专用用户模式并将其分配给用户的脚本示例。

首先，为需要加入名为DevelopmentDatabase的数据库的新用户创建模式（每个模式必须在自己的批次中创建）：

 use DevelopmentDatabase; GO CREATE SCHEMA Dev1; GO CREATE SCHEMA Dev2; GO

其次，使用分配的默认模式创建第一个用户：

 CREATE LOGIN DevLogin123 WITH PASSWORD = 'first_pass123'; CREATE USER Dev1 FOR LOGIN DevLogin123 WITH DEFAULT_SCHEMA = Dev1; GO

此时，用户Dev1的默认架构将是Dev1 。

接下来，创建另一个没有默认架构的用户：

 CREATE LOGIN DevLogin321 WITH PASSWORD = 'second_pass321'; CREATE USER Dev2 FOR LOGIN DevLogin321; GO

用户Dev2的默认架构是dbo 。

现在更改用户Dev2以将其默认架构更改为Dev2 ：

 ALTER USER Dev2 WITH DEFAULT_SCHEMA = Dev2; GO

现在用户Dev2的默认架构是Dev2 。

此脚本演示了为 Microsoft SQL Server 数据库中的用户分配和更改默认架构的两种方法。 由于 SQL Server 支持多种用户身份验证方法（最常见的是 Windows 身份验证），并且用户登录可能由系统管理员而不是 DBA 处理，因此分配/更改默认架构的ALTER USER方法将更有用。

注意：我使架构的名称与用户的名称相同。 在 SQL Server 中不必这样，但这是我的偏好，因为 (1) 它与在 Oracle 中的完成方式相匹配，并且 (2) 它简化了用户管理（解决了 DBA 对正确操作的最大反对意见首先）——你知道用户的名字，并且你自动知道用户的默认模式。

结论：公共同义词是构建稳定且受到良好保护的多用户开发环境的重要工具。 不幸的是，在我对行业的观察中，它更经常被用于错误的原因——让团队遭受公共同义词的混乱和其他缺点，却没有意识到它们的好处。 改变这种做法以从公共同义词中获得真正的好处可以为团队的开发工作流程带来真正的好处。

数据库访问管理和变更管理流程

正如我们刚刚讨论了大型团队对并行开发的支持，值得讨论一个单独且经常被误解的主题：变更控制流程。

变更管理通常成为一种由团队领导和 DBA 控制的繁文缛节，被叛逆的开发人员所鄙视，他们想要交付所有东西，如果不是“昨天”，那么“现在”。

作为一名 DBA，我总是在进入“我的”数据库的过程中设置保护屏障。 我有一个很好的理由：数据库是一种共享资源。

鸣叫

在源代码控制上下文中，变更管理通常被接受，因为它允许团队从新的但损坏的代码恢复到旧的但可以工作的代码。 但是在数据库环境中，变更管理看起来像是 DBA 设置的一组不合理的障碍和限制：这纯粹是疯狂的，不必要地减慢了开发速度！

让我们把这个开发人员的咆哮放在一边：我是一名 DBA，我不会向自己扔石头！ 作为一名 DBA，我总是在进入“我的”数据库的过程中设置保护屏障。 我有一个很好的理由：数据库是一种共享资源。

每个开发团队——以及他们的每个开发人员——都有一个非常明确的目标和非常具体的可交付成果。 DBA 每天的唯一目标是数据库作为共享资源的稳定性。 DBA 在组织中具有独特的作用，可以监督所有团队的所有开发工作，并控制所有开发人员访问的数据库。 是 DBA 确保所有项目和所有流程都在运行而不会相互干扰，并且每个都有运行所需的资源。

问题是当开发团队和 DBA 团队都被锁在各自的象牙塔中时。

开发人员不知道，没有访问权限，甚至不关心数据库上发生了什么，只要数据库运行得很好。 （这不是他们的可交付成果，也不会影响他们的绩效评估。）

DBA 团队将数据库放在胸前，保护它免受“一无所知”的开发人员的影响，因为他们的团队目标是数据库稳定性。 确保稳定性的最佳方式是防止破坏性更改——通常会导致尽可能保护数据库免受任何更改的态度。

正如我所见，这些对数据库的相互冲突的态度会导致开发团队和 DBA 团队之间的敌意，并导致无法工作的环境。 但 DBA 和开发团队必须共同努力以实现一个共同目标：交付业务解决方案，这也是他们最初走到一起的原因。

作为开发人员和 DBA 的两边，我知道当 DBA 更好地了解开发团队的共同任务和目标时，问题很容易解决。 就他们而言，开发人员需要将数据库视为一种共享资源，而不是抽象概念，而在那里，DBA 应该扮演教育者的角色。

非开发人员 DBA 最常见的错误是限制开发人员访问数据字典和代码优化工具。 访问 Oracle DBA_目录视图、动态V$视图和SYS表对许多 DBA 来说似乎是“DBA 特权”，而事实上，这些都是关键的开发工具。

SQL Server 也是如此，但有一个复杂之处：不能直接授予对某些系统视图的访问权限，但它只是SYSADMIN数据库角色的一部分，并且永远不应在 DBA 团队之外授予此角色。 这可以通过创建在SYSADMIN权限下执行但非 DBA 用户可以访问的视图和存储过程来解决（并且应该在项目从 Oracle 迁移到 SQL Server 的情况下解决）。 这是配置新的 SQL Server 开发环境时开发 DBA 的工作。

数据保护是 DBA 的主要职责之一。 尽管如此，开发团队通常可以完全访问未过滤的生产数据，以便进行与数据相关的故障单故障排除。 这些开发人员对数据结构的访问权限有限——他们首先创建或为他们创建的结构。

当开发团队和 DBA 团队之间建立了适当的工作关系时，创建良好的变更控制流程就变得很直观。 数据库端变更管理的特点和挑战同时是数据库的刚性和流动性——结构是刚性的，数据是流动的。

经常发生的情况是，结构修改（即数据定义语言或 DDL）的变更管理已经很好地建立起来，而数据变更几乎没有变更管理的方式。 理由很简单——数据一直在变化。

但如果我们更仔细地观察这一点，我们会发现在任何系统中，所有数据都属于以下两类之一：应用程序数据和用户数据。

应用程序数据是一个数据字典，它定义了应用程序的行为，并且对于它的进程和任何应用程序代码一样重要。 与任何其他应用程序更改一样，对这些数据的更改应遵循严格的更改控制流程。 为了在应用程序数据更改的更改控制过程中创建透明度，应用程序数据和用户数据应该明确分开。

在 Oracle 中，应该通过将应用程序和用户数据分别放在自己的模式中来完成。 在 Microsoft SQL Server 中，应该通过将每个模式放入单独的模式或（更好）放入单独的数据库来完成。 做出这些选择应该是迁移计划的一部分：Oracle 具有两级名称解析（架构/所有者 - 对象名称），而 SQL Server 具有三级名称解析（数据库 - 架构/所有者 - 对象名称）。

Oracle 和 SQL Server 世界之间混淆的一个常见来源——也许令人惊讶的是——术语数据库和服务器：

SQL Server 术语甲骨文术语定义
服务器数据库（通常与服务器互换使用，除非专门指服务器硬件、操作系统或网络元素；物理/虚拟服务器上可以有一个或多个数据库） 一个正在运行的实例，可以通过网络端口与其他实例“对话”
数据库（服务器的一部分，包含多个模式/所有者） 架构/所有者最顶层分组

在跨平台迁移项目中应该清楚地理解这种术语混淆，因为术语误解会导致错误的配置决策，难以追溯解决。

应用程序和用户数据的正确分离使 DBA 团队能够解决其第二个最重要的问题：用户数据安全。 由于用户数据是分开存放的，因此根据需要实施用户数据访问的打破玻璃程序将非常简单。

结论：变更控制过程在任何项目中都至关重要。 在软件工程中，数据库方面的变更管理经常被忽视，因为数据被认为“过于流动”。 但正是因为数据同时具有“流动性”和“持久性”，所以设计良好的变更控制流程应该是适当数据库环境架构的基石。

关于代码迁移工具的使用

标准的第一方工具 Oracle Migration Workbench 和 SQL Server Migration Assistant 在代码迁移中很有帮助。 但需要考虑的是 80/20 规则：当代码将被正确迁移 80% 时，解决剩余的 20% 将花费 80% 的迁移工作。

迄今为止，使用迁移工具的最大风险是“银弹”观念。 人们可能会想，“它会完成这项工作，我只需要进行一些清理和整理。” 我观察到一个项目由于转换团队及其技术领导的这种态度而失败。

另一方面，使用 Notepad++ 的批量替换功能作为主要编辑工具，我花了四个工作日完成中型 Microsoft SQL Server 2008 系统（大约 200 个对象）的基本转换。

到目前为止，我所讨论的所有关键迁移元素都无法通过迁移工具来解决。

当然，请使用迁移辅助工具，但请记住，这些工具仅提供编辑帮助。 生成的输出文本需要经过审查、修改，并且在某些情况下需要重写以成为具有生产价值的代码。

人工智能工具的开发可能会在未来解决这些迁移工具的缺陷，但我预计在此之前数据库之间的差异会变得模糊，任何迁移过程本身都将变得不必要。 所以，只要需要这些类型的项目，我们就需要用老式的方式来做，使用老式的人类智能。

结论：使用迁移辅助工具是有帮助的，但它不是“灵丹妙药”，任何转换项目仍然需要对以上几点进行详细审查。

Oracle/SQL Server 迁移：始终密切关注

Oracle 和 Microsoft SQL Server 是企业环境中使用最多的两个 RDBMS 平台。 两者都基本符合 ANSI SQL 标准，并且只需很少的修改，甚至可以按原样移动小段代码。

这种相似性造成了一种欺骗性的印象，即跨两个平台的迁移是一项简单、直接的任务，并且可以轻松地从使用一个 RDBMS 后端到另一个应用程序采用相同的应用程序。

在实践中，此类平台迁移绝非易事，必须考虑每个平台内部运作的精细元素，最重要的是，它们实现对数据管理最关键元素（交易）的支持的方式。

虽然我介绍了两个 RDBMS 平台，它们是我专业的核心，但同样的警告——“看起来相似并不意味着它工作相似”——应该应用于在任何其他符合 SQL 的数据库管理系统之间移动代码。 在所有情况下，首先要关注的是源平台和目标平台之间事务管理的实现有何不同。