Database Reference
In-Depth Information
Now, restart the Principal instance and Mirror instance to avoid any connectivity issues
between the instances.
3.
Set the partner for the MIRROR instance using the below script:
ALTER DATABASE <DatabaseName>
SET PARTNER = 'TCP://DBM-PRIM.DBIA-SSQA.com:5022'
Set the partner for PRINCIPAL instance using below script:
ALTER DATABASE <DatabaseName>
SET PARTNER = 'TCP://DBM-MIRO.DBIA-SSQA.com:5023'
If the DBM instances are not running under the same 'domain login', you will need to create
a windows login on each participating instance. This can be achieved with a TSQL script as
follows:
USE master
GO
CREATE LOGIN [SSQAPROD\DBMAdmin]FROM WINDOWS
GO
The previous script must be executed on a Mirror instance in order to allow access to the
principal and witness SQL Server instances. Additionally, you must explicitly grant the remote
login access to the configured endpoints.
GRANT CONNECT ON ENDPOINT::Mirroring TO [SSQAPROD\DBMAdmin]
GO
How it works...
Connection management for database mirroring is based on endpoints. An endpoint is
also treated as one of the server objects that enables SQL Server to communicate over the
network.
For database mirroring, a server instance requires a dedicated database mirroring endpoint to
receive database mirroring connections from other server instances. The database mirroring
endpoint of a server instance is associated with the port on which the instance listens for
database mirroring messages. Each database mirroring endpoint server listens on a unique
TCP port number.
The wizard or
TSQL
method will configure the endpoints including granting permissions to a
service account to communicate using the endpoint, as per the
CREATE
ENDPOINT
syntax.
Each SQL Server instance can have only one database mirroring endpoint. All databases in
that instance - that are involved in database mirroring - must use the same endpoint. If
the principal, mirroring, or witness instances exist on the same physical server then you are
required to use different port numbers on their individual endpoints.
Search WWH ::
Custom Search