How to Remove Active/Passive node from SQL Server 2016 Failover Cluster with Demo

How to Remove Active/Passive node from SQL Server 2016 Failover Cluster


Active and Passive Node:


Active Passive Node SQL Services will be running on One Node only

that means you should install SQL Server on first Node then you will add second Node to the first Node

so when failover occur it will move to other node

Active/Active or Active/Passive Clustering

SQL Server clusters are often referred to as either being Active/Active or Active/Passive.  When the terminology is used correctly this simply means that either all nodes (servers) participating in a Microsoft SQL Server cluster are dedicated to running at least a single SQL instance (Active-Active) or at least one of these nodes is reserved as a standby to accept failover of a SQL instance if one occurs.  You could very well have an Active-Active-Active-Passive cluster, in the case of my production environment before the very long day I spoke of above; it's really still just classified as Active-Passive.  That's it; quite simple in today's world of convoluted technical terminology.  I do hope you're not disappointed.  Thank you for reading this tip and goodbye...

Ah, but now for the more challenging question of "When to use Active-Active or Active-Passive architecture in your SQL Server environment."  Knowing the terminology only takes you so far.  To answer this question, we need to look at why one would not always use Active-Passive architecture.  Sadly, the answer is typically going to be cost. 

Active-Active Clustering Considerations

When we created our first Microsoft SQL Server 2005 cluster we did so with two nodes; an instance of SQL Server 2005 Enterprise Edition running on each node.  This was also a 64-bit environment and had substantial amounts of RAM (though not nearly enough we would come to find out.)  There was a terabyte of SAN dedicated for the cluster.  This was a pricey consumer of space in our data center.  The purpose of this cluster was not for High Availability (HA) as a Microsoft SQL Server cluster is typically designed, but rather for a mass consolidation effort.  At the time I supported over 60 instances of SQL 2000 and about 10 SQL Server 2005 instances.  We were able to tap into our capital fund for this new cluster in order to hopefully consolidate fifteen of these instances into the two instances that were to be hosted on the cluster.  The purposes of the consolidation effort:  reduced administrative overhead at both the hardware and database layers, upgrade of hardware that had reached the end of their support agreements, reduced licensing costs, and increased productivity for the Database Administrator. 

However, as many of you have experienced in your organizations, many of the vendor-created databases we purchased were not only slow to adopt Microsoft SQL Server 2005, but also slow or unwilling to apply resources in their companies to certify that their databases and applications could run in 8.0 compatibility mode on a SQL 2005 instance.  Furthermore, we even had select vendors tell us that they would not support a database running in a clustered environment.  We ended up with our clustered environment for SQL 2005 hosting only a fraction of our SQL databases from those we targeted for consolidation.  Maximum CPU never exceeded 20% and memory requirements never reached half of the 16gb of RAM installed for each server (node).  Our situation was the same as for many others who choose to cluster in an Active-Active fashion:  we had limited funds available for the clustering initiative, and/or our actual or estimated load on the SQL servers would not consume more than half the resources for each node. 

I think this is the right time to make an important note that has not been discussed to this point:  just because a node in a clustered environment is already hosting a SQL instance (acting as an Active node, in other words) does not preclude it from hosting another SQL instance.  In fact, the server behaves no differently in this respect as it does in a non-clustered environment.  A server can host multiple instances of SQL Server:  a single default instance, and multiple additional named instances.  Your only limits are the edition of Microsoft SQL Server, and of course, system resources.

In our Active-Active configuration we were able to achieve two important goals:  consolidation, to some extent was achieved as was high-availability.  Though we had no passive node in our cluster, we could safely fail over either instance so that both were running on the same physical server and not over-taxing the available resources of the server.  Our SQL instances were configured to use a minimum of 4gb of RAM, and a maximum of 6gb.  These settings reserved memory for the O/S and other non-SQL processes and also ensured that if a failover occurred the server could continue to handle the load of both instances at the levels of utilization we had monitored to date.

If you have the resources available to support the SQL instances in your cluster in this manner, without the need to maintain a passive node, then this architecture may be affective for you.  However, this means that you are most-likely not utilizing the SQL instances to their full potential, or if you are that you've over-purchased hardware or memory for the workload involved.  Essentially, it means you've not spent your money efficiently (unless this was your intention.)  Most organizations end up with Active-Active SQL Server clusters not out of design, but out of circumstance.  They may start out with a SQL Server cluster with a single SQL instance and a passive node ready for failover, but find that they are directed to add a new instance to the cluster for some "hot" project that did not have funding for hardware (been there, bought the refrigerator magnet) or have under-estimated their consolidation effort and are forced to add a new instance to the cluster with the promise of a new passive node the next time funds are made available.  (Hey look, a t-shirt to go with that fridge magnet!)  I do hope I've made my point that Active-Active clustering may have it's role, but it usually means you're forgoing better utilization of your hardware during the majority of time when the SQL instance(s) are running properly.

Active-Passive Clustering Considerations

High-Availability technically means that your SQL instances are available for user consumption of data.  I would prefer to refine this definition and state that the data should not just be readily available, but also responding to user requests at a rate that are consistent with regular performance benchmarks.  This means that your cluster failover process needs to allow for consistency in hardware between the nodes in your cluster. 

Best practices state that all nodes in a cluster should be identical in terms of hardware, memory, and SQL Server editions.  In the event of a failover in a cluster with a passive node, the SQL instance's ownership is switched to the passive node.  After a brief period of downtime, typically around 10 seconds, the SQL instance should be running in an identical state of performance as it did on the original owner node (if the physical specifications for each node are identical.)  You would configure the Microsoft SQL Server 2005 instance to utilize more memory then in an Active-Active architecture since you will only be running a single instance of SQL on a node at any given time. 

I still strongly suggest reserving 4gb of RAM for the O/S and non-SQL operations of the server.  You can add more load to the SQL instance on the active node in an Active-Passive cluster versus a similarly-designed active node in an Active-Active cluster.  Your utilization of resources on the active node will be much more economical as a result.  In our environment we also utilize our passive node to host the resources for the backup volume on our clustered environment.  We utilize SQL native tools to backup to the SAN volume and have a third-party tool that writes these disk backups to tape on a nightly basis.  The overhead associated with this process occurs on the passive node.  In the event of a failover we have the ability to reschedule this process to a low point in SQL activity if we find contention for resources. 

There is an important trade-off for the benefits of high-availability and consistency of performance.  You must purchase and support a server that will hopefully never be used in production.  A final item of note in regards to passive nodes:  Microsoft does not require you to license the passive node of SQL Server 2005 in an Active-Passive clustered environment.

Further Considerations

You must understand that no matter which architectural plan is used in your environment, you still end up with only a single instance of your data and the SQL Server objects (logins, Agent jobs, etc.) on your clustered instance.  Unlike other forms of high-availability design afforded to the Microsoft SQL Server DBA, clustering only provides the means to ensure you are protected from server hardware failure.  It is strongly recommended that you incorporate some form of redundancy into your disaster recovery plan and not rely on clustering alone.  Such options include SAN redundancy products, database mirroring, log shipping, or replication to protect you against media failure on your SAN for which clustering fails to protect.

Demo:

Server manager we can see the Node01 information

Here you can Node01 SQL Server is running it should be active node and owner node also shows as Node01

you can see Node 01 and Node 02 info here

Disk info here

Node 01 SQL Server is running we can see from Configuration manager

here it passive node that is NODE02

On Node01 Launch SQL setup file

Now you can see Maintenance on left side

Now click Remove Node from SQL Server failover cluster

Now show validation status

it shows name of this node

Now click Remove

Node Removal is in progress

SQL Node 01 SQL Server is removed

Repeat the same steps on Passive Node Node 02

we can confirm SQL service status both node

Launch failover cluster manager

Now we can right click on role and remove it from cluster

To generate SQL Server Health Check Report via Database Mail in SQL

To generate SQL Server Health Check Report via Database Mail in SQL


Step 1:

we need to create following tables on SQL Server db

CREATE TABLE [dbo].[backup_healthcheck](
[server_name] [varchar](100) NOT NULL,
[database_name] [varchar](100) NOT NULL,
[LastFullBackup] [datetime] NULL,
[LastDifferential] [datetime] NULL,
[LastLog] [datetime] NULL
)



CREATE TABLE [dbo].[DB_status](
[server_name] [varchar](100) NOT NULL,
[name] [varchar](100) NULL,
[state_desc] [varchar](50) NULL
) 


CREATE TABLE [dbo].[disk_details](
[server_name] [varchar](100) NOT NULL,
[Disk] [varchar](100) NOT NULL,
[FreeSpaceInGB] [varchar](100) NULL,
[TotalSpaceInGB] [varchar](100) NULL,
[FreeSpaceInPct] [varchar](100) NULL
)



CREATE TABLE [dbo].[Job_Details](
[Server_Name] [varchar](100) NOT NULL,
[Job_Name] [varchar](300) NOT NULL,
[Category] [varchar](400) NULL,
[Job_enabled] [varchar](10) NOT NULL,
[last_run_date] [varchar](100) NULL,
[last_run_status] [varchar](200) NULL
)

Step2:

Need to create Database  DB_Admin on SQL Server

Step 3:

Need to create SP from following script on DB_Admin db

CREATE  procedure [dbo].[usp_mon_Health_status_of_all_servers]      
      
       
      
as      
      
       
      
begin      
      
       
      
declare @sql nvarchar(4000)      
      
       
      
declare @dbs nvarchar(4000)      
      
       
      
declare @jobs nvarchar(4000)      
      
       
      
declare @disk nvarchar(4000)      
      
       
      
declare @return_code int      
      
       
      
declare @last_backup_date datetime      
      
       
      
       
      
declare @server_name sysname      
      
       
      
       
      
       
      
declare servers_cursor cursor for      
      
       
      
       
      
select srvname from master.dbo.sysservers where srvname not in ('repl_distributor','LOGSHIPLINK_USER-PC\MSSQL2012DESTSQL_-264832719')--('USER-PC\MSSQL2012SOURCE')      
      
      
order by srvname      
      
       
      
       
      
delete from backup_healthcheck      
      
       
      
       
      
delete from db_status      
      
       
      
       
      
delete from Job_Details      
      
       
      
delete from disk_details      
      
       
      
       
      
open servers_cursor      
      
       
      
fetch servers_cursor into @server_name      
      
       
      
while @@fetch_status = 0      
      
       
      
begin      
      
       
      
        set @sql = ''      
      
       
      
                   set @sql = 'insert into backup_healthcheck      
      
       
      
                   SELECT server_name = '''  + @server_name + ''',sdb.name,      
      
       
      
       
      
    MAX(CASE WHEN backupset.type = ''D'' THEN backupset.backup_finish_date ELSE NULL END) AS LastFullBackup,      
      
       
      
  MAX(CASE WHEN backupset.type = ''I'' THEN backupset.backup_finish_date ELSE NULL END) AS LastDifferential,      
      
    MAX(CASE WHEN backupset.type = ''L'' THEN backupset.backup_finish_date ELSE NULL END) AS LastLog      
      
                FROM [' + @server_name + '].master.sys.sysdatabases sdb      
      
       
      
LEFT OUTER JOIN [' + @server_name + '].msdb.dbo.backupset ON backupset.database_name = sdb.Name      
      
       
      
       
      
where sdb.name not in (''tempdb'',''ReportServer'',''ReportServerTempDB'')      
      
       
      
GROUP BY sdb.name      
      
       
      
ORDER BY sdb.name DESC'      
      
       
      
--print @sql      
      
        exec sp_executesql @sql      
      
       
      
       
      
--set @disk = ''      
      
--set @disk = 'insert into disk_details select distinct server_name = '''  + @server_name + ''',dovs.volume_mount_point AS Drive,      
      
--CONVERT(decimal,dovs.available_bytes/1048576/1024) AS FreeSpaceInGB,      
      
--convert(decimal,dovs.total_bytes/1048576/1024) as TotalSpaceInGB,      
      
--cast(CONVERT(decimal,dovs.available_bytes/1048576/1024)/convert(decimal,dovs.total_bytes/1048576/1024)*100 as decimal(38,2)) as FreeSpaceInPct      
      
--FROM [' + @server_name + '].master.sys.master_files mf      
      
--CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs'      
      
       
      
--        exec sp_executesql @disk      
      
       
      
       
      
set @dbs = ''      
      
       
      
set @dbs = 'insert into db_status select server_name = '''  + @server_name + ''',name,state_desc,getdate()       
      
       
      
       
      
       
      
FROM [' + @server_name + '].master.sys.databases order by name'      
      
       
      
--print @dbs      
      
       
      
       
      
       
      
exec sp_executesql @dbs      
      
       
      
       
      
       
      
set @jobs = ''      
      
       
      
set @jobs = 'insert into job_Details select server_name = '''  + @server_name +''',sj.name as job_name, sc.name as Category, sj.Enabled,      
      
       
      
        (SELECT top 1 endTime = CONVERT      
      
       
      
     ( DATETIME, RTRIM(run_date)) + (  run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 + 25 * run_duration) / 216e4      
      
       
      
FROM [' + @server_name + '].msdb.dbo.sysjobhistory sjh where sjh.job_id = sj.job_id order by run_date desc, run_time desc) as last_run_date,      
      
       
      
CASE sjs.last_run_outcome      
      
       
      
WHEN 0 THEN ''Failed''      
      
       
      
WHEN 1 THEN ''Successful''      
      
       
      
WHEN 3 THEN ''Cancelled''      
      
       
      
WHEN 4 THEN ''In Progress''      
      
       
      
END AS LastRunStatus      
      
       
      
from [' + @server_name + '].msdb.dbo.sysjobs sj      
      
       
      
join [' + @server_name + '].msdb.dbo.syscategories sc      
      
       
      
        on sj.category_id = sc.category_id      
      
       
      
join [' + @server_name + '].msdb.dbo.sysjobservers sjs      
      
       
      
        on sjs.job_id = sj.job_id'      
      
       
exec sp_executesql @jobs      
      
  fetch servers_cursor into @server_name      
       
      
--print @jobs      
      
       
      
            
      
      
      
   Declare @emailSubject VARCHAR(100) ,@Body varchar(100),@sub varchar(1000)      
      
       
      
         set @sub='SQL Server Health Check Report for SQL Server'       
      
      
DECLARE @tableA_HTML  NVARCHAR(MAX) ;      
DECLARE @tableB_HTML  NVARCHAR(MAX) ;      
DECLARE @tableC_HTML  NVARCHAR(MAX) ;      
      
DECLARE @HTML  NVARCHAR(MAX) ;      
      
SET @tableA_HTML =      
    N'<H1>DB_status:</H1>' +      
    N'<table border="1">' +      
    N'<tr><th>server_name</th><th>name</th><th>state_desc</th><th>Status_date</th>     </tr>' +      
    CAST ( ( SELECT td =  server_name , '',      
                    td =  name , '',      
                    td =  state_desc, '',      
     td =   Status_date , ''       
              FROM DB_Admin.dbo.DB_status           
              FOR XML PATH('tr'), TYPE       
    ) AS NVARCHAR(MAX) ) +      
    N'</table>' ;      
      
       
SET @tableB_HTML =      
    N'<H1>Job_Details:</H1>' +      
    N'<table border="1">' +      
    N'<tr><th>Server_Name</th><th>Job_Name</th><th>Category</th><th>Job_enabled</th>  <th>last_run_date</th>       
 <th>last_run_status</th>  </tr>' +      
    CAST ( ( SELECT td =  Server_Name , '',      
                    td =  Job_Name , '',      
                    td =  Category, '',      
     td =  Job_enabled , '' ,      
     td =  last_run_date , '' ,      
     td =  last_run_status , ''       
              FROM DB_Admin.dbo.Job_Details           
              FOR XML PATH('tr'), TYPE       
    ) AS NVARCHAR(MAX) ) +      
    N'</table>' ;      
      
       
SET @tableC_HTML =      
    N'<H1>backup_healthcheck:</H1>' +      
    N'<table border="1">' +      
    N'<tr><th>server_name</th><th>database_name</th><th>LastFullBackup</th><th>LastDifferential</th>        
    <th>LastLog</th>       
       </tr>' +      
    CAST ( ( SELECT td =  server_name , '',      
                    td =  database_name , '',      
                    td =  LastFullBackup, '',      
     td =   LastDifferential , '' ,      
     td =   LastLog , ''       
              FROM DB_Admin.dbo.backup_healthcheck           
              FOR XML PATH('tr'), TYPE       
    ) AS NVARCHAR(MAX) ) +      
    N'</table>' ;      
      
      
      
      
SET @HTML = @tableA_HTML + ' <br/> ' +  @tableB_HTML + ' <br/> ' +  @tableC_HTML + '' ; -- Line Break      
SET @HTML = REPLACE(@HTML,'<th>','<th  bgcolor="Orange"/>' ); -- For BackGround Color       
      
      
      
   
      
      
      
end      
      
       
      
close servers_cursor      
      
       
      
       
      
deallocate servers_cursor      
      
       
 EXEC msdb.dbo.sp_send_dbmail      
      
    @recipients='servername@servermail.com;',      
      
    @profile_name = 'dbmailProfile',      
      
    @subject = @sub,      
      
    @body = @HTML,      
      
       @body_format = 'HTML'      
       
      
end      

Step 4:

Need to create SQL  Jobs

USE [msdb]
GO

/****** Object:  Job [DB_server_health_job]   
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 29-11-2017 18:01:30 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DB_server_health_job',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'USER-PC\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step 1]    Script Date: 29-11-2017 18:01:30 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec db_admin.dbo.usp_mon_Health_status_of_all_servers',
@database_name=N'DB_Admin',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Once a day',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20170911,
@active_end_date=99991231,
@active_start_time=115900,
@active_end_time=235959,
@schedule_uid=N'351f1f71-4248-400e-8a7f-60e5d2ea98b1'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO



Step 5:

Need to Create linked server and configure Database Mail



Step 6:

Run the job to get the report