SQL AlwaysOn

Moonlight 176 Reputation points
2020-10-31T08:57:15.763+00:00

Hello

I have SQL AlwaysOn installed and configured and i want to add more replica to my SQL AlwaysOn but i have huge DB size so if i go with autoseed maybe i will face some issues , i have red we can backup and restore on the new replica then initiate the synchronization, please how can i go with this approach

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,820 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shashank Singh 6,246 Reputation points
    2020-10-31T13:08:16.79+00:00

    but i have huge DB size so if i go with autoseed maybe i will face some issues

    I did auto seeding for a 3 TB replica and believe me if you consider total time in backup, copy and restore I have personally seen auto seeding outperforming it by 1-2 hours. but the catch here is the storage need to to FAST storage for auto seeding to work. The only drawback is the DMV which shows auto seeing status is not "totally" accurate.

    i have red we can backup and restore on the new replica then initiate the synchronization, please how can i go with this approach

    That is quite easy. You backup the replica on shared folder/drive and the restore will also use this shared folder. You need to make sure that the shared folder is accessible to ALL the replicas. For more details see select-initial-data-synchronization-page-always-on-availability-group-wizards. If you are using SSMS you would see below screen where u have to specify shared storage

    36551-ds.png

    1 person found this answer helpful.
    0 comments
  2. Cris Zhan-MSFT 6,606 Reputation points
    2020-11-02T08:05:49.227+00:00

    Hi @Moonlight ,

    >i have red we can backup and restore on the new replica then initiate the synchronization, please how can i go with this approach

    Restore a recent database backup of the primary database and subsequent log backups onto the server instance that hosts the secondary replica, using RESTORE WITH NORECOVERY. Then you can choose the "Join only" option on the Select Initial Data Synchronization page when using the Add Replica to Availability Group Wizard.

    For more details please refer to the following article.
    https://www.mssqltips.com/sqlservertip/4598/add-secondary-replica-to-existing-sql-server-alwayson-availability-group/

    Hot issues in October—Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

    0 comments