CONCEPT: Synchronizing SQL Server Server Level Objects Between Nodes

SQL Server provides instance level fail-over in the form of shared disk clustering. In this configuration if a cluster node fails then the entire SQL Server instance fails to another node bringing with it all relevant objects and configurations.

Other SQL Server HA/DR methods are database level redundancy. This means that databases are synchronised but instance level objects are not included as part of the configuration. Examples of database level synchronisation include:

SQL Server Availability Groups

SQL Server Database Mirroring

SQL Server Log Shipping

SQL Server Mirroring

Examples of SQL Server Instance level configurations which will not automatically be synchronised in these configurations include:

SQL Server logins

SQL Server jobs

Linked servers

Audit configurations

The issue facing many database administrators, is while SQL Server provides a robust methods for synchronising database data, the instance level objects are often required for the database application to function correctly on fail over.

Without automated syncronization of these objects it is left to the DBA to manually track and manual sync objects as changes are made. This manual process is naturally error prone and the outcome can be detrimental, such as the database application not being able to function on fail over. In an extreme example, if the primary node is lost permanently the configuration of the server level objects (logins, jobs, linked servers) may also be lost permanently making the recovery of normal application function difficult and time consuming post failover.