An important part of ensuring application functionality with Availability Groups is manually maintaining any uncontained objects across each of the replicas in the Availability Group. As more of our clients upgrade to SQL Server 2012 and implement Availability Groups, we’ve had to face the challenges of maintaining uncontained objects across replicas multiple times and there hasn’t been a good solution to the problem. No one solution is a perfect fit for every client, and one of the challenges is the difference in change control policies that might exist. Some environments might be agreeable to an SSIS package that runs nightly to copy all logins and jobs, where others require a script be generated to accomplish the same tasks.
To solve this problem, we developed the SQL Server 2012 Availability Group Add-in for SQL Server 2012 Management Studio specifically targeted at enhancing the UIs for Availability Groups. The add-in creates an additional menu in Object Explorer for the Availability Group node for easy access.
By clicking on the menu item, the Availability Group Synchronization UI form will open allowing you to easily step through the configuration of the different objects to synchronize and how. Currently the add-in supports synchronizing:
- User-defined Server Roles
- Server Logins and Permissions
- SQL Server Agent Jobs
The current output is a SQLCMD mode script in a new query window to allow an administrator to review all of the changes prior to changing SQL Server Management Studio to SQLCMD mode to execute the script. This requires an additional step, but also fulfills the requirements of many change control processes. We may evaluate based on feedback enabling the ability to automatically synchronize instead of generating a script in a future update to the add-in.
This add-in is under continuous development to add additional objects to synchronize and additional reporting for configuration reviews across replicas. The latest build of the add-in can be downloaded from the SQL Server 2012 Availability Group Add-in page on our site.