Baten ICT

Prepare AD First thing to do is adding a KdsRootKey by issuing this cmdlet on the DC: Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10)) Security Group Create a new global security group. This group will contain all servers that will use gMSA. After adding the servers, they will require a reboot in order for their tokens to pick up membership in the group. This group will be given specific permissions to its members that will allow the member servers to retreive the gMSA password. e.g. Create group nl.mgt.SQL2017DE.gs in OU batenict.corp/DTB Desktop/Groups/Security Groups/ gMSA Now we can create the gMSA: New-ADServiceAccount -Name nl.SQL2017DE -DNSHostName nl.SQL2017DE.batenict.corp -PrincipalsAllowedToRetrieveManagedPassword nl.mgt.SQL2017DE.gs After creating the gMSA add some permissions: Add a new SELF permission with grants on Read msDS-PrincipalName and Write msDS-PrincipalName Service Dependencies To prevent auto-start issues, add the following services to the existing Dep

When using Availability Groups (or database mirroring for that matter), all instance level objects like logins, are not automatically distributed across all participating instances. The same goes for SQL Agent jobs. All of these objects need to be replicated somehow. Besides that, SQL Agent jobs must be “Primary or not”-aware. One simple solution to this problem, is to insert a step in each job, which should run first, with the following code. Set this step to continue with the next step on success, and to quit the job reporting success on a failure. IF ( SELECT ARS . role_desc FROM sys . dm_hadr_availability_replica_states AS ARS INNER JOIN sys . availability_groups AS AG ON ARS . group_id = AG . group_id AN D ARS . is_local = 1 ) <> 'PRIMARY' BEGIN --We're on the secondary node, throw an error THROW 50001 , 'Unable to execute job on secondary node' , 1 ; END

The clean way of removing the witness is by issuing this command while connected to the principal: ALTER DATABASE foo SET WITNESS OFF; Sometimes however, both partners have been dismantled. In that case this will work: Start witness SQL Server in single user mode. Connect my instance with an admin connection. Delete orphaned entries in sys.syslogshippers. Restart SQL in multi user mode. Source: https://blogs.msdn.microsoft.com/ialonso/2011/12/13/how-can-you-drop-the-witness-of-a-mirroring-session-when-the-partners-have-been-dismantled/