SQL Server Clustering

-

Failover Cluster

SQL Server Failover clustering provides high-availability support for an entire SQL Server Instance.
SQL Server failover Clusters are built on top of Windows server failover clusters.
A SQL Server failover cluster also known as failover cluster instance , consists of the following
  • One or more Windows Server failover cluster nodes.
  • A cluster resource group dedicated for the sql server failover cluster which contains the following:
    • IP addresses
    • Shared disks used for the SQL Server database and log storage
    • Resource DLLs that control the SQL Server failover behaviour.
  • Check -pointed registry keys that are automatically kept in sync across the failover cluster nodes.
A SQL Server failover cluster appears on the n/w as a single SQL Server instance on a single computer. Internally , only one of the nodes owns the cluster resource group at a time , serving all the client requests for that failover cluster instance. In case of a failure ( hardware failures , operating system failures , application or service failures ), or a planned upgrade , the group ownership is moved to another node in the failover cluster. This process is called failover. By leveraging the Windows Server failover cluster functionality , SQL Server failover cluster provides high availability through redundancy at the instance level.






 


 





AlwaysOn AG

The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

 An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.

AlwaysOn Availability Groups provides a rich set of options that improve database availability and that enable improved resource use. The key components are as follows:
  •  Supports up to nine availability replicas. An availability replica is an instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group. Each availability group supports one primary replica and up to eight secondary replicas.
  • Supports an availability group listener for each availability group. An availability group listener is a server name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica. The listener provides fast application failover after an availability group fails over.
  • Supports a flexible failover policy for greater control over availability-group failover.
  • Supports automatic page repair for protection against page corruption.
  • Supports encryption and compression, which provide a secure, high performing transport.


 

 


How to: Recover from Failover Cluster Failure

In this scenario, failure is caused by hardware failure in Node 1 of a two-node cluster. This hardware failure could be caused, for example, by the failure of a disk controller or the operating system.
To recover from a failover cluster hardware failure

    After Node 1 fails, the SQL Server failover cluster fails over to Node 2.

    Evict Node 1 from Microsoft Cluster Service (MSCS). To evict a node from MSCS, from Node 2, open Cluster Administrator, right-click the node you want to remove, and then click Evict Node.

    Verify that Node 1 has been evicted from the cluster definition.

    Install new hardware to replace the failed hardware in Node 1.

    Using the Cluster Administrator, configure Microsoft Cluster Server (MSCS) to add Node 1 to the existing cluster. For more information, see Before Installing Failover Clustering.

    Ensure that the administrator accounts are the same on all cluster nodes. For more information, see How to: Create a New SQL Server Failover Cluster (Setup).

    Run SQL Server Setup to add Node 1 to the failover cluster. For more information, see How to: Add or Remove Nodes in a SQL Server Failover Cluster (Setup).




Comments

Post a Comment

Popular posts from this blog

SQL SERVER – Event ID 107- Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

-
SQL SERVER – Event ID 107- Report Server Windows Service cannot connect to the report server database. Error with Report Server One of my clients reported an error which is the subject of the blog. In the event viewer they were seeing below error:     Event Type: Error Event Source: Report Server Windows Service (MSSQLSERVER) Event Category: Management Event ID: 107 Computer: SQLSERVER Description: Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database The error is generic because it doesn’t tell the exact error which is causing a connectivity error. My immediate question was that, are there any more errors? So, I asked them to look at Reporting Service Log file. Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database. The application domain WindowsService_17 failed to initialize. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report se
Read more

SQL Server Builds Information

-
Image
Very Useful information about SQL Server Builds .  It is nicely presented in the blog . Please see  below for our reference . Microsoft SQL Server Version List What version of SQL Server do I have? This unofficial build chart lists all of the known Service Packs ( SP ), Cumulative Updates ( CU ), patches, hotfixes and other builds of MS SQL Server 2017, 2016, 2014, 2012, 2008 R2, 2008, 2005, 2000, 7.0, 6.5 and 6.0 that have been released. Useful articles: How to identify your SQL Server version and edition SQL Server Internal Database Versions Microsoft SQL Server Support Lifecycle Microsoft SQL Server Home Microsoft SQL Server Developer Center Microsoft TechNet: Microsoft SQL Server Microsoft Knowledge Base Sqlservr.exe versions   Quick summary:   RTM (no SP ) SP1 SP2 SP3 SP4   SQL Server 2017      codename vNext 14.0.1000.169 *new           SQL Server 2016 13.0.1601.5 13.0.4001.0 or 13.1.4001.0 13.0.5026.0 or 13.2.5026.0   S
Read more

Using DBCA silent install and disabling automatic memory management

-
Image
Avoid issue [DBT-11211] /app/oracle/product/12.2.0/dbhome_1/bin=> dbca -silent -createDatabase -templateName db_kp_template.dbt -gdbname dgtest1 -sid dgtest1 -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 20 -emConfiguration NONE [FATAL] [DBT-11211] The Automatic Memory Management option is not allowed when the total physical memory is greater than 4GB.    CAUSE: The current total physical memory is 11GB. Solution : disable automatic memory management with in the response file and you will be fine :) .  /app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates=> dbca -silent -createDatabase -templateName db_kp_template.dbt -gdbname dgtest1 -sid dgtest1 -responseFile NO_VALUE -characterSet AL32UTF8 -automaticMemoryManagement FALSE -memoryPercentage 20 -emConfiguration NONE Enter SYS user password: Enter SYSTEM user password: [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.    CAUS
Read more