Baten ICT

Recently I had the need to change the formatting of some guid values in a .csv file. Turns out, this is pretty easy to do in Powershell. To change "0d2c3b6e-b10a-4fc3-9cdd-499dc16c81fa" into {0d2c3b6e-b10a-4fc3-9cdd-499dc16c81fa} use this nice Powershell trickery: $ line = "`" $( New-Guid ) `",`"blablabla`",`"blablabla`",`" $( New-Guid ) `",`"blablabla`"" $ line $ line -replace '\"([0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12})\"' , '{${1}}' Or using Notepad++: Search for: (")([0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12})(") Replace by: {\2}

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