SQL SERVER DBA MIGRATION CONCEPT

 

MIGRATION:

• Migration means moving one or set of databases from one server to another server.

Pre-Migration Checklist:

1. Analyze the target server disk space for the new database, if the disk space is insufficient add more space on the target server
2. Confirm the data and log file location to the destination server
3. Collect the information on the database properties (Auto Stats, DB owner , Recovery Model , compatibility level , Trustworthy options etc ).
4. Collect information dependent applications , make sure application services will be stopped during the database migration.
5. Collect information database connections , users and their permissions . (Optional).
6. Check the database for orphaned users if necessary.
7. Check the SQL Server for all dependent objects ( SQL Agent Jobs and linked servers ).
8. Check if the database is part of a maintenance plan.

Steps in migration:

  1.Script to Check the Disk and Database Size:

• To check disc space command:

                xp_fixeddrives

• To Check database size:

           exec sp_helpdb [dbName]

     2.Transfer logins:

• Before moving the databases,script out the logins on source server and keep them a side.
• There is a stored procedure sp_help_revlogin,We have execute this stored procedure in the server.
• First it creates the stored procedure under master database.This will not come as part of sql installation.
• Once the stored procedure created we have to run the stored procedure second time it will script out all logins of source server including password in encrypted format.
• We can copy all logins or few logins according to our requirement.
• Copy all the logins to destination server and execute them on server.same login and password will be created.
•  second way,Right click on login and script as copy them on other server.
•  This process will not copy passwords.

   3.Transfer jobs:

• Right click on job-->script job as-->create to new window,copy the script and run on other server.
• When ever we want to transfer all jobs we need create a package and transfer that package.

  4.Transfer the packages:

• We have to connect to Integration services-->go to msdb database-->under stored packages we will have SSIS packages-->
• Right click on packages-->on the package wizard select export package
• Provide destination server location:
•  Now connect to destination server-->go to integration services-->goto stored packages-->under MSDB right click on packages and
• select import package,package will be created on server.

   5.Transfer the databases:

      Backup and restore:

• Take backup on source server and copy them to destination server.
•  Right click on database in destination server-->select restore and provide backup details and click ok backup and restore is online.
•  source will not be distributed here.Roll back is easy.

      Attach and Dettach:

• Right click on database of source server-->go to task-->select dettach database,database will go offline copy files from source server and paste them.
• Goto destination server-->select attach-->provide path details

  6.Script to List Linked Servers

     select  * from sys.sysservers

  7.Check new database properties:

     Verify the database size from database properties

      From options verify--recovery model,db owner,compatability level and collation settings.

     Change the db owner using: sp_change dbowner 'sa'

    Change compatability level:

    ALTER DATABASE DatabaseName

    SET SINGLE_USER

   GO

   EXEC sp_dbcmptlevel DatabaseName, 90;

   GO

   ALTER DATABASE DatabaseName

   SET MULTI_USER

   GO

8.Find and fix orphaned users:

• When ever taking full backup and copy & restore on destination server.
• All user account will copy destination server.
• User account with out corresponding login account is called orphaned users.
• As the login will present in active directory or syslogins of master db.
• Some times sychronization will miss even after transfer logins also.

     To find orphaned users:

     sp_change_user_login 'report'

    To fix orphaned users:

    sp_change_users_login 'update_one' 'user name' 'login name'

Update one will establish sychronization between username and login name