SQL Database Mirroring Steps

1. Delete if any endpoint exist for mirroring from principal and Mirror server
a. Select endpoint
i. SELECT * FROM sys.tcp_endpoints
b. Delete Endpoint
i. Drop endpoint <endpoint name>
2. Create Firewall rule
a. Principal database server
i. Create outbound rule for that particular port
1. Rule type: Port
2. Protocol type: TCP, Specific remote port: <Port number>
3. All The connection
4. Enable all (Domain, private, public)
5. Name the rule.
ii. Create Inbound rule for that particular port
b. Mirror database server
i. Inbound Rule
ii. Outbound Rule
3. Create endpoint in both server
CREATE ENDPOINT <Endpoint Name>
STATE = STARTED
AS TCP (LISTENER_PORT = 5555)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
4. All port number must be same and confirm that no any port is in use.
5. Set recovery model to Full in principal database
a. Property – > option -> Recovery model -> Full.
6. Generate full backup from Principal database.
7. Restore database from backup in Mirror server.
a. Make sure that database name and path will be same.
8. Create mirror from principal database
a. Principal database -> property -> mirror -> Configure security -> next -> Service account (Leave it blank). -> start mirr

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s