SQL Replication Between Publisher and Subscriber - SQL Server upgrade

Glenn Escarayan 141 Reputation points
2022-12-20T22:07:00.663+00:00

Hi MS Tea,

We are trying to design migration scenario of sql server with couple of databases and there is a 1 is to 7 Publisher and Subcribers configure. is there a way to avoid the replication between publisher and subscriber? If not, what is the best approach to migrate it. Should we Disable Publishing and Distribution for each? Also there is 1 sql server running on 2012 R2 and needs to be upgraded to 2019. What is the best way to migrate this with less downtime as the 2012 R2 is a business critical production machine.

Please advise.

Thanks,
Glenn E.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,446 questions
{count} votes

Accepted answer
  1. Seeya Xi-MSFT 16,571 Reputation points
    2022-12-21T05:54:43.82+00:00

    Hi @glennescarayan,

    It's not quite clear what exactly you want to upgrade. If you want to upgrade SQL Server, see this: Upgrade or patch replicated databases
    The upgrade path to SQL Server is different depending on the deployment pattern. SQL Server offers two upgrade paths in general:
    Side-by-side: Deploy a parallel environment and move databases along with the associated instance level objects, such as logins, jobs, etc. to the new environment.
    In-place upgrade: Allow the SQL Server installation media to upgrade the existing SQL Server installation by replacing the SQL Server bits, and upgrading the database objects. For environments running Always On Availability Groups or Failover Cluster Instances, an in-place upgrade is combined with a rolling upgrade to minimize downtime.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Glenn Escarayan 141 Reputation points
    2022-12-22T20:45:02.387+00:00

    Hi Seeya/Erland

    Below is the additional/scenario and info needed.

    1. Scenario 1: Upgrade Windows server OS 2012 R2 to Windows 2019. Best approach to minimize downtime?
    2. Scenario 2: SQL Server Architecture. It is a windows server upgrade from 2012 r2 to 2019. 1 server as publisher and couple of servers as subscribers. What is the best approach for this? what is the impact of replication? all server is running on windows server 2012 R2. Can we stop the replication and sync later? How can the replication be avoided?

    Thanks,
    Glenn

    0 comments No comments

  2. Erland Sommarskog 116.5K Reputation points MVP
    2022-12-22T22:03:17.453+00:00

    I assume that the database is not in a failover cluster or an availability group?

    If are going to upgrade the OS, it will take the time it takes, and SQL Server and the database will be unavailable while the upgrade is running. The flip side of this is that you do not need to make any arrangements with replication. Once everything is upgraded, I can't but see that everything will be rolling.

    If you want to minimise downtime, you would have to migrate to new hardware. Which you can do by first restoring a backup and then apply transaction-log backups until you do the switch. But in this case, you may have to work more with the replication topology.

    Which SQL Server version do you have?

    0 comments No comments

  3. Glenn Escarayan 141 Reputation points
    2022-12-23T06:26:02.473+00:00

    Hell Erland,

    Database is on currently o AG and SQL version is 2016 and 2019. Any consideration needed?

    Thanks,
    Glenn


  4. Glenn Escarayan 141 Reputation points
    2022-12-23T16:28:40.447+00:00

    Hello Erland,

    They are all virtualized machine hosted in vmware.

    Thanks,
    GCE


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.