SQL Server: Failover Cluster

Hi, My name is Jimmy Sam. If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.

top   prev   next  

SQL Server Windows Cluster Setup


Windows Clustering Step By Step 

Brentozar: setup windows cluster, step by step 

FCI: Failover Cluster Step-by-Step Guide: Configuring a Two-Node File Server Failover Cluster 

Brentozar: Setup windows cluster and sql ag in standard edition 


top   prev   next  

Enable SQL Server Availability Group At Each Instance


enable/disable availability group 

alwayson ag in multisubnet 

rem -------------------------------------------------------------------
SQL Server Configuration Manager

SQL Server 2016 	C:\Windows\SysWOW64\SQLServerManager13.msc

SQL Server 2014 	C:\Windows\SysWOW64\SQLServerManager12.msc

SQL Server 2012 	C:\Windows\SysWOW64\SQLServerManager11.msc

SQL Server 2008 	C:\Windows\SysWOW64\SQLServerManager10.msc

rem -----------------------------------------------------------------

In SQL Server Configuration Manager, click SQL Server Services, 
right-click SQL Server (), 
where  is the name of a local server instance 
for which you want to enable Always On Availability Groups, and click Properties.

Select the Always On High Availability tab.

Verify that Windows failover cluster name field contains the name of the local failover cluster. 
If this field is blank, this server instance currently does not support Always On availability groups. 
Either the local computer is not a cluster node, the WSFC cluster has been shut down, 
or this edition of SQL Server 2017 that does not support Always On availability groups.

Select the Enable Always On Availability Groups check box, and click OK.

powershell: 
Enable-SqlAlways  On  -Path SQLSERVER:\SQL\Computer\Instance 
Disable-SqlAlways On  -Path SQLSERVER:\SQL\Computer\Instance  

SQL Server Configuration Manager saves your change. 
Then, you must manually restart the SQL Server service. 
This enables you to choose a restart time that is best for your business requirements. 
When the SQL Server service restarts, Always On will be enabled, 
and the IsHadrEnabled server property will be set to 1

rem -------------------------------------------------------------------

Always On availability groups feature must have been enabled on the each instance of SQL Server 
that will host an availability replica for one or more availability groups. 

rem -------------------------------------------------------------------

Enable Always On Availability Groups on only one server instance at a time. 
After enabling Always On Availability 
wait until the SQL Server service has restarted before you proceed to another server instance. 

rem -------------------------------------------------------------------

While Always On Availability Groups is enabled on an instance of SQL Server, 
the server instance has full control on the WSFC cluster. 

rem ------------------------------------------------------------------

SELECT SERVERPROPERTY ('IsHadrEnabled');  

# powershell
get-item . | select IsHadrEnabled

Using SQL Server Management Studio

To determine whether Always On Availability Groups is enabled

In Object Explorer, right-click the server instance, and click Properties.

In the Server Properties dialog box, click the General page. 
The Is HADR Enabled property displays one of the following values:

True, if Always On Availability Groups is enabled

False, if Always On Availability Groups is disabled.


top   prev   next  

Show Dashboard


SSMS --> Object Explorer --> AlwaysOn High Availability -->
right-click on your Availability Group  --> click Show Dashboard
1): Start Failover Wizard
2): View Alwayson Health Events
3): View Cluster Quorum Information


top   prev   next  

SQL Server Failover Cluster


"Server Manager" --> Local Server --> Tools --> Failover Cluster Manager


top   prev   next  

SQL Server Availability Group Setup


Creation and Configuration of Availability Groups 

Use the Availability Group Wizard (SQL Server Management Studio) 

sqlpassion I 

sqlpassion II 

sqlpassion III 

sqlpassion IV 

Brentozar: Setup windows cluster and sql ag in standard edition 

FCI: Clustering SQL Server 2012 on Windows Server 2012 Step-by-Step  

SQL Server Alwayson ( 1 )  

SQL Server Alwayson ( 2 )  

SQL Server Alwayson ( 3 )  

SQL Server Alwayson ( 4 )  

Discussion: Windows Clustering 

In Chinese: SQL AG Alwayson In Windows Server 2012 WSFC 
_ 


top   prev   next  

SQL FCI


SQL FCI I 
SQL FCI II 
SQL FCI III 
SQL FCI IV 




top   prev   next  

FCI Quorum

quorum 

quorum without shared storage 

Implement a SQL Server HA failover solution without shared storage 



top   prev   next  

Service Account: Domain Admin

service account best practice: Group Managed Service Accounts 
Active Directory automatically updates the group managed service account password without restarting services


top   prev   next  

Disable jobs if it's on secondary replica


DECLARE @my_db_name sysname;

set @my_db_name = N'MYDB'
-- set @my_db_name = db_name();

-- disable jobs if it's on secondary replica
If master.sys.fn_hadr_is_primary_replica ( @my_db_name ) <> 1 
BEGIN

DECLARE @my_job_name nvarchar(128);
DECLARE curJobList  CURSOR fast_forward FOR
select name from msdb.dbo.sysjobs j 
where 
enabled = 1 and 
name in
(N'DB_Daily_2AM_CleanupDB');
open curJobList
fetch next from curJobList into @my_job_name;
while @@FETCH_STATUS = 0
begin
print @my_job_name
exec msdb.dbo.sp_update_job @job_name = @my_job_name, @enabled = 0;
fetch next from curJobList into @my_job_name;
end;
CLOSE curJobList;
DEALLOCATE curJobList;
END;

-- enable jobs if it's on primary replica
If master.sys.fn_hadr_is_primary_replica ( @my_db_name ) = 1 
BEGIN

DECLARE @my_job_name_primary nvarchar(128);
DECLARE curJobList_primary  CURSOR fast_forward FOR
select name from msdb.dbo.sysjobs j 
where 
enabled = 0 and 
name in
(N'DB_Daily_2AM_CleanupDB');
open curJobList_primary
fetch next from curJobList_primary into @my_job_name_primary;
while @@FETCH_STATUS = 0
begin
print @my_job_name_primary
exec msdb.dbo.sp_update_job @job_name = @my_job_name_primary, @enabled = 1;
fetch next from curJobList_primary into @my_job_name_primary;
end;
CLOSE curJobList_primary;
DEALLOCATE curJobList_primary;
END;