Практический опыт миграции с MySQL на SQL Express. Сравнение схем и перенос данных

Статья
10/20/2021

Практический опыт миграции с MySQL на SQL Express. Сравнение схем и перенос данных

7. Сравнение структур баз

Итак на данный момент у нас имеется старый контент сайта в БД bsm_demo на MySQL и новый, но пустой сайт в БД bitrix на SQL Express. Необходимо перенести старый контент в новую базу. В связи с этим встал вопрос, насколько тождественны структуры баз Битрикса в случае MySQL и SQL Express. Для исследования этого вопроса использовался прилинкованный сервер со стороны SQL Express на MySQL. Прилинкованный сервер использует MSDASQL (OLE DB поверх ODBC) в связи с тем, что доступного OLE DB-провайдера на MySQL не нашлось (см. п. 2). На машину с установленными MySQL и SQL Express был установлен ODBC-драйвер для MySQL 5.1 (http://dev.mysql.com/downloads/connector/odbc/5.1.html) и создан прилинкованный сервер без создания DSN:

          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'

Скрипт 7.1

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

Рис. 7.1

В первую очередь интересовал вопрос, насколько отличаются базы по составу таблиц. Данный запрос выводит несовпадения:

use bitrix with tbl_rows_sqlsrv as ( select t.name, p.n from sys.tables t join (select object_id, sum(row_count) n from sys.dm_db_partition_stats where index_id in (0, 1) group by object_id) p on t.object_id = p.object_id ) , tbl_rows_mysql as ( select * from openquery(mysql, 'select table_name name, table_rows n from information_schema.tables where table_schema = ''bsm_demo''') ) select sqlsrv.name, sqlsrv.n, mysql.name, mysql.n from tbl_rows_sqlsrv sqlsrv full outer join tbl_rows_mysql mysql on sqlsrv.name = mysql.name where sqlsrv.name is null or mysql.name is null or sqlsrv.n <> mysql.n name n name n B_OPTION 112 b_option 113 B_STAT_SESSION_DATA 0 b_stat_session_data 1 B_FILE_ACTION 0 NULL NULL B_POSTING_LOCK 0 NULL NULL

B_FAVORITE_LANG 0 NULL NULL

Скрипт 7.2

Вывод. Все 319 таблиц в базе при инсталляции MySQL имеют соответствия (т.е. таблицы с тем же именем) в SQL Serverной инсталляции.

В SQL Serverной инсталляции имеются 3 таблицы, не имеющих соответствия в MySQLной инсталляции:

B_FILE_ACTION
B_POSTING_LOCK
B_FAVORITE_LANG

Это благоприятная ситуация для нас, поскольку мы собираемся переносить данные из MySQL в SQL Server. Хуже, если бы, наоборот, в MySQL имелись таблицы, которые бы было непонятно куда переносить в SQL Server.

Из 319 таблиц MySQLной инсталляции 2 не совпадают по числу строк с соответствющими им таблицами в SQL Serverной инсталляции:

SQL Server		MySQL
B_OPTION	112	b_option	113
B_STAT_SESSION_DATA	0	b_stat_session_data	1

Этот факт имеет, скорее, роль комментария, поскольку в данном случае инсталляции Битрикса как в случае MySQL, так и SQL Server "чистые". В реальной жизни клиент уже будет работать продолжительный период с MySQLной базой, поэтому данных там, очевидно, будет больше, чем в "свежей" SQL Serverной БД. Речь не идет о каком-то слиянии, реконсилиации, просто данные из MySQL требуется перенести в соответствующие таблицы SQL Server, перетерев все, что туда уже успел добавить процесс инсталляции Битрикс. Инсталляция Битрикса в варианте SQL Server создает, по сути, готовые структуры для приема данных со стороны SQL Server и избавляет нас от необходимости рассматривать миграцию метаданных.

Следующим пунктом было исследование наборов полей в соответствующих таблицах, т.е. берем таблицу MySQL и сравниваем с таблицей с таким же именем в SQL Server по именам полей: какие поля есть в таблице MySQL, которых нет в таблице SQL Server и наоборот.

use bitrix with col_sqlsrv as ( select t.name as tbl_name, c.name as col_name from sys.columns c join sys.tables t on c.object_id = t.object_id ) , col_mysql as ( select * from openquery(mysql, 'select table_name tbl_name, column_name col_name from information_schema.columns where table_schema = ''bsm_demo''') ) select * from col_sqlsrv sqlsrv full outer join col_mysql mysql on sqlsrv.tbl_name = mysql.tbl_name and sqlsrv.col_name = mysql.col_name

where sqlsrv.col_name is null or mysql.col_name is null

Скрипт 7.3

Сравнение выявило абсолютное тождество наборов полей в соответствующих таблицах. Исключением являются три таблицы в SQL Server, которых нет в варианте установки MySQL (см. Скрипт 7.2).

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

with
col_sqlsrv(tbl_name, col_name,  col_pos) as (
select object_name(object_id), name, column_id from sys.columns
)
, col_mysql(tbl_name, col_name,  col_pos) as (
select * from openquery(mysql, 'select table_name, column_name, ordinal_position from information_schema.columns where table_schema = ''bsm_demo''')
)
select * from col_sqlsrv sqlsrv join col_mysql mysql
on sqlsrv.tbl_name = mysql.tbl_name and sqlsrv.col_name = mysql.col_name
where sqlsrv.col_pos <> mysql.col_pos

Скрипт 7.4

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

Наконец, интересовало соответствие типов и длины в одноименных колонках. Были получены типы колонок, использующиеся Битриксом в случае MySQLной и SQL Serverной инсталляций:

select * from openquery(mysql, 'select distinct data_type from information_schema.columns where table_schema = ''bsm_demo''')
select distinct type_name(c.user_type_id) from sys.columns c join sys.tables t on c.object_id = t.object_id where type = 'U'

Скрипт 7.5

В SQL Server их оказалось 11, в MySQL – 16. Например, вMySQL используется тип smallint, а в SQL Server его не нашли. В MySQL есть разновидности блобовских типов, которых действительно нет в SQL Server, например, mediumtext, longtext. В случае SQL Server им соответствует один тип text. Кстати говоря, Битрикс в SQL Server почему-то до сих пор вовсю использует text/image, хотя они скоро выйдут из употребления, т.к. уже больше 3-х лет назад появились varchar(max), varbinary(max). Также, например, Битрикс использует тип date в MySQL, а в SQL Server по старинке datetime. Вручную была построена таблица соответствия типов, т.е. какой тип MySQL в какой тип SQL Server можно без потерь переносить. Если существует экземпляр типа А, который не перенесется в тип Б без обрезания или дополнительных преобразований, такой перенос считается невозможным. Все типы и в MySQL, и в SQL Server можно разбить на числовые, строковые, бинарные и календарные. Перенос внутри каждой категории считается допустимым, при этом длина поля приемника должна быть не меньше, чем у источника, а в случае численных полей с фиксированной точкой то же распространяется и на кол-во знаков после запятой. Вот запрос, который проверяет нарушения этого правила:

with
col_sqlsrv(tbl_name, col_name, col_type, col_len, col_prec, col_scal) as (
select object_name(object_id), name, type_name(user_type_id), max_length, precision, scale from sys.columns
),
col_sqlsrv1(tbl_name, col_name, col_type, col_len, col_prec, col_scal) as (
select tbl_name, col_name,
   case when col_type in ('bigint', 'int', 'tinyint', 'decimal', 'numeric') then 'N'

        when col_type in ('float') then 'F' 

        when col_type in ('datetime') then 'D'

        when col_type in ('char', 'varchar', 'text') then 'C'

        when col_type in ('image') then 'B' 

   end, 

   case when col_type = 'text' then power(cast(2 as bigint), 31) - 1

        else col_len

   end, 

   col_prec, col_scal from col_sqlsrv

)
, col_mysql(tbl_name, col_name, col_type, col_len, col_prec, col_scal) as (
select * from openquery(mysql, 'select table_name, column_name, data_type, character_maximum_length, numeric_precision, numeric_scale from information_schema.columns where table_schema = ''bsm_demo''')
)
, col_mysql1(tbl_name, col_name, col_type, col_len, col_prec, col_scal) as (
select tbl_name, col_name,
   case when col_type in ('bigint', 'int', 'tinyint', 'decimal', 'smallint') then 'N'

        when col_type in ('float') then 'F' 

        when col_type in ('datetime', 'timestamp') then 'D'

        when col_type in ('char', 'varchar', 'text', 'mediumtext', 'longtext') then 'C'

        when col_type in ('longblob') then 'B' end, 

   col_len, col_prec, col_scal from col_mysql

)
select sqlsrv.*, mysql.col_type, mysql.col_len, mysql.col_prec, mysql.col_scal from col_sqlsrv1 sqlsrv join col_mysql1 mysql
on sqlsrv.tbl_name = mysql.tbl_name and sqlsrv.col_name = mysql.col_name
where mysql.col_type <> sqlsrv.col_type
or mysql.col_type = sqlsrv.col_type and mysql.col_len > sqlsrv.col_len
or mysql.col_type = sqlsrv.col_type and (mysql.col_prec > sqlsrv.col_prec or mysql.col_scal > sqlsrv.col_scal)
order by 1, 2

Скрипт 7.6

Первое условие в where не нарушается, что означает, что с преобразованием типов проблем нет – числовые типы переносятся в числовые, текстовые в текстовые и т.д. Однако выявлено 128 колонок, нарушающих второе и третье условие в where, что означает, что при переносе данных возможно возникновение ошибки из-за недостаточного размера поля приемника.

		SQL Express				MySQL
Таблица	Колонка	Категория типа	Размер поля	Численная длина	После запятой	Категория типа	Размер поля	Численная длина	После запятой
B_ADV_BANNER	COMMENTS	C	1000	0	0	C	65535	NULL	NULL
B_ADV_BANNER	KEYWORDS	C	1000	0	0	C	65535	NULL	NULL
B_ADV_BANNER	STATUS_COMMENTS	C	500	0	0	C	65535	NULL	NULL
B_ADV_BANNER	URL	C	8000	0	0	C	65535	NULL	NULL
B_ADV_CONTRACT	ADMIN_COMMENTS	C	500	0	0	C	65535	NULL	NULL
B_ADV_CONTRACT	DESCRIPTION	C	2000	0	0	C	65535	NULL	NULL
B_ADV_CONTRACT	KEYWORDS	C	1000	0	0	C	65535	NULL	NULL
B_ADV_TYPE	DESCRIPTION	C	500	0	0	C	65535	NULL	NULL
B_CATALOG_LOAD	VALUE	C	2000	0	0	C	65535	NULL	NULL
B_EVENT	C_FIELDS	C	2147483647	0	0	C	4294967295	NULL	NULL
B_EVENT_LOG	REQUEST_URI	C	2000	0	0	C	65535	NULL	NULL
B_EVENT_LOG	USER_AGENT	C	2000	0	0	C	65535	NULL	NULL
B_EVENT_TYPE	LID	C	2	0	0	C	201	NULL	NULL
B_FAVORITE	COMMENTS	C	8000	0	0	C	65535	NULL	NULL
B_FAVORITE	URL	C	8000	0	0	C	65535	NULL	NULL
B_FORM	DESCRIPTION	C	8000	0	0	C	65535	NULL	NULL
B_FORM	FILTER_RESULT_TEMPLATE	C	8000	0	0	C	65535	NULL	NULL
B_FORM	TABLE_RESULT_TEMPLATE	C	8000	0	0	C	65535	NULL	NULL
B_FORM_2_GROUP	PERMISSION	N	1	3	0	N	NULL	10	0
B_FORM_ANSWER	FIELD_PARAM	C	8000	0	0	C	65535	NULL	NULL
B_FORM_ANSWER	MESSAGE	C	8000	0	0	C	65535	NULL	NULL
B_FORM_FIELD	COMMENTS	C	8000	0	0	C	65535	NULL	NULL
B_FORM_FIELD	FILTER_TITLE	C	8000	0	0	C	65535	NULL	NULL
B_FORM_FIELD	RESULTS_TABLE_TITLE	C	8000	0	0	C	65535	NULL	NULL
B_FORM_FIELD	TITLE	C	8000	0	0	C	65535	NULL	NULL
B_FORM_RESULT_ANSWER	ANSWER_TEXT	C	8000	0	0	C	65535	NULL	NULL
B_FORM_RESULT_ANSWER	ANSWER_TEXT_SEARCH	C	2147483647	0	0	C	4294967295	NULL	NULL
B_FORM_RESULT_ANSWER	ANSWER_VALUE_SEARCH	C	2147483647	0	0	C	4294967295	NULL	NULL
B_FORM_RESULT_ANSWER	USER_TEXT	C	2147483647	0	0	C	4294967295	NULL	NULL
B_FORM_RESULT_ANSWER	USER_TEXT_SEARCH	C	2147483647	0	0	C	4294967295	NULL	NULL
B_FORM_STATUS	DESCRIPTION	C	8000	0	0	C	65535	NULL	NULL
B_FORUM	DESCRIPTION	C	1000	0	0	C	65535	NULL	NULL
B_FORUM_FILTER	USE_IT	C	1	0	0	C	50	NULL	NULL
B_FORUM_MESSAGE	ID	N	4	10	0	N	NULL	19	0
B_FORUM_MESSAGE	TOPIC_ID	N	4	10	0	N	NULL	19	0
B_FORUM_PRIVATE_MESSAGE	ID	N	4	10	0	N	NULL	19	0
B_FORUM_PRIVATE_MESSAGE	IS_READ	C	1	0	0	C	50	NULL	NULL
B_FORUM_PRIVATE_MESSAGE	USE_SMILES	C	1	0	0	C	50	NULL	NULL
B_FORUM_STAT	ID	N	4	10	0	N	NULL	19	0
B_FORUM_SUBSCRIBE	NEW_TOPIC_ONLY	C	1	0	0	C	50	NULL	NULL
B_FORUM_TOPIC	ID	N	4	10	0	N	NULL	19	0
B_FORUM_TOPIC	LAST_MESSAGE_ID	N	4	10	0	N	NULL	19	0
B_FORUM_TOPIC	TOPIC_ID	N	4	10	0	N	NULL	19	0
B_FORUM_USER	ID	N	4	10	0	N	NULL	19	0
B_FORUM_USER_TOPIC	ID	N	4	10	0	N	NULL	19	0
B_IBLOCK_ELEMENT	DETAIL_TEXT	C	2147483647	0	0	C	4294967295	NULL	NULL
B_IBLOCK_ELEMENT	PREVIEW_TEXT	C	2000	0	0	C	65535	NULL	NULL
B_IBLOCK_ELEMENT_PROPERTY	VALUE	C	2000	0	0	C	65535	NULL	NULL
B_IBLOCK_FIELDS	DEFAULT_VALUE	C	2147483647	0	0	C	4294967295	NULL	NULL
B_LDAP_SERVER	DESCRIPTION	C	5000	0	0	C	65535	NULL	NULL
B_LDAP_SERVER	FIELD_MAP	C	2000	0	0	C	65535	NULL	NULL
B_LEARN_CHAPTER	DETAIL_TEXT	C	2147483647	0	0	C	4294967295	NULL	NULL
B_LEARN_LESSON	DETAIL_TEXT	C	2147483647	0	0	C	4294967295	NULL	NULL
B_LIST_RUBRIC	DESCRIPTION	C	2000	0	0	C	65535	NULL	NULL
B_MAIL_FILTER	ACTION_VARS	C	5000	0	0	C	65535	NULL	NULL
B_MAIL_FILTER	DESCRIPTION	C	2000	0	0	C	65535	NULL	NULL
B_MAIL_FILTER_COND	STRINGS	C	5000	0	0	C	65535	NULL	NULL
B_MAIL_MAILBOX	DESCRIPTION	C	5000	0	0	C	65535	NULL	NULL
B_MAIL_MESSAGE	BODY	C	2147483647	0	0	C	4294967295	NULL	NULL
B_MAIL_MESSAGE	FULL_TEXT	C	2147483647	0	0	C	4294967295	NULL	NULL
B_MAIL_MSG_ATTACHMENT	FILE_DATA	B	16	0	0	B	4294967295	NULL	NULL
B_PERF_ERROR	ERRFILE	C	2000	0	0	C	65535	NULL	NULL
B_PERF_ERROR	ERRSTR	C	2000	0	0	C	65535	NULL	NULL
B_PERF_HIT	REQUEST_URI	C	2000	0	0	C	65535	NULL	NULL
B_PERF_HIT	SCRIPT_NAME	C	2000	0	0	C	65535	NULL	NULL
B_PERF_SQL	COMPONENT_NAME	C	2000	0	0	C	65535	NULL	NULL
B_PERF_SQL	MODULE_NAME	C	2000	0	0	C	65535	NULL	NULL
b_search_content	PARAM1	C	1000	0	0	C	65535	NULL	NULL
b_search_content	PARAM2	C	1000	0	0	C	65535	NULL	NULL
b_search_content	SEARCHABLE_CONTENT	C	2147483647	0	0	C	4294967295	NULL	NULL
b_search_custom_rank	PARAM1	C	2000	0	0	C	65535	NULL	NULL
b_search_custom_rank	PARAM2	C	2000	0	0	C	65535	NULL	NULL
B_SEC_SESSION	SESSION_DATA	C	2147483647	0	0	C	4294967295	NULL	NULL
B_STAT_ADV	DESCRIPTION	C	8000	0	0	C	65535	NULL	NULL
B_STAT_EVENT	DESCRIPTION	C	8000	0	0	C	65535	NULL	NULL
B_STAT_EVENT_LIST	REDIRECT_URL	C	8000	0	0	C	65535	NULL	NULL
B_STAT_EVENT_LIST	REFERER_URL	C	8000	0	0	C	65535	NULL	NULL
B_STAT_EVENT_LIST	URL	C	8000	0	0	C	65535	NULL	NULL
B_STAT_GUEST	FIRST_URL_FROM	C	8000	0	0	C	65535	NULL	NULL
B_STAT_GUEST	FIRST_URL_TO	C	8000	0	0	C	65535	NULL	NULL
B_STAT_GUEST	LAST_CITY_INFO	C	8000	0	0	C	65535	NULL	NULL
B_STAT_GUEST	LAST_COOKIE	C	8000	0	0	C	65535	NULL	NULL
B_STAT_GUEST	LAST_URL_LAST	C	8000	0	0	C	65535	NULL	NULL
B_STAT_GUEST	LAST_USER_AGENT	C	8000	0	0	C	65535	NULL	NULL
B_STAT_HIT	COOKIES	C	8000	0	0	C	65535	NULL	NULL
B_STAT_HIT	URL	C	8000	0	0	C	65535	NULL	NULL
B_STAT_HIT	URL_FROM	C	8000	0	0	C	65535	NULL	NULL
B_STAT_HIT	USER_AGENT	C	8000	0	0	C	65535	NULL	NULL
B_STAT_PAGE	URL	C	2000	0	0	C	65535	NULL	NULL
B_STAT_PATH	PAGES	C	8000	0	0	C	65535	NULL	NULL
B_STAT_PATH_CACHE	PATH_PAGES	C	8000	0	0	C	65535	NULL	NULL
B_STAT_PHRASE_LIST	URL_FROM	C	8000	0	0	C	65535	NULL	NULL
B_STAT_PHRASE_LIST	URL_TO	C	8000	0	0	C	65535	NULL	NULL
B_STAT_REFERER_LIST	URL_FROM	C	8000	0	0	C	65535	NULL	NULL
B_STAT_REFERER_LIST	URL_TO	C	8000	0	0	C	65535	NULL	NULL
B_STAT_SEARCHER	USER_AGENT	C	8000	0	0	C	65535	NULL	NULL
B_STAT_SEARCHER_HIT	URL	C	8000	0	0	C	65535	NULL	NULL
B_STAT_SEARCHER_HIT	USER_AGENT	C	8000	0	0	C	65535	NULL	NULL
B_STAT_SESSION	URL_FROM	C	8000	0	0	C	65535	NULL	NULL
B_STAT_SESSION	URL_LAST	C	8000	0	0	C	65535	NULL	NULL
B_STAT_SESSION	URL_TO	C	8000	0	0	C	65535	NULL	NULL
B_STAT_SESSION	USER_AGENT	C	8000	0	0	C	65535	NULL	NULL
B_STAT_SESSION_DATA	SESSION_DATA	C	8000	0	0	C	65535	NULL	NULL
B_STOP_LIST	COMMENTS	C	8000	0	0	C	65535	NULL	NULL
B_STOP_LIST	MESSAGE	C	8000	0	0	C	65535	NULL	NULL
B_STOP_LIST	URL_FROM	C	8000	0	0	C	65535	NULL	NULL
B_STOP_LIST	URL_REDIRECT	C	8000	0	0	C	65535	NULL	NULL
B_STOP_LIST	URL_TO	C	8000	0	0	C	65535	NULL	NULL
B_STOP_LIST	USER_AGENT	C	8000	0	0	C	65535	NULL	NULL
B_TICKET	AUTO_CLOSE_DAYS	N	1	3	0	N	NULL	10	0
B_TICKET	LAST_MESSAGE_SID	C	8000	0	0	C	65535	NULL	NULL
B_TICKET	OWNER_SID	C	8000	0	0	C	65535	NULL	NULL
B_TICKET	SUPPORT_COMMENTS	C	8000	0	0	C	65535	NULL	NULL
B_TICKET	TITLE	C	2000	0	0	C	65535	NULL	NULL
B_TICKET_DICTIONARY	DESCR	C	8000	0	0	C	65535	NULL	NULL
B_TICKET_MESSAGE	EXTERNAL_FIELD_1	C	8000	0	0	C	65535	NULL	NULL
B_TICKET_MESSAGE	MESSAGE	C	2147483647	0	0	C	4294967295	NULL	NULL
B_TICKET_MESSAGE	MESSAGE_SEARCH	C	2147483647	0	0	C	4294967295	NULL	NULL
B_TICKET_MESSAGE	OWNER_SID	C	8000	0	0	C	65535	NULL	NULL
B_TICKET_SLA	DESCRIPTION	C	8000	0	0	C	65535	NULL	NULL
B_VOTE	DESCRIPTION	C	5000	0	0	C	65535	NULL	NULL
B_VOTE_ANSWER	FIELD_TYPE	N	1	3	0	N	NULL	10	0
B_VOTE_ANSWER	MESSAGE	C	5000	0	0	C	65535	NULL	NULL
B_VOTE_EVENT_ANSWER	MESSAGE	C	8000	0	0	C	65535	NULL	NULL
B_VOTE_QUESTION	QUESTION	C	5000	0	0	C	65535	NULL	NULL
B_WORKFLOW_DOCUMENT	COMMENTS	C	8000	0	0	C	65535	NULL	NULL
B_WORKFLOW_LOG	COMMENTS	C	8000	0	0	C	65535	NULL	NULL
B_WORKFLOW_STATUS	DESCRIPTION	C	8000	0	0	C	65535	NULL	NULL

Строго говоря, следовало бы также исследовать тождественность признаков NULL у соответствующих полей и других ограничений. Например, если некоторое поле допускает NULLы в MySQL, но является NOT NULL в SQL Server, перенос данных может завершиться с ошибкой. Однако в целях экономии времени эти проверки было решено не проводить, а перейти сразу к процессу переноса данных. Если такие несоответствия имеются, они будет выявлены в ходе переноса.

8. Перенос данных

Однако использовать MySQL ODBC Connector 5.1 для переноса данных оказалось не лучшим вариантом, поскольку были выявлены ситуации, в которых его работоспособность нарушалась. В их числе отсутствие поддержки типа longtext:

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.

Скрипт 8.1

Можно обеспечить перенос подстроками по 4000 символов, но такой способ нельзя признать оптимальным. В связи с этим мы рекомендуем использовать для переноса данных MySQL Connector/Net 6.0, который можно свободно скачать по адресу http://dev.mysql.com/downloads/connector/net/6.0.html. Его установка не вызывает каких-либо сложностей:

Рис. 8.1

Рис. 8.2

Рис. 8.3

Под словом "провайдер" понимается расширение функциональности .NET. Connector/Net 6.0 добавляет новые пространства имен MySql.Data в .NET, что позволяет работать c MySQL из .NET-приложений столь же элегантно, как, например, с SQL Server при помощи System.Data.SqlClient

Рис. 8.4

В плане OLE DB-провайдеров никаких новшеств он не привносит, т.к., вероятно, используется прямой доступ, подобно SQL Native Client, следовательно, сказать что-то новое про создание прилинкованного сервера по сравнению с п.7 на его основе нельзя. В качестве рабочего инструмента для миграции можно использовать Visual Studio, в частности, ее бесплатную редакцию - https://www.microsoft.com/express/download/. Откройте Visual Studio, выберите в меню File -> New -> Project

Рис. 8.5

Выберите в качестве шаблона проекта создание нового консольного приложения на C#:

Рис. 8.6

Добавьте к ссылкам (References) проекта пространство имен MySql.Data, которое добавляет MySql Connector/Net 6.0, как показано на Рис. 8.4.

Рис. 8.7

и напишите следующий код:

using System;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
class Program
{
static MySqlConnection mySqlCnn;

static SqlConnection sqlSrvCnn;



static void Main(string[] args)

{

    sqlSrvCnn = new SqlConnection(@"server=(local)\SQLExpress;database=bitrix;trusted_connection=true;MultipleActiveResultSets=true");

    sqlSrvCnn.Open();

    mySqlCnn = new MySqlConnection("server=127.0.0.1;port=31006;uid=root;pwd=;database=bsm_demo;Pooling=False");

    mySqlCnn.Open();



    DisEnableFKConstraints(true);

    DataTable tblList = GetSourceTablesFromMySQLDB();

    CleanDestTablesInSQLSrvDB(tblList);

    TransferData(tblList);

    DisEnableFKConstraints(false);



    mySqlCnn.Close();

    sqlSrvCnn.Close();

}



/// &lt;summary&gt;

/// Копирует данные из таблицы в MySQL в одноименную таблицу в SQL Server

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

/// &lt;/summary&gt;

/// &lt;param name="tblName"&gt;Имя таблицы&lt;/param&gt;

static void CopyDataFromMySQLTblToCorrespondingSQLSrvTbl(string tblName)

{

    //Читаем по порядку поля в таблице-назначения

    SqlCommand sqlSrvCmd = sqlSrvCnn.CreateCommand();

    sqlSrvCmd.CommandText = "select name from sys.columns where object_id = object_id(@tblName) order by column_id";

    sqlSrvCmd.Parameters.AddWithValue("@tblName", tblName);

    SqlDataReader sqlSrvDr = sqlSrvCmd.ExecuteReader(CommandBehavior.SingleResult);

    //Составляем строку запроса для источника, перечисляя туда поля в том порядке, как они следуют в назначении

    StringBuilder mySqlCmdText = new StringBuilder("select ");

    //Имя поля заключаем в аналог квадратных скобок - на случай, если оно будет совпадать с одним из зарезервированых слов MySQL.

    while (sqlSrvDr.Read()) mySqlCmdText.Append("`" + sqlSrvDr.GetSqlString(0).Value + "`,");

    sqlSrvDr.Close();

    mySqlCmdText.Remove(mySqlCmdText.Length - 1, 1);

    mySqlCmdText.Append(" from " + tblName);



    MySqlCommand mySqlCmd = new MySqlCommand(mySqlCmdText.ToString(), mySqlCnn);

    MySqlDataReader mySqlDr = mySqlCmd.ExecuteReader();



    SqlBulkCopy bcp = new SqlBulkCopy(sqlSrvCnn, SqlBulkCopyOptions.KeepIdentity, null);

    //KeepIdentity означает set identity_insert &lt;tblName&gt; on/off

    //Поскольку в mySqlDr поля идут в том же порядке, что и в назначении, SqlBulkCopy.ColumnMappings не требуется.

    bcp.DestinationTableName = tblName;

// Заправляем шланг ридера объекту SqlBulkCopy, чтобы он качал из него содержимое в bcp.DestinationTableName
    bcp.WriteToServer(mySqlDr);



    mySqlDr.Close();

}



/// &lt;summary&gt;

/// Получает список таблиц из MySQLной базы

/// &lt;/summary&gt;

/// &lt;returns&gt;Список таблиц&lt;/returns&gt;

static DataTable GetSourceTablesFromMySQLDB()

{

    DataTable tbl = new DataTable();

    tbl.Load(new MySqlCommand("show tables", mySqlCnn).ExecuteReader());

    return tbl;

}



/// &lt;summary&gt;

/// Удаляет в каждой таблице из списка все ее записи

/// &lt;/summary&gt;

/// &lt;param name="tblList"&gt;Список таблиц&lt;/param&gt;

static void CleanDestTablesInSQLSrvDB(DataTable tblList)

{

    Debug.WriteLine("Очистка таблиц назначения...");

    foreach (DataRow r in tblList.Rows)

    {

        new SqlCommand("delete " + r[0].ToString(), sqlSrvCnn).ExecuteNonQuery();

        Debug.WriteLine("Очищена таблица " + r[0].ToString());

    }

    Debug.WriteLine("Очистка закончена.");

}



static void TransferData(DataTable tblList)

{

    Debug.WriteLine("Загрузка данных...");

    foreach (DataRow r in tblList.Rows)

    {

        CopyDataFromMySQLTblToCorrespondingSQLSrvTbl(r[0].ToString());

        Debug.WriteLine("Перенесена таблица " + r[0].ToString());

    }

    Debug.WriteLine("Загрузка завершена.");

}



/// &lt;summary&gt;

/// Процедура отключает/включает все ограничения внешнего ключа над таблицами в БД SQL Server

/// &lt;/summary&gt;

/// &lt;param name="switchOff"&gt;Если да, то отключить, нет - включить&lt;/param&gt;

static void DisEnableFKConstraints(bool switchOff)

{

    string prefix = switchOff ? "От" : "В";

    Debug.WriteLine(prefix + "ключение FK-ограничений...");

    SqlDataReader sdr = new SqlCommand("select name, object_name(parent_object_id) from sys.foreign_keys", sqlSrvCnn).ExecuteReader();

    while (sdr.Read())

    {

        string fkName = sdr.GetString(0), tblName = sdr.GetString(1);

        new SqlCommand(String.Format("alter table {0} {1}check constraint {2}", tblName, switchOff ? "no" : "", fkName), sqlSrvCnn).ExecuteNonQuery();

        Debug.WriteLine(String.Format("{0}ключено ограничение {1} в таблице {2}", prefix, fkName, tblName));

    }

    sdr.Close();

    Debug.WriteLine(prefix + "ключение FK-ограничений завершено.");

}

}

Скрипт 8.2

Необходимо сделать некоторые комментарии к коду.

Как показывает

select * from sys.objects where type = 'F'

(или sys.foreign_keys/ sys.foreign_key_columns) в базе имеются ограничения внешнего ключа. Следовательно, первоначально следует вставлять данные в referenced_object (РК), а затем в parent_object (FK), чтобы избежать нарушений ограничений внешнего ключа. Возможны ситуации, когда referenced_object сам, в свою очередь, имеет referenced_object. Следовательно, требуется упорядочить таблицы, выбрав сначала те referenced_objects, которые не имеют FK-ограничений, вставить данные в них, затем в те таблицы, для которых они являются РК-таблицами и т.д. Чтобы не усложнять скрипт миграции, было принято решение на время переноса данных отключить все FK-ограничения, вставить данные, а затем снова включить. Отключение FK-ограничений выполняется при помощи команды ALTER TABLE <имя FK-таблицы> NOCHECK CONSTRAINT <имя ограничения>, а включение, соответственно, - CHECK. (От/в)ключение ограничений внешнего ключа делает процедура DisEnableFKConstraints(bool switchOff). В том, что FK-ограничения отключены, можно убедиться по запросу

select * from sys.foreign_keys

в результатах которого колонка is_disabled стала 1 для всех записей.

Перед загрузкой данных содержимое таблиц SQL Express следует очистить. Несмотря на отключенные ограничения чистить таблицы при помощи TRUNCATE TABLE не получится. Приходится использовать команду DELETE <имя таблицы> для удаления из каждой таблицы всех ее записей.

Можно видеть

select * from sys.columns where is_identity = 1

или select * from sys.identity_columns, что на некоторых таблицах имеются колонки с автоинкрементом. Однако специально отключать автоинкремент перед вставкой SET IDENTITY_INSERT <имя таблицы> ON | OFF не требуется, т.к. это "за сценой" делает объект SqlBulkCopy при помощи параметра KeepIdentity.

Последовательность действий выглядит следующим образом.

Открываются соединения с БД MySQL и SQL Express. MARS в SQL Serverном соединении потребовалось включить из-за процедуры DisEnableFKConstraints, где мы держим на соединении открытый DataReader со списком FK, по которому бежим, и на каждой записи выполняем ExecuteNonQuery() на том же соединении.
Отключаем все ограничения внешнего ключа в БД SQL Express, чтобы не заботиться о последовательности очистки и заливки.
Получаем список таблиц из БД MySQL. Он сохраняется в DataTable tblList.
Пробегаемся по этому списку и очищаем в нем все таблицы.
Пробегаемся по этому списку и переносим данные из каждой таблицы MySQL в одноименную таблицу SQL Express.

Из п.7 мы можем быть уверены, что каждая таблица в MySQL имеет соответствие в SQL Express и набор полей приемника тождественен источнику с точностью до порядка следования. Колонки, для которых возможны потери при копировании, перечислены в Скрипт 7.6.

Перед выполнением загрузки из MySQL на всякий случай лучше выполнить резервное копирование базы данных на SQL Express несмотря на то, что она пуста, т.е. содержит только "заводские" установки и весь наработанный контент хранится в базе MySQL. Резервное копирование базы данных SQL Server можно выполнить при помощи команды

backup database Bitrix to  disk = 'c:\Bitrix\bitrix.bak' with noformat, init, name = N'Bitrix-Full Database Backup', skip, stats = 10

Скрипт 8.3

а восстановление (при необходимости) -

alter database Bitrix set single_user with rollback immediate use master

restore database Bitrix from disk = 'c:\Bitrix\Bitrix.bak' with recovery, stats = 20

Скрипт 8.4

9. То же самое на PowerShell

Если у клиента нет Visual Studio и он по каким-либо причинам не может установить Express-редакцию, ниже приводится вариация Скрипта 8.2, мигрирующего базу Битрикс с MySQL на SQL Express, на языке сценариев PowerShell:

cls
function DisEnableFKConstraints([bool] $switchOff)
{
    [string] $prefix; if ($switchOff) { $prefix = "От" } else { $prefix = "В" };

    Write-Host ($prefix + "ключение FK-ограничений...")

        [System.Data.SqlClient.SqlDataReader] $sdr = (New-Object System.Data.SqlClient.SqlCommand("select name, object_name(parent_object_id) from sys.foreign_keys", $sqlSrvCnn)).ExecuteReader()

        while ($sdr.Read())

    {

        [string] $fkName = $sdr.GetString(0); [string] $tblName = $sdr.GetString(1)

              [string] $prefix1 = ""; if ($switchOff) {$prefix1 = "no"}

              [string] $cmdText = "alter table {0} {1}check constraint {2}" -f $tblName,  $prefix1, $fkName

        (New-Object System.Data.SqlClient.SqlCommand($cmdText, $sqlSrvCnn)).ExecuteNonQuery() 

        Write-Host ("{0}ключено ограничение {1} в таблице {2}" -f $prefix, $fkName, $tblName)

    }

    $sdr.Close();

    Write-Host ($prefix + "ключение FK-ограничений завершено.")

}
function CleanDestTablesInSQLSrvDB([System.Data.DataTable] $tblList)
{
Write-Host "Очистка таблиц назначения..."

foreach ($r in $tblList.Rows)

{

    [string] $cmdText = "delete " + $r[0]

        (New-Object System.Data.SqlClient.SqlCommand($cmdText, $sqlSrvCnn)).ExecuteNonQuery()

    Write-Host ("Очищена таблица " + $r[0])

}

Write-Host "Очистка закончена."

}
function TransferData([System.Data.DataTable] $tblList)
{
Write-Host "Загрузка данных..."

foreach ($r in $tblList.Rows)

{

    CopyDataFromMySQLTblToCorrespondingSQLSrvTbl($r[0]) 

    Write-Host ("Перенесена таблица " + $r[0])

}

Write-Host "Загрузка завершена."

}
function CopyDataFromMySQLTblToCorrespondingSQLSrvTbl([string] $tblName)
{
[System.Data.SqlClient.SqlCommand] $sqlSrvCmd = $sqlSrvCnn.CreateCommand() 

  $sqlSrvCmd.CommandText = "select name from sys.columns where object_id = object_id(@tblName) order by column_id"

$sqlSrvCmd.Parameters.AddWithValue("@tblName", $tblName) 

  [System.Data.SqlClient.SqlDataReader] $sqlSrvRdr = $sqlSrvCmd.ExecuteReader() 

[System.Text.StringBuilder] $mySqlCmdText = New-Object System.Text.StringBuilder("select ")

while ($sqlSrvRdr.Read()) { $mySqlCmdText.Append("``" + $sqlSrvRdr.GetSqlString(0) + "``,") } 

$sqlSrvRdr.Close()

$mySqlCmdText.Remove($mySqlCmdText.Length - 1, 1) 

$mySqlCmdText.Append(" from " + $tblName) 

  [MySql.Data.MySqlClient.MySqlCommand] $mySqlCmd = New-Object MySql.Data.MySqlClient.MySqlCommand($mySqlCmdText.ToString(), $mySqlCnn);

[MySql.Data.MySqlClient.MySqlDataReader] $mySqlRdr = $mySqlCmd.ExecuteReader()



[System.Data.SqlClient.SqlBulkCopy] $bcp = New-Object System.Data.SqlClient.SqlBulkCopy($sqlSrvCnn, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity, $null)

$bcp.DestinationTableName = $tblName

$bcp.WriteToServer($mySqlRdr) 

  $bcp.Close()



$mySqlRdr.Close() 

}
################################################################ MAIN ##############################################################################################################################
[System.Data.SqlClient.SqlConnection] $sqlSrvCnn = New-Object System.Data.SqlClient.SqlConnection("server=(local)\SQLExpress;database=bitrix;trusted_connection=true;MultipleActiveResultSets=true")
$sqlSrvCnn.Open()
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
[MySql.Data.MySqlClient.MySqlConnection] $mySqlCnn = New-Object MySql.Data.MySqlClient.MySqlConnection("server=127.0.0.1;port=31006;uid=root;pwd=;database=bsm_demo;Pooling=False")
$mySqlCnn.Open()
$mySqlRdr = (New-Object MySql.Data.MySqlClient.MySqlCommand("show tables;", $mySqlCnn)).ExecuteReader()
[System.Data.DataTable] $tblList = New-Object System.Data.DataTable
$tblList.Load($mySqlRdr)
$mySqlRdr.Close()
DisEnableFKConstraints $true | Out-Null
CleanDestTablesInSQLSrvDB $tblList
TransferData $tblList | Out-Null
DisEnableFKConstraints $false | Out-Null
$sqlSrvCnn.Close()
$mySqlCnn.Close()

Скрипт 9.1

10. Последовательность действий клиента

Клиент располагает MySQLным вариантом установки Битрикс. Для перехода с MySQL на SQL Express ему необходимо

Установить SQL Express, как показано в п.5.
Установить .NET Connector к MySQL, как показано на Рис. 8.1 - Рис. 8.3.
Переименовать каталог www в папке Bitrix Environment и установить Битрикс на SQL Express, как показано в п.6.

Рис. 10.1

Закрыть окно Рис. 10.1. Остановить процесс Bitrix Environment:

Рис. 10.2

Проверить, что MySQL по-прежнему запущен (mysqld-opt.exe значится в числе работающих процессов). Если нет, запустить, как показано в Скрипте 4.1.

Выполнить Скрипт 9.1:

Рис. 10.3

Вновь запустить Bitrix Environment:

Рис. 10.4

Мы видим, что информация из MySQL перенеслась в SQL Express.

При возникновении непредвиденной ситуации вернуться на исходную позицию можно, переименовав переименованную папку обратно в www.

Авторы: Алексей Шуленин, Денис Шаромов

Дополнительные ресурсы