Migrating from one db to other

Question

Migrating from one db to other

a2zwd

SSCrazy

Points: 2353
More actions
September 24, 2011 at 11:39 am

#257731

Hi,
I am working on a travel booking application. Currently we are using third party application where the database is sql server 2005.
But, our company decided to run down the application and move to new application(bought recently).
New applicaton used sql server 2008.
We are told to migrate data from old application and feed to new application to preserve the old data and also pull data to our local datawarehouse. And also we need to bring data from new application as well to maintain BI system.
What are the factors we should concern to do this? How do we design the model to support old system data and also new system data and maintain the new system?
What should be the middle tier database structure/model? Any suggestions would be highly appreciated.
thanks

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

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

raman.stephen SSC Enthusiast Points: 164 More actions · Answer 1

Hi,

Our company made the jump from SQL Server 2005 to SQL Server 2008 R2. The move was a success for the most part, but the proper procedures needed to be taken. We have multiple server environments (Development, Test, Staging, and Production). During preparation, we created a list of changes. I don't recall any large deprecations (e.g. change or removal of system functions). But you will need to note any, and check if your stored procedures use any of these.

I found that we did not have to change anything in SSIS or SSRS (if you are using).

I may be wrong, but I believe schemas are on by default in 2008.

There were a few stored procedures where we had (example):

SELECT * FROM tblTest

We needed to change it to dbo.tblTest (or add a schema other than dbo and use it).

In best practices, schemas are the right way to go if you are not already using.

In common practice of any migraton, you must follow the acronym TTAMT (Test, Test, and More Test).

We also noticed that DataBase Compatability was retained for SQL Server 2005 after the migration. From what I have read, this will cause future problems when migrating to the upcoming SQL Denali.

Steve Jones - SSC Editor SSC Guru Points: 734649 More actions · Answer 2

The structure of the new system is set, correct? If you have purchased it, then you have a schema and objects.

Migrating the data from an old system or a warehouse involves you mapping the columns as appropriate. There's no general way to tell you how to do this. You have to find out the meaning of each column and then map the appropriate ones. That may involve combining or splitting data as you move between systems.

Perry Whittle SSC Guru Points: 233949 More actions · Answer 3

Generally your vendor should be able to provide the mappings between the legacy database schema and the new database schema.

IMHO, If they can't then one has to wonder what their product and support are going to be worth

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

a2zwd SSCrazy Points: 2353 More actions · Answer 4

Hi all,

Thanks for the replies. We dont know the schema of the new system yet.

What I mean is how do we model the extract, staging bla bla.

What could be the first step and the structure of staging...

Steve Jones - SSC Editor SSC Guru Points: 734649 More actions · Answer 5

What do you mean by modeling the extract and staging?

Staging is setting up tables that receive the data intermediately. You may or may not do this, but these tables usually have the new schema.

There's no way to model the extract without doing the extract. It may involve combining or splitting columns, or moving data among many tables, or combining it. Without a new schema, there's nothing to model.