MsSQL (Clustering)


Before we Begin first you need to understand clustering
A database cluster is a collection of databases that is managed by a single instance of a running database server. After initialization, a database cluster will contain a database named postgres, which is meant as a default database for use by utilities, users and third party applications.

Simpler form: A cluster is a group of two or more independent servers operating as a single system. A database server cluster could help you achieve high availability or improve I/O throughput for your database service.
Clustering Concept

Here is a diagram of a SQL Server cluster. The cluster is named SQLCLUSTER01. It has two nodes (servers), which are named SQLCLU01NODE01 and SQLCLU01NODE02. People connect to the SQL Server instance at SQLCLU01A\SQL. The instance has been configured on port 1433.

Oh no! There’s been a failure in our environment!

Here’s what happened.
The SQLCLU01NODE01 server crashed unexpectedly. When this happened, the Windows Failover Cluster service saw that it went offline. It brought up the SQL Server services on SQLCLU01NODE02. The SQLCLU01A\SQL instance started up and connected to all the same databases on the shared storage– there’s one copy of the data, and it doesn’t move. As part of the SQL Server startup, any transactions that were in flight and had not committed at the time of the crash were rolled back.
While this automatic failover was occurring, users could not connect to the SQLCLU01A\SQL instance. However, after it came back up they were able to resume operations as normal, and had no idea that a server was still offline.

AlwaysOn Availability Groups is the big new feature in the High Availability stack of SQL Server 2012. Simply said it is the successor of Database Mirroring that was introduced with SQL Server 2005 SP1. Database Mirroring is a great technology for achieving HA (High Availability) and DR (Disaster Recovery), but has some weaknesses and limitations:
  • You can only mirror 1 database through a Database Mirroring session. When you have an application that consists of several database (just think of SharePoint Server), then you need to have several distinct Mirroring sessions.
  • The Failover is done on the Mirroring session level, which means you can only failover 1 database at a given time. Again – if you have more than 1 database, you need different Mirroring sessions and you have to coordinate a multi-database failover at your own.
  • There is only 1 Mirroring partner possible. You can’t mirror to different partners. The Mirroring partner can be run with Synchronous Commit for achieving High Availability or with Asynchronous Commit for achieving Disaster Recovery. If you are using Synchronous Commit you can get an Automatic Failover if you deploy a Witness instance. This witness instance is used for acquiring a quorum and avoiding a so-called Split-Brain scenario.
  • The Mirroring database is always replaying Transaction Log records, which means that you are not able to access the database for read-only access (the undo part of Recovery has not yet run). The only possibility is to use Database Snapshots to get a consistent view of your database at a given point in time and refresh the Database Snapshot on a regularly basis, but again – you have to do this at your own.

Beginning with SQL Server 2012 Microsoft provides us now AlwaysOn Availability Groups which are the successor of Database Mirroring.
AlwaysOn Availability Groups offers you the following advantages over traditional Database Mirroring:
  • Multi-Database Failovers
  • Multiple Secondaries
  • Active Secondaries
  • Integrated HA Management
Source

There are several requirements regarding which databases can be a part of AlwaysOn Availability Groups:
  • Availability groups must be created with user databases. Systems databases can't be used.
  • Databases must be read-write. Read-only databases aren't supported.
  • Databases must be multiuser databases.
  • Databases can't use the AUTO_CLOSE feature.
  • Databases must use the full recovery model, and there must be a full backup of them.
  • A given database can only be in a single availability group, and that database can't be configured to use database mirroring.

Microsoft also recommends that a database file's path be the same on the primary and secondary servers.
AlwaysOn Source

An implementation of AlwaysOn AG for HA and DR (using quorum mode Node Majority) is depicted below.

















Knowledge Source

Comments

Post a Comment

Popular posts from this blog

How to verify systems designed in Business Analyst

Image
Verification is what most people think of when they hear the word testing — it’s the process of testing whether a business analysis solution does what it’s designed to do. During verification, the testing team (which may consist of developers, quality assurance [QA] people, and some business analysts [BAs]) put the software through its paces to both confirm that it operates as expected and ensure that it conforms to the design specifications laid out earlier in the project. Verification testing includes four phases — one pretest phase and three phases of actual testing. Smoke Test Also called a build verification test, a smoke test is a pretest that determines whether full testing can even begin in the first place. It reveals any simple failures in the solution that may prevent you from executing the tests in the next three phases. Some project teams may link this test to unit testing. Unit Test The unit test is the first actual phase of testing. It involves testing each uni...
Read more

Customizations: Oracle SaaS vs PaaS

Image
  It has always been a questions from customer, on what's the customization extent of SaaS and where one should approach PaaS services in Oracle universe. After working into this domain for 3+ years and having multiple iterations into multiple projects and tons of pre-sales in past few years this has brought me a good level of clarity to the extent where customers are happy to know the point when they should switch to PaaS. Understanding and practicing industry best practice should always be best practice for an organization as that helps to manage that for long run. As best systems are those which requires minimal of manual efforts to maintain. SaaS Customizations Page composer OTBI reports Custom Workflow - BPM Page Composer: For certain applications one can use Page Composer to configure application page components, such as page content and layout for other users.Here are the changes one can make using this tool: Add, move, delete, show, and hide fields ...
Read more

Agile Model

Image
Agile Method This article is a introduction to Agile software development methodologies and it's application. This is not only suitable for software developers but also for Team Leaders, Project Managers, Product Managers, Development Managers, Testers, QA Managers, QA Engineers, Technical Writers, UX Designers, anyone involved in the delivering software.It doesn't describe any code or technology it's an method or an software engineering model which helps to develop a tool to it's best requirements or goals. Agile Method is a combination of various sprint cycles initially of 1 to 4 weeks per sprint cycle. Deployment is alligned with the changing business need after the completion of every sprint cycle. Agile adapts of frequent feedback by delivering workable product after every iteration/sprint cycle end. In other words Agile Method is a series of short iteration/sprint cycles. Agile Model is an incremental version of multiple waterfall model cycle so,br the cha...
Read more