SQL Server Migration Checklist

Are you migrating your SQL Servers?
Here is a quick checklist for your SQL Server Migration.

1. Build your New Server, Install SQL Server and required updates and keep the server ready for migration
2. Stop Application Service(s).
   This is to ensure that the no applications are connected to the Databases during the migration process.
3. Change the Properties of the databases that are part of Migration to "Read-only".
   This is to ensure that the data modification is not happening by any other sources.
4.  Take a FULL backup of all the User databases that are involved in the Migration Process. 
5. Move the backups to the destination server or a Shared location, then restore them to the appropriate drives on the destination.
6. Change the compatibility level of the databases (Optional)
   Do this if the applications connecting to these databases are independent of the database compatibility level.
7. Transfer logins using SSIS (Transfer Logins Task) or using "sp_help_revlogin"
   More information about sp_help_revlogin is at http://support.microsoft.com/kb/246133
8. Check for Orphaned Users in the databases and Fix them (if Any)
9. Update Usage on the migrated Databases
10. Update Stats on the migrated Databases
11. Re-Index or Re-Organize Indexes on the migrated Databases
12. Transfer Jobs using SSIS or manually create them
13. Build Maintenance plans (if Any)
14. Recompile database objects if required
15. Move or rebuild SSIS or DTS packages (if Any)
16. Create Alerts and Operators (if Any)
17. Setup High Availability Options (if Any Like Replication, LogShipping, Mirroring)
18. Test the High Availability options that were setup in the previous step
19. Point the Application(s) to new Server and start the Application Service(s)
20. Test the Application(s)

SQL Server 2008 Service fails to start after Service Pack Installation

Some times you may find that the SQL Server Service is not starting after applying a service pack and when you check in the Event Viewer you find the below message.

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

SQL Server Setup creates a database with the data file name temp_MS_AgentSigningCertificate_Database.mdf during the installation process and if the SQL Server setup is not able to create that database in the default data path then the above error is returned as it is not able to find the path.

To fix this issue.

1. Go to Registry editor, To open this, go to "Run" and type "regedit" and click "ok"
2. First go to this path and make sure that the path in the key SQLDataRoot exists. If not then give a valid path to this key.
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<instance name>\Setup
3. Then go to this path and make sure that the path in the keys "BackupDirectory", "DefaultData" and "DefaultLog" exists. If not then give a valid path to these keys.
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<instance name>\MSSQLServer
4. Now you should be able to start the SQL Server Service without any issues.

Once the Service is started, verify the SQL Server, databases and others to make sure everything is fine.

Also verify if the Service pack or the CU is installed correctly.

Unable to start mail session (reason: No mail profile defined) - Message in Error Log

There will be many different messages that will be logged in the Error Log of SQL Server. Among them you might also find the below message some times.

Date 6/1/2011 8:18:27 AM Log SQL Server Agent (Archive #2 - 6/1/2011 8:18:00 AM) Message [098] SQLServerAgent terminated (normally)

This is the most common message that will be logged when there is no "Mail Session" defined for the SQL Server Agent Alert System.

To Fix this or to make this message disappear in the Error Log, you have to enable "Mail Session" and re-start the SQL Server Agent.

To Do this,

1. Connect to the Server
2. Right Click on the "SQL Server Agent" and go to "Properties"  
3. Then Go to "Alert System" Tab
4. Make sure the check box "Enable Mail Profile" is checked 
5. Click OK to exit
6. Re-start the SQL Server Agent Service

Now if you again look into the Error Log you will not find this message anymore after re-starting the Agent Service.