QGrip and SQL Server Always On
Some complexity is added when managing databases running in a SQL Server Always On cluster. Good news is that QGrip does not only support SQL Server Always On clusters, it makes it easier to manage. QGrip will use the backup preferences of the Availability Group to decide on which replica a backup job should run. The Optimise job (rebuild index/update statistics) will always run on the replica that currently has the role Primary. To prevent connectivity problems after a Failover, QGrip also checks the logins on all instances in an Always On cluster and issues warnings when
- Login does not exist on all Instances,
- The SID of the login is not the same on all Instances,
- Login password is not the same on all Instances.
DB Host: Instance vs Listener
In QGrip, the difference between a ‘standard’ database and a database in an Availability Group on an SQL Server Always On cluster, is the DB Host. In case of Always On, the DB Host is the Listener, otherwise it is the Instance. When a QGrip user requests a Backup job, the user does not need to know on which replica the job should run. Just selecting the Listener and the Database is sufficient and QGrip will do the rest.
Restore & Clone
When QGrip is used, restoring a database participating in an Always On cluster is as simple as restoring a database on a Stand-Alone Instance. The same method is used when a database is cloned or imported to an Always On cluster. QGrip will automatically
- Remove the database from the Availability Group,
- Restore the backup files on all replicas simultaneously,
- Recover the database on the primary Replica,
- Add the database to the Availability Group,
- Run the Post Clone scripts against the Listener (Clone & Import Database).
For more details about the QGrip Restore, Clone and Import Database, check the document: Restore, Clone & Import Database
