• Статья
Перенос данных с MySQL на SQL Express. Часть I. ODBC

С оригиналом поста можно ознакомиться в блоге Алексея Шуленина https://blogs.msdn.com/alexejs/archive/2009/06/17/mysql-sql-express-i-odbc.aspx.

За последние два месяца заметно участились вопросы на тему миграции с MySQL. Анархисты двинулись в сторону PostgreSQL и Berkeley DB, реалисты – в направлении SQL Express. Данная заметка предназаначена для реалистов. Сначала я собирался написать, что она рассчитана на мускулистов, которые решили не усложнять себе жизнь и выбрали посмотреть SQL Express, но вовремя понял, что подобной аннотацией обрекаю себя на вводный курс молодого бойца. Не хотелось бы. Не оттого, что зазнался или ленюсь, просто опасаюсь не отразить какие-то моменты, которые покажутся очевидными и давно всем известными. Поэтому введение в SQL Server мы опускаем. Кто водил трактор "Коммунар", без труда поведет Т-34. Аналогия справедлива до определенных пределов. Зависит от задачи. Если человек сидел за штурвалом Л-39, ему будет проще, чем остальным, освоить перехватчик, но учиться все равно придется. В этой заметке сложных задач не будет. Основными действующими персонажам в ней будут MySQL 5.0

рис. 1

и SQL Express 2008:

рис. 2

Требуется доступиться до первого и передать данные на второй.

Для начала стоит поставить SQL Express. Можно ставить рядом с MySQL. У меня стоят и не дерутся.

Под вывеской Express Edition выдаются следующие три комплекта. Здесь https://www.microsoft.com/rus/express/sql/download они выдаются по-русски, здесь https://www.microsoft.com/express/sql/download/ по-английски:

1. SQL Server 2008 Express (Runtime Only)

Это голый датабазный движок безо всяких фенек и гуевых наворотов с некоторыми ограничениями по сравнению с взрослыми редакциями (узнать об ограничениях здесь - https://www.microsoft.com/sqlserver/2008/en/us/editions.aspx). Обычно его берут, чтобы распространять в составе своего приложения. Распространять тоже можно абсолютно легально и бесплатно, там это написано.

2. SQL Server 2008 Express with Tools

Это п.1 + Management Studio (графическая тула для разработки и администрирования).

3. SQL Server 2008 Express with Advanced Services

Это п.2 + полнотекстовый поиск и службы отчетности.

От жадности я скачал п.3 - https://www.microsoft.com/downloads/details.aspx?FamilyId=B5D1B8C3-FDA5-4508-B0D0-1311D670E336&displaylang=en. Если есть желание поставить не английскую, а русскую версию, выберите в комбобоксе Change Language на страничке русский язык. Ставится без вопросов. Если вопросы возникнут, задавать на форуме https://social.msdn.microsoft.com/Forums/ru-RU/sqlclubru/threads.

Там же поблизости выдают Visual Studio (https://www.microsoft.com/exPress/). Тоже Express, тоже нахаляву. Скачайте до кучи, раз вы все равно в те края зашли. Она нам пригодится в следующем посте.

Основное содержание этой заметки у меня было подготовлено достаточно давно. За то время, пока я собирался публиковать ее в блоге, на SQL Server 2008 вышел 1-й сервис-пак. Поэтому если вы еще не бросились сразу качать, а решили дочитать заметку до конца, не берите пример с меня (рис.2), а поставьте себе SP1.

Если вас устраивает п.1, то вот здесь - https://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=01af61e6-2f63-4291-bcad-fd500f6027ff – лежит дистрибутив экспресса с уже включенным сервис-паком, можно брать и ставиться с нуля. Что означают три приведенные там файла и который из них брать, разъясняется здесь - https://blogs.msdn.com/sqlexpress/archive/2008/08/22/the-sql-express-2008-family-is-getting-bigger.aspx. Кароче, для х64 качаете первый файл, для х86 – третий.

Если вам нужен п.2 или 3, то сервис-пак придется качать и накатывать отдельно. Вот здесь лежит SP1 отдельно - https://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=en. Неудобство состоит в том, что здесь он сразу для всего: You can use these packages to upgrade any SQL Server 2008 edition, следовательно, весит до черта. Извините.

К тому времени, как вы будете читать эту заметку, возможно, выйдут еще какие-нибудь обновления. С ними будете разбираться сами. Наверняка их отследит Алексей Князев в своем блоге - http://mvp.itcommunity.ru/blogs/rsug/archive/2009/05/19/64752.aspx. На данный момент после SP1 доступны обновления Cumulative update package 2 for SQL Server 2008 Service Pack 1 и Cumulative update package 5 for SQL Server 2008. Только их просто так не дают, требуется наступить на грабли и предъявить шишку.

Аналогично обстоят дела с обновлениями к Visual Studio Express. Можно скачать дистрибутив с уже включенным 1-м сервис паком - https://www.microsoft.com/downloads/details.aspx?FamilyId=F3FBB04E-92C2-4701-B4BA-92E26E408569&displaylang=en. Здесь сервис-пак лежит отдельно - https://www.microsoft.com/downloads/details.aspx?FamilyId=FBEE1648-7106-44A7-9649-6D9F6D58056E&displaylang=en.

Вернемся к задаче. Классическим инструментом для такого типа задач в SQL Server являются Integration Services. К сожалению, они не входят в состав Express Edition. Нужно придумать что-нибудь попроще. Вариантом попроще является прилинкованный сервер. Прилинкованным сервером (linked server) в SQL Server называется возможность создания соединения к OLE DB-источнику и выполнение на нем команд из SQL Server. Параметры соединения и безопасности хранятся в настройках прилинкованного сервера - https://msdn.microsoft.com/ru-ru/library/ms190479.aspx. Единственный известный мне OLE DB Provider для MySQL от Cherry City Software (http://cherrycitysoftware.com/ccs/Providers/ProvMySQL.aspx) работает с пятеркой, мягко говоря, несколько фигово: http://forums.mysql.com/read.php?37,60568,197303#msg-197303, поэтому, когда народ по простоте ненавязчиво интересуется, а где, типа, взять OLE DB Provider для MySQL, ожидая шквал откликов, - ну как же: OLE DB – это такой стандарт, который, вроде как, должны поддерживать все уважающие себя производители СУБД, а MySQL не самая распоследняя из них, так вот, когда народ задает такой вопрос, настоящие знатоки поэзии либо отмалчиваются (http://www.mysql.ru/webboard/index.html?n1=8669&n2=1), либо рекомендуют ODBC (http://www.sql.ru/forum/actualthread.aspx?tid=572384). Пришлось остановиться на ODBC.ODBC-драйвер я брал с сайта MySQL - MySQL Connector/ODBC 5.1 Downloads, дистрибутив с Windows MSI Installer. Ставится влет. Пошаговую инструкцию, если вдруг кому-то понадобится, можно почерпнуть здесь -http://freehost.com.ua/faq/buleten/article/article_id/20. В списке драйверов образуется драйвер MySQL ODBC 5.1, версии 5.01.05.00, MYODBC5.DLL от 18/08/2008.

рис. 3

Теперь до MySQL можно доступаться через ODBC. Делать это можно, предварительно создав ODBC DSN (Data Source Name), либо напрямую прописывая координаты источника в строке соединения. Рассмотрим оба этих способа.

Создание DSN.

Найдите в Windows кнопку Start, которая обычно находится слева внизу или слева или справа вверху экрана. Нажмите ее. Выберите пункт Adminstrative Tools и в нем Data Sources (ODBC).

рис. 4

Если пункта Adminstrative Tools нет, зайдите в Control Panel и найдите его там. Если вы хотите, чтобы на будущее Adminstrative Tools появлялись сразу в стартовом меню, кликните правой кнопкой на кнопку Start, выберите Properties, закладка Start Menu, нажмите кнопку Customize, найдите в списке System Administrative Tools \ Display on the All Programs menu and the Strat menu и поставьте напротив него жирную точку.

В ODBC создаем новый System DSN. Встаем на закладку System DSN, жмем кнопку Add.

рис. 5

Из списка драйверов (рис.3) выбираем MySQLный драйвер, жмем кнопку Finish. Откроется окно конфигурации MySQLного драйвера. Введите в него информацию о соединении с источником. Нажмите кнопку Details и введите при необходимости дополнительную информацию по настройкам соединения. У меня согласно рекомендациям отмечены следующие пункты: закладка Flag1 - Return matched rows instead of affected rows; Allow big result sets; Use compression; Treat BIGINT columns as INT columns, Enable safe options; Flag2 - Don't prompt when connecting; Flag3 - Include table name in SQLDescribeCol(); Disable transaction support; Allow multiple statements. Остальные закладки оставлены по умолчанию.

рис. 6

Создание прилинкованного сервера.

Откройте SQL Server Management Studio (Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio)

рис. 7

Введите информацию о соединении с SQL Server и нажмите кнопку Connect:

рис. 8

В панели Object Explorer раскройте пункт Server Objects и кликните правой кнопкой по папке Linked Servers. В контекстном меню выберите пункт New Linked Server.

рис. 9

Задайте параметры создаваемому прилинкованному серверу: имя (без разницы), OLE DB провайдер – MSDASQL (в предыдущем пункте мы содали ODBC-соединение, но прилинкованный сервер хочет OLE DB, поэтому выбираем OLE DB провайдер для работы с ODBC, Data Source – указываем имя ранее созданного ODBC DSN.

рис. 10

Следующий экран (Security) позволяет отобразить SQL Serverных пользователей на MySQLных. В данном случае я просто говорю, что все SQL Serverные пользователи будут идти на MySQL под рутом.

рис. 11

На следующем экране (Server Options) при необходимости выставляются дополнительные опции соединения. RPC OUT означает, что я могу вызывать процедуры с прилинкованного сервера, а RPC – что MySQL, в свою очередь, может их вызывать с меня.

рис. 12

Жмем ОК и идем в запросное окно, чтобы убедиться, что прилинкованный сервер создан удачно. Хотя если бы у нее соединение к нему не проходило, она бы уже ругнулась. Нажмите New Query:

рис. 13

Напишите запрос

exec sp_tables_ex @table_server = 'MySQL'

Нажмите Ctrl-E или ! Execute в верхней панели. Мы получили список таблиц с MySQL.

рис. 14

Теперь SQL Server соединен с MySQL, как обычное клиентское приложение, и может обращаться к нему с запросами. Результаты запросов доступны на стороне SQL Server:

select * from openquery(mysql, 'select version()')

рис. 15

Чтобы выполнить в окне запросов SSMS не весь скрипт, а отдельную строчку, выделите ее и скажите Ctrl-E или ! Execute в верхней панели.

Для отправки запросов на прилинкованный сервер со стороны SQL Server используется функция openquery(). Первым ее параметром выступает имя прилинкованного сервера, в данном случае MySQL, вторым – текст запроса. SQL Server ничего не делает с этим запросом, а просто отдает его прилинкованному серверу как есть и принимает от него результаты. Следовательно, запрос пишется на том диалекте SQL, который понимает прилинкованный сервер. Вы это уже заметили, потому что функции version() в SQL Server нет, есть @@version. Можно показать это более наглядно, например, при помощи пресловутого предиката LIMIT, которого нет в SQL Server, что всякий раз вызывает приступы афигенной гордости у мускулистов, потому что такое же решение с использованием row_number() получается чуть длиннее.

select * from openquery(mysql, 'select * from information_schema.tables limit 10, 5;')

рис. 16

Если со стороны SQL Server на MySQL требуется послать команду, не возвращающую результатов, например, DDL или DML-оператор, это делается при помощи оператора execute. Например,

exec ('create table t (fld int);') at MySQL

Можно проверить, что на стороне MySQL действительно появилась табличка t в той его базе, которая прописана в свойствах прилинкованного сервера, точнее, в настройках ODBC DSN (рис.6), на который построен этот прилинкованный сервер (рис.10), с теми колонками, как заказывали.

select table_schema, table_name, table_type, engine, version, table_rows, create_time from openquery(MySQL, 'select * from information_schema.tables where table_name = ''t'';')
 
select table_schema, table_name, column_name, ordinal_position, data_type, column_type, privileges, is_nullable, column_default from openquery(MySQL, 'select * from information_schema.columns where table_name = ''t'' and column_name = ''fld'';')

рис. 17

Аналогично при помощи ф-ции execute можно добавлять, обновлять, удалять записи, вызывать хранимые процедуры и т.д. Не даром же мы включали поддержку RPC в опциях прилинкованного сервера (рис.12)

рис. 18

Можно пойти непосредственно на MySQL и там убедиться, что SQL Server ничего не мухлюет, такая табличка есть и записи добавились:

рис. 19

Создание прилинкованного сервера без создания ODBC DSN

В принципе, после установки ODBCшного драйвера можно обойтись без создания источника данных (рис.5, 6), а передавать строку соединения источника в свойствах прилинкованного сервера. Прилинкованный сервер, кстати говоря, можно создавать скриптом. Подсмотреть скрипт можно, кликнув правой кнопкой в Object Explorere на прилинкованный сервер и сказав Script Linked Server. Аналогично скрипты можно генерить на большинство объектов SQL Server.

рис. 20

Взяв этот скрипт за основу и слегка его модифицировав, можно создать прилинкованный сервер на MySQL с использованием безDSNного (DSNless) соединения. В этом случае драйвер, имя сервера MySQL, порт, имя базы и другие параметры подключения указываются в свойствах прилинкованного сервера:

if exists (select 1 from sys.servers where name = 'MySQL') 
 exec sp_dropserver @server = 'MySQL', @droplogins = 'droplogins'
 
go
 
exec sp_addlinkedserver @server = 'MySQL', @srvproduct = 'MySQLDatabase', @provider = 'MSDASQL', 
 @provstr = 'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; PORT=31006; DATABASE=bsm_demo; UID=root; PWD=; OPTION=3'
 
 go
 
exec sp_addlinkedsrvlogin @rmtsrvname = 'MySQL', @useself = 'false', @locallogin = NULL, 
 @rmtuser = 'root', @rmtpassword = ''
 
go
 
exec sp_serveroption @server = 'MySQL', @optname = 'rpc', @optvalue = 'true'
 
exec sp_serveroption @server = 'MySQL', @optname = 'rpc out', @optvalue = 'true'

Скрипт 1

рис. 21

Прилинкованный сервер позволяет без проблем забирать данные из таблиц MySQL в таблицы SQL Server для большинства стандартных SQL-типов:

if object_id('tempdb..#b_sale_location_city', 'U') is not null drop table #b_sale_location_city
 
select * into #b_sale_location_city from openquery(MySQL, 'select * from b_sale_location_city')
 
select id, name from #b_sale_location_city

рис. 22

В зависмости от кривизны драйвера бывают некоторые нюансы. Например,

select * from openquery(mysql, 'select CATEGORY_ID from b_blog_post')

Msg 7347, Level 16, State 1, Line 1

OLE DB provider 'MSDASQL' for linked server 'mysql' returned data that does not match expected data length for column '[MSDASQL].CATEGORY_ID'. The (maximum) expected data length is 200, while the returned data length is 6.

Из select * from openquery(mysql, 'select * from information_schema.columns where table_name = ''b_blog_post''') видно, что в действительности поле CATEGORY_ID имеет тип char(100). Я обошел эту проблему, явно указав MySQL коллацию: select * from openquery(mysql, 'select convert(CATEGORY_ID using latin1) from b_blog_post').

Или select * from openquery(mysql, 'select DETAIL_TEXT from b_learn_lesson')

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Из select * from openquery(mysql, 'select * from information_schema.columns where table_name = ''b_learn_lesson''') видно, что это поле имеет тип longtext. Тип longtext текущий MySQLный драйвер (mysql-connector-odbc-5.1.5-win32.msi) упорно не понимает. Это известная проблема, об которую спотыкаются многие - http://www.eggheadcafe.com/conversation.aspx?messageid=34292206&threadid=34292163. Решение неизвестно. "If you're using out of the box ODBC longtext won't be supported". Вашу мать, дорогая редакция! Я понимаю, если бы нынешний драйвер для MySQL писал какой-нибудь дядя Вася со стороны, но его писали свои же для себя же. Могли бы как-то расстараться сделать нормальную поддержку своих же собственных типов. Если бы, не дай бог, он назывался Microsoft MySQL ODBC Driver, по нам бы уже не прошелся только ленивый. А так ничего, все хавают и вроде как довольны. На данный момент не придумал ничего лучше, чем доставать по кускам. select * from openquery(mysql, 'select left(DETAIL_TEXT, 4000) from b_learn_lesson'); select * from openquery(mysql, 'select substring(DETAIL_TEXT, 4001, 4000) from b_learn_lesson') и т.д.

Автор: Алексей Шуленин