The migration process is indeed very straight forward (video); however I ran into a few minor issues with my migration from SQL Server 2005 to MySQL.

At first, the MySQL Migration Toolkit didn’t appear to have SQL Server 2005 to MySQL database support. Now, I’ve used the MySQL Migration toolkit for data migration in the past (with SQL Server 2000) and it worked flawlessly for me.

However, when I tried using the MySQL Migration Toolkit to import my SQL Server 2005 database (using “SQL Server” as the source database with the default of port 1433), the MySQL Migration Toolkit would throw a “Connection Refused” exception. As a result, I changed the port to 1434 (the correct port) and then the MySQL Migration Toolkit would proceed further, but then it would throw an exception at the “reverse engineering” step.

After a brief Internet search, I saw that this “SQL 2005 to MySQL” migration issue had been submitted at the MySQL bug tracking site: Bug #20674.

Basically, to solve my SQL Server 2005 to MySQL database migration problem, I had to change the SQL Server 2005 configuration in the SQL Server Configuration Manager. You have to set SQL Server 2005 Network Configuration -> Protocols for MSSQLSERVER -> TCP/IP to Enabled.

After I made this change to the SQL Server 2005 configuration (and restarted the MSSQL server), I was able to use the MySQL Migration Toolkit with port 1433 and the SQL Server 2005 to MySQL database import worked as intended! WooHoo!!

Additional notes…
You may need to enable MS SQL Server Mixed Mode (SQL Server and Windows Authentication mode) so that the MySQL Migration Toolkit can establish a connection.

Related links:

MySQL Migration Toolkit – the official site from MySQL.

How to: Change Server Authentication Mode – How to enable MS SQL 2005 Server Mixed Mode.

Migrate data from SQL Server to MySQL – useful article for SQL Server to MySQL migration.

VIDEO: SQL Server Migration using the MySQL Migration Toolkit – From the MySQL folks, this video explains how to migrate data from SQL Server to MySQL.

SQLYog Enterprise – MySQL database synchronization and more.

Tags: database, MySQL, programming by nolan
No Comments »

If you’re coming from another database environment such as Microsoft SQL Server and you’re looking to generate create table scripts on Windows with MySQL, you may want to consider the following options …

• mysqldump.exe

From the command prompt, navigate to your MySQL bin directory and use the mysqldump.exe command-line application as follows…

mysqldump -u USER --password="MYPWD" DBNAME --no-data > create_tables.sql

If you leave off the –no-data argument, mysqldump will dump the entire database into the create script. The mysqldump.exe documentation explains all the possible arguments and there are very useful user comments/scripts at the bottom of the online documentation page.

To bring a database script back into MySQL:

mysql.exe DBNAME < create_tables.sql

• MySQL Query Browser - Windows application

Within MySQL Query Browser, right click on a particular table (under a particular database in the Schemata treeview) and select "Copy CREATE statement to clipboard." This option will allow you to copy and paste the MySQL create table script into your favorite text editor.

Also, while inside the MySQL Query Browser, you can open a new Script tab under "File-> New Script Tab" (or by pressing Ctrl + Shift + T). Once the script tab is open, drag tables onto the script tab to generate the CREATE TABLE script.

More useful links and information concerning how to generate MySQL create table scripts...

• mysqldump.exe documentation - all command-line options explained and useful comments at the bottom of the page.
• MySQL Query Browser: The Script Editor - Windows application for executing MySQL queries, etc.
• MySQL Client Programs documentation - more useful MySQL command-line applications explained.

Tags: database, MySQL, programming by nolan
No Comments »

If you’re wanting to setup SubSonic and MySQL for your ASP.NET Web Application, here is a quick overview that might be useful…

• Download and install…
• SubSonic (download) – Builds your Data Access Layer (DAL) and provides useful utility functions.
• MySQL Community Server – The world’s most popular open source database.
• MySQL 5.2.1 .NET Connector or later version. – MySQL .NET Data Access Provider.
  
• Create a MySQL database and a MySQL test table. Add dummy data to the test table. In the below example, the database is named “test” and the table is named “School.”
  
• Modify your ASP.NET web application’s web.config.

  An example web.config for SubSonic and MySQL :

  <configSections>
  <section type=”SubSonic.SubSonicSection, SubSonic” name=”SubSonicService” requirePermission=”false” />
  …
  </configSections>

  <appsettings />
  <connectionStrings>
  <add name=”MySqlDbString” connectionString=”Server=localhost;User ID=User;Password=PW;Persist Security Info=True;Database=test” providerName=”MySql.Data.MySqlClient” />
  </connectionStrings>
  <SubSonicService defaultProvider=”MySqlDataProvider”>
  <providers>
  <add type=”SubSonic.MySqlDataProvider, SubSonic” name=”MySqlDataProvider” generatedNamespace=”DAL” connectionStringName=”MySqlDbString” />
  </providers>
  </SubSonicService>

• Change the above web.config settings to match your configuration.

  A couple of tips…

• Set generatedNamespace=”DAL” to whatever namespace would be appropriate for your purposes. There are many SubSonic options that you may want to modify such as to include or exclude certain tables. You may also want to modify the templates.
• Add a Reference to your project to “MySQL.Data.dll” and “SubSonic.dll” in your Web Application for good measure.
  
• In your Visual Studio Project Solution Explorer, click “Show All Files” to see your web application’s bin directory.

  

  Right click on the project’s “bin” folder and “Include in Project.” This action will bring your bin directory into your project.

• Add “SubSonic.dll” to your bin folder. Right click on the “bin” folder in your Visual Studio Solution Explorer, select “Add an existing item…”, and choose your SubSonic.dll. The SubSonic.dll can be found in your installation directory for SubSonic (probably under Program Files). If you’ve already added a reference to SubSonic to your web application, this step probably won’t be needed.
• Within your Visual Studio project, create a “data access layer” folder and a “generated” folder (the generated folder should be under your data access layer folder). In this example, I’ve created a “Data Access Layer” folder as well as “Generated” folder under this Data Access Layer folder. The “Data Access Layer” folder will contain your customizations and the “Generated” folder will contain the automatically generated SubSonic classes.
  
• Setup your Visual Studio to support the generation of classes with SubSonic (one time setup for your project). Tools-> External Tools -> Add…

  

  For the title: SubSonic DAL
  
  Command: Browse to your SubSonic installation and reference the sonic.exe

  Arguments: generate /out “Data Access Layer\Generated”

  * if your DAL folder contains spaces, don’t forget to enclose with quotes.
  
  Initial Directory: Click the “>” button and select $(ProjectDir)

  Select “Use Output Window”
  Select “Prompt for Arguments.”

  Click “OK” and save the changes.

  ---

  Create a button in your Visual Studio project for this new External Tool and/or add a Build event to automate the creation of your SubSonic database classes. You should now be able to use SubSonic as an External Tool. You may read more about External Tools on MSDN.

• Use “SubSonic DAL” External Tool within Visual Studio. The Visual Studio 2008 output window will show if your database class generation was successful or if something went wrong.

• If you’re getting errors, be sure you’re using the correct CaSE in your web.config.
• Be sure your connectionStringName for the SubSonicService matches your connection string name and that you have necessary database permissions.
• Refresh your Visual Studio Project (Use the “Refresh” button at the top of your Web Application’s Solution Explorer) to view the generated classes under your “Data Access Layer\Generated” folder. Include the newly generated classes. Be sure that all of your SubSonic generated classes are included your project (use the same process as above for including the “bin” folder).

You’re done!

Now, add a Gridview to your .NET web application and start playing with SubSonic and MySQL…

• Query (examples)
• Active Record Example
• GridView Paging (code example beneath the video)
• Web Forms Controls
• Scaffold

Also, the SubSonic site is full examples, documentation, and webcasts.

Tags: database, MySQL, programming, SubSonic by nolan
1 Comment »

At this time, there doesn’t seem to be a good guide published on setting up your .NET web application to use MySQL as your ASP.NET Membership Provider.

The following worked for me. However, no warranty is expressed or implied.

• Download and install MySQL Connector/Net 5.2.1 or later version.
  
• Add a reference to MySQL.Web to your web application.
  C:\Program Files\MySQL\MySQL Connector Net 5.2.1\Web Providers\MySql.Web.dll
• Add the autogenerateschema=”true” attribute. Since the MySQL database schema wasn’t automatically created for me, I ended up using the autogenerateschema attribute. The attribute will signal the MySQL provider to build (or upgrade) the database schema.The MySQL 5.2.1 release notes state the following…
  

  Using the new provider schema
  =============================
  For this release. the only way to upgrade a given server to the new schema is to
  add a configuration option for one of your providers. The option is ‘autogenerateschema’.
  By setting this to true, the provider will silently upgrade the server to the new schema.
  Please note that there is no reversing of this procedure so please just do this on test
  setups and not on your production systems.

  Personally, I found it easiest to just add autogenerateschema=”true” to my machine.config on my development machine (as opposed to web.config) and it’s under providers…

  <membership>
  <providers>
  <add name=”MySQLMembershipProvider” autogenerateschema=”true” ….
  </providers>

  Save the changes.

• Edit your web application’s web.config.
  

  <connectionStrings>
  <remove name=”LocalMySqlServer”/>
  <add name=”LocalMySqlServer” connectionString=”Datasource=localhost;Database=DB;uid=Username;pwd=Password;”
  providerName=”MySql.Data.MySqlClient”/> </connectionStrings>
  

  
  Save the changes.

• Build your Web Application.
  
• Now, administer/config your web application.
  Under the ASP.NET Web Site Administration Tool provider tab, click “Select a Different Provider (advanced)” and change the provider to MySQLMembershipProvider.

At this point, you should be able to use MySQL as your ASP.NET Membership and Role Provider (the tables will be automatically built for you).

After the tables are built, you’ll want to setup your web application’s web.config (using your machine.config as a template) so that you will have access to all of the membership provider settings.

Tags: database, MySQL, programming, useful by nolan
43 Comments »