Configuring Log Shipping between SQL Server 2005 & 2008
If you restore the database in recovery then consecutive log backup cannot be restored.
Restore with Recovery
Try the below steps.
Take a full backup of the database in the primary server (SQL 2k5)
Copy the backup to the secondary server (SQL 2k8)
Restore the database with Norecovery (SQL 2k8)
Take a tail backup of the database in the primary server(SQL 2k5), this could be small in size
Copy and restore the tlog backup in the secondary server with either NORECOVERY \ STANDBYMODE(SQL 2k8)
Configure log shipping between primary (SQL 2k5) to secondary server (SQL 2k8) and make sure its working fine.
Once log shipping is in place, move all the logins from primary (SQL 2k5) server to secondary server (SQL2k8), this step ensures that the ID used in application exists in the secondary server with same SID & password. You can either use "Transfer login SSIS task" or "this KB article"
Now logins are all created in the secondary server with the same SID, now we can start failover.
Shutdown the application \ services etc
Disable all log shipping jobs both in primary and secondary servers
Take a tlog backup and make the database in readonly (so that no further transactions is happened)mode in the primary server (SQL 2k5)
Copy and restore the tlog backup in the secondary server (SQL 2k8) with RECOVERY (so that appliation can make changes to the db)
Change the connection string \ ODBC entry etc to point the application to the secondary server (SQL 2k8) and make sure its working fine.
Once the application started working and updates will be sent to the secondary server (SQL2k8), you can start inplace upgrade of your primary server to SQL server 2008.
Check these steps and let me know if it works!
No comments:
Post a Comment