Migrate SQL 2000 to SQL 2005

1. Database
Make database backups the first
1.1 Direct upgrade
 --Database instance of SQL Server 2000 with SP3 or later can be upgraded directly to SQL 2005.
 --Using upgrade advisor to prepare for upgrades. it will suggest what should be done before and after upgrade. If you are scanning instances of Reporting Services, you must install Upgrade Advisor on the report server.otherwise, it can be installed anywhere that can run it.
1.2 Co-existence upgrade
 -- install a separate SQL Server 2005 box and restore the database backup to the new box. This requires more work to do, but it's safer since the original environment is still there.

2. Logins and Users

http://support.microsoft.com/kb/246133/

1. Run the following script on the source SQL Server.
   
   

   USE master 
   GO 
   IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL 
   DROP PROCEDURE sp_hexadecimal 
   GO 
   CREATE PROCEDURE sp_hexadecimal 
   @binvalue varbinary(256), 
   @hexvalue varchar(256) OUTPUT 
   AS 
   DECLARE @charvalue varchar(256) 
   DECLARE @i int 
   DECLARE @length int 
   DECLARE @hexstring char(16) 
   SELECT @charvalue = '0x' 
   SELECT @i = 1 
   SELECT @length = DATALENGTH (@binvalue) 
   SELECT @hexstring = '0123456789ABCDEF' 
   WHILE (@i <= @length) 
   BEGIN 
   DECLARE @tempint int 
   DECLARE @firstint int 
   DECLARE @secondint int 
   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) 
   SELECT @firstint = FLOOR(@tempint/16) 
   SELECT @secondint = @tempint - (@firstint*16) 
   SELECT @charvalue = @charvalue + 
   SUBSTRING(@hexstring, @firstint+1, 1) + 
   SUBSTRING(@hexstring, @secondint+1, 1) 
   SELECT @i = @i + 1 
   END 
   SELECT @hexvalue = @charvalue 
   GO 
   
   IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL 
   DROP PROCEDURE sp_help_revlogin_2000_to_2005 
   GO 
   CREATE PROCEDURE sp_help_revlogin_2000_to_2005 
   
   @login_name sysname = NULL, 
   @include_db bit = 0, 
   @include_role bit = 0 
   
   AS 
   DECLARE @name sysname 
   DECLARE @xstatus int 
   DECLARE @binpwd varbinary (256) 
   DECLARE @dfltdb varchar (256) 
   DECLARE @txtpwd sysname 
   DECLARE @tmpstr varchar (256) 
   DECLARE @SID_varbinary varbinary(85) 
   DECLARE @SID_string varchar(256) 
   
   IF (@login_name IS NULL) 
   DECLARE login_curs CURSOR STATIC FOR 
   SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') 
   FROM master.dbo.sysxlogins 
   WHERE srvid IS NULL AND 
   [name] <> 'sa' 
   ELSE 
   DECLARE login_curs CURSOR FOR 
   SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') 
   FROM master.dbo.sysxlogins 
   WHERE srvid IS NULL AND 
   [name] = @login_name 
   
   OPEN login_curs 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   IF (@@fetch_status = -1) 
   BEGIN 
   PRINT 'No login(s) found.' 
   CLOSE login_curs 
   DEALLOCATE login_curs 
   RETURN -1 
   END 
   
   SET @tmpstr = '/* sp_help_revlogin script ' 
   PRINT @tmpstr 
   SET @tmpstr = '** Generated ' 
   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' 
   PRINT @tmpstr 
   PRINT '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** CREATE LOGINS *****/' 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   IF (@xstatus & 4) = 4 
   BEGIN -- NT authenticated account/group 
   IF (@xstatus & 1) = 1 
   BEGIN -- NT login is denied access 
   SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + '''' 
   PRINT @tmpstr 
   END 
   ELSE 
   BEGIN -- NT login has access 
   SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')' 
   PRINT @tmpstr 
   SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS' 
   PRINT @tmpstr 
   END 
   END 
   ELSE 
   BEGIN -- SQL Server authentication 
   EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT 
   
   IF (@binpwd IS NOT NULL) 
   BEGIN -- Non-null password 
   EXEC sp_hexadecimal @binpwd, @txtpwd OUT 
   SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED' 
   END 
   ELSE 
   BEGIN -- Null password 
   SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=''''' 
   END 
   
   SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string 
   PRINT @tmpstr 
   END 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   
   IF @include_db = 1 
   BEGIN 
   PRINT '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** SET DEFAULT DATABASES *****/' 
   
   FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']' 
   PRINT @tmpstr 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   END 
   
   IF @include_role = 1 
   BEGIN 
   PRINT '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** SET SERVER ROLES *****/' 
   
   FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   IF @xstatus &16 = 16 -- sysadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &32 = 32 -- securityadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &64 = 64 -- serveradmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &128 = 128 -- setupadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &256 = 256 --processadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &512 = 512 -- diskadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &1024 = 1024 -- dbcreator 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &4096 = 4096 -- bulkadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin''' 
   PRINT @tmpstr 
   END 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   END 
   
   CLOSE login_curs 
   DEALLOCATE login_curs 
   RETURN 0 
   GO
   
   exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
   GO

2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.

3. Discontinued functionality or feature
    syslocks -->sys.dm_tran_locks
    isql -->sqlcmd
    old style of outer join *= and =* should be replaced by JOIN syntaxt
    Others are here: http://msdn.microsoft.com/en-us/library/ms144262%28v=sql.90%29.aspx

4. Deprecated features
    try to use new syntax for deprecated features.
    backup transaction --> backup log
    setuser --> execute as
    sp_add_server --> sp_add_linked_server
    restore database with dbo_only --> restore database with restricted_user
    Check the full list here: http://msdn.microsoft.com/en-us/library/ms143729%28v=sql.90%29.aspx

5. Breaking changes must be dealt with
    http://msdn.microsoft.com/en-us/library/ms143179%28v=sql.90%29.aspx

6. Compatibility level

    Set the new database to still compatible with SQL 2000 until systems have been fully tested.

7. Convert to new and more efficient syntax
  pivot, unpivot (T_SQL case when in sql 2000)
  OUTPUT for insert/update
  top @n with variable
  APPLY to easily integrate with table-valued UDFs.
  Ranking functions. thinking about row_number() for web page pagenation.
  Error handling, try,,,catch...
  CTE, thinking to rewrite the direved queries.
  and Recursive queries with CTE,

A Glance on SQL Server Performance Tunning

Here is a brief on what I use.

1. Tools to use

perfmon, sql profiler, sql trace, SSMS, DBCC and system views, functions and procedures.

Use perfmon to check the overall SQL Server performance status.

Use Profiler and SQL Trace to identify costly queries.
Use Execution Plan to find out poor performance part in an execution.
Use DBCC sqlperf(waitstats)  to find waitings. Use DBCC to find other potential perf issues.

Some tables or functions for SQL2000:

syslockinfo, sysprocesses, sysperinfo, ::fn_virtualfilestats

Here is an example of using ::fn_virtualfilestats to check the I/O statistics on database files.

 select     DbId
    ,FileId
    ,TimeStamp
    ,NumberReads
    ,NumberWrites
    ,BytesRead
    ,BytesWritten
    ,IoStallMS
from ::fn_VirtualFileStats
    (dbid,fileid)
 --IoStallMS<= 20 ms for log
)

2. Identifying costly queries, build the performance baseline.

--check the parsing time and execution time. this is not as important as statistics io
set statistics time on
--check mainly the logical reads. scan count also counts, but less important.
set statistics io on


use profiler to capture a workload and check the CPU, duration, reads and writes

3. Analyze the execution plan on the costly queries

When you check the execution plan, try to find these the first:
(higher percentages, fat bars, scans, etc..)
-- high number of percentage of cost
-- thicker data flow lines
-- bookmark lookup( key lookup for clustered index and RID lookup on non-clustered index are its another name. address it by creating covering index)
-- scan operations
-- hash joining

The execution plan can be extracted directly from DMV if sql server is 2005 or above.

select qs.*,s.text,qp.query_plan from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) s cross apply sys.dm_exec_query_plan(qs.plan_handle) qp where s.text like '%xxxx%'

clicking on the link of query plan to display the query plan in SSMS.

set showplan_xml on, then the execution is to display the execution plan. not really executing the statement

set statistics xml on, then it executes the statement and return the xml execution plan. it's the same as turning on the include actual execution plan in SSMS.

4. Tune the statements and further check their costs, comparing with baseline built in step one.

lock hint and lock escalation

Using a lock hint such as ROWLOCK only alters the initial lock plan. Lock hints do not prevent lock escalation.

Check SQL Server Information

@@DBVERSION
xp_msver

SELECT SERVERPROPERTY('xxx');

SQL Server Database Storage Structure

Page: 8kb
The basic storage unit of SQL Server is the page which can store up to 8KB of
data. there are 128 pages per megabyte

The page has a 96 bytes header which contains information such as object id and free space.

The storage engine won't allow a record expand to multiple pages. this means maximum length of a row is limited to a little bit over 8000.

Extent:
A set of 8 contiguous pages or 64KB or 16 extents per MB.


Uniform extents are owned by a single database object and all of the 8 pages can only be used by the owning object. Mixed extents can be used by up to eight objects. A new table or index is usually allocated to a mixed extent but when it grows to eight pages will be switched to a uniform extent.


Physical File:
data file, log file etc. They can automatically grow according to the setting. 



A database will typically be mapped over a number of operating system files. Files can only be used by one database and data and log information is never mixed in the same files.



A database will start with a primary file which in turn points to any subsequent files used by a database. Conventionally these files have .mdf as their filename extension. Secondary data files then contain all of the other associated data for a database and come with the extension .ndf. Log files are used to recover the 
database and there will always be at least one log file per database with the extension .ldf. These file extensions are only conventions and can be customised, but it is not recommended as it may cause you some support issues later on.



A file group is a number of database files brought together for easier allocation and administration.. If there are a group of files in a file group then all of the files will need to be full before the file sizes autogrow using a “round robin” algorithm. Log data is never managed as part of a file group as it has a different role to the other data file types.  

Calculate table width in SQL Server

1. use sp_help to get length for each column, paste them to spreadsheet to have a quick summary
2. use dbcc showcontig() with tableresults to get the statistics such as min record size, max record size and average record size
3. in new version of SQL server(2005 and above), use DMV to query the statistics information

sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

SELECT CAST(DB_NAME(DATABASE_ID) AS VARCHAR(20)) AS 'DatabaseName',
CAST(OBJECT_NAME([OBJECT_ID]) AS VARCHAR(20)) AS 'TableName',
index_id, 
index_type_desc,
alloc_unit_type_desc,
min_record_size_in_bytes, 
max_record_size_in_bytes, 
avg_record_size_in_bytes 
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID('db'),NULL,NULL,NULL,'DETAILED')
ORDER BY avg_record_size_in_bytes DESC;

View SQL Agent Job history

1. Query the underlying tables

SELECT [JobName] = JOB.name,

[Step] = HIST.step_id,

[StepName] = HIST.step_name,

[Message] = HIST.message,

[Status] = CASE WHEN HIST.run_status = 0 THEN 'Failed'

WHEN HIST.run_status = 1 THEN 'Succeeded'

WHEN HIST.run_status = 2 THEN 'Retry'

WHEN HIST.run_status = 3 THEN 'Canceled'

END,

[RunDate] = HIST.run_date,

[RunTime] = HIST.run_time,

[Duration] = HIST.run_duration

FROM sysjobs JOB

INNER JOIN sysjobhistory HIST ON HIST.job_id = JOB.job_id

WHERE JOB.name = 'jobname'

ORDER BY HIST.run_date, HIST.run_time



2. EXECUTE MSDB.DBO.SP_HELP_JOBHISTORY NULL,'job name'


BTW, To purge the log,

EXEC dbo.sp_purge_jobhistory @job_name = N'job' ;

Pagenation SQL Server solution

1. It's easy in SQL 2012. it has similar feature as those in MySQL and PostGreSQL. Use OFFSET and FETCH to limit the rows it returns back.

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

2. In SQL 2005 or 2008, ranking function row_num() can be used. Drawback is the the performance overhead. CTE can be used here to make it look better.

SELECT  *

FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *

          FROM      Orders

          WHERE     OrderDate >= '1980-01-01'

        ) AS RowConstrainedResult

WHERE   RowNum >= 1

    AND RowNum < 20

ORDER BY RowNum

WITH Paging_CTE AS

(

SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *

          FROM      Orders

          WHERE     OrderDate >= '1980-01-01'

)

SELECT

*

FROM

Paging_CTE

WHERE RowNum > 0 AND RowNum <= 20

3. as more common approach, in SQL 2000, due to lack of features, some other way need to be invented.

3.1 use temporary table

CREATE TABLE #Temp (

    ID int IDENTITY PRIMARY KEY,

    PK  /* this can be combined PK */

)

INSERT INTO #Temp SELECT PK FROM Table where xxxx ORDER BY SortColumns

SELECT xxx FROM Table JOIN #Temp temp ON Table.PKs = temp.PKs 

WHERE ID > StartRow AND ID < EndRow

ORDER BY temp.ID 

3.2 set rowcount with proper sort column, very inefficient

SET ROWCOUNT @StartRow

SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn

SET ROWCOUNT @PageSize

SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

3.3 retrieve all the rows and put in web session. pagenation is done by web app.

Using fileproperty funciton in SQL Server

fileproperty is database context sensitive.
Returns the specified file name property value when a file name in the current database and a property name are specified. Returns NULL for files that are not in the current database.


propertyies can be:
IsReadOnly,IsPrimaryFile,IsLogFile,SpaceUsed


use databasexxx
select fileproperty('xxxx','spaceused')

here xxxx is the logical file name defined in databasexxx

Detach and Attach database in SQL Server

some of the commands I used today.

alter database xxx set single_user with rollback immediate

exec sp_detach_db [xxx]

exec xp_cmdshell 'del or rename the log file'

exec sp_attach_db [xxx],'path to the mdf file'

alter database [xxx]set recovery simple|full

alter database [xxx] modify file ( name='xxx_log' ,filegrowth= 100mb)
--remove the strong windows passwork policy checking

alter login sa with check_policy = off

sp_password  @old =   'xxx',

     @new = 'yyyy'

     ,@loginame = 'sa'

Resolving blocking issues in SQL Server

1. Use nolock table hint on the source table when it selects data for archiving
2. Add proper indexes to accelerate the select statement.
3. Remove referential constraints if possible. E.g removing the foreign key if the application is proved to not beak the integrity.
4. Split transaction into smaller transactions by using set rowcount command
Declare @numrows int
Set @numrows=1
While (@numrows>0)
Begin
                (Begin tran)
Set rowcount 500
Delete xxxxx
(Commit tran)
(Waitfor delay ’00:00:0x’)
End
Set rowcount 0
5. Guaranty the data integrity in application level
(6. Make the archiving happen more frequently. E.g. hourly instead of daily)

SQL Server 2000 Deadlock analysis

SQL 2000:

1.Enable trace 1204 to capture the deadlock in the SQL Server's error log.

DBCC TRACEON(3605, 1204)
or
DBCC TRACEON(3605)
DBCC TRACEON(1204)

To turn them off , use

DBCC TRACEOFF(3605, 1204)

From the log, you find out the contented resource the first. 

For page lock, use dbcc page() to figure out the involved the objects.

dbcc traceon(3604) --enabled the page header

dbcc page(db,file,page,1)

dbcc traceoff(3604)

use sysobjects, sysindexes to find out the involved objects.

http://msdn.microsoft.com/en-us/library/ms178104(v=sql.105).aspx 

2. Use SQL Profiler to capture the deadlock.it's better to also capture other information to assist analyzing the deadlock. usually, that includes these ones:

Locks: Lock:Deadlock and Lock:Deadlock Chain
Stored Procedures: RPC:Completed, RPC:Starting, SP:StmtCompleted, SP:StmtStarted
Transactions: SQL Transaction
TSQL: SQL:BatchCompleted, SQL:BatchStarting, SQL:StmtCompleted, SQL:StmtStarting

From the deadlock it detected, you can figure out which two SPs are involved. Then analyse the

statements from each SP sorted by their sequence, you probably can find out which two statements

are conflicting.

This approach is not quit useful.