Database Server Migration Guide

R

R

D

... 3

I

... 4

O

... 4

P

-M

C

... 4

D

M

C

... 7

R

EQUIREMENTS AND

R

ECOMMENDATION

D

ISCLAIMER

Whilst every effort has been made to ensure that the information and content within this document is accurate, up-to-date and reliable, Gulf Solution Group disclaims any and all liability as to any results or conditions arising after the application of this information. Actions taken from information in the document are solely at the user's own risk. The user should thoroughly test the information in a non-production environment first before applying it to LIVE environment

This document also contains links to third-party sites which are not under the control of gulf solution and gulf solution is not responsible for the contents on any linked site or any link contained in a linked site, or any changes or updates to such sites. Gulf solution is providing these links to you only as a convenience, and the inclusion of any link does not imply that gulf solution endorses or accepts any responsibility for the content on such third-party sites

Performance and response time are affected by many factors related to hardware (such as RAM, processor speed, and hard disk subsystem performance), network configuration (such as NIC performance, cable type, topology, operating system, parameters, and traffic), and the database (such as size, number of concurrent users, and the type of activities each user is performing). In addition, network and workstation operating systems, third-party software products, and our own products are continuously updated with new features and options, which often place greater demands on hardware. Gulf Solutions Group staff may provide limited informal guidance based on information made available. However, neither these system requirements nor our staff's guidance constitute a guarantee of compatibility, outcome, or performance. We encourage organizations to consult their own IT System's Staff or external experts to ensure optimum results. Comprehensive technical consulting services are available through Gulf Solutions Group and under a separate consulting arrangement; our consultants evaluate the installation of all Gulf Solutions Group applications and recommend optimal hardware/system configuration options.

I

NTRODUCTION

Many times we get requests to migrate a single user database or multiple user databases to different server. At the time of migrating databases, there are chances that we can miss some important steps, for example trustworthy property, dependent jobs, linked server, logins etc. There are also chances we are missing important DBA items which can hurt SQL Server performance. As a solution to decrease making mistakes during the migration process there is a checklist that should follow for the database migration. In addition, there are steps which should be taken which can enhance the database performance after the migration.

O

BJECTIVE

The objective of this document is to provide the guidelines for the SQL Server Database migration from one server to another.

P

RE

-M

IGRATION

C

HECKLIST

1. Analyze the disk space of the target server for the new database, if the disk space is not enough add more space on the target server

Script to Check the Disk and Database Size -- Procedure to check disc space

exec master..xp_fixeddrives -- To Check database size exec sp_helpdb [dbName] or

use [dbName]

select str(sum(convert(dec(17,2),size)) / 128,10,2) + 'MB' from dbo.sysfiles

2. Confirm the data and log file location for the target server.

Script to find data and log file location used by SQL Server instance

3. Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy option etc.)

Script to Check the Disk and Database Size select

sysDB.database_id,

sysDB.Name as 'Database Name', syslogin.Name as 'DB Owner', sysDB.state_desc, sysDB.recovery_model_desc, sysDB.collation_name, sysDB.user_access_desc, sysDB.compatibility_level, sysDB.is_read_only, sysDB.is_auto_close_on, sysDB.is_auto_shrink_on, sysDB.is_auto_create_stats_on, sysDB.is_auto_update_stats_on, sysDB.is_fulltext_enabled, sysDB.is_trustworthy_on from sys.databases sysDB

INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

4. Collect the information of dependent applications; make sure application services will be stopped during the database migration.

To Stop SQL SERVER and SQL Server Agent using Command Prompt net stop “SQL Server Agent (Instance Name)”

net stop “SQL Server (Instance Name)”

To Start SQL SERVER and SQL Server Agent using Command Prompt net start “SQL Server (Instance Name)”

You can also check http://technet.microsoft.com/en-us/library/ms175516.aspx to see how to stop application services.

5. Collect the information of database logins, users and their permissions. (Optional) 6. Check the database for the Orphan users if any

Script to List Orphan Users

sp_change_users_login 'report' GO

7. Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers) 8. List out all Maintenance plans and their schedules.

Script to List Database Dependent Jobs

select distinct name, database_name from sysjobs sj INNER JOIN sysjobsteps sjt on sj.job_id = sjt.job_id

9. List out all Linked server(s) currently being used.

Script to List Linked Servers select * from sys.sysservers

10. Check the service pack.

11. Check the Edition and make sure targeted server do not have old version.

See http://support.microsoft.com/kb/321185 to know how to find SQL Server version and Service pack.

D

ATABASE

M

IGRATION

C

HECKLIST

These are the steps you would go through to make the change. 1. Stop the application services

2. Change the database to read-only mode (Optional)

-- Script to make the database readonly USE [master]

GO

ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT GO

ALTER DATABASE [DBName] SET READ_ONLY GO

3. Take the Full backups of all System and Application databases involved in migration 4. Restore the databases on the target server on the appropriate drives

5. Cross check the database properties as per the database property script output, change the database properties as per the pre migration- checklist

Script to Change DB Owner

This will change the database owner to "sa". This can be used to change owner you would like.

USE databaseName

EXEC sp_changedbowner 'sa'

Script to Turn on Trustworthy Option

If trustworthy option was set, this will turn it on for the database.

ALTER DATABASE database_name SET TRUSTWORTHY ON

Script to Change the Database Compatibility Level

When you upgrade to a new version, the old compatibility level will remain. This script shows how to change the compatibility level to SQL Server 2005 compatibility.

ALTER DATABASE DatabaseName SET SINGLE_USER

GO

EXEC sp_dbcmptlevel DatabaseName, 90; GO

ALTER DATABASE DatabaseName SET MULTI_USER GO

6. Execute the output of Login transfer script on the target server. See Also: http://support.microsoft.com/kb/246133.

7. Check for Orphan Users and Fix Orphan Users

-- Script to check the orphan user EXEC sp_change_users_login 'Report'

--Use below code to fix the Orphan User issue DECLARE @username varchar(25)

DECLARE fixusers CURSOR FOR

SELECT UserName = name FROM sysusers

WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null

ORDER BY name OPEN fixusers

FETCH NEXT FROM fixusers INTO @username

WHILE @@FETCH_STATUS = 0 BEGIN

EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM fixusers

INTO @username END

8. Execute DBCC UPDATEUSAGE on the restored database.

Run the DBCC UPDATEUSAGE command against the migrated database when upgrading to a newer version of SQL Server.

DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS DBCC CHECKDB

OR

DBCC CHECKDB('database_name') WITH ALL_ERRORMSGS

9. Rebuild Indexes (Optional) As per the requirement and time window you can execute

this option. This will rebuild or reorganize all indexes for a particular table.

Index Rebuild: - This process drops the existing Index and Recreates the index. Index Reorganize: - This process physically reorganizes the leaf nodes of the index. -- Script for Index Rebuild

USE [DBName]; GO

ALTER INDEX ALL ON [ObjectName] REBUILD GO

-- Script for Index Reorganize USE AdventureWorks; GO

ALTER INDEX ALL ON [ObjectName] REORGANIZE GO

10. Update index statistics

Script to update index statistics sp_updatestats

12. Recompile procedures See also: http://www.mssqltips.com/tip.asp?tip=1260

13. Start the application services, check the application functionality and check the Windows event logs.

14. Check the SQL Server Error Log for login failures and other errors 15. Point the application to new server.

R

EFERENCES

SQL Server Database Migration Checklist

Dubai

Corporate Head Office:

Dubai Internet City, Building No. 2, Suite 212, Dubai - United Arab Emirates. Telephone: +971 4 3910 470 | www.gulfsolution.com | [email protected]

Karachi

Park Avenue, Suite 404, Shahrah-E-Faisal, Karachi - Pakistan.

Telephone: +92 213 4304 784 | www.gulfsolution.com.pk | [email protected]

Bahrain

Telephone : +973 3 9271 539 | www.gulfsolution.com | [email protected]