{"id":14179,"date":"2020-05-19T15:47:25","date_gmt":"2020-05-19T13:47:25","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-synchronize-logins-on-alwayson-replicas-with-dbatools\/"},"modified":"2025-10-01T11:44:22","modified_gmt":"2025-10-01T09:44:22","slug":"sql-server-synchronize-logins-on-alwayson-replicas-with-dbatools","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-synchronize-logins-on-alwayson-replicas-with-dbatools\/","title":{"rendered":"SQL Server: Synchronize logins on AlwaysOn replicas with dbatools"},"content":{"rendered":"

The SQL Server environment\u00a0 I worked with today has dozens of SQL Server instances using AlwaysOn Availability Groups for High Availability.
When a login is created on the Primary replica of an Availability Group it is not synchronized automatically on secondary replicas. This might cause some issues after a failover (Failed logins).<\/p>\n

Since this is not done automatically by SQL Server out of the box the DBA has to perform this task.
To avoid doing this with T-SQL (sp_help_revlogin<\/a>) or SSMS I use the magical dbatools<\/a> and perform the following tasks once a week.\"dbatools\"<\/a><\/span><\/p>\n

    \n
  1. Get the number of logins on each instance.<\/li>\n<\/ol>\n
    \n
    \n\n\n\n
    \n
    1<\/div>\n
    2<\/div>\n
    3<\/div>\n
    4<\/div>\n
    5<\/div>\n<\/td>\n
    		\n
    \n
    $primary<\/code> = <\/code>Get-DbaLogin<\/code> -SqlInstance<\/code> 'SQL01\\APPX'<\/code><\/div>\n
    $primary<\/code>.Count<\/code><\/div>\n
    \u00a0<\/div>\n
    $secondary<\/code> = <\/code>Get-DbaLogin<\/code> -SqlInstance<\/code> 'SQL02\\APPX'<\/code><\/div>\n
    $secondary<\/code>.Count<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
    \n
    \n\n\n\n
    \n
    1<\/div>\n
    2<\/div>\n
    3<\/div>\n
    4<\/div>\n<\/td>\n
    		\n
    \n
    PS C:\\> <\/code>$primary<\/code>.Count<\/code><\/div>\n
    41<\/code><\/div>\n
    PS C:\\> <\/code>$secondary<\/code>.Count<\/code><\/div>\n
    40<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
      \n
    1. If numbers don’t match, I use the Copy-Login function to synchronize the missing login.<\/li>\n<\/ol>\n
      \n
      \n\n\n\n
      \n
      1<\/div>\n
      2<\/div>\n<\/td>\n
      		\n
      \n
      Copy-DbaLogin<\/code> -Source<\/code> 'SQL01\\APPX'<\/code> -Destination<\/code> 'SQL02\\APPX'<\/code> `<\/code><\/div>\n
      \u00a0\u00a0\u00a0\u00a0<\/code>-Login<\/code> ((<\/code>$primary<\/code> | <\/code>Where-Object<\/code> Name<\/code> -notin<\/code> $secondary<\/code>.Name).Name)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
      \n
      \n\n\n\n
      \n
      1<\/div>\n
      2<\/div>\n
      3<\/div>\n
      4<\/div>\n<\/td>\n
      		\n
      \n
      PS C:\\><\/code><\/div>\n
      Type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Status\u00a0\u00a0\u00a0\u00a0 Notes<\/code><\/div>\n
      ----\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ----\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ------\u00a0\u00a0\u00a0\u00a0 -----<\/code><\/div>\n
      Login - SqlLogin loginName\u00a0\u00a0\u00a0 Successful<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

      Obviously, there are many drawbacks to this process;<\/p>\n