November 23, 2022 at 5:29 pm
Hi
We are going to update our old SQL physical servers to brand new machines.
In the planning in my head stage but need some advice.
Thinking I can setup the new hardware with the exact same OS, SQL version, instance name but with a different server name. Then when the new server is setup - all DB's restored so it is an exact match of the old server (except for server name) - ie
OLDMACHINE\SQLSERVERA
NEWMACHINE\SQLSERVERA
I will then turn off SQL services on old machine and use add server drop server to rename the new machine 'OLDMACHINE' and swap the ip addresses?
Does this make sense - or is there an easy way to do this I'm not thinking of?
November 24, 2022 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 28, 2022 at 3:09 pm
Sounds similar to what I did a while back. It was several years ago, so not fresh in my memory.
November 30, 2022 at 7:55 am
For me, I would start by asking 'How can I reduce the pain for the future'. Two parts of the answer to this are given below. They can be done independently of each other but work well together.
One part of reducing the pain is to use DNS aliases to give a level of indirection between the application and the DB server. This allows the DB server name to be changed (for upgrade, BCO, or whatever) with no impact on connection strings.
Therefore, start your migration by setting up a DNS alias to your DB server and then go round all the applications and change their connection strings to use the server alias name. At cutover time you simply change the alias to point to the new server.
One other part is to give a layer of indirection between the OS of your DB server and the underlying hardware. Hopefully you will already have a Hyper-V environment at your site, if not this is an opportunity to get it started.
Build your DB server as a guest machine. In this way it can be quickly moved to any of the host machine servers. At my old place all the DB servers were guests and they did a complete hardware refresh without any changes needed to the DB servers. They did the standard thing of building a new Hyper-V host and adding it to the cluster. During a DB server maintenance window it was started on one of the new hosts. Job done.
They are now looking at upgrading to SQL2022. Most likely new guest servers will be build using W2022 to host SQL. A distributed Availability Group will be used to populate the new servers. Cutover will be a dAG failover aligned with a DNS alias change. Very little pain and job done.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 30, 2022 at 4:20 pm
I'll second Ed's suggestion about DNS aliasing. We're moving all our servers to a new data center with new names but with the same DNS alias as the old network. The connection strings should just work. And definitely go with VMs as Ed suggested if you have any say in that.
-Tom
November 30, 2022 at 6:49 pm
Thanks Guys. Gonna do some testing but most likely going with DNS alias (CNAME)
Thanks again.
December 1, 2022 at 7:51 am
My old place used two levels of DNS aliases. We called them Application Aliases and Failover Aliases.
Each of our applications used its own alias to connect to its DBs. Therefore (say) the HR site of apps would use apphrdb.domain.name in its connection string and the finance suite would use appfin.domain.name in its connection string.
This gives a level of indirection between the application DBs and the DB server topography. It becomes possible to consolidate all sets of DBs to a single server or disperse them to whatever server topology you want, all without changing any connection strings. You just change where the application alias is pointed.
We had a SQL cluster in site a and another in site b, linked by a distributed Availability Group (dAG) to give us BCO capability. Sites a and b were peers of each other and obviously the SQL clusters at each site had unique names. The Failover Alias points to the current dAG primary, and exists to reduce risk at failover time. All the application aliases relating to DBs on the SQL cluster point to the failover alias for the cluster. In other words the target for the application alias is another alias, not an actual server.
At failover time the dAG gets failed over and the failover alias gets repointed at the new active cluster. This means all affected applications get their target DB server changed at the same time, and there is no risk of missing out changing one out of a long list of application aliases.
A bit of planning with Aliases can save a lot of pain as the business evolves.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 1, 2022 at 8:14 am
They say you should treat your servers as cattle, not as pets. When a pet gets poorly you take it to the vet and spend a lot of money treating it until it is well again. When one of the cattle gets poorly you shoot it.
A DB server is too complicated to be just one of the herd, but it is wrong to treat it as a pet. Consider it more like a prize bull, one worth giving a bit of attention to but also prepared to shoot if it becomes too poorly.
One of the key tools in making a DB server a prize bull is virtualisation. A DB server installed on to a physical box will always be a pet and demand more attention than it should get. A virtual DB server, especially if it is part of a guest cluster, can more easily be a prize bull.
All members of a guest cluster get built the same. If one member becomes poorly then build a new member, join it to the cluster and evict the poorly member (then shoot it).
Migrating through members of a cluster can also be useful when doing OS upgrades. My old place did this when upgrading the DB servers from W2016 to W2019. On the other hand, we always chose to build a new cluster with a new name when upgrading SQL versions.
Running SQL as a guest costs about 0.5GHZ of a single core in CPU overhead and about 500MB in memory overhead. The payback is flexibility of server topography. That flexibility gives a lower risk of a physical server failure causing an outage and a greater choice of pathways to deal with changing server topology to cope with business requirement changes.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply