SRK-TechBlog

Technologies Blog

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

 

SharePoint DocumentSet and why you should always use them!

There are so many workflow solution that exist on planet, however what that differentiates and makes SharePoint stand out as workflow solution is its capability to document centric workflows and you can even use them for document sets. Normally in other solutions you have option of attachments. Contrary to that in SharePoint, content is the first class citizen. Consider a workflow where a set of documents move with the workflow with versioning of documents feature still there, and grouping of documents also possible. For e.g we developed a loan management system for one of the leading bank of the region with loan documents being centric to the workflow. That turned into a big success.

SharePoint is a content management system. Managing Content and documents are the core of this software. One such concept that it facilitates is grouping set of documents into a set. This is enabled through a feature called Document Set. 

Or you may say, Document Set is a special feature of SharePoint that can help group multiple document to make up a set and treated a single object or simply stating a "folder". e.g. as below,

 

Why SSL fate is doomed and TLS is the only option left

SSL, which refers to Secure Socket Layer, is a protocol used to provide secure connections between a client and a server. A TCP connection can provide a reliable link between a server and a client but cannot provide services such as confidentiality, integrity and end point authentication. So, SSL was introduced by Netscape in early 1990s to provide these services. The first version of SSL, which is known as SSL 1.0, was never released to the public as it had many security holes. However, in 1995, SSL 2.0, which provided better security than SSL 1.0, was introduced and, in 1996, SSL 3.0 was introduced with more improvements. The next versions of the SSL protocol appeared under the name TLS.

SSL, which is implemented in the transport layer, can secure a protocol such as TCP by applying various security measures. It will provide confidentiality by using encryptions to prevent anyone from eavesdropping. It uses both asymmetric and symmetric encryption. First, using asymmetric key encryption, a symmetric session key is established which then would be used for encrypting the traffic. Asymmetric key cryptography is also used for digital certificates used to authenticate the server. Then Message Authentication Code, which uses various hashing techniques, is used to provide integrity (identify any unauthenticated modification done to the real data). So a protocol like SSL allows transmitting sensitive information such as banktransactions and credit card information over the internet. Also, it is used for providing confidentiality for services such as email, web browsing, messaging, and voice over IP.

SSL is now outdated and has many security issues where its usage is not much recommended currently. SSL 3.0 was enabled by default until recently in many browsers but now they are planning to disable in the future versions due to severe security bugs such as POODLE attack.

Power of Two Factor Authentication using Microsoft Identity Manager 2016

Microsoft Identity Manager 2016 can increase the security and reduce help desk calls for password management substantially. It allows integration of authentication infrastructure with SMS gateway so that users mobile device can be used as OTP device for the password reset, unlock requirements. 

The windows environment, becomes latest with the industry trend of using One Time Password that is sent using SMS Messages.

The last organization where we implemented this technology became a great value to the infrastructure.

Never Install SQL Ent or Standard edition. Always use developer edition. Here's why

SQL editions can give you tough times, especially when you have used enterprise edition and later you find you cannot downgrade it to standard edition. Often times this is core requirement from the customer once they renew their Microsoft licenses.

Only edition flexible enough to be downgraded as well as upgraded is the developer edition which works for six months without any issues.

Once this period elapses you have chance to either upgrade to enterprise edition or downgrade the solution.

It will save you lot of wasted deployment hours.

SQL Editions

 source: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-2017?view=sql-server-2017#-edition-upgrade