Many organizations are benefiting from the performance gains and ease of administration of the Amazon RDS platform. The idea of breaking free from commercial licenses, backup administration, and data center maintenance is always a welcome thought. But where to begin, and how complex is the journey to migrate from SQL Server to RDS PostgreSQL-Compatible Edition are common questions. AWS offers different paths for your migration journey, such as the AWS Schema Conversion Tool (AWS SCT), which makes conversions to PostgreSQL easier, and Babelfish, which allows Aurora to understand the SQL Server TDS protocol and language.
| Summary | |
| Heterogeneous Migrations Workshop Session | |
| Debajit led a workshop on heterogeneous migrations, a topic of interest to the attendees. He planned a two-hour session to provide a hands-on experience, with the aim to complete the configurations within that timeframe. Debajit also mentioned that the session would cover the steps, configurations, and components required for database migrations, and the use of AWS tools. He clarified that heterogeneous migrations are the opposite of homogeneous migrations. | |
| Database Migration Challenges and Solutions | |
| Debajit discussed the complexities of database migration, particularly the challenges between different database engines and the benefits of homogeneous migrations. He introduced the concept of replication instance in AWS's Database Migration Service (DMS) and demonstrated how to create a source SQL Server and a target RDS PostgreSQL using the AWS Management Console. He also discussed the process of setting up a server, including the creation of a VPC, launching a server on the public subnet, and configuring the security group. Debajit highlighted the limitations of the current system, suggested improvements, and recommended the use of a good server in the R series for production environments. He emphasized the importance of defining a proper VPC and understanding the DMS, its schema conversions, and the complexity of migration. | |
| Database Migration From PostgreSQL to SQL Server | |
| Debajit discussed the creation of a PostgreSQL database and its migration to an SQL Server. He demonstrated logging into the newly created server and installing the necessary tools, including the SQL Server Management Studio and the AWS Schema Conversion Tool (SCT). Debajit explained the purpose of the SCT, which is to convert schemas between different database systems, and guided through the process of downloading and installing it. He also mentioned that they would create a sample database for the webinar workshop to use for the migration process. | |
| Database Creation and Migration Process | |
| Debajit demonstrated the process of creating a new database and table using a tool, emphasizing the importance of having a primary key in the source database for successful migration. He then showed how to insert data into the new table and advised that specific logins need to be created for the migration service to communicate with the SQL server. Debajit also touched on the concept of a source server and mentioned other tools like DMS and SCT, without going into detail. | |
| Creating New Login for SQL Server Authentication | |
| Debajit explained the process of creating a new login for a user in SQL Server, which is essential for the user to be authenticated as a trusted source by DMS. He detailed the two-stage security process, which involves creating authentication at both the instance and database levels. He emphasized the importance of these steps as a prerequisite for migrations and demonstrated how to navigate through the SQL Server configuration manager to set up the authentication. He also highlighted the need to restart the SQL Server for the changes to take effect. Debajit faced some issues with the authentication sequence using Dms and resolved them by checking permissions and security settings. After rectifying the missed details, he successfully logged into the SQL server using Dms and completed the sequel Server Configuration. | |
| AWS Schema Conversion Tool Demonstration | |
| Debajit demonstrated the process of setting up and using the AWS schema conversion tool for migrating a database from PostgreSQL to Microsoft SQL Server. He explained the steps to connect the tool to the source and target servers, and the importance of having the correct JDBC driver for the specific database. He also mentioned that while the tool currently only supports up to SQL Server 15, support for SQL Server 16 will be available soon. Debajit also showed how to use the tool's reporting features to identify any objects that cannot be migrated. | |
| Database Connection, Migration, and Assessment | |
| Debajit confirmed the successful connection of the DMS to the SQL server using JDBC and clarified the necessity of a database driver for communication with the database. He also discussed the complexities and importance of database migration, emphasizing the need for a migration assessment report and careful planning. Debajit demonstrated how to migrate a database schema using PostgreSQL's schema conversion tool, highlighting that the tool only converts the schema, not the actual data. | |
| Data Database Migration Service Demonstration | |
| Debajit explained the process of migrating data using the Data Database Migration Service (DMS), which is yet to be created. He emphasized the importance of choosing the appropriate instance size, ensuring high availability options, and using a private network for migration to ensure security and reliability. He demonstrated the creation of endpoints, starting with the source endpoint, and discussed the need to map the replication server and test its communication with the source server. He also highlighted the necessity of setting up a security group as a firewall for on-premise setups and the ongoing testing process. vivek expressed satisfaction with the clarification provided. | |
| Configuring SQL Server Database on AWS | |
| Debajit and vivek discussed configuring a SQL Server database on AWS. Debajit initially provided the server name but realized the IP address was required. After entering the private IP address, the issue was resolved. They then created a target endpoint on an RDS database instance, specifying the server name and enabling SSL mode for security. The discussion focused on properly configuring database connections in AWS environments. | |
| Database Migration and Replication Process | |
| Debajit and vivek discussed the process of database migration and replication. Debajit explained the steps involved, including creating endpoints, running tests, and performing a database migration task. They also discussed the options of migrating existing data or replicating ongoing changes, with vivek expressing concern about replicating data he didn't want to be included. Debajit assured him that he could control which data was migrated. They also discussed the creation of a new table for the migrated data and how to ensure the data from the source was synchronized with the target. | |
| Database System Demonstration and Compatibility | |
| Debajit demonstrated a feedback form and a database system, explaining how to add and track records. vivek expressed that while the system was helpful, it could be challenging in real-time scenarios due to connectivity issues. Debajit agreed and mentioned a workshop from AWS that could help. Ranjna asked about the compatibility of the Pg Admin tool with different operating systems, to which Debajit clarified that the tool is OS-dependent, but there's a common line tool for Linux. The team expressed satisfaction with the session and thanked Debajit for his time. | |