sp_validate_replica_hosts_as_publishers (Transact-SQL)

Artykuł
02/13/2015

sp_validate_replica_hosts_as_publishers is an extension of sp_validate_redirected_publisher that allows all secondary replicas to be validated, rather than just the current primary replica. sp_validate_replicat_hosts_as_publisher validates an entire AlwaysOn replication topology. sp_validate_replica_hosts_as_publishers must be executed directly on the distributor by using a remote desktop session to avoid a double-hop security error (21892).

Ikona łącza do tematu Transact-SQL Syntax Conventions

Składnia

sp_validate_replica_hosts_as_publishers 
    [ @original_publisher = ] 'original_publisher',
    [ @publisher_db = ] 'database_name', 
    [ @redirected_publisher = ] 'new_publisher' output

Arguments

[ @original_publisher = ] 'original_publisher'
The name of the instance of SQL Server that originally published the database. original_publisher is sysname, with no default.
[ @publisher_db = ] 'publisher_db'
The name of the database being published. publisher_db is sysname, with no default.
[ @redirected_publisher = ] 'redirected_publisher'
The target of redirection when sp_redirect_publisher was called for the original publisher/published database pair. redirected_publisher is sysname, with no default.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None.

Uwagi

If no entry exists for the publisher and the publishing database, sp_validate_redirected_publisher returns null for the output parameter @redirected\_publisher. Otherwise, the associated redirected publisher is returned, both on success and failure.

If the validation succeeds, sp_validate_redirected_publisher returns a success indication.

If the validation fails, appropriate errors are raised. sp_validate_redirected_publisher makes a best effort to raise all issues and not just the first encountered.

[!UWAGA]

sp_validate_replica_hosts_as_publishers will fail with the following error when validating secondary replica hosts that do not allow read access, or require read intent to be specified.

Msg 21899, Level 11, State 1, Procedure sp_hadr_verify_subscribers_at_publisher, Line 109

The query at the redirected publisher 'MyReplicaHostName' to determine whether there were sysserver entries for the subscribers of the original publisher 'MyOriginalPublisher' failed with error '976', error message 'Error 976, Level 14, State 1, Message: The target database, 'MyPublishedDB', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.

One or more publisher validation errors were encountered for replica host 'MyReplicaHostName'.

Permissions

Caller must either be a member of the sysadmin fixed server role, the db_owner fixed database role for the distribution database, or a member of a publication access list for a defined publication associated with the publisher database.