How to force failover on Clusterless Availability Groups with FORCE_FAILOVER_ALLOW_DATA_LOSS

Clusterless Availability Groups are not true HA as there is no underlying cluster - and generally is intended as read only. I've also seen this set up being used in migration scenarios. Although not HA - it is still possible to commit a manual failover from a PRIMARY to SECONDARY. For example in a DR scenario Prior to commiting a failover identify the set up is a Clusterless Availability Group configuration . When the Clusterless Availability Groups was set up the command will be similar to the following CREATE AVAILABILITY GROUP command. Note: >> Here we connect to our primary... Read more →

How to view Service Principal Names in Active Directory

In an environment with Active Directory and SQL Server using Kerberos ,Service principal names (spn) become important. Windows supports delegating to NTLM protocol if Kerberos is not available , but being able to use Kerberos is the primary method. NTLM (New Technology LAN Manager) is a propietery Microsoft authentication protocol.The NTLM protocol is considered a legacy authentication protocol with various vulnerabilities, which pose a security risk. It's based on a relatively weak cryptographic scheme and is vulnerable to various attacks. It's replaced with Kerberos, which is a lot more secure and recommended. NTLM authentication should only be used in a... Read more →

How to extract Active Directory users with Get-ADGroup and Get-ADGroupMember

Question: I have a large list of Active Directory Groups and would like to extract the users from these Active Directory Groups. How can I script to iterate through a large list of Active Directory Group and list users using Powershell? Answer: Powershell has a very effective set of Active Directory modules. Within the set of AD modules , it is possible to achieve listing AD group members , group per group , combining Powershell Get-ADGroup and Get-ADGroupMember The basic flow is to extract the AD groups - place them into a DataTable and then iterate through the DataTable rows... Read more →

How to fix Database might contain bulk logged changes that have not been backed up for Always On

Question: I'm trying to add a new database to an existing Clustered Always On High Availability Group , but every time I execute the following statement , I get the error message below alter availability group MY_AG1 add database [mydb]; Msg 1475, Level 16, State 105, Line 5 Database "mydb" might contain bulk logged changes that have not been backed up. Take a log backup on the principal database or primary database. Then restore this backup either on the mirror database to enable database mirroring or on every secondary database to enable you to join it to the availability group.... Read more →

Does SQL Server require a restart after memory change

Question: I’d like to increase the memory allocated to our SQL Server instances. Once the ‘maximum server memory…’ is increased, are there any SQL services which should be restarted? Answer:-You don’t need to restart SQL Server, adjusting SQL Server’s Max Server memory is an online option . When you do make the change,you’ll highly likely cause data or procs to leave their caches so queries could be slower for a short while after you run it. If your environment is sensitive to performance consider making this change in a non production window. Ensure you leave enough memory on host for... Read more →