March 2, 2018 at 4:24 pm
I have a database which is part of Availability Group across 3 servers. All servers are at same patch i.e SP1 + CU7 SQL 2016. I am noticing two different issues.
Scenario 1 -
All 3 nodes are set to synchronous mode and auto failover. If i add a new database an existing AG group with auto seed, the database doesn't show up at all on 2 other nodes.
Scenario 2 -
Of the 3 nodes, 2 are in synchronous and 1 in asynchronous. If i add a new database to existing AG group, database would show on the the node which is set to synchronous and won't show up on the async node.
Wondering if other DBA's have experienced this. TIA
March 5, 2018 at 6:26 am
curious_sqldba - Friday, March 2, 2018 4:24 PMI have a database which is part of Availability Group across 3 servers. All servers are at same patch i.e SP1 + CU7 SQL 2016. I am noticing two different issues.Scenario 1 -
All 3 nodes are set to synchronous mode and auto failover. If i add a new database an existing AG group with auto seed, the database doesn't show up at all on 2 other nodes.Scenario 2 -
Of the 3 nodes, 2 are in synchronous and 1 in asynchronous. If i add a new database to existing AG group, database would show on the the node which is set to synchronous and won't show up on the async node.
Wondering if other DBA's have experienced this. TIA
Regarding Scenario 1, do you mean ever? Are you using the GUI or scripting it? Anything in the error log? Any blocking on the other instances at the time of the seeding attempts?
March 5, 2018 at 8:06 am
curious_sqldba - Friday, March 2, 2018 4:24 PMI have a database which is part of Availability Group across 3 servers. All servers are at same patch i.e SP1 + CU7 SQL 2016. I am noticing two different issues.Scenario 1 -
All 3 nodes are set to synchronous mode and auto failover.
Only 2 nodes may be set to auto failover at any time!
curious_sqldba - Friday, March 2, 2018 4:24 PM
If i add a new database an existing AG group with auto seed, the database doesn't show up at all on 2 other nodes.
How large is the database?
Have you tried manually initialising the secondary database on the secondaries?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2018 at 9:01 am
Perry Whittle - Monday, March 5, 2018 8:06 AMcurious_sqldba - Friday, March 2, 2018 4:24 PMI have a database which is part of Availability Group across 3 servers. All servers are at same patch i.e SP1 + CU7 SQL 2016. I am noticing two different issues.Scenario 1 -
All 3 nodes are set to synchronous mode and auto failover.Only 2 nodes may be set to auto failover at any time!
curious_sqldba - Friday, March 2, 2018 4:24 PM
If i add a new database an existing AG group with auto seed, the database doesn't show up at all on 2 other nodes.How large is the database?
Have you tried manually initialising the secondary database on the secondaries?
Point noted about 2 nodes . Dbsize is less than 100 MB. I could try manually but defeats the purpose of auto seed.
March 5, 2018 at 9:08 am
curious_sqldba - Monday, March 5, 2018 9:01 AMPerry Whittle - Monday, March 5, 2018 8:06 AMcurious_sqldba - Friday, March 2, 2018 4:24 PMI have a database which is part of Availability Group across 3 servers. All servers are at same patch i.e SP1 + CU7 SQL 2016. I am noticing two different issues.Scenario 1 -
All 3 nodes are set to synchronous mode and auto failover.Only 2 nodes may be set to auto failover at any time!
curious_sqldba - Friday, March 2, 2018 4:24 PM
If i add a new database an existing AG group with auto seed, the database doesn't show up at all on 2 other nodes.How large is the database?
Have you tried manually initialising the secondary database on the secondaries?Point noted about 2 nodes . Dbsize is less than 100 MB. I could try manually but defeats the purpose of auto seed.
do the file paths match between the instances?
any errors in the logs, etc?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2018 at 3:04 pm
If i put the two nodes ( Primary and Secondary ) in Synchronous mode with Auto failover and 3rd node with Asynchronous with Manual failover, now it seems to work. When i create a new database on primary, take full backup and add in AG through GUI it does show up on other nodes.
Looks like there will need to be a at-least one full backup taken before adding database in AG. I thought auto-seeding would just do the backup portion also.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply