January 14, 2020 at 12:48 am
With SQL Server upgrade every time the server name would change and that would affect the application side of the server and need to change database connections each time. I understand that there is few options
Rename the server name by sp_dropserver/sp_addserver, however this would not help since during the testing you would still need to change database connections right to the new server name until the old server name is removed from the Active directory or drop the previous instance? How about alias option? Please advise?
January 14, 2020 at 1:01 am
I'm assuming by "SQL Server upgrade" you mean migrating to a new instance on a newer version, as an inline upgrade wouldn't affect server name. With that assumption, the solution I try to use is a DNS alias for each database or application/system.
Basically, imagine your domain (domain.local) has SCCM deployed. You have the various SCCM databases on ServerA.domain.local (SQL 2014) and now it is time to upgrade. You build ServerB.domain.local and get it production-ready. When it comes time to migrate to ServerB, you need to update the connection strings in SCCM to point to ServerB.
If, however, you create a DNS ALIAS or DNS CNAME record called SCCM.domain.local and point this alias/cname at ServerA, then reconfigure SCCM to use SCCM.domain.local instead of the server name, when it comes time to migrate you can simply change the DNS ALIAS/CNAME to redirect to ServerB instead of ServerA. Rolling back can be as simple as reverting the DNS change. In addition, you can have your infrastructure admins setup a subdomain, such as db.domain.local, where you can lower the TTL value to enforce a quick changeover when you update the ALIAS/CNAME.
While this doesn't eliminate all the work of having the change names, it removes that responsibility away from application admins/developers and leaves it with the DBA or infrastructure admins. It also simplifies changes to connections because you don't need to modify all the different connection strings in an application, you simply edit the DNS settings.
Note that if you're going to use a DNS ALIAS/CNAME for each system rather than per server (which I recommend in case you move different apps to different servers over time), you need to use a DNS ALIAS and not a CNAME so that multiple records can point to the single underlying server name.
January 14, 2020 at 5:53 pm
I think the short answer is "synonyms"
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
January 14, 2020 at 7:51 pm
I don't think Synonyms would help here, as that would help renaming an object within a database, but not renaming a server. HandyD's method is actually fairly easy to do, we've done it before.
If you can't do it that way for whatever reason, you could setup Aliases in SQL Server Configuration Manager tool. The problem here, it looks like you can export your alias list, but I don't see an import option.
February 28, 2020 at 4:55 pm
Thanks HandyD. It is a way to do it. However, the server is not standalone it will be configured for Always on. Would that make any difference?
March 20, 2020 at 2:49 am
The concept HandyD proposed will work fine with Availability Groups, but instead of pointing a CNAME to a specific SQL instance, point it to the AG Listener. We've been doing this for several months and it has worked just fine.
May 11, 2020 at 9:54 pm
Thanks. I have another question to ensure and outlined a 4-node cluster i am planning to configure. Node 1 - Primary
Node 2 - Secondary (HA) - synchronous, Node 3 - Secondary (DR) - asynchronous , Node 4 - Secondary (Reporting only,no sql backups since it will be used for select only the log will not grow with full recovery model) - asynchronous
The instance would fail over (set it up to be automatic) between nodes 1 & 2, manual fail over to node 3 - and never fail over to node 4.
So i am thinking the AG listener is between node1, node2 and node3 and never fail over to node 4. So in that case CNAME should be pointing to a Node4 instead of AG listener? Do you see any problem with this configuration. Does Windows AD admin would make CNAME changes? Thanks in Advance!
May 12, 2020 at 3:00 pm
Please start new threads for new questions.
Always use DNS names for connections. This makes things much simpler in testing and moving around your routing to different machines. This is a layer of abstraction with the CNAME that allows a name to point to a different IP. This is typically a networking function. Sometimes Windows Admins work with DNS, sometimes not. Depends on your infrastructure.
May 13, 2020 at 4:44 pm
My understanding is that database name can stay the same, dns alias would be for the server name right?
May 13, 2020 at 5:13 pm
The DNS Alias is for the IP, which connects you to the endpoint.
May 15, 2020 at 4:01 pm
Thanks Steve. Just to clarify currently the application is pointing to Alias, this will be side by side upgrade, once the upgrade is done the DNS alias will be upgraded with the new IP and the database can be same whatever was in the old server unless we decided to change the name right? Thanks
May 15, 2020 at 4:25 pm
database names can be the same. These are only namespaced inside the instance.
May 20, 2020 at 11:33 am
Something to remember if aliasing - it can become VERY confusing for a new starter. I've been dropped into an environment where some servers used aliased names and struggled to find the servers until I realised what was happening! DNS aliasing is a better way than SQL client - partly because it's more transparent, partly because it works universally rather than on a local machine. Temporary workarounds have a horrible tendency to become permanent spaghetti!
June 29, 2020 at 5:25 pm
Currently I have alias pointing to our current server. However, I am planing to use the same alias name after the upgrade. So the question is how does the testing would be take place against the new server. Would users needs to use database server name until the alias is configured to the new server after the upgrade?
July 6, 2020 at 6:22 pm
Any thoughts?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply