SQL Server Availability Group without Cluster And AD In Windows

SQL Server Availability Group without Cluster And AD In Windows
SQL Server Availability Group without Cluster – Dear DBAs, Do you think is this possible? Luckily yes, its possible to create an availability group without a Windows Failover Cluster on Windows. But unfortunately, there are few restrictions. Also, we don’t need to restore the Database on secondary. The availability group has a feature called Automatic Seeding to restore the database automatically from the Primary server.

Limitations:

  1. There is NO AUTO Failover.
  2. It’ll work on Windows Server 2016 + SQL Server 2017.

But yeah, its really a great feature for Read Scale. Lets see how can we configure the availability group without the WSFC.

My PoC Setup:

  1. Windows Server 2016.
  2. SQL Server 2017 Developer Edition.

Node 1 Node 2
compueter name : sql1 compueter name : sql2
IP address : 172.31.63.217 IP address : 172.31.61.159
DNS suffix : sqlgossip.com DNS suffix : sqlgossip.com

Naming conversion and DNS Prefix.

1. Before start setting up this, we need to follow some proper naming conversion and DNS suffix.
2. Go to computer properties and change the computer name.
3. Click more and set your domain(anything you can give).

SQL Server Availability Group without Cluster And AD In Windows

Change Computer name and set DNS

4. Go to network connections and go to properties of Ethernet adapter.
5. Click IPv4 and go to properties and click advanced.
6. In the DNS tab click add in the Append this DNS suffix.
7. Type your domain name and click OK.

SQL Server Availability Group without Cluster And AD In Windows

Set DNS entry in network adapter

8. Edit the hosts file to make local DNS entry.
Open this file.

C:\Windows\System32\drivers\etc\hosts

# Add these lines.
172.31.63.217 sql1.sqlgossip.com
172.31.61.159 sql2.sqlgossip.com

9. Restart your windows to apply the name change.

Prepare SQL Server:

Without Cluster and AD we are going to use Certificate based authentication to make communication between the instances.
So create a certificate on both nodes and create an endpoint with the certificate authentication.

-- ==================  
-- Run This On SQL 1
-- ==================

-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Sql@123'
GO
 
-- Create a new certificate
CREATE CERTIFICATE SQL1_cert
WITH SUBJECT = 'SQLAG_Certificate_Private - SQL 1',
START_DATE = '20170101'
GO
 
-- Backup the public key of the certificate to the filesystem
BACKUP CERTIFICATE SQL1_cert
TO FILE = 'c:\SQLfiles\SQLAG_Certificate_SQL1_Public.cert'
GO
 
-- Create an endpoint for the Availability Group
CREATE ENDPOINT SQLAG_Endpoint
STATE = STARTED
AS TCP
(
  LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
  AUTHENTICATION = CERTIFICATE SQL1_cert,
  ROLE = ALL, 
  ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

-- ==================  
-- Run This On SQL 2
-- ==================


-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Sql@123'
GO
 
-- Create a new certificate
CREATE CERTIFICATE SQL2_cert
WITH SUBJECT = 'SQLAG_Certificate_Private - SQL 2',
START_DATE = '20170101'
GO
 
-- Backup the public key of the certificate to the filesystem
BACKUP CERTIFICATE SQL2_cert
TO FILE = 'C:\SQLfiles\SQLAG_Certificate_SQL2_Public.cert'
GO
 
-- Create an endpoint for the Availability Group
CREATE ENDPOINT SQLAG_Endpoint
STATE = STARTED
AS TCP
(
  LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
  AUTHENTICATION = CERTIFICATE SQL2_cert,
  ROLE = ALL, 
  ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

Copy the certificate from SQL1 to SQL2 and SQL2 to SQL1.
On Node1:
Copy SQL2 certificate to C:\SQLfiles\
On Node2:
Copy SQL1 certificate to C:\SQLfiles\
SQL Server Availability Group without Cluster And AD In Windows
Now create logins on both servers and authorize to use the another node’s certificate.

-- ==================  
-- Run This On SQL 1
-- ==================

-- Create login for the other node
CREATE LOGIN SQL2Login WITH PASSWORD = 'Sql@123'
GO
 
-- Create user for the login
CREATE USER SQL2User FOR LOGIN SQL2Login
GO
 
-- Import the public key portion of the certificate from the other node
CREATE CERTIFICATE SQLAG_Certificate_SQL2_Public
AUTHORIZATION SQL2User
FROM FILE = 'c:\SQLfiles\SQLAG_Certificate_SQL2_Public.cert'
GO
 
-- Grant the CONNECT permission to the login
GRANT CONNECT ON ENDPOINT::SQLAG_Endpoint TO SQL2Login
GO

-- ==================  
-- Run This On SQL 2
-- ==================

-- Create login for the other node
CREATE LOGIN SQL1Login WITH PASSWORD = 'Sql@123'
GO
 
-- Create user for the login
CREATE USER SQL1User FOR LOGIN SQL1Login
GO
 
-- Import the public key portion of the certificate from the other node
CREATE CERTIFICATE SQLAG_Certificate_SQL2_Public
AUTHORIZATION SQL1User
FROM FILE = 'c:\SQLfiles\SQLAG_Certificate_SQL1_Public.cert'
GO
 
-- Grant the CONNECT permission to the login
GRANT CONNECT ON ENDPOINT::SQLAG_Endpoint TO SQL1Login
GO

Its time to enable Alwayson Availability Group feature. Go to SQL Server configuration manager and enable Availability Group feature on both nodes.

SQL Server Availability Group without Cluster And AD In Windows

enable alwayson feature

Restart SQL Service to enable the alwayson feature.

Prepare Database for Availability Group:

Create a database and take a full backup.

-- ==================  
-- Run This On SQL 1
-- ==================

-- create a database for replication
create database sqladmin

-- Backup database (its the pre-reuirement for a database to add ag)
backup database sqladmin to disk ='C:\SQLfiles\sqladmin_full.bak'

Create Availability Group without Cluster:

1. In the Alwayson Availability Group option, click create availability group wizard.
2. Give a name to your availability group.
3. In the cluster type, you can see two types.

  • External – Any external cluster software.
  • NONE – No auto failover.
  • WSFC – This will visible only if you have a WSFC then select this option.

4. So in our case select NONE as cluster type.
SQL Server Availability Group without Cluster And AD In Windows
5. Click the database that needs to be added to the availability group.
SQL Server Availability Group without Cluster And AD In Windows
6. Add the secondary server and choose readable secondary.

7. In Sync Preference, select automatic seeding (we don’t need to restore the database on the Secondary server).

8. Click next and finish the setup.
SQL Server Availability Group without Cluster And AD In WindowsSQL Server Availability Group without Cluster And AD In Windows
9. Thats its, the availability group has been created with the cluster and Active directory.
SQL Server Availability Group without Cluster And AD In Windows10. Test the Alwayson Replication:
11. Create a test table on SQL1 and insert some values. And see those values on SQL2.

-- ==================  
-- Run This On SQL 1
-- ==================

-- create some sample records
use sqladmin
go

create table ag_test (numbers int)
go

insert into ag_test values (1);
insert into ag_test values (2);
insert into ag_test values (3);
insert into ag_test values (4);
insert into ag_test values (5);


-- ==================  
-- Run This On SQL 2
-- ==================
-- check the replication

use sqladmin
go

select * from ag_test;

SQL Server Availability Group without Cluster And AD In Windows

Want to learn SQL Server On Linux: Here you go

You may also like this

9 Replies to “SQL Server Availability Group without Cluster And AD In Windows”

  1. You CANNOT configure AG without WSFC in SQL Server 2016 and Win server 2016. All I know is you can have AG without AD but with WSFC. Your blog is incorrect

    Reply

  2. Just to add the cluster less AG feature is in vNext not in SQL Server 2016.

    Reply

    1. shashank – I accept, But I have tested it on SQL Server 2017. So the procedure is correct but the pre-requirement is wrong. I have corrected it now. Thanks for your clarification.

      Reply

  3. Hi ! I wonder how to get connection string from AG so I can add to my winform app . Thank for your help

    Reply

      1. Thanks for your help! But I still a question ….
        Is that necessary to need a new instance => 3 Servers ( 2 Servers make AG and 1 Servers make Listener) ? OR we just need 2 Servers just deploy become AG and the connection string point to Listener of them… Thank you very much !

        Reply

        1. No No, Listener is not a part of seperate server. Listener is also a part of availability. It’ll work in Servers. You can’t create it separately. 3 servers for maintain the quorum. Or use 2 servers and 1 fileshare witness.

          Reply

          1. Thank you so much.. I’m very appreciated for your help… I’m just need one more question, please help me this.. With your blog.. We can make AG without AD and Failover Cluster.. So my project is when the primary replica is down, and still have the secondary replica… But the secondary replica is just read only.. It can not write access .. Is there some solution make secondary could read-write access like primary replica… Thanks for your help so much ! 🙂

          2. This is the limitation of this feature. It just overcome the Mirroring. In Mirroring, you can’t read the data. But here you can do. It won’t do the automatic failover. Not only SQL server, where ever you want to setup the HA with automatic failover, its mandatory to setup a cluster with quorum.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.