0

We want to migrate our SQL Server 2012 on server 2012R2 to SQL Server 2019 on server 2016 and keep the ip addresses but change hostnames.

i can not change the name of listener and ip addresses from application code. We have 10 TB data. We have been using single availability group.

Current situation :

LISTENER : SQLLSN : 10.10.12.12

SQL_OLD1 - 10.10.12.2
SQL_OLD2 - 10.10.12.3
SQL_OLD3 - 10.10.12.4


Windows Server 2016 - SQL Server 2019

SQL_NEW1 - 10.10.12.5
SQL_NEW2 - 10.10.12.6
SQL_NEW3 - 10.10.12.7

After migration:

LISTENER : SQLLSN : 10.10.12.12

SQL_NEW1 - 10.10.12.2
SQL_NEW2 - 10.10.12.3
SQL_NEW3 - 10.10.12.4

Any information and suggestion would be very appreciated.

Improve this question

2 Answers 2

Reset to default
3

Since it's all in the same subnet, when you cut over just remove the AG listener from the old cluster and add one with the same name and IP to the new cluster.

There's no need to change the IP addresses of the new nodes to match the IP addresses of the old servers. The App should be connecting to the AG Listener IP only.

So you end up with:

LISTENER : SQLLSN : 10.10.12.12

SQL_NEW1 - 10.10.12.5
SQL_NEW2 - 10.10.12.6
SQL_NEW3 - 10.10.12.7
Improve this answer
3
  • 1
    If the IP of the nodes can be changed why not to join new server to the cluster? Maybe not all at once but replace one by one. Unless it is not migration to Windows 2019 it is possible to join it to the same cluster. It would allow to migrate to the new servers almost without down time. We already did it in our company.
    – Marcin
    Commented Jan 19, 2022 at 22:04
  • @Marcin Hi , care to share your migration steps? I will do similar something
    – Cell-o
    Commented Jan 20, 2022 at 5:50
  • Hi, First join one of new sql to the cluster and make it fully in sync, then I would remove one of old node to have it like that: SQL_OLD1 - primary SQL_OLD2 SQL_NEW1 Then join the second new and make it in sync: SQL_OLD1 - primary SQL_OLD2 SQL_NEW1 SQL_NEW2 At this point you can do failover to one of the new nodes When failover is done, remove one of the old sql: SQL_OLD1 SQL_NEW1 - primary SQL_NEW2 Join the last new sql server and remove the old one SQL_NEW1 - primary SQL_NEW2 SQL_NEW3 and that is all. Hope it will help you
    – Marcin
    Commented Jan 20, 2022 at 10:50
0

As there is no formatting in the comment I paste the plan here:

First join one of new sql to the cluster and make it fully in sync, then I would remove one of old node to have it like that:

  • SQL_OLD1 - primary
  • SQL_OLD2
  • SQL_NEW1

Then join the second new and make it in sync:

  • SQL_OLD1 - primary
  • SQL_OLD2
  • SQL_NEW1
  • SQL_NEW2

At this point you can do failover to one of the new nodes When failover is done, remove one of the old sql:

  • SQL_OLD1
  • SQL_NEW1 - primary
  • SQL_NEW2

Join the last new sql server and remove the old one

  • SQL_NEW1 - primary
  • SQL_NEW2
  • SQL_NEW3

and that is all.

You could also add all nodes at once but you will have unnecessary overhead on replication. So that is why I would take this longer tour.

Hope it will help you

Improve this answer
2
  • Thanks for the information . btw, I want to keep ip addresses same as old machines. is it possible ?
    – Cell-o
    Commented Jan 20, 2022 at 11:29
  • Then I would remove one of the node from the cluster change IP and join the new sql with IP from the old server. Don't change IP on the server that is joined to the cluster.
    – Marcin
    Commented Jan 20, 2022 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.