It is almost 7 years when I last used MySQL. In these 7 years MySQL has changed a lot and I also became a true MS SQL user/administrator. I realized this when I started setting up MySQL on my lab. Must say our SQL Server system is the best in usability front. I am sharing my learning's in this page.
Installing/Setting up MySQL:
Migration of MySQL to SQL Server 2008 R2:
MySQL type
SQL Server 2008 mapping
Conversion remarks
Possible mappings
BIT (N)
varbinary (8)
The binary value has N bits. N = 1..64
Not applicable
TINYINT (M)
BOOL, BOOLEAN = TINYINT (1)
tinyint
M is the number of decimal places in the output for this value.
tinyint, smallint, int, bigint, numeric(p,s), decimal(p,s), float(p), double precision, real, smallmoney, money
SMALLINT (M)
smallint
MEDIUMINT (M)
int
INT (M)
INTEGER (M)
BIGINT (M)
bigint
FLOAT (P)
float (P)
None.
numeric(p,s), decimal(p,s), float(p), double precision, real, smallmoney, money
FLOAT [(P, S)]
float (24)
MySQL allows a nonstandard syntax: FLOAT(P,S) or REAL(P,S) or DOUBLE PRECISION(P,S). Here, “(P,S)” means that values are displayed with up to P digits total, of which S digits may be after the decimal point. MySQL performs rounding when storing values. If M and D are omitted, values are stored up to the size limits allowed by the hardware.
DOUBLE [(P, S)]
DOUBLE PRECISION [(P, S)]
REAL [(P, S)]
float (53)
DECIMAL [(P [, S])]
DEC [(P [, S])]
NUMERIC [(P [, S])]
FIXED [(P [, S])]
decimal [(P [, S])]
numeric [(P [, S])]
Decimal types can have up to 65 digits. For a decimal with a precision of more than 38, use the float or double data type.
DATETIME [(D)]
datetime2
MySQL can store dates from 0000-00-00 to 9999-12-31. MySQL can store zero-value of year, month, and year.
smalldatetime, datetime, datetime2
DATE [(D)]
date
smalldatetime, datetime, datetime2, date
TIME
time
Range is '-838:59:59' to '838:59:59'.
smalldatetime, datetime, datetime2, time, varchar, nvarchar
TIMESTAMP
smalldatetime
Range is '1970-01-01 00:00:00' to partway through the year 2037. If not defined during conversion, this type gets the current datetime value.
datetime, datetime2, rowversion, timestamp, varbinary(8), binary(8)
YEAR [(2| 4)]
In four-digit format, allowable values are 1901 to 2155, and 0000. In two-digit format, allowable values are 70 to 69, representing years from 1970 to 2069.
datetime, date, varchar(4)
[NATIONAL] CHAR (N)
nchar (N)
nchar
Range of N is 0 to 255 characters.
char, varchar, nchar, nvarchar
[NATIONAL] CHAR
[NATIONAL] VARCHAR (N)
CHARACTER VARYING (N)
nvarchar (N | max)
Range of N is 0 to 65,535.
If N is less than or equal to 8000, use nvarchar(N). If it is greater than 8000, use nvarchar(max).
TINYTEXT
nvarchar (255)
TEXT (N)
A TEXT column with a maximum length of 65,535 characters.
char, varchar, nchar, nvarchar, varchar(max), nvarchar(max)
MEDIUMTEXT
nvarchar (max)
LONGTEXT
BINARY (N)
binary (N)
binary, varbinary, char, varchar, nchar, nvarchar
VARBINARY (N)
varbinary (N)
TINYBLOB
varbinary (255)
binary, varbinary, varbinary(max)
BLOB (N)
varbinary (N | max)
A BLOB column with a maximum length of 65,535 bytes.
MEDIUMBLOB
varbinary (max)
LONGBLOB
ENUM
See ENUM and SET Data Types in this guide.
SET
In this entire migration process, only important point is mapping the data types as per the SQL Server. Once done, rest all the steps are very much easy. So now I am done with migration…when are you trying?