MySQL to MS SQL Server 2008 R2 Migration Experience with SSMA

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:

• I started with hunting for an installer in MySQL page.
• I got a free edition in MySQL called MySQL Community Server.
• There were multiple options in the download section but I was confused to see 32 and 64 bit both with the term X86.
• Next confusing step was, should I select MSI or Zip archive.
• I realized that MSI installer is not full download and while installing, it might download lot more stuff so decided to go for Zip Archive.
• Downloaded the complete package and extracted in the C:\Program File but now what?
• Online documentation were not so helpful but good part that package internal documentation were better. I just had to spend some time to understand which file to open due to absence of proper index or file extensions.
• Finally MySQL was running on my VPC as a service.
• Next step was How do I manage this server. I still remember my Linux days when there used to be some 3rd party tools which used to allow MySQL management with limited extent.
• Finally I ended up with downloading MySQL Workbench the GUI tool.
• It was easy to connect through the tool but what is my User Name and Password?
• Finally I opened up my old notes and refreshed my MySQL knowledge. Unfortunately no documentation was there to help .
• Ah so at the end I was connected and I could create new Databases (Schema) in MySQL environment.
• I agree that the workbench was very easy. It allows Schema and Table creation and also allows data insertion.
• I did change root user’s password and could create few users using queries as well as from Workbench.
• I even learnt that there is something available in PHP called MyAdmin which can help me in connecting to MySQL management but unfortunately couldn’t make it working..
• Finally the MySQL demo environment was ready for migration.

Migration of MySQL to SQL Server 2008 R2:

• We just came up with a latest Migration tool for MySQL (SSMA for SQL Server).
• The current version of SSMA for MySQL is still in CTP.
• I started with the same and downloaded the latest licenses and refresh with the same (an important step to proceed with SSMA)
• I realized that I would also have to download the ODBC Driver for MySQL
• I could connect to the MySQL through SSMA.
• Next step was to connect to the destination SQL Server.
• There are different options of MySQL to SQL migration including 1:1 Database Migration or Many:1 Database Migration.
• I selected 1:1 option where I created a new database in SQL Server so that all the tables from MySQL Schema (database) can be migrated to the same.
• Now I had to map the MySQL data types as per MS SQL Server. There is a long table available in MySQL migration guide which should be referred for this step:

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	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
MEDIUMINT (M)	int	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
INT (M) INTEGER (M)	int	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
BIGINT (M)	bigint	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
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.	numeric(p,s) , decimal(p,s) , float(p) , double precision, real, smallmoney, money
DOUBLE [(P, S)] DOUBLE PRECISION [(P, S)] REAL [(P, S)]	float (53)	numeric(p,s) , decimal(p,s) , float(p), double precision, real, smallmoney, money
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.	numeric(p,s) , decimal(p,s) , float(p) , double precision, real, smallmoney, money
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)]	smallint	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).	char, varchar, nchar, nvarchar
TINYTEXT	nvarchar (255)	None.	char, varchar, nchar, nvarchar
TEXT (N)	nvarchar (N | max)	A TEXT column with a maximum length of 65,535 characters. If N is less than or equal to 8000, use nvarchar(N). If it is greater than 8000, use nvarchar(max).	char, varchar, nchar, nvarchar, varchar(max) , nvarchar(max)
MEDIUMTEXT	nvarchar (max)	None.	char, varchar, nchar, nvarchar, varchar(max) , nvarchar(max)
LONGTEXT	nvarchar (max)	None.	char, varchar, nchar, nvarchar, varchar(max) , nvarchar(max)
BINARY (N)	binary (N)	None.	binary, varbinary, char, varchar, nchar, nvarchar
VARBINARY (N)	varbinary (N)	None.	binary, varbinary, char, varchar, nchar, nvarchar
TINYBLOB	varbinary (255)	None.	binary, varbinary, varbinary(max)
BLOB (N)	varbinary (N | max)	A BLOB column with a maximum length of 65,535 bytes. If N is less than or equal to 8000, use nvarchar(N). If it is greater than 8000, use nvarchar(max).	binary, varbinary, varbinary(max)
MEDIUMBLOB	varbinary (max)	None.	binary, varbinary, varbinary(max)
LONGBLOB	varbinary (max)	None.	binary, varbinary, varbinary(max)
ENUM		See ENUM and SET Data Types in this guide.
SET		See ENUM and SET Data Types in this guide.

• The next step was to map and migrate schema.
• This would create all the tables of MySQL to SQL Server database.
• Once done, now you can select Migrate data and that’s it…Migration done.

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?

Comments

• Anonymous
  June 10, 2014
  Nice article