Upgrade advisor is one of the tools which helps to detect compatibility issues of the database code (SPs,Functions, View, Triggers) while Migrating from SQL Server 2000 to higher versions.
But with my experience this will not provide complete information .
If codebase is not in sync in various enviornments (Development,QA,UAT,Staging,Production), Database script need to be collected from these enviornments to the local system and compatibility issues can be checked.
I.
1. Install SQL Server 2008 with appropriate instance name in a test server.
2. Configure start up accounts for SQL Server, Agent, MSDTC (if required) and other services for SQL 2008
Note: If this start up account requires network access, proper share directory paths and access rights (read/write) need to be provided
3. Create an empty database in the new version of SQL server 2008/2005
Change database Compatibility to 100 (2008) /90 (2005)
4. Run Upgrade Advisor 2008/2005 on SQL 2000 version
5. Note both DTS errors and Database Engine analysis errors.
6. Correct errors reported in SQL 2000
7. Take entire database script in SQL 2000 with drop and create option selected for objects .
8. Run the database script on empty database ( Step 3 database)
9. Correct the code and Save the modified code in a file
10. Drop the empty Database ( step 3 ) in the new version of SQL Server
Following are the code issues encountered during migration
Note: Dynamic SQL Statements in the code are not checked in the following
steps. This may require manual checking or running the code .
Following tables are created for examples below
create table #tablea ( col1 int, amount numeric(10,2))
insert into #tablea values (1,10.5)
insert into #tablea values (1,40.8)
insert into #tablea values (2,84.2)
insert into #tablea values (4,100.6)
create index index_ix_col1 on #tablea (col1)
create table #tableb (col1 int, stringdesc varchar(10))
insert into #tableb values ( 1,'abcd')
insert into #tableb values ( 4,'pqrsi')
insert into #tableb values ( 8,'xyzq')
---------------------------------------------------------------------------------
1. BACKUP LOG WITH NO_LOG,TRUNCATE_ONLY statements
Resolution:
From BOL
None. The transaction log is automatically truncated when the database
is using the simple recovery model.
If you must remove the log backup chain from a database,
switch to the simple recovery model.
Remove this statement
---------------------------------------------------------------------------------
2. sysperfinfo returns a bigint value for the cntr_value column.
Modify applications that use sysperfinfo to ensure that they can handle
the bigint values of the cntr_value column.
Resolution:
If cntr_values are captured in to tables ( #temp, table varibales, actual tables)
or stored in declared variables for performance analysis,
declaration should be changed to bigint
---------------------------------------------------------------------------------
3. With some exceptions,table hints are supported in the
FROM clause of a query only when the hints are specified using the WITH keyword.
Resolution:
In SQL 2000
select col1
from #tablea a (index=index_ix_col1)
In SQL 2005 and above add with clause for table hints
select col1
from #tablea a with (index=index_ix_col1)
---------------------------------------------------------------------------------
4.Column aliases in the ORDER BY clause cannot be prefixed by the table alias.
In SQL 2000
select
case when a.col1 = 1 then a.amount else 0 end as colamount
from #tablea a
order by a.colamount
Resolution:
In SQL 2005 and above remove table alias and use column alias
select
case when a.col1 = 1 then a.amount else 0 end as colamount
from #tablea a
order by colamount
---------------------------------------------------------------------------------
Info and Resolution
5.In SQL Server 2000, SQL Server Agent can write logs to the file system for jobs
that are owned by members of the sysadmin fixed server role.
If the job owner is not a member of the sysadmin role
and if the proxy account is enabled,
SQL Server Agent can write logs to the file system by using the
credentials of the proxy account.
After you upgrade, jobs that are owned by users
who are not members of the sysadmin fixed server role
can no longer write logs to the file system.
Instead, these users can select the option to write
their logs to a table in the msdb database.
Members of the sysadmin role can still write log files to the file system.
---------------------------------------------------------------------------------
6. Each GROUP BY expression must contain at least one column that
is not an outer reference
In SQL 2000
if exists
(select 1 from #tablea a
where not exists (select 1 from #tableb b
where b.stringdesc='abcd' and a.col1=b.col1
group by b.stringdesc,a.col1)
)
print 'ok'
Resolution:
In SQL 2005 and above -- a.col1 changed to b.col1 in group by
if exists
(select 1 from #tablea a
where not exists (select 1 from #tableb b
where b.stringdesc='abcd' and a.col1=b.col1
group by b.stringdesc,b.col1)
)
print 'ok'
---------------------------------------------------------------------------------
7. Cannot create procedure '....' with a group number of 2 because a procedure
with the same name and a group number of 1 does not currently exist in the database.
Must execute CREATE PROCEDURE '....';1 first.
Resolution:
This may happen if the main SP ( without verion no or with ;1 ) failed to create the Original SP. If sp;1 is not used in SQL 2000 , create a dummy SP with create procedure SP;1 and then create different versions (;2,;3 etc)
---------------------------------------------------------------------------------
8. ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
Example:
select a.col1, a.amount as col2alias
from #tablea a
where a.col1 > 0
union all
select a.col1, a.amount
from #tablea a
where a.col1 < 0
order by a.col1 desc, a.amount
Note: col2 is aliased as col2alias which is part of the order by clause . Column aliases should be used in order by, instead table alias.column name . Also avoid table alias in the order by clause. Above statement will work in all versions of SQL Server 2000,2005,2008,better to change to the following
Resolution:
select a.col1, a.amount as col2alias
from #tablea a
where a.col1 > 0
union all
select a.col1, a.amount
from #tablea a
where a.col1 < 0
order by a.col1 desc, col2alias
---------------------------------------------------------------------------------
9. Setuser failed because of one of the following reasons: the database principal 'ownername' does not exist,its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.
Resolution:
If there are objects (tables, stored procedures etc) created with ownerid in sql 2000,corresponding schema need to be created in sql 2005 and above
---------------------------------------------------------------------------------
10. The specified schema name "ownername" either does not exist or you do not have permission to use it.
Resolution:
In SQL 2005 and above object owner names are replaced with schema.
For object owners other than dbo, create corresponding schema and create objects in the schema .
Provide proper access to the users through schema.
---------------------------------------------------------------------------------
11.
In SQL Server 2005, syslockinfo and sp_lock may return unexpected values,
and may return multiple rows where previous versions of syslockinfo and sp_lock
returned at most two rows. Also, in order to access information from syslockinfo
or execute sp_lock in SQL Server 2005, the user must have VIEW SERVER STATE permission on the server.
Resolution:
If sp_lock is used for monitoring purpose in SP or job change it accordingly to accomdate new rows
---------------------------------------------------------------------------------
12. The query uses non-ANSI Outer join Operators ( "*=" or "=*" )
Resolution
Change query to left outer join ( =*) or right outer jon ( *=)
---------------------------------------------------------------------------------
drop #tablea
drop #tableb
---------------------------------------------------------------------------------
II.
SQL Server 2005 onwards password is case sensitive irrespective of collation settings . If connection strings from web servers are not in approriate case , logins will fail . Change connection strings accordingly
III.
If bcp/bulk insert uses format files in SQL 2000 , these are not compatible in SQL 2008 . Regenerate the format files before using bulk insert/bcp .
Tuesday, November 17, 2009
Subscribe to:
Posts (Atom)