- 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
- Confirm the data and log file location for the target server
- Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy option etc)
- Collect the information of dependent applications, make sure application services will be stopped during the database migration
- Collect the information of database logins, users and their permissions. (Optional)
- Check the database for the Orphan users if any
- Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers)
- Check, if the database is part of any maintenance plan
Below are various scripts you can run to collect data.
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 GO
Script to Check Database Properties
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
Script to List Orphan Users
sp_change_users_login 'report' GO
Script to List Linked Servers
select *from sys.sysservers
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
Database Migration Checklist
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 read-only USE [master] GO ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT GO ALTER DATABASE [DBName] SET READ_ONLY
3. Take the latest backup of all the 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 to any 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.
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, to create logins on the target server you can get the code from this technet article: http://support.microsoft.com/kb/246133.
7. Fix Orphan Users
Script to Check 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 CLOSE fixusers DEALLOCATE fixusers
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.
Take a look at this tip to rebuild all indexes.
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. Updating Index Statistics
11. Recompile procedures
Take a look at this tip to recompile all objects.
This will recompile a particular stored procedure.
12. Start the application services, check the application functionality and check the Windows event logs.
13. Check the SQL Server Error Log for login failures and other errors
Take a look at this tip on how to read SQL Server Error Logs.
EXEC xp_readerrorlog 0,1,”Error”,Null
14. Once the application team confirms that application is running fine take the databases offline on the source server or make them read only
-- 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 -- Script to take the database offline EXEC sp_dboption N'DBName', N'offline', N'true' OR ALTER DATABASE [DBName] SET OFFLINE WITH ROLLBACK IMMEDIATE
Database Migration / Upgrade in SQL Server:
Microsoft has performed extensive enhancements on SQL Server 2005 product suite. The product is introduced in market not only as a RDBMS that meets new generation database needs but also as an advanced BI technology product. Although DTS and SSIS are both ETL tools, their architectures diverge greatly. The advanced features that are introduced in the new product suites are:
- Enterprise Performance
- High Availability
- Developer Productivity
- Advanced Business Intelligence
- Competitive features
This article explores the migration/upgrade path available for companies wanting to migrate to SQL Server 2005 from SQL Server 2000 or SQL Server 7.0. It also discusses the new SQL Server 2005 Upgrade Advisor tool that Microsoft has developed for the upgradation.
The basic difference between upgrade and migration is that, an upgrade is an automated process in which a set-up program moves an old instance of the database to a new instance, keeping the metadata (with some exception) and data same. Migration is a manual process, where the old and the new instances resides at the same time and are helpful to verify correctness of the migration. Upgrading the database engine from older version of SQL Server to SQL Server 2005 is done easily using a set-up wizard. Moving the data from Data Transformation Service (DTS) to SSIS requires a migration. Following table summarizes the upgrade path for each component of SQL Server 2005.
|SQL Server 2005 Component||Upgrade/Migration Path|
|Database engine||Upgrade Tool: Setup
Migration Method: Side-by-side installation, then database backup/restore, detach/attach
|Analysis Service||Upgrade Tool: Setup
Migration Tool: Migration Wizard migrates objects, requires optimization and client provider upgrades
|Integration Service||Upgrade Tool: None
Migration Tool: DTS Migration Wizard
Migration Method: Migration Wizard converts 50-70 percent of tasks, requires some manual migration; runtime DTS DLLs available in SSIS; package re-architecture is recommended
|Reporting Service||Upgrade Tool: Setup
Migration Method: Side-by-side installation and deployment of reports on new instance
|Notification Service||Upgrade Tool: None
Migration Tool: Upgrade of Notification Services instances during install