SQL Server AlwaysOn Architecture and Step by Step Installation -3 Manual Fail Over Steps

Hi,

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.

 

 

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *