Migrate from MySQL to SQL Server 2008

You can migrate from the MySQL database to SQL Server 2008 using the following steps:

1. Decide how you will map MySQL databases to SQL Server 2008. You have two main options:

• Map each MySQL database to a separate SQL Server database. 
  For example, you could map the MyDB MySQL database to MyDB SQL Server database.
• Map each MySQL database to a single SQL Server database but a separate schema. 
  For example, you could map the MyDB MySQL database to MySQLDatabases SQL Server database, schema MyDB.

2. In SQL Server, schemas are not necessarily linked to a specific user or a login, and one server contains multiple databases.

3. Convert database objects; these are tables, tables constraints, indexes, view, procedures, functions, and triggers.

4. Map data types from the MySQL data type to a SQL Server data type.

5. Rewrite your views, procedures, and functions according to SQL Server syntax.

6. Change your applications as necessary so that they can connect and work with SQL Server 2008.

After a successful database conversion, migrate your data from the old MySQL database to the newly created SQL Server 2008 database. For this task, you could use Microsoft® SQL Server® Integration Services (SSIS), for example.http://learn.iis.net/page.aspx/759/migrate-from-mysql-to-sql-server-2008/

Also you try 
SSMA is stands for the SQL server Migration Assistance

The minimum required for the migration of Microsoft SQL Server 2000 to Microsoft SQL Server 2008. You can use the same principles to migrate a Microsoft SQL Server 2005 to Microsoft SQL Server 2008.


1. SQL server 2008 upgrade advisor

1.1. Before migrating
Microsoft provides a tool called "Microsoft SQL Server 2008 Upgrade Advisor" to alert you of any changes in design between Microsoft SQL Server 2000/2005 and SQL Server 2008. It is strongly recommended to run this software before migration.

1.2. Download
You can download this tool from the link below:

“Download the Microsoft SQL Server 2008 Upgrade Advisor. Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 to help you prepare for upgrades to SQL Server 2008.”
http://www.microsoft.com/downloads/details.aspx?familyid=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en 

After installation, a new tab appears in: Start>> All Programs >> Microsoft SQL server 2008 >> SQL Server 2008 Upgrade Advisor

1.3. Report before migration
Run “SQL Server 2008 Upgrade Advisor”.
Then click on “Launch Upgrade Advisor Analysis Wizard”.
Then click on "Detect". The tool will automatically select the components installed on your platform.
It is also interesting to give a trace profiler tool containing a representative of your business so that it detects all the elements that would longer supported or recommended in Microsoft SQL Server 2008.


Then configure the connection to your SQL server 2000 instance. After a few minutes a report will be generated with warning or points on which you must bring your attention. These items may include Full Text Search, replication, objects that no longer exist or have been modified in the new version, plans to maintain ...
The tool will provide two other types of information:
1. Objects affected
2. Advice you can find a workaround or fix the problem.

Sample report provided by the tool:


3. Migration with the database restore method

3.1. Restoring a database SQL server 2000
On your new instance Microsoft SQL Server 2008, connect to Management Studio 2008. Then click on the "Restore Database". Then follow the instructions.

RESTORE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186858.aspx

How to: Restore a Database Backup (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms177429.aspx
 
3.2. Compatibility Level SQL 2000/ SQL 2008
If you restore your database in SQL Server 2000 SQL Server 2008, the level of compatibility will default mode "SQL Server 2000 (80).
To know the level of compatibility, Make a right click on the name of the database>> "Property"
Then in the dialog "Database Properties", click "Options"



To enjoy all the new features in the new engine SQL server 2008, you must change the compatibility level to 100.

To know the differences between compatibility 80, 90 or 100, I invite you to read the following article
http://msdn.microsoft.com/en-us/library/bb510680.aspx

sp_dbcmptlevel (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms178653.aspx 
 

3.3. Transferring SQL Server logins and Windows
There are different ways to migrate your users
- SQL Server Intégration Services, with component « "transfer Login task ».
- SQL Server Management Studio, with “Copy Database Wizard”
- You can also draw on examples of script between SQL Server 2000 and 2005 KB Article http://support.microsoft.com/kb/246133

3.4. SQL Server Agent jobs
You can migrate your SQL Server Agent jobs using Enterprise Manager 2000. You can find more detail in the documentation below:

How to script jobs using Transact-SQL (Enterprise Manager)
http://msdn.microsoft.com/en-us/library/aa177024%28SQL.80%29.aspx

3.5. Other components
You must also reconfigure the components such as SQL database Mail extended stored procedures, linked servers...

3.6. Update statistics
It is recommended that, after having committed or changed the compatibility mode to 100, execute the stored procedure: sp_updatestats

The procedure allows sp_updatestats system to recalculate the statistics and make an update for all the statistics on each table in your base data. To avoid errors related to the statistics of the previous version.

sp_updatestats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173804.aspx

You will have to not only use upgrade advisor to check the mirgration, but also you will have to manually test all contents. From 2000 to 2008, it's a very big jump. Proper testing is always recommended.

There are quite a lot good answers in this forum, such as

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/c5d703a5-27bc-4527-aa22-8e5e0d660652/

also you can look at this ms doc

SQL Server 2008 Upgrade Technical Reference Guide

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en