Migrating MySQL Databases to SQL Server

 

1. Database
   1. Working with SSMA Projects: SQL Server Migration Assistant for MySQL
   2. Connecting to MySQL

             

 c. Connecting to SQL Server

         

        d. Mapping MySQL Databases to SQL Server Schemas
         e. Connecting to MS SQL DB
         f. Converting MySQL Databases

 

         g. Synchronization

 

          II. JPA

              a. persistence.xml

• <!--  <property name="hibernate.dialect"       value="org.hibernate.dialect.MySQLDialect"/> -->
• <propertyname="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>

             b. JBoss SQL Driver

<?xml version="1.0" encoding="UTF-8"?>

<module xmlns="urn:jboss:module:1.1" name="com.microsoft.sqlserver.jdbc">

    <resources>
        <resource-root path="sqljdbc4-2.0.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
        <module name="javax.servlet.api" optional="true"/>
    </dependencies>

</module>

        

            c. JBoss Connection String

                <datasource jta="true" jndi-name="java:jboss/datasources/rtsDatasource" pool-name="rtsPool" enabled="true" use-java-context="true" use-ccm="true">

                    <connection-url>jdbc:sqlserver://hostname:port;databaseName=rts;autoReconnect=true</connection-url>

                    <driver>sqljdbc</driver>

                    <security>

                        <user-name>username</user-name>

                        <password>password</password>

                    </security>

                </datasource>

III. Java Entity

a. Datetime:

If we want to use date pattern: dd.MMM.yyyy hh:mm a,

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "rcr_approved_date", length = 0, columnDefinition="DATETIME")
public Date getApprovedDate() {
      return approvedDate;
}

 

b. ManyToOne: mapping not found

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "its_cdd_candidate_id")
    @NotFound(action = NotFoundAction.IGNORE) //to prevent from candidate not found in interview schedule table
    public Candidate getCandidate() {
        return this.candidate;
    }

For instance, we have 1-n relationship. In some case, we have to disable FK to remove parent.

So when we fetch records from children, system will an error called: Record not found.

 

c. Datebase records process: Select Max INSERT size. This  depends on the size of line of insertion not the line of code.

Database prevent us from inserting more than 1000 records.

 

Every table has its ID (PK and AUTO GENERATE), so we have to use this code to insert records to database:

SET 
IDENTITY_INSERT 
"table_name" 
ON;

SET 
IDENTITY_INSERT 
"table_name" 
OFF;