Post reply

Replication Cleanup

  • 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

  • June 11, 2008 at 7:07 am

    #827215

    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

  • June 11, 2008 at 7:19 am

    #827223

    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.

  • June 11, 2008 at 9:18 am

    #827340

    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)

  • June 12, 2008 at 6:40 am

    #827794

    Thanks!

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

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