Post reply

Migrating a SQL Server 2008(Publisher and Subscriber) to SQL 2014, retaining Server Name

  • February 5, 2018 at 5:56 am

    #410210

    Hello Experts,

    I have a production SQL Server of version 2008 running on a cluster which is proposed to be migrated to a SQL Server 2014 cluster and the end result is to keep server names same.

    The end result of renaming the SQL Server has been tried and tested well and is not a problem. However, the server is involved heavily in replication. It acts as a Publisher as well as Subscriber to different other SQL Servers.

    If the server names were different, then the replication had to be rebuild using'replication support only' method. However since we are retaining the same name, I would like to hear if there is a better way to create replication other than dropping and re-creating.

    One approach that has been occupying my mind is to:

    1) Stop and disable all the log-reader agents and distribution agents so that no replication agent tries to hit this server.
    2) Copy the master DBs and restore other user DBs to new server
    3) Change the names to the existing one(take 2008 one offline)
    4) Bingo

    However, there are few obstacles in this approach:
    1) Master DB cannot be restored to a different version so I need to do something to have a copy of 2014 version
    2) User databases are large, some around 1 TB so restoring them with fresh copy will not be feasible during 3-4 hours maintenance window.
    3) I read that 'preserve replication settings don't work when we restore DBs in 'no-recovery mode'

    I hope you can understand what I am trying to achieve. Please share your inputs and suggestions regarding this challenge.

    Thanks
    Chandan Jha

  • February 7, 2018 at 4:20 am

    #1978826

    Can anyone suggest me an idea please.

  • March 13, 2018 at 1:47 am

    #1982859

    chandan_jha18 - Wednesday, February 7, 2018 4:20 AM

    Can anyone suggest me an idea please.

    Why can't you try the below for your work ?

    https://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases/

    Also we've had the same upgrade from SQL 2008 Reporting services to 2014. But we had a different server name, for a new server , we have a different server name but the URL was same as before.

  • March 13, 2018 at 8:41 am

    #1982951

    subramaniam.chandrasekar - Tuesday, March 13, 2018 1:47 AM

    chandan_jha18 - Wednesday, February 7, 2018 4:20 AM

    Can anyone suggest me an idea please.

    Why can't you try the below for your work ?

    https://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases/

    Also we've had the same upgrade from SQL 2008 Reporting services to 2014. But we had a different server name, for a new server , we have a different server name but the URL was same as before.

    That doesn't make any sense. Publisher and Subscriber relate to replication, not reporting services.
    The poster is looking for alternatives to rebuilding publications, subscriptions, etc. when migrating.

    Sue

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

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