从Oracle到SQL Server和从SQL Server到Oracle的迁移指南（3）

本文概述

• Oracle读取一致性及其在SQL Server中的等效功能
• Oracle和Microsoft SQL Server中的公共(和私有)同义词
• 数据库访问管理和变更管理过程
• 关于代码迁移工具的使用
• Oracle / SQL Server迁移：始终仔细观察

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

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

Oracle读取一致性及其在SQL Server中的等效功能

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

这意味着, 如果你在12：01：02.345发出SELECT语句, 并且在返回结果集之前运行了5分钟, 则12：01：02.345到数据库中已提交的所有数据(仅数据)将使之生效进入你的回报集。在数据库处理你的语句所需的5分钟内, 你的返回集将不会添加任何新数据, 也不会进行任何更新, 也不会显示任何删除。

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

为了支持它, 应保留撤消信息。如果被覆盖, 则会导致臭名昭著的ORA-01555：快照太旧错误。

抛开撤消段管理以及如何导航ORA-01555：快照太旧的错误, 让我们看看读取一致性对Oracle中任何实际实现的影响。另外, 如何在SQL Server中对其进行镜像(不支持PostgreSQL)(与其他RDBMS实现一样), 并且可能不支持PostgreSQL？

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

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

但是读取一致性也意味着你没有最新的数据状态。在某些情况下(例如制作特定时间的报告)非常好时, 可能会在其他情况下造成重大问题。

没有最新的数据(甚至是”脏数据”或未提交的数据)可能很关键：经典方案是酒店客房预订系统。

考虑以下用例：你有两个客户服务代理, 他们正在同时接受房间预订订单。你如何确保客房不被预订满？

在SQL Server中, 你可以启动显式事务并从可用房间的列表(可以是表或视图)中选择记录。只要未完成该交易(通过COMMIT或ROLLBACK), 任何人都无法获得与你选择的房间相同的房间记录。这样可以防止进行双重预订, 但也可以使每个其他代理依次等待对方一次完成一次预订请求。

在Oracle中, 可以通过对符合搜索条件的记录发出SELECT … FOR UPDATE语句来达到相同的结果。

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

结论：Oracle读取一致性不是”全是好”还是”全是坏”, 而是平台的重要属性, 需要很好地理解它, 并且对于跨平台代码迁移至关重要。

Oracle和Microsoft SQL Server中的公共(和私有)同义词

“公共同义词是邪恶的。”这并不是我个人的发现, 但直到我的天, 周和年被公共同义词保存后, 我才接受它作为福音。

在许多数据库环境中(我会说我有机会使用过所有Oracle环境, 但我没有设计过这些环境), 对每个对象使用CREATE PUBLIC SYNONYM是常规操作, 因为”我们一直都这样做。”

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

但是, 如果正确并有理由地加以实施和管理, Oracle公共同义词可能会非常有用, 并且可以大大提高团队效率, 从而大大提高团队效率。是的, 我说”团队生产力”。但是如何？为此, 我们需要了解名称解析在Oracle中的工作方式。

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

注意：引发的错误将是ORA-00942：DML语句不存在表或视图, 或者PLS-00201：必须为存储过程或函数调用声明标识符” my_object”。

按照此名称解析顺序, 很容易看出, 当开发人员在其自己的架构中工作时, 任何与公共同义词同名的本地对象都将隐藏该公共同义词。 (注意：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控制的繁文tape节的形式, 被叛逆的开发人员鄙视, 他们想要在”昨天”之后而不是”现在”交付一切。

作为一名DBA, 我总是在进入”我的”数据库的过程中设置保护性障碍。我对此有一个很好的理由：数据库是共享资源。鸣叫

在源代码管理上下文中, 变更管理通常被接受, 因为它允许团队将新的但已中断的代码恢复为旧的但仍在工作的代码。但是在数据库环境中, 变更管理似乎是DBA设置的一系列不合理的障碍和限制：这纯粹是疯狂的行为, 不必要地减慢了开发速度！

让我们把开发人员的烦恼放在一边：我是一名DBA, 我不会对自己投以石头！作为一名DBA, 我总是在进入”我的”数据库的过程中设置保护性障碍。我对此有一个很好的理由：数据库是共享资源。

每个开发团队及其每个开发人员都有一个非常明确定义的目标和非常具体的可交付成果。每天, DBA的办公桌上唯一的目标是数据库作为共享资源的稳定性。 DBA在组织中具有独特的作用, 可以监督所有团队的所有开发工作, 并控制所有开发人员都可以访问的数据库。由DBA负责确保所有项目和所有流程都在不相互干扰的情况下运行, 并且每个人都拥有运作所需的资源。

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

开发人员不知道, 无权访问, 甚至不关心数据库上发生的一切, 只要它们对他们来说运行正常即可。 (这不是他们的成果, 也不会影响他们的绩效评估。)

DBA团队将数据库保持在最靠近的位置, 从而保护了数据库免受那些”一无所知”的开发人员的注意, 因为他们的团队目标是数据库稳定性。保证稳定性的最佳方法是防止破坏性更改, 这通常导致人们采取一种尽可能保护数据库免受任何更改的态度。

正如我所看到的, 对数据库的这些冲突态度可能导致开发团队和DBA团队之间产生敌意, 并导致无法工作的环境。但是DBA和开发团队必须共同努力实现一个共同的目标：提供一个业务解决方案, 这首先是将他们整合在一起的原因。

经历了开发人员与DBA分歧的两个方面, 我知道, 当DBA更好地理解开发团队的共同任务和目标时, 这个问题很容易解决。在他们方面, 开发人员需要将数据库视为一个共享资源而不是一个抽象的概念, 并且在这里, DBA应该扮演教育者的角色。

非开发人员DBA犯的最常见错误是限制开发人员对数据字典和代码优化工具的访问。实际上, 当访问Oracle DBA_目录视图, 动态V $视图和SYS表时, 它们是关键的开发工具, 因此它们似乎具有” 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世界之间常见的混淆源是术语数据库和服务器：

跨平台迁移项目中应该清楚地理解这种术语混合, 因为术语错误解释会导致错误的配置决策, 而这些决策很难追溯。

应用程序和用户数据的正确分离使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兼容的数据库管理系统之间移动代码。在所有情况下, 首先要注意的是源平台和目标平台之间事务管理的实现方式如何不同。