May 13, 2022 at 5:07 pm
We are in the process of migrating to new Windows AD. What would be the best approach for this with minimal down time? Essentially we have several databases in AG on different nodes and a single listener name. The idea is to move to new Windows AD/new servers and retain the listener name. Normally if it were within the same domain I would just build a new node and attach it to the existing cluster/AG and failover, but because we have a new domain it appears that I cannot do that even when the trust between two domains exists.
May 14, 2022 at 7:39 am
Unsure if a distributed availability group works cross domains but would be a possibility.
Then destroy the DAG at migration time and then recreate the listener on the new side as part of migration activities.
Otherwise as a normal migration, migrate and use cnames instead or again change the listener at migration time
May 14, 2022 at 3:17 pm
This is fairly well documented here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15&tabs=automatic
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
May 23, 2022 at 6:37 am
I am assuming you want to retain the same listener name because it is referenced in connection strings, and that you are running on-prem. I will give an alternative approach that will require you to do a one-off change to connection strings but will set you up for a more flexible future.
Life as a DBA becomes a bit easier if you exploit DNS aliases, especially if you use both application and BCO aliases to manage the logical and physical DB server topologies.
1) Set up a DNS alias for every AG listener. This will be the BCO alias and should point to the listener name.
2) Set up DNS aliases for each group of databases. For example if you have some databases that form a CRM system and another set that form a finance system and they could (or are) be hosted on separate DB servers then set up a CRM alias and a finance alias. Each of these application aliases should point to the BCO alias for the relevant AG listener.
3) Change all your connection strings so they point to the relevant application alias. Your applications will connect to your databases via the DNS aliases and the AG listener
The end result is that you have separated the logical and physical DB server topologies. You can split or consolidate your database groups across whatever DB servers you want, changing where relevant DNS aliases point to, but (importantly!) without changing any more connection strings. The connection request is resolved by DNS first to your BCO alias and then to the listener name. I have not noticed any connection time delay in having a two-level DNS alias hierarchy.
If this is related to the OP situation, moving to a new DB server is now fairly easy. a) Set up a distributed AG (dAG) between the old and new servers. b) At cut-over time do the failover of the dAG and at the same time change the relevant BCO alias to point to the new listener.
Simples. But make sure you test everything. Hopefully you will have a Dev environment with the same topology as Production. Plan and test and implement and revert and troubleshoot with Dev before you touch Production.
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
May 23, 2022 at 7:08 am
If you have SSRS or PowerBI on-prem then aliases can also make life easier for these.
The idea here is to have a stable connection string for your end users, but allow the DBA to move the reporting system to wherever convenient. BTW if you are using Enterprise Edition with a SA license and still use SSRS then you should plan to replace it with PowerBI server. It give you improved functionality for no extra cost.
1) Install IIS on every DB server that hosts a reporting system
2) Create a Default Document within IIS to re-route a non-specific HTTP(S) request to your reporting system URL.
3) Create a DNS alias that your users will use to connect to reporting. This should point to the BCO alias for the server set up in my previous post.
4) Update the rs server config files so the reported internal and external rs server URLs match the alias name. Also do whatever changes are needed for HTTPS if you use that.
5) Tell you report users to use your reporting DNS alias. For example if it is called acmereports they just need to type //acmereports
You now have separate logical and physical report topologies. You can move where the report databases are hosted simply by changing the target of the report alias your users use.
Behind the scenes what is happening is this:
a) Your users type //acmereports which is resolved by DNS to route to the AG listener name that hosts the report databases
b) The AG listener ignores the HTTP request
c) The IIS running on the server that hosts the active AG listener node picks up the HTTP request
d) The default document changes the non-specific HTTP request to a specific one targeted at your report server
e) Your report server picks up the request and processes it
This does have a few moving parts but it does give end user connection stability to reporting, regardless of what you do with the physical environment.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply