- Address Verification
- Automate with SQL Agent
- Basics of Dynamic Columns
- Converting CSV to XML
- Create an RSS Feed
- Create Website Thumbnails
- Custom Rest Provider
- Data Generator Test
- Data Generator to get Public Holidays
- Database Merge
- Dynamic Lookups
- Email Validatation
- RSS as a Datasource
- Script Table from Schema
- Send Dataset as Email
- Set Exchange Permissions
- Split Columns into Multiple Rows
- Tidy File Paths
- Transform Data
- Using Regular Expression in DSS
- Using VS External Tools to Run
- Value Store Service and Incremental Sync
Simego Database Merge
When we switched our database provider for our Helpdesk and Customer data from a AWS RDS SQL Express database to Microsoft SQL Azure, to help comply with the GDPR regulations.
We thought we would share how we used our own tools Data Sync and Ouvvi to build this data migration.
The migration involved merging two databases into one, remapping ID's where the row identifiers have changed and removing old data we no longer need to keep as per the GDPR regulations.
The migration process was going to run multiple times over a number of weeks as we developed the updated website and tested the new site and data migration. Therefore we needed the ability to easily re-run the migration from the live database and would also need to run the migration quickly on the go-live day.
To begin with we created the 'DB Migration' project in Ouvvi to hold all of projects required for each table to be migrated.
Each step in this project was set up to link a SQL table from the original Database to the new instance of the SQL table in the Azure SQL Database. In total we had 30 steps, equating to 30 tables to be migrated. Where we needed to apply some re-mapping we used the Data Sync Lookup functions to obtain the re-mapped ID's via a temporary re-mapping table.
We used Calculated Column Lookup functions to provide the remapping function via an intermediate mapping Table. This mapping table made the whole transformation quick and easy for example we could remap the User IDs simply with the LOOKUPB function.
LOOKUPB("ToUserID", "UserMap", WHEN("FromUserID", UserID))
This migration project took a few hours to set up just because of the number of tables we were required to sync and where we had to implement the re-mapping. But using the Connection Library meant we didn't need to keep adding connections to each project we could just drag and drop the required table onto the data source.
In Ouvvi we created a new Empty Data Sync project step for each Table that we planned to migrate over then one by one we opened the projects and built the mapping.
Where we had large tables of Data we ran these interactively in Data Sync for the initial data move and then converted the project to use Incremental Sync for the Ouvvi process so it was quick to run.
As with all migrations, the amount of data will effect the time it takes to run. The first time we ran the migration project it took just under an hour to run, subsequent runs were 2-3 minutes.
This meant that as soon as we were ready to fully migrate to Azure with one click of a button we would be ready to go and the Data Migration from Live to the new Azure SQL DB was completed in 1 min 22 seconds.