changing compatability level from80(2000) to 90(2005)

Hi

I am planning to migrate my database from sql 2000 to sql 2005

I need to know when i change the compatibility from 80 to 90

1) What are the things which i need to look out for. If i have changed every thing as per upgrade advisor will i have to make or anticipate further changes.

2) Please tell me whether if i dont use 90 compatability level will i miss out anything?

What am i missing out ?

Will it be possible to do online index re-build with compatability level set to 80.

Is there a list of new benefits/changes i will be getting when i move to sql 2005 compatability set to 90.

Please do reply .Awaiting your answers,....

Thanks in advance.

The differences are all here (table lists them all about half way down the page) - http://msdn2.microsoft.com/en-us/library/ms178653.aspx
Many other things to watch for are here - http://msdn2.microsoft.com/en-us/library/ms143179.aspx

Online indexing operations are supported in 80 compatibility (on SQL Server 2005 Enterprise as that is the only version that supports them).

|||

Thank you for your reply

I want to know whether i wont be able to use any feature of Management studio or any other feature if i am not changing to compatability level 90

Thanks in advance

|||

Couple that I can think of

You can't use Database Diagrams

You can't use custom reports (on the summary tab) - http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/22/ssms-custom-reports-backwards-compatibility-or-lack-thereof.aspx

Change Self reference values in a data flow task

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi

Hi Gopi,

Did you ever resolve this? If so, how? I am facing the same issue.

Thanks,

Dave

|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.
|||

Hi Dave,

Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records

Cheers,

Gopi

Change Self reference values in a data flow task

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi

Hi Gopi,

Did you ever resolve this? If so, how? I am facing the same issue.

Thanks,

Dave|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.|||

Hi Dave,

Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records

Cheers,

Gopi