February 5, 2020 at 10:11 pm
Hello,
I will soon find myself working on a data migration in which the ERP data of one company will need to be added to ours. Both companies use the same ERP, which should help simplify things a bit, though there will still be some considerable effort involved in mapping data between the two systems.
I'm hoping to get some advice from the wise folks here as to the cleanest and easiest methods of actually implementing a migration like this through SQL Server. I'm envisioning lots of mapping tables and insert scripts and careful accounting for foreign keys, but are there tools you make use of that make this process less painful?
February 5, 2020 at 10:33 pm
if both use the same ERP system maybe the vendor has a tool that will "merge" both into a single instance - have you contacted them about it?
And not all systems will allow you to do that as a database level - in some cases you loose support from the vendor so that needs to be taken in consideration.
if doing it through SQL is "acceptable" by the vendor then you will need to do the analysis and see how the keys are, how one company mapped particular cases/rules and come up with mapping table s(or straight hardcoded values within the migration code)
you will need to separate the logic with regards to "reference tables" vs transaction tables - reference tables you do not wish to create duplicated values and you should map from one system to the other - transactions you would normally migrate as is (changing keys where needed) or if history not important leave some of it behind - or aggregate at a year level.
From previous migrations that I did we used a stored proc per destination table (or in some cases 1 proc per 2 or 3 directly related tables as client details and client addresses)
and then testing. and more testing.. and retest.
February 6, 2020 at 10:56 am
there are many ways to complete a migration of data and there are plenty of tools to help you out, that is if you have a budget for such tools.
i have created my own set of tools for completing this task and they work reasonably well. Sorry i can't share them. i use a 3 stage process, extract from source, insert into transition database, load into Target.
Each stage has its own purpose and requires lots and lots of investigation, i have merged many systems where the source and destination used the same software and also where each used different applications.
before you embark on a migration project first you need to agree what is in scope for migration, 2 create a an iterative plan consisting of N rounds of migration and testing (extract, transform, load, test, review, fix), 3 organise resource. I think the key to success here is, understanding the data in both systems. As well as creating a specification, mappings etc you should also consider validating the data before you merge this into your target. just because each company use the same software does not mean they use it in the same way.
***The first step is always the hardest *******
February 6, 2020 at 5:38 pm
Thank you Federico and SGT for the responses.
Yeah, the ERP vendor does provide a solution that involves lots of Excel spreadsheets and macros, but the business is hoping to save time and costs by having this done by the IT department in an "automated" fashion. Just hoping there might be some known tools for making some of this easier, but there's definitely no getting around the analysis of data and the mapping that will need to be done.
One question I have from the T-SQL standpoint is with regard to the handling of foreign keys. For example, if we're bringing in their customer records, those will come in with different auto-incrementing PKs than they had in their original system. When we bring in their orders, which will have foreign key references to their customers, those customer FKs will need to be remapped to point to the recently added customer records. What I'm envisioning for this is to have a table in place that has the customers with their original PKs, then as we add customer records, we note the new PK it has been assigned as a different column in that table. Then, when we add the orders, we set the FK to the new customer PK using that table as a lookup. Does this seem like an ideal/viable approach, or are their better methods of achieving this?
February 6, 2020 at 6:49 pm
you will need mapping tables for sure - with source ids and new id's
how many will depend on your system.
so initial migration step is to do the "base" tables e.g. customer, suppliers, material ans so forth and add these to their corresponding mapping tables.
then you do transaction style tables - and use the lookup tables to get their new id's .
You will have the added difficulty of ensuring that you don't add duplicated customers and so on - so a merge exercise should be done as part of the initial migration of the base tables.
what I did on some of my migrations was a setup as follows
DB 1 - transient - holding the source data that we were migrating
DB 2 - persistent - holding all code done as part of the migration as well as support tables. e.g. mapping tables, product rules and so on
DB 3 - Finaldestination -- the target database - on our case while testing we would copy down the prod db to our migration server for the duration of the development (3 years in total - significantly big financial system so 6 migrations in total) - db would be refreshed weekly (both source and destination db's)
each developers would have their own copy of db2 and db3 for their testing and we would have a "trial run" version to use after each development cycle had been tested.
February 10, 2020 at 8:48 pm
I would check with the Vendor that you can do can update the database your self otherwise as frederico_fonseca explained you may be in breach of contract therefore you will invalidate your support. It could all come back to bite you in the bum.
when i comes to Primary and foreign keys from my legacy system i use a legacy id on each table i'm migrating into to allow the target DB create data integrity.
I will create you a basic example on the approach i would use later in the week when i have a little more time.
***The first step is always the hardest *******
February 29, 2020 at 8:23 pm
Sorry it took so long for me to get back to you, been a tad busy, here is the most basic example of a migration from a source system into an identical target system.
Create source system
Create database testmig1
use testmig1
create table order_details (lineid int identity(1,1),orderid int,quantity int, product_code varchar(100), description varchar(200))
insert into order_details select 1,12,'PDC101','PDC Machine Blue' union all
select 1,9,'XXNL1','Some other Randam stuff' union all
select 1,12,'ABC111','Abacus first edition' union all
select 2,12,'PDC101','PDC Machine Blue' union all
select 2,12,'DEF123','Mouse red' union all
select 3,12,'LENOV','Lenovo 14inch ' union all
select 4,12,'KITK','Kitkat ' union all
select 5,12,'IPH1','IPHONE' union all
select 6,12,'PEN1','Pen general use Blue'
Create target system
Create database testmig2
use testmig2
Create table customers (id int identity(1,1),Cust_name varchar(100))
insert into customers
select 'Mgnifico the Customer' union all
select 'Freds Burgers' union all
select 'The Chippy' union all
select 'Dingo dollar Diner'
create table orders (id int identity(1,1),cust_id int,orderdate datetime)
insert into orders select 1,GETDATE()-10 union all
select 1,GETDATE()-20 union all
select 1,GETDATE()-30 union all
select 2,GETDATE()-17 union all
select 2,GETDATE()-28 union all
select 2,GETDATE()-39
create table order_details (lineid int identity(1,1),orderid int,quantity int, product_code varchar(100), description varchar(200))
insert into order_details select 1,12,'XN110','Chip shop chip' union all
select 1,9,'NL1','Some other Randam stuff' union all
select 2,12,'P01','PDC Machine Blue' union all
select 3,12,'LOV','Some random rubish ' union all
select 4,12,'KK','Knot Knot ' union all
select 5,12,'IP1','IP1 product ' union all
select 6,12,'PN1','Chips and stuff '
Migrate data from Source into target
--before i can migrate the data first i need to create a legacy identifier to refer back to the PK from my source table.
use testmig2
Go
Alter table customers add legacy_id int
Alter table orders add legacy_id int
Alter table order_details add legacy_id int
we will start with Customers as this is the primary table, an order cant exist without a customer,
insert into customers (Cust_name,legacy_id)
select cust_name,id from testmig1..customers
--notice how i deal with the FK Cust_id, i know i didnt create any FK constraints but im sure you get the jist
insert into orders (cust_id,orderdate,legacy_id)
select c.id,o.orderdate,o.id from testmig1..orders o
join customers c on o.cust_id=c.legacy_id
last table
insert into order_details (orderid,quantity,product_code,description,legacy_id)
select o.id,od.quantity,od.product_code,od.description,od.lineid from testmig1..order_details od
join orders o on od.orderid=o.legacy_id
Now lets run a quick check of the data to make sure the data has been successfully migrated
select * from testmig1..customers c
join testmig1..orders o on o.cust_id=c.id
join testmig1..order_details od on od.orderid=o.id
order by 1
select * from testmig2..customers c
join testmig2..orders o on o.cust_id=c.id
join testmig2..order_details od on od.orderid=o.id
where c.legacy_id is not null
order by 1
Hope this helps, depending on the type of migration i usually have a staging database, i extract from source into a staging database, i run my data validation in the staging database before I load data into the target.
***The first step is always the hardest *******
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply