Replication Cleanup

Question

Replication Cleanup

Grinja

Hall of Fame

Points: 3590
More actions
June 11, 2008 at 6:27 am

#124153

Hi,
I'm upgrading my Replicated 2000 database to 2005.
The database is both publisher and distributor. Do I need to do any replication cleanup before restoring the DB to 2005?
I was planning something along these lines:
1. Stop all merge agents.
2. Run sp_mergesubscription_cleanup on subscribers.
3. Delete Publication.
4. Backup DB.
5. Restore DB to 2005 instance and set compatibility to 90.
6. Setup Replication on 2005 instance.
The subscribers will remain on 2000.
Any info on this process would be appreciated.
Before restore the backup to a 2005 instance I was planning on the f

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dbaker-620086 SSCrazy Points: 2336 More actions · Answer 1

as you will know, there are UI changes from SQL2000 (SEM) to SQL2005 (SSMS), but also fundamental ones too under the covers (incl replication).

When contemplating a migration you should use the MS Upgrade Adviser to sniff out any pitfalls with the automatic process

searching for "Microsoft SQL Server 2005 Upgrade Advisor" on microsoft.com yields

http://msdn.microsoft.com/en-us/library/ms144256.aspx

http://go.microsoft.com/fwlink/?LinkId=45788

which I suggest you read (first is documentation) and execute (second is SQLUASetup.msi)

The equivalents for SQL2008 are

http://msdn.microsoft.com/en-us/library/bb677622(SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms144256(SQL.100).aspx

http://go.microsoft.com/fwlink/?LinkId=113913

and there is a particular "Considerations for Upgrading Replicated Databases" which is good

http://msdn.microsoft.com/en-us/library/ms143699(SQL.100).aspx

If you have MSDN subscription, you have access to the Release Candidate 0 (RC0), and there is various support from SQL2000 to SQL2008 (with/out going via SQL2005), so worth a read.

In any case, it is ALWAYS worth scripting out your current repl pub+subs

- in SEM that is "Generate SQL Script" on context menu for each pub (can preview first)

This shows the underlying sproc calls

exec sp_addpublication

exec sp_grant_publication_access

exec sp_addarticle

exec sp_addsubscription

which is handy when recreating (you should have such scripts for D/R anyway!)

The SQL2005 repl has made improvements, especially with security incl new

exec sp_addpublication_snapshot

so you should review this rather than JUST use the old scripts.

If you want you CAN obviously burn all previous pubs and start over with the SSMS GUI

HTH

Dick

Grinja Hall of Fame Points: 3590 More actions · Answer 2

Thanks for all the info and the link to the msdn article ... will give it a read.

The plan is to kill the 2000 publication, restore the DB to instance of 2005 and start replication from scratch using 2005. I will generate the scripts after.

I just want to be sure the backup I use from the 2000 instance will be 'clean' i.e. no previous replication data.

Cheers.

Manoj-485464 SSCrazy Points: 2717 More actions · Answer 3

Grinja, if you want to migrate replicated DB from2000 to 2005 that is very simple. Stop all the agents cerate script of the Replication, Take Backup of the DB and restore the backup on 2005 server. Use the script which you generated for creating theReplication between your Publisher and sunscriber.

Not more you need to do.

Manoj

MCP, MCTS (GDBA/EDA)

Grinja Hall of Fame Points: 3590 More actions · Answer 4

Grinja

Hall of Fame

Points: 3590

June 12, 2008 at 6:40 am

#827794

Thanks!