Shahid Roofi Khan

Technologies Blog

New cluster model for Windows 2019 allows router USB to be used as witness node

Cluster arbitration needs a third computer hosting a share folder as judge to facilitate automatic failover between two computers hosting high available service.

However it was costly to have one. It must be joined to domain, must not be a domain controller and must not be down and can't be in DMZ as domain authentication must also succeed.

That is really easy on hardware and software. Good News is MS Windows Server 2019 remove ALL! of these requirements.

This means NO kerberos , NO domain controller , NO certificates , and NO Cluster Name Object needed. While we are at it, NO account needed on the nodes. Oh my!!

Just use even your router's USB share to arbitrate your internal clusters !!!!

Now it does make a lot of sense to use your cloud machine to arbitrate your internal on premise cluster setups!!!

Just need that atleast SMB 2.0 level file sharing is supported by your shares.

#CloudWitness #Windows Server 2019 #NewFileShares #Security

Inventory your Servers using PowerShell

Inventory your servers is very time taking task, most the methods to automate collecting inventory information cannot fetch detailed BIOS information and MAC Address.

With powershell and WMI, I've contributed a script that additionally queries and tag that information as well. Details will include BIOS details, Serial number, Platform as well.

Output sample:

Script available at my MS Gallery contribution: https://gallery.technet.microsoft.com/How-to-inventory-all-ce4e1f34?redir=0

SQL Always-On - Choose between Async vs Sync Replication mode.

Consultants mostly get stuck at the decision as to which SQL Server Always On replication model to choose from.

SQL Server Always On offers SYNCHRONOUS vs ASYNCHRONOUS mode of replication.

One should try to have synchronous replication because then we will have zero loss recovery, but at the same time, enabling synchronous replication might not be realistically possible in 99% cases based on its cost. In Synchronous replication, data is replicated and only then write is considered completed on the primary node which ensures we have updated 2nd node. In the case of asynchronous replication, we have the 2nd node, always catching up the latest and would be having some backlog in case of loads.

Note that Microsoft does not refer to 10gbps or 50Gig link requirement or Storage Raid levels or IOPs required to have Synchronous replication. Since it is very loaded specific thing so Microsoft refers to performance counter of SQL to check if the hardware and setup is feasible for this replication type or NOT. Plus since DB is ever growing thing, so it needs to be the check on schedule instead of once only.

source: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-2017

As per Microsoft TechNet Article,

"In Always On availability groups, the availability mode is a replica property that determines whether a given availability replica can run in synchronous-commit mode. For each availability replica, the availability mode must be configured for either synchronous-commit mode, asynchronous-commit, or configuration only mode. If the primary replica is configured for asynchronous-commit mode, it does not wait for any secondary replica to write incoming transaction log records to disk (to harden the log). If a given secondary replica is configured for asynchronous-commit mode, the primary replica does not wait for that secondary replica to harden the log. If both the primary replica and a given secondary replica are both configured for synchronous-commit mode, the primary replica waits for the secondary replica to confirm that it has hardened the log (unless the secondary replica fails to ping the primary replica within the primary's session-timeout period)."

If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into the asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.


Note that synchronous replication is subject to the fact that the partner is not down. Because every transaction is committed to 2nd node also, so it has to continue to function if the 2nd node is permanently down so it waits for the timeout period and shifts to async mode temporarily if the communication is disrupted. (Ping is the criteria!!)

 This also means, Synchronous Always On Availability Groups Is Not Zero Data Loss in all cases. These are scenarios that need planning if the use case is such.

Same i've posted on Technet Gallery great feedback: https://social.technet.microsoft.com/wiki/contents/articles/52671.sql-alwayson-choosing-between-the-right-replication-model.aspx

Further Reading: Microsoft Article