HOW TO: Enable SQL Login Synchronisation for HA/DR/Distributed Databases

RockSolid allows administrations to automatically sync objects that are associated with databases in distributed configurations.  For more information see the concept article in the related topics below.

Enabling Login Synchronisation

RockSolid can synchronise logins which are associated with databases involved in HA/DR configurations, and logins associated with system databases.  To this follow this process:

  • Ensure that the source instances which require object synchronisation have Object Synchronisation enabled, either at the group level (recommended) or the instance level.
  • Navigate to the relevant level in the database hierarchy for where database synchronisation settings are to be defined.  If you use different database buckets for system and user databases you may need to define the synchronisation configuration at both levels.
  • Click on the Settings tab to view the database level policy.
  • Click on the Recovery sub-tab and locate the "Database HA/DR/Distribution Configuration" heading.
  • Enable "Object Synchronisation" for the relevant HA/DR types.
  • Enable the "Sync Database Login" option.

Once the configuration has been saved, RockSolid will monitor and raise Service Requests for logins not synchronised between matching databases on relevant instances.

Default Databases

Within SQL Server Availability Group configurations, the logins will be create as an exact copy of the alternate node logins.  However for database mirroring and database log shipping configurations there may be some variation.  

SQL Server does not allow the creation of logins which have a default database that is offline at the time of login creation.  Therefore it is not possible to create the logins with matching default database if the default database is set to the database being replicated.  Therefore in these cases RockSolid will change the default database to the [master] database.  Most applications do not require the default database to be set to correctly operate, so should perform normally.  However some applications may have this requirement, in which case will require the default database to be change after fail-over to ensure correct operation.

 

Related Topics

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.