Update server’s modeConstraint to host only Secondary Copies
Problem :
You have a cluster of 5 Servers and all of the servers are hosting PRIMARY Databases. Now, you want to make 2 servers to host only secondary databases but running REALLOCATE DATABASE does not guarantee to make server of choice as Secondary. Also you do not want to perform database backup, restore option.
Solution :
The goal of this article is to REALLOCATE DATABASES such that 2 of the SERVERS out of 5 always host SECONDARY databases. There can be cases when you want to decrease the number of PRIMARIES and increase SECONDARIES or vice versa, this article will help in reallocating databases and update the Server's mode on the go.
- We are first going to add new servers to existing 3 PRIMARY servers , these new servers will show up in “Free” State after starting the services. Note that, we have not defined initial.server.mode_constraint for the new servers . So the new servers can host either PRIMARY or SECONDARY copy of database.
- Enable the two Free servers by running :
ENABLE SERVER <Server_ID>;
- Create a database “test” with 3 Primaries and 2 Secondaries. Note that Server4 (localhost:7647) and Server5 (localhost:7657) are PRIMARY for “test” database.
SHOW DATABASE test;
- Now, we wish to make Server 4 “localhost:7647” and Server 5 “localhost:7657” as SECONDARY servers and should host only SECONDARY copies of databases. In the above screenshot, “localhost:7647” is primary for the “test” database. So we need to reallocate databases such that 3 PRIMARY and 2 SECONDARY can be maintained for the “test” database. The REALLOCATE DATABASES command does not guarantee that Server 4 “localhost:7647” and Server 5 “localhost:7657” will always host the SECONDARY copies.
To achieve this, we need to update the modeConstraint of Server 4 “localhost:7647” and Server 5 “localhost:7657”. If this server is already hosting some databases then you cannot alter the modeConstraint until the topology of databases is satisfied.
To satisfy the original topology( 3 primary 2 secondary) of databases (for ex- “test”) , we first need to alter the topology of the database such that PRIMARY can be removed from Server 4 and Server 5. Note - we will alter topology again after updating the modeConstraint of the server.
Here we alter the topology to have 3 PRIMARIES and 0 SECONDARY
ALTER DATABASE test SET TOPOLOGY 3 PRIMARIES 0 SECONDARY;
- Now, we need to deny the databases to be hosted on Server 3 and Server 4 by running
ALTER SERVER "<SERVER_ID>" SET OPTIONS{deniedDatabases:'test'};
- Run SHOW DATABASES; and you will see that “test” database now have 3 PRIMARIES and it is not hosted on Server4 and Server5
- You can now alter the modeConstraint of Server4 and Server5 as seen in the screenshot below-
ALTER SERVER <"SERVER_ID"> SET OPTIONS{modeConstraint: 'SECONDARY'}
- Alter the database topology to have 3 PRIMARY 2 SECONDARY
ALTER DATABASE test SET TOPOLOGY 3 PRIMARIES 2 SECONDARIES
- Now, run SHOW DATABASE test; and see that SECONDARY are only available on Server4 and Server5.
Comments
0 comments
Please sign in to leave a comment.