Microsoft SQL Server to MariaDB Migration

Microsoft SQL Server to MariaDB Migration

WHITE PAPER 2016

OVERVIEW

In the modern IT environment, more and more organizations are adopting open source ecosystems.

According to Gartner, by 2018, more than 70% of new in-house applications will be developed on an open-source DBMS and 50% of existing commercial relational DBMS instances will have been converted, or will be in the process of conversion. In the midst of this trend, MariaDB is leading the way as the RDBMS open source solution. MariaDB is the default database for the leading Linux distributions, and it interoperates well with a vast ecosystem of open source solution environments, including Java, Tomcat, OpenStack, and complementary NoSQL solutions, Hadoop and Spark, just to name a few. It is a fait accompli, open source is how serious IT gets done today and MariaDB is the database at the heart of it all.

Following this trend, migration from Microsoft SQL Server to MariaDB Enterprise is very popular today because of MariaDB's feature-rich, highlyperformant and secure database, coupled with simple pricing and robust community innovation. More importantly, open source works best with open source.

MariaDB has proven processes, methodologies and conversion tools which can simplify the database migration from SQL Server to MariaDB. This practice will ensure the stored procedures, user-defined functions and triggers written in Transact-SQL to be converted to MariaDB procedural SQL language.

This white paper explores the challenges that may arise during migration, and describes best practice migration approaches and solutions.

ASSESSMENT

Migration projects are unique as they may greatly differ in effort, duration and cost. Some migrations can be done in one day while others may require a few months to complete.

Migration assessment is the first step in the migration project that helps you estimate the complexity of the migration, evaluate challenges and define risks.

Step 1 - Business requirements:

? What is the scope of migration? ? How many databases? ? How many applications with embedded SQL Server code? ? Is step-by-step migration possible, or do databases and applications have to be migrated all at once?

? What is the time frame for migration? ? When must SQL Server be switched off (i.e., support contract ends)?

? Is system 24/7? What is acceptable downtime for data migration?

? What is the testing, deployment and recovery strategy? ? One-time migration, or parallel run with synchronization?

Step 2 - Collect high-level technical details such as total number of:

? Tables ? Number of rows ? Size in GB

? Views ? Indexes

? Sizes in GB ? Stored procedures

? Number of lines of code ? User-defined functions

? Number of lines of code ? Applications

? Number of lines of code ? Languages - C#, Java, C/C++, etc. ? Database access API ? ODBC, JDBC, ORM, etc.

Additionally, it is useful to bucket stored procedures, functions and triggers by conversion complexity ? high, medium and low:

? High complexity ? wide use of SQL Server specific features, XML, built-in system procedures etc.

? Medium complexity ? use of advanced SQL Server features that can be redesigned to corresponding MariaDB features

? Low complexity ? Basic SQL business logic requiring small syntax changes

Step 3 - Run the MariaDB SQLines assessment tool to get a detailed report on pertinent SQL features including:

? Data types ? Built-in SQL functions ? Transact-SQL statements, constructs, etc. ? SQL statements

SQLines assessment report example:

DATA TYPES

All built-in data types:

Data Type

1

BIT

2

DATETIME

3

DECIMAL

4

FLOAT

5

INT

6

MONEY

7

NVARCHAR

8

REAL

9

SMALLINT

10 TEXT

11 VARCHAR

12 [BIGINT]

13 [CHAR]

14 [DATETIME]

15 [DECIMAL]

16 [FLOAT]

17 [IMAGE]

18 [INT]

19 [MONEY]

20 [INCHAR]

21 [NTEXT]

22 [NUMERIC]

23 [NVARCHAR]

24 [REAL]

25 [SMALLDATETIME]

26 [SMALLINT]

27 [TIMESTAMP]

28 [UNIQUEIDENTIFIER]

29 [VARCHAR]

Occurrences 154 25 31 175 378 16 67 13 3 32 816 2 19 114 1 379 3 298 15 28 66 3 649 29 21 56 22 1 21

Total: 29 distinct data types, 3437 occurrences.

FUNCTIONS

All built-in functions:

Function

1

ACOS

2

CAST

3

CONVERT

4

COS

5

COUNT

6

DATEADD

7

DATEDIFF

8

GETDATE

9

ISNULL

10 LEFT

11 LEN

12 LTRIM

13 MAX

14 MIN

15 NEWID

16 REPLACE

17 REPLICATE

18 RIGHT

19 ROUND

20 RTRIM

21 SIN

Occurrences 1 414 38 3 16 3 2 38 604 6 8 139 52 3 1 52 1 2 10 139 2

Total: 21 distinct functions, 1535 occurrences.

STATEMENTS

All SQL statements:

Statement

1

ALTER TABLE

2

CREATE FUNCTION

3

CREATE PROCEDURE

4

CREATE TABLE

5

CREATE VIEW

6

DELETE

7

DROP TABLE

8

INSERT

9

UPDATE

10 USE

Occurrences 428 5 185 73 6 33 3 46 98 1

CREATE TABLE statements details:

Details

1

CREATE TABLE statements

2

Columns

3

NOT NULL contraints

ALTER TABLE statements details:

Details

1

ALTER TABLE statements

Total: 10 distinct statements, 878 occurrences.

SCHEMA AND DATA MIGRATION

Occurrences 73 1880 119

Occurrences 428

Actual migration typically starts with the schema and data migration, which includes migration of table definitions, constraints, views, transfer of data, and optionally, creation of indexes (which may be done later).

SQL Server and MariaDB use different data types, so you have to convert some of them as follows:

SQL Server

1

BIGINT

2

BINARY (n)

3

BIT

4

CHAR (n)

5

DATE

6

DATETIME

7

DATETIME2 (p)

8

DATETIMEOFFSET (p)

9

DECIMAL (p,s)

10 DOUBLE PRECISION

11 FLOAT

12 IMAGE 13 INT, INTEGER 14 MONEY 15 NCHAR (n)

64-bit integer Fixed-length byte string, 1 n 8000 1,0 or NULL Fixed-length string, 1 n 8000 Date (year, month and day) Date and time with fraction Date and time with fraction Date and time with fraction and time zone Fixed-point number Double-precision floating-point number Floating-point number Variable-length binary data, 2G 32-bit integer 64-bit currency amount Fixed-length Unicode UCS-2 string, 1 n 4000

MariaDB

BIGINT

BINARY (n), BLOB

TINYINT

CHAR (n)

n ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download