I will continue to explain What is the SQL Server AlwaysOn Architecture and Step by Step Installation in this article series.
Read the previous article of SQL Server AlwaysOn before this.
As in the previous article Once all the steps have been completed, right-click the Availability Groups to monitor Availability Replicas, Availability Databases, and Availability Group Listeners via Object Explorer and select the Show Dashboard as follows.
As seen from the Dashboard above, SQLC1 is Instance Primary, SQLC2 Instance Secondary and Failover Mode is Automatic and there is no problem with synchronization.
I will make an example in our TESTDB for AlwaysOn that it will insert data and synchronized to the Secondary side. Let’s go to do the following steps.
use TESTDB create table TestTable( col1 int identity(1,1) not null, col2 datetime null ) use TESTDB alter table TestTable add constraint DF_TestTable_col2 default (getdate()) for col2 go use TESTDB set nocount on go while 1=1 begin insert TestTable values(default) waitfor delay '00:00:05' end
While inserting data into SQLC1 which is primary, when we query from SQLC2 as below, the data synchronizes to SQLC2 almost instantaneously. The most important contribution in this real time synchronization is the fact that servers are side by side on the same block in the Network and there are no wait events in the network.
In SQL Server AlwaysOn, we showed that the data synchronized to the Secondary instantly. Now let’s do a manual failover and see the Failover case. You can use this method during server maintenance etc. during any maintenance on the primary server.
Run the Start Failover Wizard as follows from the AlwaysOn dashboard. Check for the last time before starting SQLC1 in Primary state, SQLC2 in Secondary state.
In our scenario, since SQLC1 is primary and SQLC2 is Secondary, select and connect secondary as follows.
Click Next in this step.
Started Manual Failover.
Manual Failover is completed successfully.
SQLC1 is now in the Secondary state and SQLC2 is in the Primary state as follows.
Once the AlwaysOn listener name or IP is used, this Failover occurs in seconds, so it is not very noticeable and the downtime in the application will be minimal.