I’m operating a 2-node SQL Server 2016 AlwaysOn Availability Group. I wish to create a non-clustered index on-line in SSMS for one of many availability databases within the AlwaysOn Availability Group. For this operation, it is extremely necessary that the index create be On-line; in different phrases, the desk mustn’t develop into locked and each reads and writes ought to proceed with out interruption or error throughout this time. Additionally, the cluster mustn’t go into an unhealthy state the place replication stops.
Right here was my plan for doing this:
Go into SQL Server Administration Studio (SSMS) on the grasp node, go to the database, desk after which Indexes part in Object Explorer and right-click on Indexes and select New Index – Non-Clustered
Add the suitable subject to the Index Key columns display
Go to Choices – and set “Permit on-line DML processing” to “True”
Click on OK
Is the above method one of the best ways to do that? Will this really create the index on-line? Whereas the index is being created, will the cluster stay in a wholesome and replicated state, will the index get created on the secondary mechanically, and can the incoming reads and writes proceed to occur with out errors?
Any assist with that is drastically appreciated!