Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 30 November 2017

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










Wednesday, 29 November 2017

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

















Friday, 27 October 2017

How to Solve Monitoring LSAlert Job Failed as Log shipping Report shows Red Even copy and restore Job Succeeded with DEMO

How to Solve Monitoring LSAlert Job Failed as Log shipping Report shows Red Even copy and restore Job Succeeded With DEMO


What is SQL Server log shipping?

SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations 

A log shipping session involves the following steps:
  • Backing up the transaction log file on the primary SQL Server instance
  • Copying the transaction log backup file across the network to one or more secondary SQL Server instances
  • Restoring the transaction log backup file on the secondary SQL Server instances

Implementation examples

One of the common log shipping scenarios is the environment with two servers (SQLServer-1 – primary and SQLServer-2 – secondary), two SQL Server instances (SQLInstance-1 and SQLInstance-2), and one SQL Server database named SQLDB-1 with log shipping running on it
Common SQL Server log shipping scenarios - the environment with two servers
Another common configuration is the environment with three (or more) servers (SQLServer-1 – primary, SQLServer-2 – secondary, and SQLServer-3 – secondary), three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3), and one SQL Server database named SQLDB-1 with log shipping running on it
SQL Server Log shipping scenarios - The environment with three (or more) servers

Operating modes

There are two available modes and they are related to the state in which the secondary, log shipped, SQL Server database will be:
  • Standby mode – the database is available for querying and users can access it, but in read-only mode
    • The database is not available only while the restore process is running
      • Users can be forced to disconnect when the restore job commence
      • The restore job can be delayed until all users disconnect themselves
  • Restore mode – the database is not accessible

Advantages and disadvantages of using SQL Server log shipping

SQL Server log shipping is primarily used as a disaster recovery solution. Using SQL Server log shipping has multiple benefits: it’s reliable and tested in details, it’s relatively easy to set up and maintain, there is a possibility for failover between SQL Servers, data can be copied on more than one location etc.
Log shipping can be combined with other disaster recovery options such as AlwaysOn Availability Groups, database mirroring, and database replication. Also, SQL Server log shipping has low cost in human and server resources
The main disadvantages in the SQL Server log shipping technique are: need to manage all the databases separately, there isn’t possibility for an automatic failover, and secondary database isn’t fully readable while the restore process is running


Primary Server



Primary Server LogShipping Report


Secondary Server 



Secondary Server  LogShipping Report


Primary Server Job Info


Secondary Server Job Info


Here in Secondary server Monitoring Job is failing due to wrong scheduled restore job alert


Here copy job scheduled as 6 hours


Here Restore job scheduled as 6 hours but alert job scheduled as 45 mints it is conflict

To fix Need to schedule as 6 hours




After our change in alert now click submit to configured 



Now we are running job


It went success now


see status as success


that is now Red alert issue is fixed it




Happy Blogging


Thursday, 26 October 2017

Step by Step How to Setup Clustering Instance with Logshipping on SQL Server With Demo


How to Setup Clustering Instance with Logshipping on SQL Server With Demo

What is Clustering?

A Microsoft SQL Server Cluster is nothing more than a collection of two or more physical servers with identical access to shared storage that provides the disk resources required to store the database files. 
These servers are referred to as "nodes".  Each of the nodes talk to one another via a private network, sending a heartbeat signal between them.  Should one node not communicate its heartbeat to the other node in the cluster the secondary node will take ownership of any dependent services being run by the node that lost communication.  This process is referred to as "failover".
A failover can occur both automatically (a server's heartbeat stops communicating) or manually.  A manual failover is beneficial in the event that patching or some other form of maintenance is required at the physical server level.  You would typically implement clustering in order to ensure that if you ever encounter hardware failure on the physical server hosting your SQL instance, your databases would continue to be available for dependent applications and their users. 
Unlike other clustering technologies that are implemented for better performance or for increased processing power via load-balancing, SQL clusters are designed for providing highly-available databases; eliminating downtime associated with hardware failure.  This architectural concept is referred to as "High Availability Clustering" or "HA Clustering" for short.  The service or groups of services that are hosted on a clustered node are respectively referred to as resources and resource groups.  Since these resources must be available to all nodes in a cluster then they must reside on a shared disk array in the form of SAN-NAS disk.  Each resource group will be mapped to a logical drive that is physically hosted on the shared disk array and will also have it's own associated IP address and network name. 

The SQL Server Installation Process on a Cluster

The SQL Server installation process detects when an installation is being attempted on a clustered node and will prompt you as to whether you wish to configure the SQL instance as clustered or not.  If you proceed with creating a clustered instance of SQL Server, the instance will be hosted on a "Virtual" Server.  Resources such as data and log files will be created on the shared SAN-NAS disk for SQL Server, SQL Server Agent, and Full-Text Indexing.
If selected in the installation process, Notification Services and Analysis Services are also cluster-aware in SQL Server 2005.  Conversely, the associated program files for the instance will be installed on the local drives of each of the clustered nodes in an identical fashion and registry values are set identically across all clustered nodes.  Since the "Virtual" server resides solely on the SAN it can be "owned" by any of the nodes you allow.  Each of the nodes can run these resources in identical fashion because each physical server/node has the program files and identical registry settings necessary to run the SQL instance. 
Furthermore, the users are oblivious to the underlying fluidity of the server.  They connect to it as they would any other physical server:  by server name (virtual server name in this case) if the default instance or by virtual server name\instance name if a named instance.  This is key for application connectivity.  Since the SQL instance simply changes ownership during a failover, connection strings the applications rely on to connect to their databases need not be recoded; the physical server may become unavailable, but the virtual server persists after the failover.

Active/Active or Active/Passive Clustering

Clusters are often referred to as either Active/Active or Active/Passive.  Just as you would expect by the name, in an Active/Active cluster there will be two or more nodes, each one owning an instance of Microsoft SQL Server.  If one node fails, the instance it owns would fail over to the other node, running along side (and contending for resources with) the other instance.  An Active/Passive architecture  requires that no matter how many nodes make up the cluster, at least one node is not the owner of an instance of SQL Server.  It is "passive" and only exists to accept a failover of a node hosting a SQL instance in the event of a failover. 
Current Microsoft licensing policies require you to only license the active nodes running Microsoft SQL Server.  The passive node need not be licensed.

How Many Nodes?

Today's clustering technology under Windows 2003 and Microsoft SQL Server 2005 Enterprise Edition allows for up to eight nodes to be combined into a single cluster.  The release of Windows 2008 and Microsoft SQL Server 2008 Enterprise Edition will bring with it the ability to double that to sixteen nodes.  (You are limited to two nodes if you utilize SQL Server Standard Edition.)  Do you want to cluster multiple databases of various SLAs within many nodes on a single cluster?  Is it beneficial to dedicate a two-node cluster to a single database?  The answer is:  "It Depends."  We look into this in detail in a future tip.

Pros and Cons

While clustering protects you from hardware failure relating to the server hosting the SQL Server instance, it does not protect you from media failure.  Unlike replication, database mirroring, or log shipping there is only a single copy of your database.  If the SAN-NAS encounters a failure then you could not only conceivably incur downtime, but possibly data loss.  It is recommended that you incorporate redundancy of your SAN-NAS or database mirroring with your clustering configuration to protect you from media failures.  Hardware and licensing costs may be high.  In an Active/Passive clustering model you'll purchase hardware you hope to never need to use.  The cluster build is more complex than a standalone server setup.  The physical build of the cluster is outside the scope of this discussion however.  Additional benefits for clustering include simplicity for installation of SQL and ease of administration and maintenance.

Summary

There is plenty to consider when planning on clustering SQL Server.  Hopefully you were able to get a better understanding of what clustering is and an idea of the terminology associated with clustering SQL Server 2005.  In the remaining tips in this series we will review these items in greater detail, walking you through the decisions you'll make on architecture, the installation process, the tools available to monitor the state of the cluster and its resources, as well as comparing clustering to the other high-availability options associated with Microsoft SQL Server.

Demo