The package has several required parameters when you go to schedule it.

These are the parameters and what they do.



DropandRecreateLoginIfSIDMismatch Boolean (default false)

If you enable this flag it will check the secondary replicas and see if the login exists already. If it does it compares the SID between the primary and the secondary and will drop the existing login on the secondary and add the login back with the correct SID.


ListenerServerName String

You need to type the name of the listener for the Availability Group (AG) in for this parameter. This is how the package knows which server is primary and gets the list of logins that have access to the databases so it know which ones to apply to the secondary replicas.


SyncServerLevelRoleMembership Boolean (Default False)

If you enable this parameter, it tells the package to sync server level roles for new logins on the secondary replicas.


SyncServerLevelRoleMembershipEvenIfSysAdmin Boolean (Default False)

If you enable this flag it tells the package to include sysadmin server level role permissions to be synced to secondary replicas. This parameter has no effect if SyncServerLevelRoleMembership parameter is set to False.



There is one TSQL script in this project. (scripts for ag Login mirroring complete.sql)

You run this script on all replicas both primary and secondary in master database to build some stored procedures that the package will use for its processing.

 

When you schedule this in SQL Agent it will need permissions as a security admin on the secondary replicas.  You will also need access to master to run the stored procedures created by the script.  A good practice for doing this part is using Proxy Accounts for SQL Agent.

If you see this error during deployment, this is nothing to worry about.  this is telling you that it could not read my password data in the package code when you go to deploy it.  This is not necessary as I did not use password.  The package is set to use SSPI (Windows Authentication) by default.  That makes the error meaningless.  I will try and setup the package to avoid this error in the future.

clip_image002

Last edited Aug 10, 2012 at 1:38 PM by michaer, version 3

Comments

michaer Jul 20, 2015 at 7:58 PM 
it would be good to have a spare but you can run this from a different sql 2012 that is not a replica as well.

dyard12 Nov 19, 2013 at 8:17 PM 
If the primary fails over do we need to have the job set up on all replicas as well?