September 22, 2020 at 10:21 pm
We are looking to move the SQL server to different datacenter. Since it's a standalone instance so it can be moved using V2V or other different options. However, the IP address might change after the move? I am thinking changing from configuration manager would be suffice the IP Address Listened by the SQL Server. Do you agree?
September 23, 2020 at 1:04 pm
You can't just change the IP address locally. That has to go through however you're administering IP addresses within your system. Then, yes, once that IP address changes, you'll have to change anything that refers to it by IP address. Or am I misunderstanding the question?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 23, 2020 at 6:11 pm
To add to what Grant has posted - unless you have made changes to the instance configuration's TCP/IP settings you don't need to change anything. The default configuration isn't locked to a specific host IP address...
Now, if you change the server name - you would need to change that in SQL Server after the migration.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 23, 2020 at 9:59 pm
We are moving the DR server to different data center and it is part of always on AG group. So is there a better way to do this migration other than what I outlined. I was thinking to evict first the dr server from the cluster and then migrate the whole db server using some migration tool and then bring it back after the move. Also it will be on different IP range. So after bringing back to cluster and does SQL Server smart enough to start the synchronization where it stopped? Any thoughts?
December 23, 2020 at 10:29 pm
Why make it harder than it needs to be?
Create the new server in the new DC - install SQL Server to the same patch level as your primary - add it to the cluster and add it to the AG. With automatic seeding the AG databases will be created and synchronized for you - but you can also perform a backup/restore and join/automatic seeding if needed (really depends on size of databases and how much data needs to be sent across the network).
Once the new node is ready - remove the old node from the AG and evict it from the cluster. This ensures you have no outage and no point in time where you are not covered for DR. It also allows you to verify the new system and validate everything is setup and working as expected - including network latency, connectivity and throughput to the new DC.
If needed - you can test a failover to the new node in the new DC/DR and maintain your current DR solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 24, 2020 at 12:37 am
This sounds like a perfect idea for me. You're awesome.
So other than above task whatever you mentioned that all needs to be done before the migration right? Also, I would still need to copy over the Logins and accounts on the new server right? Would I be able to add the new node to existing listener? When I look at the listener properties I see the ADD option is greyed out. The other option is if I drop and recreate the existing listener to add new node then it would cause down time for the application which is connecting with listener? Any thoughts?
Going back to the original question, there are some unknowns on renaming the new node to original name if they don't use listener. What if the App team was wanting to keep the node names the same, but not 100% sure it was required or could be handles through a DNS alias? If there is not a requirement to rename back to the original name or it can use a CNAME in DNS.
December 24, 2020 at 5:52 pm
Yes - this can all be done before the 'migration'. Actually - this is done in place of the migration since you won't be moving the old DR server.
Yes - you have to create the logins and agent jobs, just remember to create the logins with the same SID as created on the primary node (for SQL accounts) and the restored/replicated databases will synch with the logins, the same as when you first created the DR secondary.
The listener is setup for the AG - doesn't matter how many secondary instances have been added to the AG. Once you have added the new node you should be able to add that node to the listener if it isn't already assigned.
All you are doing here is adding a new secondary to the system...once it has been verified you remove the old secondary and the 'migration' is complete.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 8, 2021 at 5:06 pm
This was very helpful. Thanks again. Another question what is the better way of moving the traditional failover cluster, shared storage to another data center.
January 8, 2021 at 6:42 pm
This was very helpful. Thanks again. Another question what is the better way of moving the traditional failover cluster, shared storage to another data center.
This should be posted as a separate question...the answer to your question though is: it depends.
The easiest (and safest) would be to build a new cluster in the new DC - perform a backup/restore (using a differential and transaction logs to reduce downtime) and then modify the application/web/clients to use the new cluster. I say that is the easiest and safest because it gives you the ability to create the new environment, test and validate that environment fully before cutting over to the new systems.
It does require modifying the application connections...but that also gives you the opportunity to document and/or consolidate where those connections are defined and who is connecting to your system.
Other methods are available...but all would be more complex, and for me - little gain.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply