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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- microsoft sql data server tools
- sql server datetime to date
- sql server number to date
- sql server convert datetime to utc
- sql server utc to local
- sql server convert date to string
- sql server convert numeric to string
- sql server varchar to date conversion
- sql server cast datetime to varchar
- sql server datetime to varchar
- sql server convert datetime to varchar
- sql server utc to est