Remember SQL Server 2005 to SQL Server 2008 Database Migration

An upgrade migration from SQL Server 2005 to SQL Server 2008 was completed last weekend, essentially a server migration process. The more complex is the replication relationship on this server itself as both a publisher and subscriber, so the entire migration must also ensure that these replication relationships are still available. This master server is for ERP use, the modules involved are relatively many, so from the migration to verify the whole process is still relatively long. Write down this article to celebrate.

First, the main steps
1, check the backup integrity of the existing database (including the publication and subscription database) and the existence of the replication relationship
2, stop all services of the original server (including agent), copy all the data files that need to be migrated to the new server
3. Export the Publishing object on the original server
4. Attaching the database to the new server
5. New/Configure Distributor
6. New Server Publishing article (3 copies)
7. Delete the previous subscription on the new server
8. Reinitialize the subscription to the new server on the publisher Tk456/kserp

Ii. errors encountered in the process of migration and solutions
After the new environment has been configured for replication, you receive the following error message:
Invalid column name ' originator_id '. (Microsoft SQL Server, error:207)
Create replication fails on database which are migrated from SQL Server 2005 with error

--Author:leshami
--Blog: Http://blog.csdn.net/leshami

Error:

ADDITIONAL Information:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Invalid column name ' originator_id '. (Microsoft SQL Server, error:207)

Solution Solutions
Resolution:
1. Add originator_id column to syspublications table in publication db. Run below query in your publication DB.

ALTER TABLE dbo.syspublications ADD [originator_id] [int] NULL;

2. Add a column originator_id into the view dbo.syspublications inside distribution database

3. ADD a originator_id column into ihpublications table in distribution database

Third, the scene of the trailer

Remember SQL Server 2005 to SQL Server 2008 Database Migration