SQL Server 2000 to 2005, 2008 Migration Tips for Database Code from the field

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 .

Find Members of an NT Group

-- Find Members of DOMAIN belonging to a user group 'UserGroup'

exec master..xp_logininfo 'DOMAIN\UserGroup','members'
exec master..xp_logininfo 'DOMAIN\Administrators','members'

user connections ,Logical Connections, Threads, Logins , Logouts

select *
from sys.dm_os_performance_counters a
where a.object_name = 'SQLServer:General Statistics'
and counter_name in ('user connections ','Logical Connections','Logins/sec','Logouts/sec')

--Number of threads used with MSSQL 2005

select count(*) as 'Number of threads'
from sys.dm_os_waiting_tasks
where wait_type <> 'THREADPOOL';

Statements having High CPU usage on execution count

-- High CPU queries -- on execution count SELECT TOP 50 total_worker_time/execution_count AS avg_cpu_cost, execution_count,db_name(depamain.dbid) as dbname,object_name(depamain.objectid,depamain.dbid) as objname, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats OUTER APPLY ( select dbid,objectid from ( select attribute,cast(value as int) as attvalue from sys.dm_exec_plan_attributes(plan_handle) where attribute in ('dbid','objectid') ) as depa PIVOT ( MAX(depa.attvalue) FOR depa.attribute IN ("dbid", "objectid") ) AS depapvt ) as depamain ORDER BY execution_count DESC;

Statements having High CPU Usage

select top 50 total_worker_time/execution_count as avg_cpu_cost, execution_count,db_name(depamain.dbid) as dbname,object_name(depamain.objectid,depamain.dbid) as objname,
(selecct substriung(text, statement_start_offset/2 + 1,
(case when statement_end_offset = -1 then len(covert(nvarchar(max), text)) * 2 else statement_end_offset nd - statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
outer apply
( select dbid,objectid from
( select attribute,cast(value as int) as attvalue
from sys.dm_exec_plan_attributes(plan_handle)
where attribute in ('dbid','objectid')
) as depa
pivot ( max(depa.attvalue) for depa.attribute
in ("dbid", "objectid") ) as depapvt ) as depamain
where execution_count > 4
order by [avg_cpu_cost] desc;

Drop column after checking regular constraints

-- use database
go
-- SQL 2005 and above
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_lib_drop_column]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_lib_drop_column]
GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
 /* exec sp_lib_drop_column 'constratintcheckhead','accountid','l' exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
-- testing
create table constratintcheckhead ( accountid int not null primary key ,clientid int not null ) create table constraintcheckdetails ( accountid int not null ,notesid int not null primary key (accountid,notesid) )
alter table constratintcheckhead add constraint DF_accountid default 0 for accountid
alter table constratintcheckhead add constraint CK_accountid check (accountid >= 0 )
alter table constratintcheckhead add constraint UQ_account Unique (accountid,clientid ) alter table constraintcheckdetails add constraint FK_constraintcheckdetails_constratintcheckhead foreign key (accountid) references constratintcheckhead (accountid)
create index IX_constratintcheckhead_clientid on constratintcheckhead(clientid) with fillfactor = 90
create index IX_constratintcheckhead_clientid_accountid on constratintcheckhead(clientid,accountid) with fillfactor = 90
drop table constratintcheckhead
drop table constraintcheckdetails
Description :
Lists/Drops Constartints and indexes for a column and drops the column Following constraints are checked only Default,Check, Foreign Key, Primary Key,Unique Key Indexes : Clustered or Non Clustered This will not check column is replicated or part of full text etc Only SQL 2005 and above WARNING: Once constaints are dropped , column will be dropped Assumes there are not more than 10 columns in an index

Usage : exec sp_lib_drop_column 'constratintcheckhead','accountid','l'
exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
Input Parameters : @tablename , @columnname ,@flag char(1) @flag -- 'l' -- list constraints only , 'd' -- drop column , 'b' -- list constraints and drop column
Output Parameters : None
Return Value : None
Record Set : None
 */

create procedure dbo.sp_lib_drop_column @tablename varchar(150), @columnname varchar(80) ,@flag char(1) = 'l'
as
set nocount on

if @flag not in ('l','d','b')
return
declare @slno int ,@maxslno int,@vchsql varchar(4000),@ci_name varchar(150),@ci_type char(1)
create table #temp_constraints( tablename varchar(150),type_desc varchar(150),ci_name varchar(150),columnname varchar(80),ci_type char(1), parentablename varchar(150),parentcolumname varchar(80),slno int identity(1,1) primary key)
declare @numbers table ( n int primary key )
insert into @numbers values (1)
insert into @numbers values (2)
insert into @numbers values (3)
insert into @numbers values (4) insert
into @numbers values (5)
insert into @numbers values (6)
insert into @numbers values (7)
insert into @numbers values (8)
insert into @numbers values (9)
insert into @numbers values (10)

 insert into #temp_constraints (tablename,type_desc,ci_name,columnname,ci_type,parentablename,parentcolumname )select object_name(parent_object_id) as tablename,type_desc,name as ci_name,
col_name(parent_object_id,parent_column_id) as columnname,'N' as ci_type ,'' as parentablename,'' as parentcolumname
from sys.default_constraints
where object_name(parent_object_id) = @tablename
and col_name(parent_object_id,parent_column_id) = @columnname
union all
select object_name(parent_object_id) as tablename,type_desc,name as ci_name,col_name(parent_object_id,parent_column_id) as columnname, 'N' as ci_type ,'' as parentablename,'' as parentcolumname
from sys.check_constraints
where object_name(parent_object_id) = @tablename and col_name(parent_object_id,parent_column_id) = @columnname
union all
select object_name( parent_object_id) as tablename, 'FOREIGN KEY' as type_desc, object_name(constraint_object_id) as ci_name,col_name(parent_object_id,parent_column_id) as columnname,'N' as ci_type,object_name( referenced_object_id) as parentablename, col_name(referenced_object_id,referenced_column_id) as parentcolumname
from sys.foreign_key_columns
where object_name( referenced_object_id) = @tablename
and col_name(referenced_object_id,referenced_column_id) = @columnname
union all
select object_name(i.object_id) as tablename, case when o.type = 'PK' then 'PRIMARY KEY' when o.type = 'UQ' then 'UNIQUE'else 'INDEX ' end as type_desc, i.name as ci_name,t.index_column_name as columnname, case when o.type in ('PK','UQ') then 'N' else 'I' end as ci_type,'' as parentablename, '' as parentcolumname
from sys.indexes i
left outer join sys.objects o
on o.parent_object_id = i.object_id
and i.name = o.name
cross apply
( select index_col(object_name(i.object_id),index_id,n) as index_column_name from @numbers where index_col(object_name(i.object_id),index_id,n) = @columnname ) t where object_name(i.object_id) = @tablename
----- if @flag in ('l','b')
select * from #temp_constraints order by slno
 if @flag = 'l'
return
set @slno = 0
select @maxslno = max(slno) from #temp_constraints
while @slno < @maxslno
begin 
    set @slno = @slno + 1
    select @tablename = tablename, @ci_name = ci_name, @ci_type = ci_type
    from   #temp_constraints where slno = @slno
   if @ci_type = 'N' set @vchsql = 'alter table ' + @tablename + ' drop constraint '   + @ci_name else if @ci_type = 'I' set @vchsql = 'drop index ' + @tablename + '.' + @ci_name
-- print @vchsql
 exec (@vchsql)
end
drop table #temp_constraints
return