Multi Subnet failover Cluster IP Configuration in the test LAB.



I have four servers(Virtual machines) in my lab environment. below are the names of the four servers.

DOMAIN
NODE1
NODE2
NODE3

1) First, create two network adapters with two different subnets with VMWare. the image looks like below.



2) Chose these two network adapters with different subnet under DOMAIN(name of the servers is domain) server


Now you can see 3 NIC cards under domain server like below. One is Default this is for DomainIP iD, so you can see three NIC cards like below.

EtherNet0 info:



NODE1AND2 Nic Info in DOMAIN server:

NODE3 Nic info in DOMAIN server:


Now follow the steps that are there in the below URL for ROUTING AND REMOTE(extracted from sqlshack.com)
https://www.sqlshack.com/simulating-a-multi-subnet-cluster-for-setting-up-sql-server-always-on-availability-groups-lab-setup/

before you got to 3rd step configure ROUTING and REMOTE. You can not add servers to domain until and unless ROUTING is addressed. So first enable the ROUTING from the above URL

3) Though you can find 3 nic cards in the DOMAIN server you can work with the existing NIC card here(single one) in these two servers, just change the network adapter to custom. Now goto NODE1 server and VMNetwork settings you can find the below image. Chose one Network adapter from the ones we created in the beginning. I have chosen Custom(VNET2). You can also give VNET1 however it is up to you. And the IP's that you are giving here should be within the subnet range of VNET2.



NIC IP Address Info in NODE1:

You can see NODE1 IPV4 addresses here. Default gateway(subnet) and DNS address would the IP addresses of the DOMAIN server. The same goes for NODE2 as well because NODE1 and NODE2 would be in the same subnet range. The only difference is IP address will get change here I have mentioned as 192.168.175.26 for NODE2 rest remains the same(Default and DNS info remains the same).

NODE1 Nic Info:





NIC IP Address Info in NODE3: Node 3 would be in different subnet range here it would be VNET1. So chose the same network adapter from Virtual Machine setting and under Network adapter chose VNET1. For reference, you can see the above image. 

Please find the Network adapter setting of NODE3 and Properties IPV4 address.


And the properties of IPV4 address info of NODE3. Here if we observed IP address of NODE3 showing in another subnet and range and 

NODE3 Nic Card info:



After this, you can see all the nodes are accessible from one to another though they are in different subnet ranges. But ensure that you have to configure ROUTING AND REMOTE.  For this, you can check above mentioned SQLSHACK URL.

Just understanding purpose below IPS are provided.



x
x

Comments

Post a Comment

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

Pre login Handshake or Connection Timeout Period

Image
Service was unable to open new database connection when requested. SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16225; handshake=14840; Connection string - Data Source=sqlserverintancenamehere;Initial Catalog=CatalogNamehere;Integrated Security=True;Persist Security Info=False;Max Pool Size=1000;Connect Timeout=20;Load Balance Timeout=120;Packet Size=4096;Application Name=Applicationname hereBusinessLayerHost.exe;Workstation ID=HostnameHere setspn -a MSSQLSvc/hostname.doaminname.net:1433 ServiceAccountNameHere need to run the above command in a command prompt, most of the time you would NOT have permissions to run this, please check with Active Directory Team or Wi...
Read more

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server

Image
/*  There is only one master key, you can not create  multiple */ use master go CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPasswordHere' GO /*  Creating certificate in the master database, after creating  master key */ use master GO CREATE CERTIFICATE MyFirstCert WITH SUBJECT = 'TDECertificate' ; GO /* Enable databse encryption key but you can not see under this specific user database. */ USE G GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyFirstCert GO ALTER DATABASE G SET ENCRYPTION ON;  /*  Backing up certificate */ use master go BACKUP CERTIFICATE MyFirstCert TO FILE = 'C:\Certificates\MyFirstCert' WITH PRIVATE KEY ( FILE = 'C:\Certificates\MyFirstCertKey' , ENCRYPTION ...
Read more