SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Migration Options


Data Migration Options

Author
Message
don_querry
don_querry
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 29
We have SQL Server databases located on different servers for our hosted web application.

Each database can house multiple 'customers' along with their data.

We have the need to be able to 'move' data for one or many customers to from one server to another server.

Moving the data from one server to another will require copying a 'slice' of data for a selected customer to a destination SQL Server that will already house other customers.

Upon moving the data, primary and foreign keys from the source data cannot be directly copied to the destination server, since these most likely will be already generated for another customer on the destination server.

We have been looking into several approaches to accomplishing what we want including:

1) Writing a Windows Application that taked in user selections for source and destination databases and connectionstrings. Then reading the data from the source database into a dataset or something and sending the data to the destination database in chunks.

2) Investigating using SSIS to write a package that will be able to migrate a customers data from the source to destination, also handling the keys? Is this possible?

3) Having linked servers on each of our database servers and using cross database querys to 'Insert Select' the data from the source to the destination. Can the linked server name be set in sql dynamically so as to accomidate a users selection for server.

I thought maybe some of the SQL experts here have already worked through this type of situation and appreciate any recommendations.
Thanks in advance for your suggestions
tim.kendall
tim.kendall
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 11
Actually I've got a similar challenge. I couldn't see any replies here. How did you get on?
Glen Cooper
Glen Cooper
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 287
I worked on a similar problem where I had to merge 17 departments into a master database, where each department shared a common database schema.

The merges took place sequentially, and each merge simply appended its data to the growing master store.

One each merge, the primary and foreign keys are re-caculated as the script runs (using a junk variable on each table to save its old key values so they can be used to compute the new values during the merge).

I've done lots of this stuff, if that helps.

Glen

R Glen Cooper
Mike01
Mike01
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3875 Visits: 1533
I have the same issue and was wondering what you came up with for a sooution

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
jodacey
jodacey
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 47
I am currently in the process of migrating data from one server / database to another, and here's a tried and tested method that I have used for certain tables: NEGATIVE IDENTITIES
NOTE:I do all transformations in a 'StageDB' database, which is a restored copy of the Source database (both Schema and Data)- so I'm not doing any updates in the source database at all OR any inserts directly into the destination database until the data is in its final state)
Here are the steps that you need to take:
1) Drop all Foreign Key Constraints that relate to StageDB.dbo.Customers table
2) Delete all records from the StageDB.dbo.Customers table
3) Make the following changes to StageDB.dbo.Customers table:
- Add additional Integer field - 'PreviousCustomerID'
- set the CustomerID Primary Key Identity field to Negative Integer as follows:
CustomerID INT NOT NULL IDENTITY (-1, -1)
4) Insert data from SourceDB.dbo.Customers to StageDB.dbo.StageCustomers as follows:
INSERT INTO StageDB.dbo.Customers
(
PreviousCustomerID
--add all other Customer table fields here except the CustomerID
)
SELECT
CustomerID
--select all other Customer table fields
FROM
SourceDB.dbo.Customers;
4) The StageDBdbo.Customers table is now populated with Negative Identity Integers on the Primary Key field CustomerID, with the additional field 'PreviousCustomerID' which will preserve the original CustomerID Value (as suggested in previous post)
We can use this to update any tables related to the Customers table
5) Update any tables related to the Customers table in the StageDB database as follows -
UPDATE RelatedCustomerTable
SET CustomerID=cust.CustomerID
FROM StageDB.dbo.Customers as cust
WHERE cust.CustomerID=RelatedCustomerTable.PreviousCustomerID
6) Repeat Step 5 for any table that has the CustomerID field, ensuring that any Foreign Key Constraints are dropped before updating
7) Once all updates are complete - Add any Foreign Key Constraints that were originally dropped in the StageDB database. This will act as a data quality check to ensure that all IDs have been updated to match the new PKs
8) Add the new 'PreviousCustomerID' field on the DestinationDB.dbo.Customers table
9) For the final insert from the StageDB database into the DestinationDB database, you must drop the Identity from the Primary Key on the DestinationDB.dbo.Customers table so that the negative Identities are not overwritten
10) Insert data directly from the StageDB.dbo.Customers into DestinationDB.dbo.Customers
11) Add the Identity back onto the Primary Key of Customers Table
12) Insert the data from all other tables from StageDB database
*****The process is now complete*****

Using this method, you can ensure that there will never be any clash in identities and it is easy to identify which data is from migration and which data is not
TIP 1: If this data migration is not a one off procedure, it is best to wrap the stages in a transaction AND/OR put the steps in an SSIS Package - either way, ensure that you implement effective error handling and rollback routines
TIP 2: Again - if this data migration is not a one off procedure, by using the same StageDB.dbo.Customers table each time, by using the Delete statement, rather than Truncate - your maximum identity value for CustomerID will be retained so you won't be duplicating negative identities in the DestinationDB

I hope this helps,

Jo
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search