Проблемы переноса данных между различными СУБД, на примере транзита с MS SQL Server Express 2005 на MySQL 5

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

Основные трудности возникают когда необходимо полностью поменять платформу СУБД. Объекты которые необходимо перенести представляют из себя, схему СУБД, таблицы со структурой, данными и ограничениями, представления, хранимые процедуры и функции. Поэтому при переноси в первую очередь необходимо обратить внимание на, соответствие: кодировок исходного и конечного сервера; поддерживаемого стандарта языка SQL и PL/SQL.

Одна из утилит с помощью которой можно осуществить межплатформенный обмен данными, является Bulk copy program (BCP). Данная утилита имеет интерфейс командной строки, и запускается из меню Пуск(Start)->Выполнить. Главным преимущестовм данной утилиты, является высокая скорость перемещения данных, по сравнению со встроенными средствами, такими как Data Transformation Services (DTS), которое чаще всего применяется для этих целей. К недостаткам данной утилиты следует отнести то, что она не имеет графического интерфейса и не выполняет инструкции DDL по созданию таблиц, поэтому основным условием ее использования, является обязательное наличие таблиц в базе данных, кроме того, данная утилита не работает в пакетном режиме, т.е. за один раз нельзя перенести более одной таблицы, поэтому ее использование при переносе множества таблиц без специального скрипта, является довольно невозможным.

Высокая скорость работы обусловлена тем, что механизм переноса данных несколько отличается от традиционной вставки, когда операция регистрируется в журнале транзакций, после чего происходит занесение данных в базу. Следует отметить, что для больших по объему таблиц такой механизм требует значительного объема дискового пространства и по времени выполняется довольно долго. При использовании же BCP вставка записей осуществляется без регистрации этой операции в журнале транзакций, данная манипуляция называется быстрым массовым копированием (fast bulk copy). Кроме того необходимо что бы и сам MS SQL Server был настроен на поддержку данных манипуляций, для этого необходимо в свойствах базы данных на странице Options выбрать соответствующую модель резервного копирования Recovery model: Bulk-logged, так же необходимо, чтобы таблица в которую загружаются данные не имела индексов, их можно временно удалить.

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

Теперь рассмотрим различные варианты переноса СУБД пошагово c MS SQL Serever Express 2005 на MySQL 5.024a.

Использование утилиты BCP.

Первый шаг заключается в создании DDL скриптов, для их последующего выполнения на сервере, куда планируется перенести СУБД в данном случае MySQL. Сгенерировать SQL-скрипты DDL – запросов можно используя Microsoft SQL Server Management Studio Express, для этого необходимо перейти в исходную базу данных, "щёлкнуть" по ней правой кнопкой мыши и выбрать Generate SQL Scripts из меню All Tasks.

Далее необходимо выполнить эти скрипты на MySQL, для этого можно использовать утилиту MySQL Query Browser, которая входит в комплект поставки. Однако, если при проектировании таблиц использовались специфичные для MS SQL Server типы данных, то при выполнении этих скриптов MySQL генерирует ошибку, поэтому придется в ручную выполнить правку. Наиболее часто приходится приводить формат SmallDateTime к DateTime.

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

select a.a+'.'+a.b+'.'+a.c+',\' from (select Table_catalog as a,table_schema as b,
                             table_name as c from INFORMATION_SCHEMA.tables ) as a

После чего можно приступать к реализации исполняемого bat файла. Но перед этим необходимо рассмотреть параметры команды BCP.

BCP {tableName} {in | out} fileName options

• tableName – имя таблицы, с указанием базы данных и владельца, например: Finances.dbo.conformity, как видно, такой формат имени уже получен с помощью приведенного выше запроса, для последующего его копирования и вставки в исполняемый файл;
• in - перенос данных из файла в таблицу;
• out – перенос данных из таблицы в файл, в случае, если файл с таким именем уже есть, то происходит его автоматическая перезапись;
• fileName – имя внешнего файла – источника (приемника), возможно указывать абсолютное имя - d:/tmp/stock.txt, в противном случае файл будет расположен или располагается в текущем каталоге;
• options - с помощью опций можно указать множество параметров, поэтому рассмотрим наиболее важные с практической точки зрения
  • S - служит для указания имени сервера, при локальной работе является необязательным параметром;
  • U,- P - служат для аутентификации пользователя на сервере;
  • T - указывается при аутентификации на уровне операционной системы;
  • n – указывает, что данные выводимые в файл, будут иметь формат SQL Server (native-формат);
  • с – указывает, что файл будет иметь текстовый формат, имеющий в качестве разделителя "табуляцию";

Но основании вышесказанного исполняемый файл будет выглядеть следующим образом:

FOR %%i IN (\

Finances.dbo.conformity,\

Finances.dbo.documents,\

Finances.dbo.el_comp,\

Finances.dbo.s_kau,\

Finances.dbo.banks\

) DO bcp %%i out d:\tmp\tables\%%i.txt -c -T

Далее можно приступать к загрузке таблиц на MySQL, однако перед этим следует обратить внимание на кодировку полученных файлов, она должна быть windows-1251, в противном случае придется в файловом менеджере, например FAR, копировать содержимое файлов в буфер, менять кодировку( клавиша F8), а затем делать вставку, хотя существуют утилиты для автоматизации данного процесса. Непосредственно сам процес загрузки является довольно простой операцией, которую можно выполнить в пакетном режиме, для автоматизации процесса создания данной команды можно использовать возможности табличного процессора Excel, получив заранее список таблиц, запросом приведенным выше.

LOAD DATA INFILE 'd:/tmp/stock.txt' INTO TABLE test.ostatki IGNORE 1 LINES;

Опцию IGNORE 1 LINES – следует указывать, если в первой строке текстового файла содержатся заголовки столбцов.

Проблемы с которыми можно столкнуться в данном случае заключаются в следующем, вопервых, необходимо настроить MySQL на кодировку cp-1251, это делается в файле mysql.ini в разделе [mysqld] командами:

default-character-set=cp1251

skip-character-set-client-handshake

init_connect='SET NAMES cp1251'

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

Использование утилиты MySQL Migration Toolkit v 1.1.4

Данная утилита имеет удобный, интуитивно понятный графически интерфейс и практически решает поставленную задачу полностью, однако в ее работе существует ряд нюансов. Так же следует заметить, что существует платная версия – 2.3, данной утилиты, которая по заверению производителей имеет лучшую функциональность, но к сожалению в деле ее попробовать не удалось. Версия 1.1.4, очевидно, в силу своей бесплатности имеет ограниченные опциональные возможности, в частности, нет возможности выбора JDBC драйвера, т.е. для СУБД он жестко определен, хотя при необходимости можно поэкспериментировать с настройками в файлах RdbmsInfo*.lua – которые располагаются в инсталляционной папке утилит. Основная проблема была связана с тем, что несмотря на все настройки кодировок, как на стороне серверов, так и самой утилиты, получить итоговые таблицы отображающие текст в нужной кодировке после многочисленных попыток так и не удалось, сам процес переноса по времени занимал не более 10 минут примерный объем базы около 200 Мб. Далее в результате экспериментальных изысканий, было установлено, что нужно дополнительно прописывать параметры JDBC соединения. Теперь пошагово выполним процесс переноса СУБД.

В начале необходимо определить сервер, с которого будет выполняться перенос данных, рисунок 1.

Рисунок 1. Выбор исходной СУБД

В пункте дополнительных установок (Advanced Setting), обязательно в поле установка соединения (Connection String), см. рис.1 необходимо прописать следующую строку: jdbc:jtds:sqlserver://172.18.64.14:1433/Finances;user=Жмайлов;password=11111;useUnicode=true

За тем выбирается конечная СУБД, рисунок 2.

Рисунок 2. Установка параметров конечной СУБД

Здесь также в параметрах соединения необходимо прописать строку

jdbc:mysql://localhost:3306/?user=root&password;=11111&useServerPrepStmts;=false&useUnicode;=true

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

Рисунок 3. Шаг 3 - проверка соединений с СУБД


Рисунок 4. Шаг 4 - выбор схемы СУБД


Рисунок 5. Шаг 5 - получение скриптов исходной СУБД


Рисунок 6. Выбор типа объектов для переноса


Рисунок 7. Определение типа переносимых таблиц на конечной СУБД

Следует заметить, что определение типа переносимых таблиц на конечной СУБД, рисунок 7, зависит от конкретной ситуации, единственная рекомендация – использование кодировки utf8. В результате выполненных действий, на конечной СУБД будет находится точная копия базы с MS SQL Server, адаптированная под MySQL. Однако с помощью данной утилиты так и не удалось решить проблему переноса хранимых процедур.

Была предпринята попытка решения данной проблемы с помощью бесплатной программы T-SQL to MySQL procedure converter (http://puzzle.dl.sourceforge.net/sourceforge/tsql2mysql/tsql2mysql_readme.htm). Данная утилита, так же имеет интерфейс командной строки, формат запуска: tsql2mysql <[infile] >[outfile], где в угловых скобках указывается исходный файл, а outfile – имя файла, куда необходимо выполнять вывод. Следует заметить, что использование данной утилиты сильно облегчило перевод хранимых процедур, и соответствует тому, что заявлено на сайте разработчика, а также идеально подходит для небольших, по количеству строк кода процедур. Однако в случае, когда имеется большое количество строк кода, более 200 , отягощенное условиями и курсорами использование данной утилиты вызывает определенные затруднения, приходится вручную выполнять приведение синтаксиса.

Таким образом, использование утилиты BCP целесообразно в тех случаях, когда необходимо перенести единичные таблицы больших размеров, примерно более 60Мб. записей. Использование MySQL Migration Toolkit, необходимо использовать при переносе всей структуры и данных СУБД. Для переноса хранимых процедур использовать SQL to MySQL procedure converter