Руководство по переходу с Oracle на SQL Server и с SQL Server на Oracle — Pt. 3

В первой и второй частях этой серии обсуждались различия между Oracle Database и Microsoft SQL Server в их реализации транзакций и возникающие в результате ловушки преобразования, а также некоторые часто используемые элементы синтаксиса.

В этой последней части будет рассмотрено понятие согласованности чтения Oracle и преобразование архитектуры, основанной на этом понятии, в версию Microsoft SQL Server. Также будет рассмотрено использование синонимов (и как их НЕ использовать) и роль процесса контроля изменений в управлении средой вашей базы данных.

Oracle Read Consistency и его эквивалент в SQL Server

Согласованность чтения Oracle — это гарантия того, что все данные, возвращаемые одним оператором SQL, поступают из одного и того же единственного момента времени.

Это означает, что если вы выполнили SELECT в 12:01:02.345 и он выполнялся в течение 5 минут, прежде чем вернуть набор результатов, все данные (и только данные), которые были зафиксированы в базе данных по состоянию на 12:01:02.345, сделают его в ваш возвращаемый набор. В ваш возвращаемый набор не будут добавлены новые данные в течение тех 5 минут, которые потребовались базе данных для обработки вашего оператора, а также никаких обновлений, и никакие удаления не будут видны.

Архитектура Oracle обеспечивает согласованность чтения за счет внутренней отметки времени каждого изменения данных и построения результирующего набора из двух источников: постоянных файлов данных и сегмента отмены (или «сегмента отката», как он был известен до версии 10g).

Для его поддержки информация об отмене должна быть сохранена. Если он перезаписывается, это приводит к печально известной ORA-01555: snapshot too old .

Оставив в стороне управление сегментами отмены — и то, как ORA-01555: snapshot too old , — давайте посмотрим на последствия согласованности чтения для любой практической реализации в Oracle. Кроме того, как это должно быть отражено в SQL Server, который, как и в случае с другими реализациями СУБД, за возможным и квалифицированным исключением PostgreSQL, не поддерживает его?

Ключевым моментом является то, что операции чтения и записи Oracle не блокируют друг друга. Это также означает, что в возвращаемом наборе длительных запросов могут быть не самые последние данные.

Неблокирующие операции чтения и записи — это преимущество Oracle, которое влияет на область действия транзакций.

Но согласованность чтения также означает, что у вас нет последнего состояния данных. Когда в некоторых сценариях это совершенно нормально (например, создание отчета за определенное время), в других это может создать серьезные проблемы.

Отсутствие последних — даже «грязных» или незафиксированных — данных может иметь решающее значение: классический сценарий — это система бронирования гостиничных номеров.

Рассмотрим следующий вариант использования: у вас есть два агента по обслуживанию клиентов, которые одновременно принимают заказы на бронирование номеров. Как вы можете гарантировать, что номера не будут перебронированы?

В SQL Server вы можете запустить явную транзакцию и SELECT запись из списка (который может быть таблицей или представлением) доступных комнат. Пока эта транзакция не закрыта (с помощью COMMIT или ROLLBACK ), никто не может получить ту же запись комнаты, которую вы выбрали. Это предотвращает двойное бронирование, но также заставляет всех остальных агентов ждать друг друга, чтобы выполнить запросы на резервирование по одному, последовательно.

В Oracle вы можете добиться того же результата, выполнив SELECT ... FOR UPDATE для записей, соответствующих вашим критериям поиска.

Примечание. Существуют лучшие решения, например, установка временного флажка, помечающего комнату как «рассматриваемую», вместо того, чтобы слепо блокировать доступ к ней. Но это архитектурные решения, а не языковые варианты.

Заключение . Согласованность чтения Oracle — это не «все хорошо» или «все плохо», а важное свойство платформы, которое необходимо хорошо понимать и которое имеет решающее значение для межплатформенной миграции кода.

Общедоступные (и частные) синонимы в Oracle и Microsoft SQL Server

«Общедоступные синонимы — зло». Это не совсем мое личное открытие, но я принимал его как истину до тех пор, пока мой день, неделя и год не были спасены публичными синонимами.

Во многих средах баз данных — я бы сказал, во всех средах Oracle, с которыми мне доводилось работать, но не в тех, которые я проектировал, — использование CREATE PUBLIC SYNONYM для каждого объекта было обычным делом, потому что «мы всегда поступали так».

В этих средах общедоступные синонимы имели только одну функцию: разрешать ссылку на объект без указания его владельца. И это одна из плохо продуманных причин для обнародования синонимов.

Тем не менее общедоступные синонимы Oracle могут быть чрезвычайно полезными и давать преимущества производительности команды, которые значительно перевешивают все их недостатки, если они реализованы и управляются правильно и обоснованно. Да, я сказал «командная продуктивность». Но как? Для этого нам нужно понять, как работает разрешение имен в Oracle.

Когда синтаксический анализатор Oracle находит имя (незарезервированное ключевое слово), он пытается сопоставить его с существующим объектом базы данных в следующем порядке:

Примечание. Возникнет ошибка ORA-00942: table or view does not exist для операторов DML или PLS-00201: identifier 'my_object' must be declared для хранимых процедур или вызовов функций.

В этом порядке разрешения имен легко увидеть, что когда разработчик работает в своей собственной схеме, любой локальный объект с тем же именем, что и общедоступный синоним, скроет этот общедоступный синоним. (Примечание: в Oracle 18c реализован тип схемы «только для входа», и это обсуждение не относится к нему.)

Общедоступные синонимы для Scaling Teams: Oracle Change Control

Давайте теперь посмотрим на гипотетическую команду из 100 разработчиков, работающих над одной и той же базой данных (это то, что я испытал). Кроме того, давайте предположим, что все они работают локально на своих персональных рабочих станциях и независимо выполняют сборку, не связанную с базой данных, и все они связаны с одной и той же средой разработки базы данных. Разрешение слияния кода с кодом, не относящимся к базе данных (будь то C#, Java, C++, Python или что-то еще), будет выполнено во время проверки контроля изменений и вступит в силу при следующей сборке кода. Но таблицы базы данных, код и данные необходимо изменять несколько раз в течение текущей разработки. Каждый разработчик делает это самостоятельно, и это вступает в силу немедленно.

Для этого все объекты базы данных создаются в общей схеме приложения. Это схема , на которую ссылается приложение. Каждый разработчик:

• Подключается к базе данных со своей личной учетной записью пользователя/схемой
• Всегда начинается с пустой личной схемы
• Ссылается на общую схему только посредством разрешения имени в общедоступный синоним, как описано выше.

Когда разработчику необходимо внести какие -либо изменения в базу данных — создать или изменить таблицу, изменить код процедуры или даже изменить набор данных для поддержки какого-либо тестового сценария — он создает копию объекта в своей личной схеме. Они делают это, получая код DDL с помощью команды DESCRIBE и запуская его локально.

С этого момента код этого разработчика будет видеть локальную версию объекта и данных, которые не будут видны (и не будут влиять) никому другому. После завершения разработки измененный код базы данных возвращается в систему управления версиями, и конфликты разрешаются. Затем окончательный код (и данные, если необходимо) реализуется в общей схеме.

После этого вся команда разработчиков может снова видеть ту же базу данных. Разработчик, только что доставивший код, удаляет все объекты из своей личной схемы и готов к новому заданию.

Эта возможность облегчить независимую параллельную работу для нескольких разработчиков является основным преимуществом общедоступных синонимов — значение, которое трудно переоценить. Однако на практике я по-прежнему вижу команды, создающие общедоступные синонимы в реализациях 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), а подключение пользователей может выполняться системными администраторами, а не администраторами баз данных, метод ALTER USER для назначения/изменения схемы по умолчанию будет более удобным.

Примечание. Я сделал имя схемы таким же, как имя пользователя. Это не обязательно должно быть так в SQL Server, но я предпочитаю это, потому что (1) это соответствует тому, как это делается в Oracle, и (2) это упрощает управление пользователями (отвечая на самое большое возражение со стороны администратора баз данных делать это правильно в первую очередь) — вы знаете имя пользователя и автоматически знаете схему пользователя по умолчанию.

Вывод : Общедоступные синонимы — важный инструмент для построения стабильной и хорошо защищенной многопользовательской среды разработки. К сожалению, по моим наблюдениям в отрасли, он чаще используется по неправильным причинам, в результате чего команды страдают от путаницы и других недостатков общедоступных синонимов, не осознавая их преимуществ. Изменение этой практики для получения реальных преимуществ от общедоступных синонимов может принести реальные преимущества рабочему процессу разработки команды.

Управление доступом к базе данных и процессы управления изменениями

Поскольку мы только что говорили о поддержке параллельной разработки большими командами, стоит обратиться к одной отдельной и часто неправильно понимаемой теме: процессам управления изменениями.

Управление изменениями часто становится формой бюрократии, контролируемой руководителями групп и администраторами баз данных, которую презирают мятежные разработчики, которые хотят сделать все если не «вчера», то «сейчас».

Как администратор базы данных, я всегда ставлю защитные барьеры на пути к «моей» базе данных. И у меня есть для этого очень веская причина: база данных — это общий ресурс.

Твитнуть

В контексте управления исходным кодом управление изменениями является общепринятым, поскольку оно позволяет команде вернуться от нового, но неработающего кода к старому, но работающему коду. Но в контексте базы данных управление изменениями может показаться набором необоснованных барьеров и ограничений, устанавливаемых администраторами баз данных: это чистое безумие, которое напрасно замедляет разработку!

Оставим в стороне разглагольствования разработчиков: я администратор баз данных и не буду бросать в себя камни! Как администратор базы данных, я всегда ставлю защитные барьеры на пути к «своей» базе данных. И у меня есть для этого очень веская причина: база данных — это общий ресурс.

Каждая команда разработчиков — и каждый из их разработчиков — имеет очень четко определенную цель и очень конкретный результат. Единственная задача, которая каждый день стоит перед администратором базы данных, — это стабильность базы данных как общего ресурса. Администратор базы данных играет уникальную роль в организации, наблюдая за всеми усилиями по разработке во всех командах и управляя базой данных, к которой имеют доступ все разработчики. Именно администратор базы данных гарантирует, что все проекты и все процессы работают, не мешая друг другу, и что у каждого есть ресурсы, необходимые для функционирования.

Проблема в том, что команды разработчиков и администраторов баз данных сидят запертыми в своих башнях из слоновой кости.

Разработчики не знают, не имеют доступа и даже не заботятся о том, что происходит с базой данных, пока она работает для них нормально. (Это не их результат и не повлияет на оценку их работы.)

Команда администраторов баз данных держит базу данных при себе, защищая ее от разработчиков, которые «ничего не знают» о ней, потому что цель их команды — стабильность базы данных. И лучший способ обеспечить стабильность — это предотвратить деструктивные изменения, что часто приводит к максимально возможной защите базы данных от любых изменений.

Такое противоречивое отношение к базе данных может, как я видел, привести к враждебности между командами разработчиков и администраторов баз данных и привести к неработоспособной среде. Но администраторы баз данных и команда разработчиков должны работать вместе для достижения общей цели: предоставить бизнес-решение, что в первую очередь и объединило их.

Побывав по обе стороны разделения между разработчиком и администратором баз данных, я знаю, что эту проблему легко решить, когда администраторы баз данных лучше понимают общие задачи и цели групп разработчиков. Со своей стороны, разработчики должны рассматривать базу данных не как абстрактное понятие, а как общий ресурс, и здесь администратор базы данных должен взять на себя роль преподавателя.

Самая распространенная ошибка администраторов баз данных, не являющихся разработчиками, — это ограничение доступа разработчиков к словарю данных и инструментам оптимизации кода. Доступ к представлениям каталога Oracle DBA_ , динамическим представлениям V$ и таблицам SYS многим администраторам баз данных кажется «привилегированным», хотя на самом деле это критически важные инструменты разработки.

То же самое относится и к SQL Server, но с одной осложнением: доступ к некоторым системным представлениям не может быть предоставлен напрямую, но это только часть роли базы данных SYSADMIN , и эту роль никогда не следует предоставлять за пределами группы администраторов баз данных. Это может быть решено (и должно быть решено в случае миграции проекта с Oracle на SQL Server) путем создания представлений и хранимых процедур, которые выполняются с привилегиями SYSADMIN , но доступны для пользователей, не являющихся администраторами баз данных. Это работа администратора базы данных по разработке, которая должна выполняться при настройке новой среды разработки SQL Server.

Защита данных является одной из основных обязанностей администратора баз данных. Несмотря на это, команды разработчиков довольно часто имеют полный доступ к нефильтрованным производственным данным, что позволяет устранять неполадки, связанные с заявками, связанными с данными. Это те самые разработчики, которые имеют ограниченный доступ к структуре данных — структуре, созданной ими или для них в первую очередь.

Когда установлены надлежащие рабочие отношения между командами разработчиков и администраторов баз данных, создание хорошего процесса управления изменениями становится интуитивно понятным. Специфика и проблема управления изменениями на стороне базы данных заключается в одновременной жесткости и подвижности базы данных — структура неизменна, данные подвижны.

Часто бывает так, что управление изменениями при модификации структуры, т. е. в языке определения данных или DDL, хорошо зарекомендовало себя, в то время как изменения данных практически не влияют на управление изменениями. Обоснование простое — данные все время меняются.

Но если мы посмотрим на это более внимательно, то увидим, что в любой системе все данные попадают в одну из двух категорий: данные приложения и данные пользователя.

Данные приложения — это словарь данных, который определяет поведение приложения и так же важен для его процессов, как и код любого приложения. Изменения этих данных должны находиться под строгим контролем изменений, как и любые другие изменения приложений. Чтобы создать прозрачность в процессе управления изменениями для изменений данных приложения, данные приложения и пользовательские данные должны быть явно разделены.

В Oracle это следует делать, помещая данные приложения и пользователя в свою собственную схему. В Microsoft SQL Server это нужно делать, помещая каждый в отдельную схему или, что гораздо лучше, в отдельную базу данных. Выбор этих вариантов должен быть частью планирования миграции: Oracle имеет двухуровневое разрешение имен (схема/владелец — имя объекта), тогда как SQL Server имеет трехуровневое разрешение имен (база данных — схема/владелец — имя объекта).

Правильное разделение данных приложения и пользовательских данных позволяет группе администраторов баз данных решить вторую по важности задачу: безопасность пользовательских данных. Поскольку пользовательские данные хранятся отдельно, будет очень просто внедрить процедуру аварийного доступа для доступа к пользовательским данным по мере необходимости.

Вывод : процессы контроля изменений имеют решающее значение в любом проекте. В программной инженерии часто пренебрегают управлением изменениями на стороне базы данных, потому что данные считаются «слишком изменчивыми». Но именно потому, что данные являются «изменчивыми» и «постоянными» одновременно, хорошо спроектированный процесс контроля изменений должен быть краеугольным камнем правильной архитектуры среды базы данных.

Об использовании инструментов миграции кода

Стандартные сторонние инструменты, Oracle Migration Workbench и SQL Server Migration Assistant, могут быть полезны при миграции кода. Но что необходимо принимать во внимание, так это правило 80/20: когда код будет перенесен на 80% правильно, разрешение оставшихся 20% займет 80% ваших усилий по миграции.

Самый большой риск при использовании инструментов миграции — это, безусловно, восприятие «серебряной пули». У кого-то может возникнуть соблазн подумать: «Это справится со своей задачей, и мне просто нужно немного почистить и привести в порядок». Я наблюдал проект, который провалился из-за такого отношения команды конверсии и ее технического руководства.

С другой стороны, мне потребовалось четыре рабочих дня, чтобы выполнить базовое преобразование системы Microsoft SQL Server 2008 среднего размера (около 200 объектов) с использованием функции массовой замены Notepad++ в качестве основного инструмента редактирования.

Ни один из критических элементов миграции, которые я рассматривал до сих пор, не может быть решен с помощью инструментов миграции.

Конечно, используйте инструменты помощи при миграции, но помните, что они помогают только при редактировании. Полученный выходной текст необходимо проверить, изменить и, в некоторых случаях, переписать, чтобы он стал кодом, пригодным для производства.

Разработка инструментов искусственного интеллекта может устранить эти недостатки инструментов миграции в будущем, но я ожидаю, что различия между базами данных исчезнут до этого, и любой процесс миграции сам по себе станет ненужным. Так что, пока такие проекты нужны, нам придется делать это по-старому, используя старомодный человеческий интеллект.

Заключение . Использование инструментов помощи при миграции полезно, но это не панацея, и любой проект по преобразованию по-прежнему требует подробного рассмотрения вышеперечисленных моментов.

Миграция Oracle/SQL Server: всегда будьте внимательны

Oracle и Microsoft SQL Server являются двумя наиболее распространенными платформами РСУБД в корпоративной среде. Оба имеют базовое соответствие стандарту ANSI SQL, и небольшие сегменты кода могут быть перемещены с очень небольшими изменениями или даже как есть.

Это сходство создает обманчивое впечатление, что миграция между двумя платформами является простой и понятной задачей и что одно и то же приложение может быть легко перенесено с одной серверной части РСУБД на другую.

На практике такие миграции платформ далеко не тривиальны и должны учитывать тонкие элементы внутренней работы каждой платформы и, прежде всего, то, как они реализуют поддержку наиболее важного элемента управления данными: транзакций.

Хотя я рассмотрел две платформы РСУБД, которые лежат в основе моих знаний, одно и то же предупреждение — «похоже выглядит не значит, что работает одинаково» — следует применять к перемещению кода между любыми другими системами управления базами данных, совместимыми с SQL. И во всех случаях в первую очередь следует обратить внимание на то, как отличается реализация управления транзакциями между исходной и целевой платформами.