SQL Server to AWS RDS Migration Steps

This document covers the steps to Migrate SQL Server from on Premise DB to  AWS Cloud RDS AURORA DB. Steps are mentioned below-

Following are the steps-

1) Create Aurora DB on cloud on CLIP VPC

• Login To Amazon AWS and Create new RDS Aurora DB in CLIP3 VPC
• For this task below is DB Created-
  
  Server Instance-clip.cluster-ctwaaurutcz2.us-east-1.rds.amazonaws.com:3306
  User Name- clipuser
  Password-<Same as current Clip user password>

2) Install AWS Schema Conversion Tool on machine

Setup available here-

 \\st.com\dept$\Information Technology Dept\IT-Sterling GA\SOA\FrameworksTeam\RDS Changes

3) Convert Schema to Amazon Aurora

• Connect to Source Database(SQL Server DB Instance)
• Connect to Target Database(AURORA cloud- Details provided above)
• Select the Schema from Source DB and Click on Convert Report
• Report will show the details about conversion compatibility
• In Our case except following Objects, other objects were compatible 

1.  Procedures-

                  CLIP3_ARCHIVE_AND_PURGE

                  MIGRATE_CLIP_ARCHIVE_CONFIG

       2. Check constraints

                 CK_TRACKING_EVENT(We dropped this and used Reference table)

                 CK_PREFERENCE(This we will take care of using Trigger)

       3. Tables which has Auto-Increment keys should define that key as primary Key Index otherwise MySQL does not support autoincrement

       4. Attached Migration Report which has details around findings for DB Objects

4) Fix the conversion findings

• Removed CK_PREFERENCE check constraints
• CK_TRACKING_EVENT check is replaced with Reference Key constraint with a new table.
• Re-written the stored procedure to make it compatible with MySQL
• Added Primary key Index to the tables which are missing(EMAIL_ALERT_TEMPLATE, UI_PREF_CONFIG)

5) Push Data from SQL Server to Amazon Aurora(SQL Workbench)

• Download SQL Workbench(Build118) and unzip it
• Download both SQL server(available at com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar in repository) and MySQL Database drivers(mysql-connector-java-5.1.39-bin.jar attached here)
• Create two connection profiles for each of these databases and give drivers path in connection profile(On Click of Manage Drivers)
• Go to Tools on SQL Workbench and click on Data Pumper tool
• Connect to both source and target database
• Move data for each table one by one(Here we need DBA for QA and PROD to move data to AURORA)

6) Changes in Application to use new Aurora DB

• Change in server.xml for each JVM to set new connection string to connect with new database
• Connection String mentioned below-
  
  url="jdbc:mysql://clip.cluster-ctwaaurutcz2.us-east-1.rds.amazonaws.com:3306/CLIP3"
  password=<new password if different>
  driverClassName="com.mysql.jdbc.Driver"
• Add mysql-connector-java-5.1.39-bin.jar to <node>/lib folder
• Change persistence XML to use MYSQL Dialect
• Change in CLIP-UI
  1) main/resources/META-INF/persistence.xml
  
  Changed this property to point MySQLDialect
   <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
  
  2)shared-model/src/main/java/com/sterling/clip/shared/model/TradingPartner.java
  
  Removed [] from @Table(name = "TRADING_PARTNER") line from Model class. Table name in [] is not supported in MySQL.
  
  3)shared-model/src/main/resources/META-INF/spring/spring-shared-model-context.xml
  
  Changed JPA Properties to point MySQL Dialect. We should externalizing this as well??
  
  <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
• Change in BIF
  
  bif\receiver\receiver-gateway\src\main\resources\META-INF\spring\bif-receiver-gateway-context.xml
  
  Externalize this property to support BIF in both SQL Server and MYSQL versions
  
  <prop key="org.quartz.jobStore.driverDelegateClass">${database.driver.jdbcdelegate}</prop>
  
  For On PREM branch, we have to use SQLServerJDBC delegate and for cloud, we have to use StdJDBCDelegate as shown below.
  
  Added this property to receiver-gateway's app.properties file for mysql so it can read from property file.
  
     database.driver.jdbcdelegate=org.quartz.impl.jdbcjobstore.StdJDBCDelegate
• Change in Receiver-gateway
  (Copied from Above)
  Added this property to receiver-gateway's app.properties file for mysql so it can read from property file.
  
     database.driver.jdbcdelegate=org.quartz.impl.jdbcjobstore.StdJDBCDelegate
  
• Change in OMS-Receiver
  No Changes.
• Change in OMS-Sender
  No Changes.
• Change in Sender-gateway
  No Changes.