Live Chat Icon For mobile
Live Chat Icon
Popular Categories.NET  (143).NET Core  (23)Angular  (39)ASP.NET  (47)ASP.NET Core  (53)ASP.NET MVC  (83)Azure  (27)Blazor  (49)DocIO  (18)Essential JS 2  (64)Essential Studio  (159)Flutter  (53)JavaScript  (134)Microsoft  (96)PDF  (48)React  (29)Succinctly series  (118)Syncfusion  (400)TypeScript  (30)Uno Platform  (2)UWP  (4)Vue  (25)Webinar  (14)Windows Forms  (52)WPF  (92)Xamarin  (105)XlsIO  (20)Other CategoriesBarcode  (4)BI  (29)Bold BI  (3)Build conference  (6)Business intelligence  (53)Button  (4)C#  (100)Chart  (40)Cloud  (8)Company  (445)Dashboard  (4)Data Science  (3)Data Validation  (2)DataGrid  (31)Development  (234)Doc  (7)eBook  (89)Enterprise  (22)Entity Framework  (5)Essential Tools  (14)Excel  (14)Extensions  (7)File Manager  (3)Gantt  (6)Gauge  (4)Git  (3)Grid  (24)HTML  (8)Installer  (1)Knockout  (2)LINQPad  (1)Linux  (1)M-Commerce  (1)Metro Studio  (11)Mobile  (140)Mobile MVC  (9)OLAP server  (1)Orubase  (12)Partners  (20)PDF viewer  (15)Performance  (2)PHP  (1)PivotGrid  (4)Predictive Analytics  (6)Report Server  (3)Reporting  (10)Reporting / Back Office  (11)Rich Text Editor  (5)Road Map  (9)Scheduler  (15)SfDataGrid  (7)Silverlight  (21)Sneak Peek  (12)Solution Services  (2)Spreadsheet  (2)SQL  (4)Stock Chart  (1)Surface  (4)Tablets  (5)Theme  (9)Tips and Tricks  (17)UI  (62)Uncategorized  (68)Unix  (2)User interface  (49)Visual State Manager  (1)Visual Studio  (12)Visual Studio Code  (7)Web  (99)What's new  (62)Windows 8  (19)Windows App  (1)Windows Phone  (15)Windows Phone 7  (9)WinRT  (26)
Share on twitter
Share on facebook
Share on linkedin
How to Migrate SQL Server in an ASP.NET MVC Application to MySQL and PostgreSQL

How to Migrate SQL Server in an ASP.NET MVC Application to MySQL and PostgreSQL

In this blog, we are going to learn the procedure to migrate a SQL Server database in an ASP.NET MVC application to MySQL and PostgreSQL.

All three, Microsoft SQL Server, MySQL and PostgreSQL, are relational database management systems (RDBMS). MSSQL is developed and maintained by Microsoft, whereas MySQL and PostgreSQL are open-source software. MySQL is maintained by Oracle.

PostgreSQL is an advanced, enterprise-class, and open-source object relational database management system (ORDBMS) that supports both SQL and JSON querying. It provides good performance along with low maintenance.

Reasons to choose MySQL

The following are reasons for someone to choose MySQL for their database needs:

  • It needs less operational storage space.
  • As it is open-source software, it is free to use.
  • It allows the database files to be accessed and manipulated by different processes at runtime.

Reasons to choose PostgreSQL

  • It is open-source software and free to use.
  • It supports a concurrency management system. This allows you to access and manipulate multiple processes at the same time.

These are some of the reasons for developers to choose or migrate from MSSQL to MySQL or PostgreSQL. Let’s proceed with the steps involved in the migration process.

Prerequisites

In this blog, I am using Visual Studio to develop my application. Be sure that these necessary software packages are installed in your machine:

Create an ASP.NET MVC application with SQL Server database

In this section, we are going to create an ASP.NET MVC application with SQL Server database for demonstrating the conversion.

    1. Create an ASP.NET MVC application in Visual Studio 2019.
    2. Install the latest versions of the following NuGet packages:
      • EntityFramework
      • System.Data.SqlClient
      • Mysql.Data.Entity
      • Npgsql.EntityFramework
    3. Create a model class Student with the following code to record students’ ID, first name, last name, email ID, and other details.
      using System;
      using System.ComponentModel.DataAnnotations;
      using System.ComponentModel.DataAnnotations.Schema;
      
      namespace StudentManagementSQLServer.Context
      {
          public class Student
          {
              [Key]
              [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
              public int Id { get; set; }
      
              [Column(TypeName = "varchar(MAX)")]
              public string FirstName { get; set; }
      
              [Column(TypeName = "varchar")]
              [MaxLength(100)]
              public string LastName { get; set; }
      
              [Column(TypeName = "varchar")]
              [MaxLength(100)]
              public string Email { get; set; }
      
              [Column(TypeName = "nvarchar(MAX)")]
              public string ProfileImage { get; set; }
      
              [Column(TypeName = "nvarchar(MAX)")]
              public string Address { get; set; }
      
              [Column(TypeName = "decimal")]
              public decimal Age { get; set; }
      
              [Column(TypeName = "datetime")]
              public DateTime DOB { get; set; }
      
              [Column(TypeName = "datetime")]
              public DateTime? CreatedDate { get; set; }
      
              public int? CreatedBy { get; set; }
      
              public bool IsDeleted { get; set; }
      
          }
      }
    4. Then, create a DBContext StudentContext.
      using System.Data.Entity;
      
      namespace StudentManagementSQLServer.Context
      {
      public class StudentContext : DbContext
      {
      public DbSet<Student> Student { get; set; }
      public StudentContext() : base("StudentDBConnection") { }
      }
      }

      Note: StudentDBConnection points out the database connection detail maintained in the Web.Config file.

    5. Add the database connection string details in the Web.Config file.
      <connectionStrings>
      <add name="StudentDBConnection" providerName="System.Data.SqlClient" connectionString="Server=*********;Database=StudentManagement_MVC;user id=*********;password=*********;MultipleActiveResultSets=true;" />
      </connectionStrings>

      Note:The database in MSSQL will be created with the same name as that of the Connection String Database.

      This sample project is available in this GitHub location.

Migration from MSSQL to MySQL

In the previous section, we saw the steps to create an ASP.NET MVC application with a MSSQL database. Follow the next steps to migrate the MSSQL database to a MySQL database.

Step 1: Install the NuGet package MySql.Data.Entity by searching for it in the Browse tab in the NuGet Package Manager.

Install the NuGet package MySql.Data.EntityStep 2: Update the DbConfigurationType in the DBContext file to migrate to the MySQL database configuration.

using MySql.Data.Entity;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace StudentManagementMySQL.Context
{
    [DbConfigurationType(typeof(MySqlEFConfiguration))]
    public class StudentContext : DbContext
    {
        public DbSet<Student> Student { get; set; }
        public StudentContext() : base("StudentDBConnection") { }
    }
}

Step 3: Change the connection string value in the Web.Config for MySQL database configuration.

<connectionStrings>
<add name="StudentDBConnection" providerName="MySql.Data.MySqlClient" connectionString="Server=localhost;Database=StudentManagement_MVC;user id=root;password=123456;"/>
</connectionStrings>

Step 4: Ensure the MySQL version of data types support in SQL Server.

You can find the comparison of sample student modals between the MSSQL and MySQL databases in the following table.

MSSQLMySQLConversion Remarks
Nvarchar(max)Textnvarchar allows 4,000 characters but max indicates maximum storage size of 2^31-1 bytes.
Varchar(max)Textvarchar allows 4,000 characters but max indicates maximum storage size of 2^31-1 bytes.
BoolBoolNo conversion
DecimalDecimalNo conversion
VarcharVarcharNo conversion
NvarcharNvarcharNo conversion
IntIntNo conversion
DatetimeDatetimeNo conversion

For more details, check out the following link:

https://www.mssqltips.com/sqlservertutorial/2203/mysql-to-sql-server-data-type-comparisons/

Restore database

In order to use code-based migration, you need to execute the following commands in the Package Manager Console in Visual Studio.

Step 1: Open the Package Manager Console with View -> Other Windows -> Package Manager Console.

Step 2: Execute the Enable-Migrations command.
Execute the Enable-Migrations command
Step 3: Run the Add-Migration {{MigrationName}} command like in the following screenshot. In our case, the migration name is StudentMigration. So, the command with the parameter would be Add-Migration StudentMigration.

Run the Add-Migration StudentMigration commandStep 4: When migrations are done, it will create the <timestamp>_StudentMigration.cs file in the Migrations folder.

Please ensure that you get a file structure like the following image after successful migration.
Migrations folder with the list of files

Step 5: Run the Update-Database command.
Run the Update-Database command

Step 6: After successful command execution, the database schema will be restored in the indicated database (see screenshot of restored database).
MySQL database schema

That’s it, the MSSQL to MySQL migration is complete. Let’s now proceed with the conversion of MSSQL to PostgreSQL.

Migration from MSSQL to PostgreSQL

For this conversion, we have to use the project with the MSSQL database and not the one acquired from the previous steps. You can get the project with MSSQL from this GitHub location. Follow these steps to perform migration from a MSSQL database to a PostgreSQL database.

Step 1: Install the package Npgsql.EntityFramework by searching for it in the Browse tab in the NuGet Package Manager.
Install the package Npgsql.EntityFramework

Required changes in Web.Config

Step 2: Update the DBContext file to migrate to PostgreSQL database configuration.

<connectionStrings>
<add name="StudentDBConnection" providerName="Npgsql" connectionString="Server=localhost;Database=StudentManagement_MVC;user id=postgres;password=123456;Port=5432;Integrated Security=true;Pooling=true;" />
</connectionStrings>

Step 3: Add the defaultConnectionFactory tag inside the entityFramework tag in the Web.Config file.

<entityFramework>
<defaultConnectionFactory type="Npgsql.NpgsqlFactory, Npgsql" />
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, Npgsql.EntityFramework" />
</providers>
</entityFramework>

Step 4: Add the system.data details as follows in the Web.Config file.

<system.data>
<DbProviderFactories>
<remove invariant="Npgsql" />
<add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" support="FF" />
</DbProviderFactories>
</system.data>

Step 5: Ensure the conversion data types during the MSSQL-to-PostgreSQL conversion.

Find the comparison of sample student modals for the MSSQL and PostgreSQL databases in the following table.

MSSQL PostgreSQLConversion Remarks
Nvarchar(max)TextVariable length Unicode UCS-2 data, <= 2GB
Varchar(max)TextVariable length char string, <= 2GB
BoolBoolNo conversion
DecimalDecimalNo conversion
VarcharVarcharNo conversion
NvarcharNvarcharNo conversion
IntIntNo conversion
DatetimeTIMESTAMPDate and time with fractional seconds

For more details, check out the following link:

https://www.enterprisedb.com/blog/microsoft-sql-server-mssql-vs-postgresql-comparison-details-what-differences#:~:text=PostgreSQL%20is%20easy%2Dto%2Duse,developed%20and%20operated%20by%20Microsoft.

Restore database

In order to use code-based migration, execute the following commands in the Package Manager Console in Visual Studio.

Step 1: Open the Package Manager Console View -> Other windows -> Package Manager Console.

Step 2: Run the Enable-Migrations command in the Package Manager Console.
Run the Enable-Migrations command

Step 3: Run the Add-Migration {{MigrationName}} command like in the following screenshot. In our case, the migration name is StudentMigration1. So, the command with the parameter would be Add-Migration StudentMigration1.

Run the Add-Migration StudentMigration1 command

Step 4: When migrations are done, it will create the <timestamp>_StudentMigration.cs file in the Migrations folder. Ensure you got a file structure like in the following image after successful migration.
Migrations folder showing the list of files

Step 5: Run the Update-Database command.
Run the Update-Database command

Step 6: After successful command execution, the database schema will be restored in the indicated database (see screenshot of restored database in the image).
PostgreSQL database Schema

References

https://www.c-sharpcorner.com/article/code-first-migration-asp-net-mvc-5-with-entityframework-mysql/

https://www.codeproject.com/Articles/783552/Using-PostgreSQL-with-Entity-Framework-in-ASP-NET

Resources

You can check out all these samples in this GitHub repository.

Conclusion

I hope this blog provided a clear idea of how to migrate an MSSQL database used in an ASP.NET MVC application to MySQL and PostgreSQL databases. Please try these steps and share your comments in the comment box.

The Syncfusion ASP.NET MVC UI control library contains over 70 high-performance, lightweight, modular, and responsive UI controls in a single package. Use them to increase your productivity!

For existing customers, the newest version is available for download from the License and Downloads page. If you are not yet a Syncfusion customer, you can try our 30-day free trial to check out our available features. Also, try our samples from this GitHub location.

You can also contact us through our support forumDirect-Trac, or feedback portal. We are always happy to assist you!

Tags:

Share this post:

Share on twitter
Share on facebook
Share on linkedin

Leave a comment

Popular Now

Be the first to get updates

Subscribe RSS feed
Scroll To Top