We want migrate to sql 2019 and I did the test and set up Hight availability with twho 2 nodes,but and the set up of AG I have four option, I gave that option a shared path for the transaction log but I saw that a transaction log was generated, which increased space in the same transaction log every time they made updates and uploads to the database. Which one would be recommended for a production environment and at the same time how to manage the transaction log? Should I implement a log shipping strategy with always high availability Group 2019? which is the best option?
As with most things SQL, best is variable and highly dependent on your environment.
If all of my databases were small, then I would choose Automatic Seeding.
Otherwise, I would use Join Only or Skip Initial Data Synchronization (they are really the same, it's if you have done the prep work PRIOR to this step or if yo u want to do it later.
For medium to largish databases, my steps would be:
- Restore a FULL database to the secondary
- Continually restore LOG files to the secondary.
- STOP new log backups on primary (temporary)
- Restore last log backup to secondary (with NORECOVERY)
- Join to Availability Group
- Resume log backups on primary