3

I'm trying to set up a test environment here to learn more and apply this on our production environment.

I've created and installed the Windows Cluster with CLuster manager. Now I'm going to install SQL Server Enterprise 2014 on both machines and create HA with then, with availability groups. The failover will be manual. They will be in synchronized data transfer.

My question is, Can I normally install "stand-alone installarion or add..." the SQL server, or I need to use "new sql server failover cluster installation".

I cant find anything about this on the internet.

Im going to install sql server on both nodes that will be configured in the cluster.

3 Answers 3

7

If you want to just use stand alone instances - which is the most common method of implementing availability groups [stand alone to stand alone] - then you only need to do a stand alone install. So, yes, you can do the stand alone if that's what you're looking for.

It is possible to use a SQL Server failover cluster instance install to also work with availability groups but it is not required.

2
  • Thank you Sean.one more question. If I created a failover cluster with a shared location ( that one when the noe 1 is down, second node assumes and continue using that shared location ), do I need to use the "failover cluster sql installation" ? if not, well I need to understand better this....thanks.
    – Racer SQL
    Aug 22, 2019 at 20:17
  • What you're describing there (using a shared disk location) is a SQL Server FCI installation, which for that you'd want to use the create a new fci installation for the first node and add a node for the rest. @RacerSQL Aug 22, 2019 at 21:30
4

My question is, Can I normally install "stand-alone installarion"

Yes absolutely, the Availability Groups meant for HA/DR solution with stand-alone instances (Replicas) with directly connected storage unlike FCI where it required special installation procedure and shared storage etc..

Although, the answer is already clear, following are the summarized steps of Availability Groups setup and Reference document that might be helpful (especially during production implementation)..

Summarized Steps (after WSFC ready):

  1. Enable HADR at SQL Service properties (vai SQL Configuration manager at all replicas)
  2. Set Database recovery to FULL that need to be added into AG
  3. Perform Full backup of user database
  4. Begin Creating Availability Group (wizard vai SSMS)
  5. Add replicas (Detailed Ref..)
  6. Endpoints configuration
  7. Listener configuration (optional)
  8. Read-only routing configuration (optional)

Following areas must be consider to avoid port conflict (custom ports are recommended):

  1. SQL ports (Default: 1433)
  2. Endpoint ports (Default: 5022)
  3. Listener ports (no default)
1

There are two HA/DR technologies that you can use. Official names are:

They are quite different but both rely on WSFC (except for Basic Availability Groups).

For FCI you need a shared storage which is also a single point of failure. You only have one copy of data and if it fails then everything fails. Also only one of the SQL Servers can run at a time, the secondary will be shutdown and cannot be used at all until fail-over happens. On the positive side, FCI takes care of all the databases including system databases (master, msdb), so you don't have to worry about logins, jobs etc.

AG doesn't need a shared storage. Each SQL Server instance has it's own copy of the database(s). All of the instances are constantly running with one instance in read-write mode and the rest are read-only. But AG only replicate user databases so you have to take care of all the server level objects yourself (jobs, logins etc.).
Logins must have the same SIDs on all the servers!
Each time you add a new database you have to manually add it to AG group.

So depending on what HA/DR solution you want to use:

  • For AG you need to install "stand-alone installation or add..."
  • For FCI "new sql server failover cluster installation" and also "add cluster node" on another server(s)
  • It's also possible to configure AG on top of FCI but it only make sense if you have at least 3 servers and it's not commonly used. For example you can install FCI SQL1 on NodeA and NodeB, another FCI SQL2 in remote datacenter on NodeC and NodeD and then configure an async AG between SQL1 and SQL2 instances. In this case your HA is implemented using FCI and DR as AG.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.