際際滷

際際滷Share a Scribd company logo

From MSSQL to MariaDB

?
?
I Goo Lee
I Goo Lee

From MSSQL to MariaDB

Convert to study materialsBETA

Transform any presentation into ready-made study material！select from outputs like summaries, definitions, and practice questions.

1 of 28
Downloaded 53 times
From MSSQL to MariaDB 2015. 3. 7. (?)???? ???
? ?? - ?) ????? - ?) ????? - ?) ??JAPAN ???(?.QUADDIMENSIONS) - ?) ????? ??? ?? ? ?? - ? ?? ? ???? - MySQL Power Group - SQLTAG.ORG
?? 1 ?? ?? 3 ?? ?? 2 ?? ??
Migration ?? ?? ? ?? ?? - ?? ??? MSSQL ???? MariaDB? ??? ?? ? Migration ?? ?? ? Migration ?? ?? ???? ?? ???? ?? ?? ?? ?? ?? Object ? 00 ? +Table Type ? 00 ? ?MSSQL ?? Table ?? ?MSSQL Data +SP, Function Type ? 00 ? ?MSSQL ?? DB ? Object APPLICATION ? 00 ? ?APPLICATION? SQL ? ?? ? ??, ??? ???? ?? ?APPLICATION? SQL ? ?? ???? ???? ?? Activity ?? ??(2014/00/00 ~ 00/00) R&R ?? W W+1 W+2 W+3 W+4 W+5 W+6 W+7 W+8 ??? DBA ?????? ?? ?? ?? ?? ?? O O ???? O O ?? DB Object ?? X O Application ?? ＠ O Data ??(TEST ?? ??) X O TEST Application TEST ?? O O ?? ??? ?? O O 1
Migration ?? ?? - Database Schema Migration ?? ?? ???? ?? ?? ? ????,??? ??, ???? ?? Table, Index, Function, Procedure ´ Table ?? ?? ????? ?? [1??] [2??] [3??] ???? ?? ?? ??? ?? ???? ??? ?? ?? ?? ?? ? ?? MariaDBMSSQL 1
?????? ?? ?? ??? ? ? ?? - SQL Server ? ???? DB OBJECT? ?? ??? ??? ??? ?? ??? ??? ? ??? ??? ??? ???? - MariaDB? ????? ?????. ??? ????? ??? ? ? ?? ??? ???? ???? 2 ? ???? - MySQL Workbench: Database Migration MySQL Workbench ??? ??? ?? DATABASE WIZARD ?? ???? MSSQL? DB OBJECT ??? MariaDB? DDL?? MSSQL DB ???? ?? ????? ??? ? ??. - SQL Server Migration Assistant for MySQL (MySQLToSQL) SQL Server Migration Assistant ?? ???? ??? MariaDB ??? ?? ?? ? ????? ??? ? ??.
?????? ?? ?? ??? ? - SQL Server Migration Assistant (SSMA) ???? 2
?????? ?? ?? ??? ? - MySQL Workbench Data Migration Wizard ???? 2
DEMO (????)
?? 2 ?? ?? 3 ?? ?? 1 ?? ??
??? ??? ? ?? - SQL Server ? MariaDB? ??? ??? DATA TYPE? ??? ??? ??. ??? ??? ?? ???? DATA?? MariaDB? ???? ??? ? ?? ???? ???? 1 ? ???? - DATA TYPE? ??? ?? ?? Rule? ???? ?? RULE ??? ???? ??? ? ??? ??.
MSSQL to MariaDB DATA TYPE MAPPING2 SQL Server MariaDB tinyint TINYINT (UNSIGNED) smallint SMALLINT int MEDIUMINT INT bigint BIGINT ? ?? SQL Server MariaDB Decimal(p,s) DECIMAL(M,D) ? ?? ??? ????
MSSQL to MariaDB DATA TYPE MAPPING - ??2 SQL Server MariaDB float(n) FLOAT(N) float(24) FLOAT(M,D) float(53) DOUBLE(M,D) REAL(M,D) ? ?? ??? SQL Server MariaDB bit BIT bool / boolean ? BIT ????
MSSQL to MariaDB DATA TYPE MAPPING - ??2 SQL Server MariaDB datetime2/datetime DATETIME date DATE time TIME smalldatetime TIMESTAMP smallint YEAR ? ?? ? ?? ????
MSSQL to MariaDB DATA TYPE MAPPING - ??2 SQL Server MariaDB nchar(n) / char(n) CHAR nvarchar(n|max)/varchar(n|max) VARCHAR TINYTEXT TEXT(M) MEDIUMTEXT LONGTEXT nvarbinary(n|max)/varbinary(n|max) TINYBLOB BLOB MEDIUMBLOB LONGBLOB ? ?? ????
MSSQL to MariaDB DATA TYPE MAPPING - ??2 SQL Server MariaDB hierarchyid ? uniqueidentifier ? sql_variant ? table ? ? MSSQL ?? ??? ?? ????
? ? ??? ?? ? ?? - ?? ??? SQL Server ??? ??? ???? ???? ?? ???? varchar(max), nvarchar(max)? ?? ?????. ?? ??? ??? ?? ???? MariaDB? ??? ? ?? ??? ??? ???? ??? MariaDB? TEXT or BLOB ? ??? ??? ???? ?? ??? 64KB? ??? ??? ?? ??? ????. ???? 3 ? ???? - varchar(max), nvarchar(max) ? ??? ???? ??? ??? ???? ?????? Application ? ??? ??? ?? ??? ?? ? ??? ??? ??? ???. - ???? ?? ??? 64KB? ???? ??? VARCHAR ? VARBINARY ??? ????.
?????? ? ?? - ????? ???? ?? ??? ???? ?? ?? - Primary Key? ???? ???? ???? ?? ???? ?? ??. ???? 4 ? ???? - MariaDB?? ??? ?? ???? ??? ?? 6??? ??. - ??? ???, ??? ???, ?? ???, ? ???, ??? ?? ???/ ??? ? ???, ????? - ??? ??? ??? ? ?? ??? ?? ???? ??.
???? & ???? ? ?? - ??? ???? ?? ??? MariaDB??? ?? ????? ????? ???? ???? ???? 5 ? ???? & ???? ?? - SELECT * FROM information_schema.columns WHERE table_schema = ｀mariadb_database¨ and table_name = ｀mariadb_table¨; - SHOW CREATE TABLE mariadb_table; ? ???? - MariaDB?? ???? ???? ??? ???? utf8 ? ??? ?????? ???? ??. - ????(collation)? ????? ???? ??? ????? ????? ?? ????? ???? ??? utf8? ?? ????? utf8_general_ci ??????.
???? (AUTO_INCREMENT) ?? ???? 6 ? ?? - innodb ???? ???? AUTO_INCREMENT ??? ?? ???? ??? ? 1075 ?? ?? ? ???? - innodb ???? ????? AUTO_INCREMENT ??? ????? ?? ??? ? ? ??? ??? ????? ?? ?? ???? ??? ????? ????. - MyISAM ???? ????? AUTO_INCREMENT ??? ????? ?? ??? ?? ??? ?? ?? ???? ??? ????? ????.
?? ?? ?? ?? ?? ?? ?? ? ???? ???? 7 SQL Server Management Studio ??? ???? ????? ???
????? ? ?? - SQL Server ? ??? DATA? MariaDB? ??? ??? ???? ???? 8 ? ???? - SQL Server ? SSIS Package? ???? ????? ????. - SQL Server ???? ???? ??? ??? ???? ???? ? ? MariaDB? LOAD DATA INFILE ??? ???? ????. - SQL Server ? Linked Server ? MariaDB? ??? ? OpenQuery ??? ???? ????.
??? ?? ??? ???? 9 ?? SQL Server MariaDB ? ? datepart weekday ?? ? SELECT datepart(dw, ｀2015-03-02¨); SELECT weekday(｀2015-03-02¨); ? ? ???? * ???? 1 ~ ??? 7 ? RETURN? * ???? 0 ~ ??? 6 ? RETURN ?
??? ? ?? ???? 10 ? ?? ?? ?? ???? SQL?? ??? MariaDB ?? ??? ?? ? ??? ?? ??? ?? ????? ? ?? ?? ?? ? ?? ?? ?? ???? ?? ?? ???? ??? ?? ?? ? ???? ?? ? ?????? ?? ??? ??? ?? ?? ? ?? ???? ?? ??? ?? ?? ?? DBA/??? DBA/??? ?? ??? Syntax ?? ?? ?? ???? ?? ??? ??? ?? ?? ???? ?? ???? ?? ???? ??
?? 3 ?? ?? 2 ?? ?? 1 ?? ??
MariaDB vs MSSQL ?? ?? ?? ?? 1 ? ? SQL Server MariaDB ?? ?? ALTER TABLE table_name ADD column_name column_property ALTER TABLE table_name ADD column_name column_property; ?? ?? ALTER TABLE table_name ALTER COLUMN column_name new_column_property ALTER TABLE table_name MODIFY COLUMN column_name new_column_property; ?? ?? EXEC sp_rename Table_name.Old_column_name New_column_name ALTER TABLE table_name CHANGE COLUMN Old_column_name New_column_name new_column_property; ?? ?? ALTER TABLE table_name DROP column_name ALTER TABLE table_name DROP COLUMN column_name; ?? ?? EXEC sp_addextendedproperty @name=N¨property_name¨, @value=¨description¨ ´ ALTER TABLE table_name MODIFY COLUMN column_name column_property COMMENT ｀description¨; ? ? SQL Server MariaDB ??? ?? CREATE TABLE [schema].table_name ( column_name data_type column_constraints, ´ table_constraints ) ) [ON filegroup / partition_scheme] CREATE [OR REPLACE] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options ] ... [partition_options] ????? ?? ?? ?? ???: CREATE TABLE #table_name ?? ?? ???: CREATE TABLE ##table_name CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options ] ... [partition_options]
References ? 3rd Party Tools C SQL Server Migration Assistant for MySQL (MySQLToSQL) https://msdn.microsoft.com/en-us/library/hh313109(v=sql.110).aspx C MySQL Workbench: Database Migration http://www.mysql.com/products/workbench/migrate/ ? ?? C ???, ???? DBA? ?? Real MySQL, ????, 2012 C ???, MariaDB 10.0? MySQL 5.6? ??? ??? Real MariaDB, ????, 2014 C ???, MariaDB ?? ?? ???, ?????, 2014
?????!

More Related Content

From MSSQL to MariaDB

  • 1. From MSSQL to MariaDB 2015. 3. 7. (?)???? ???
  • 2. ? ?? - ?) ????? - ?) ????? - ?) ??JAPAN ???(?.QUADDIMENSIONS) - ?) ????? ??? ?? ? ?? - ? ?? ? ???? - MySQL Power Group - SQLTAG.ORG
  • 3. ?? 1 ?? ?? 3 ?? ?? 2 ?? ??
  • 4. Migration ?? ?? ? ?? ?? - ?? ??? MSSQL ???? MariaDB? ??? ?? ? Migration ?? ?? ? Migration ?? ?? ???? ?? ???? ?? ?? ?? ?? ?? Object ? 00 ? +Table Type ? 00 ? ?MSSQL ?? Table ?? ?MSSQL Data +SP, Function Type ? 00 ? ?MSSQL ?? DB ? Object APPLICATION ? 00 ? ?APPLICATION? SQL ? ?? ? ??, ??? ???? ?? ?APPLICATION? SQL ? ?? ???? ???? ?? Activity ?? ??(2014/00/00 ~ 00/00) R&R ?? W W+1 W+2 W+3 W+4 W+5 W+6 W+7 W+8 ??? DBA ?????? ?? ?? ?? ?? ?? O O ???? O O ?? DB Object ?? X O Application ?? ＠ O Data ??(TEST ?? ??) X O TEST Application TEST ?? O O ?? ??? ?? O O 1
  • 5. Migration ?? ?? - Database Schema Migration ?? ?? ???? ?? ?? ? ????,??? ??, ???? ?? Table, Index, Function, Procedure ´ Table ?? ?? ????? ?? [1??] [2??] [3??] ???? ?? ?? ??? ?? ???? ??? ?? ?? ?? ?? ? ?? MariaDBMSSQL 1
  • 6. ?????? ?? ?? ??? ? ? ?? - SQL Server ? ???? DB OBJECT? ?? ??? ??? ??? ?? ??? ??? ? ??? ??? ??? ???? - MariaDB? ????? ?????. ??? ????? ??? ? ? ?? ??? ???? ???? 2 ? ???? - MySQL Workbench: Database Migration MySQL Workbench ??? ??? ?? DATABASE WIZARD ?? ???? MSSQL? DB OBJECT ??? MariaDB? DDL?? MSSQL DB ???? ?? ????? ??? ? ??. - SQL Server Migration Assistant for MySQL (MySQLToSQL) SQL Server Migration Assistant ?? ???? ??? MariaDB ??? ?? ?? ? ????? ??? ? ??.
  • 7. ?????? ?? ?? ??? ? - SQL Server Migration Assistant (SSMA) ???? 2
  • 8. ?????? ?? ?? ??? ? - MySQL Workbench Data Migration Wizard ???? 2
  • 10. ?? 2 ?? ?? 3 ?? ?? 1 ?? ??
  • 11. ??? ??? ? ?? - SQL Server ? MariaDB? ??? ??? DATA TYPE? ??? ??? ??. ??? ??? ?? ???? DATA?? MariaDB? ???? ??? ? ?? ???? ???? 1 ? ???? - DATA TYPE? ??? ?? ?? Rule? ???? ?? RULE ??? ???? ??? ? ??? ??.
  • 12. MSSQL to MariaDB DATA TYPE MAPPING2 SQL Server MariaDB tinyint TINYINT (UNSIGNED) smallint SMALLINT int MEDIUMINT INT bigint BIGINT ? ?? SQL Server MariaDB Decimal(p,s) DECIMAL(M,D) ? ?? ??? ????
  • 13. MSSQL to MariaDB DATA TYPE MAPPING - ??2 SQL Server MariaDB float(n) FLOAT(N) float(24) FLOAT(M,D) float(53) DOUBLE(M,D) REAL(M,D) ? ?? ??? SQL Server MariaDB bit BIT bool / boolean ? BIT ????
  • 14. MSSQL to MariaDB DATA TYPE MAPPING - ??2 SQL Server MariaDB datetime2/datetime DATETIME date DATE time TIME smalldatetime TIMESTAMP smallint YEAR ? ?? ? ?? ????
  • 15. MSSQL to MariaDB DATA TYPE MAPPING - ??2 SQL Server MariaDB nchar(n) / char(n) CHAR nvarchar(n|max)/varchar(n|max) VARCHAR TINYTEXT TEXT(M) MEDIUMTEXT LONGTEXT nvarbinary(n|max)/varbinary(n|max) TINYBLOB BLOB MEDIUMBLOB LONGBLOB ? ?? ????
  • 16. MSSQL to MariaDB DATA TYPE MAPPING - ??2 SQL Server MariaDB hierarchyid ? uniqueidentifier ? sql_variant ? table ? ? MSSQL ?? ??? ?? ????
  • 17. ? ? ??? ?? ? ?? - ?? ??? SQL Server ??? ??? ???? ???? ?? ???? varchar(max), nvarchar(max)? ?? ?????. ?? ??? ??? ?? ???? MariaDB? ??? ? ?? ??? ??? ???? ??? MariaDB? TEXT or BLOB ? ??? ??? ???? ?? ??? 64KB? ??? ??? ?? ??? ????. ???? 3 ? ???? - varchar(max), nvarchar(max) ? ??? ???? ??? ??? ???? ?????? Application ? ??? ??? ?? ??? ?? ? ??? ??? ??? ???. - ???? ?? ??? 64KB? ???? ??? VARCHAR ? VARBINARY ??? ????.
  • 18. ?????? ? ?? - ????? ???? ?? ??? ???? ?? ?? - Primary Key? ???? ???? ???? ?? ???? ?? ??. ???? 4 ? ???? - MariaDB?? ??? ?? ???? ??? ?? 6??? ??. - ??? ???, ??? ???, ?? ???, ? ???, ??? ?? ???/ ??? ? ???, ????? - ??? ??? ??? ? ?? ??? ?? ???? ??.
  • 19. ???? & ???? ? ?? - ??? ???? ?? ??? MariaDB??? ?? ????? ????? ???? ???? ???? 5 ? ???? & ???? ?? - SELECT * FROM information_schema.columns WHERE table_schema = ｀mariadb_database¨ and table_name = ｀mariadb_table¨; - SHOW CREATE TABLE mariadb_table; ? ???? - MariaDB?? ???? ???? ??? ???? utf8 ? ??? ?????? ???? ??. - ????(collation)? ????? ???? ??? ????? ????? ?? ????? ???? ??? utf8? ?? ????? utf8_general_ci ??????.
  • 20. ???? (AUTO_INCREMENT) ?? ???? 6 ? ?? - innodb ???? ???? AUTO_INCREMENT ??? ?? ???? ??? ? 1075 ?? ?? ? ???? - innodb ???? ????? AUTO_INCREMENT ??? ????? ?? ??? ? ? ??? ??? ????? ?? ?? ???? ??? ????? ????. - MyISAM ???? ????? AUTO_INCREMENT ??? ????? ?? ??? ?? ??? ?? ?? ???? ??? ????? ????.
  • 21. ?? ?? ?? ?? ?? ?? ?? ? ???? ???? 7 SQL Server Management Studio ??? ???? ????? ???
  • 22. ????? ? ?? - SQL Server ? ??? DATA? MariaDB? ??? ??? ???? ???? 8 ? ???? - SQL Server ? SSIS Package? ???? ????? ????. - SQL Server ???? ???? ??? ??? ???? ???? ? ? MariaDB? LOAD DATA INFILE ??? ???? ????. - SQL Server ? Linked Server ? MariaDB? ??? ? OpenQuery ??? ???? ????.
  • 23. ??? ?? ??? ???? 9 ?? SQL Server MariaDB ? ? datepart weekday ?? ? SELECT datepart(dw, ｀2015-03-02¨); SELECT weekday(｀2015-03-02¨); ? ? ???? * ???? 1 ~ ??? 7 ? RETURN? * ???? 0 ~ ??? 6 ? RETURN ?
  • 24. ??? ? ?? ???? 10 ? ?? ?? ?? ???? SQL?? ??? MariaDB ?? ??? ?? ? ??? ?? ??? ?? ????? ? ?? ?? ?? ? ?? ?? ?? ???? ?? ?? ???? ??? ?? ?? ? ???? ?? ? ?????? ?? ??? ??? ?? ?? ? ?? ???? ?? ??? ?? ?? ?? DBA/??? DBA/??? ?? ??? Syntax ?? ?? ?? ???? ?? ??? ??? ?? ?? ???? ?? ???? ?? ???? ??
  • 25. ?? 3 ?? ?? 2 ?? ?? 1 ?? ??
  • 26. MariaDB vs MSSQL ?? ?? ?? ?? 1 ? ? SQL Server MariaDB ?? ?? ALTER TABLE table_name ADD column_name column_property ALTER TABLE table_name ADD column_name column_property; ?? ?? ALTER TABLE table_name ALTER COLUMN column_name new_column_property ALTER TABLE table_name MODIFY COLUMN column_name new_column_property; ?? ?? EXEC sp_rename Table_name.Old_column_name New_column_name ALTER TABLE table_name CHANGE COLUMN Old_column_name New_column_name new_column_property; ?? ?? ALTER TABLE table_name DROP column_name ALTER TABLE table_name DROP COLUMN column_name; ?? ?? EXEC sp_addextendedproperty @name=N¨property_name¨, @value=¨description¨ ´ ALTER TABLE table_name MODIFY COLUMN column_name column_property COMMENT ｀description¨; ? ? SQL Server MariaDB ??? ?? CREATE TABLE [schema].table_name ( column_name data_type column_constraints, ´ table_constraints ) ) [ON filegroup / partition_scheme] CREATE [OR REPLACE] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options ] ... [partition_options] ????? ?? ?? ?? ???: CREATE TABLE #table_name ?? ?? ???: CREATE TABLE ##table_name CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options ] ... [partition_options]
  • 27. References ? 3rd Party Tools C SQL Server Migration Assistant for MySQL (MySQLToSQL) https://msdn.microsoft.com/en-us/library/hh313109(v=sql.110).aspx C MySQL Workbench: Database Migration http://www.mysql.com/products/workbench/migrate/ ? ?? C ???, ???? DBA? ?? Real MySQL, ????, 2012 C ???, MariaDB 10.0? MySQL 5.6? ??? ??? Real MariaDB, ????, 2014 C ???, MariaDB ?? ?? ???, ?????, 2014