Hi,

I will continue to explain What is the SQL Server AlwaysOn Architecture and Step by Step Installation in this article series.

SQL Server AlwaysOn

Read the previous article of SQL Server AlwaysOn before this.

SQL Server AlwaysOn ( Availability Group ) Architecture and Step by Step Installation -2

AlwaysOn Availability Group Install

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.

You can access the Next posts related with SQL Server Always On Installation with the following link.

https://ittutorial.org/sql-server-alwayson-architecture-and-step-by-step-installation-4-add-and-remove-database-steps/

Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.

https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/