Data migration

Question

Data migration

Gopal-49563

Ten Centuries

Points: 1209
More actions
May 24, 2004 at 9:55 am

#82686

I would like to invite suggestions and brainstorming session for data migration plan whenever there is a Schema change to preserve previous data.
At the same time one has to take care of previous version of your schema in the system. For example if there are versions 3.1 3.2 3.3 already in production and now you are making release for 3.4 which also include schema change.
The new plan for data migration should take care of migrating data on 3.1 to 3.4 or 3.2 to 3.4 or 3.3 to 3.4.
I am sure every DBA requires this at some point of time. Additional suggestions are most welcome to improve this requirement.

Thanks
GOPAL

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mark baekdal Newbie Points: 1 More actions · Answer 1

We use DB Ghost and Visual SourceSafe to manage all our schemas and static data, we also have a massive data migration requirement as the legacy systems are bought onto the new systems. As the schema changes the migration procedures are modified to suit. Because all the schemas are in SourceSafe which is labeled daily, when things change, our daily build process notifies us of any potential problems with importing any data and these are quickly rectified. This means we are ready to release at any time.

NTL database consultant.

vilas Relan Newbie Points: 1 More actions · Answer 2

Hi All,

I want to migrate MS Access database to SQL server 2000 database. Please provide the script for the same.

I have a column in MS Access of datatype autonumber. It is not getting converted to identity column in SQL server.

I want to create a utility for converting the MS Access database to SQL server 2000 database.

It is very urgent. Your help is appericiated.

Thanks & Regards,

Vilas Relan

John Jakob SSCommitted Points: 1720 More actions · Answer 3

>> For example if there are versions 3.1 3.2 3.3 already in production

Excuse my total ignorance, but why have multiple versions in Production at once?

I'd prefer a simpler approach, with only a single version in production at a time. For example, version 3.3 in production, with version 3.4 in the wings--waiting to go to production.

If possible, only address one "backward compatibility" DATA issue per release. And if the new release is going to have DATA compatibility issues AND significant new schema changes, then consider breaking that release up into two separate releases -- e.g., ver 3.4 and ver. 3.5.

As they say, the "KISS" model is the way to go.

- john

John Jakob SSCommitted Points: 1720 More actions · Answer 4

>> I want to migrate MS Access database to SQL server 2000 database. Please provide the script for the same.

Hey Vilas,

You totally hijacked the original poster's thread -- that's a really uncool thing in some forums!

But since you asked the question -- and since you want a "script", I'll give you a hint:

You'll probably need to create a linked-server to the MS Access database, and then create the target table in SQL using "CREATE TABLE...". Then do something like this to copy the data to the SQL table:

SET IDENTITY_INSERT [SQLTableName] ON

GO

INSERT INTO [SQLTableName] ([Field1], [Field2], ...)

SELECT [Field1], [Field2], ...

FROM [LinkedServerName].DBName.dbo.[MSAccessTableName]

SET IDENTITY_INSERT [SQLTableName] OFF

GO

Notice you need to set the IDENTITY_INSERT property for the table on, and specify the complete field list in the INSERT statement in both clauses.

Good luck.

- john

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 5

John,

It sounds like you are working in an enterprise shop (or equivalent) where there is only one instance of the schema in production. Consider the case where a product development team needs to support not just the current release but also several previous releases. The customers will decide when to update their product, and may or may not choose to skip over entire releases. In this case, there needs to be a well-thought-out upgrade scenario or mass chaos occurs, typically resulting in loss of customers.

One approach is to require the customers to apply all intermediate patches in sequence that they were released. This is a painful, time consuming process but generally results in the most stable environment at the end of the process.

There are several tools on the market (I like Adept SQLDiff) that will generate patch scripts to take you directly from version x to version y. However, any semantic changes in the DB may be lost depending on how the changes were implemented. For example, suppose release X changed all "blue" values to "pink", release Y changed "pink" to "green", and release Z changed half of the "green" values to "purple" if the deployment date was Sunday. It is very unlikely that a patch script will take the Db directly from X to Z without scrambling the data.

Does this make sense?

Wayne

Gopal-49563 Ten Centuries Points: 1209 More actions · Answer 6

Wayne

Its been a long ago when i posted this thread. Anyway thanks for understanding the problem right. I will look into SQLDiff.

But temporarily I designed my own schema to take care of future releases and jumping from any release to any higher release. Though I had to put some assumptions like baseline release was fixed, which means at somepoint of time all the production sites will have at least the baseline version (say 3.3).

I will not be able to illustrate all the cases and solutions as it is a long procedure to describe in short forum like this. I just know that it is working excellent.

I appreciate all you guys.

Gopal