This post is the sequel of my post Database Mirroring in SQL Server - Configuration. In this post, we'll see about what to do after Database Mirroring is configured, how to monitor a mirroring session and troubleshooting the mirroring configuration.
Post Mirroring Configuration
After Database Mirroring is configured, the following steps have to be taken to ensure a correct service to clients,
- The logins, jobs, SSIS packages etc that are created in Principal Instance must be created in Mirror Instance manually as they are not automatically replicated when mirroring is configured.
- Any new changes made on Principal such as adding new logins, mapping new users to databases should be reflected manually in the Mirror instance too.
Monitoring the Mirroring Session
After mirroring is configured, it can be checked using the following query,
SELECT
*
FROM
sys.database_mirroring
Run the above query in Principal and Mirror to know the status of instance, role etc. The first column of this view is database_id. For first records the values of this view is NULL because database_ids 1,2,3 and 4 are of system databases Master, Model, Msdb and Tempdb respectively. System databases cannot be mirrored, so they have NULL values.
For more information about this view, go here,
Database Mirroring Session can be monitored by using a built-in tool named Database Mirroring Monitor.
Go to Object Explorer, right click the Principal or Mirror Database. Select Tasks and then click Launch Database Mirroring Monitor.
The databases that are configured for Mirroring are registered automatically with this tool. If not, they can be registered manually by clicking Register Mirrored Database on Home screen. From there, Principal and Mirror can be connected and registered. After registering, Database Mirroring Monitor shows the status of Principal and Mirror and their connection status with Witness. It is refreshed automatically for every 30 seconds and shows any unsent log including its size, estimated time to send it from Principal to Mirror etc. There lies a button History on clicking which the synchronization history appears. The history results can be filtered according to the given options.
In the Warnings tab, warnings can be configured by setting desired threshold values for different kinds of warnings. When a configured threshold is exceeded, an event is logged to the Application Even Log. This can be configured as an email alert.
Removing Database Mirroring
Configuring something may be difficult sometimes but removing something is very easy. So is the case with Mirroring. Mirroring can be removed with just one mouse click.
In Object Explorer, right click on the Principal database and click Properties. Under Mirroring tab, click on Remove Mirroring button. You will be asked for a confirmation. Confirm if mirroring has to be removed and it will be removed. Now you've removed Mirroring but you're not done with it completely. In the same Database Properties window, you can still see a Server Network Address for your principal instance. This is because there are endpoints configured and they have to be removed. For this, connect to Principal, Mirror and Witness (if any) and run the following SQL statement.
DROP
ENDPOINT
<Endpoint-Name>;
With this command, mirroring is removed completely including its endpoints. The Mirror database remains in a RESTORING state as it was created WITH NO RECOVERY option. To bring it into normal state, restore its backup WITH RECOVERY option or just run the following command,
RESTORE
DATABASE
<Database_Name>
WITH
RECOVERY
Troubleshooting Database Mirroring Configuration
While configuring Database Mirroring on a single computer with three instances, an error rises with Error Message 1418. This message indicates that the server network address cannot be reached or does not exist. It asks you to verify the server network address and re-issue the command.
For this, there is a simple solution. Go to SQL Server Configuration Manager and right click on SQL Server service and click on Properties. Under Log On tab, select This Account. In the Account Name field, provide your local system Account Name or click on Browse and search your account name by using Check Name button. Provide and confirm your account password and click Apply. It asks for restarting the service, click Yes and the service will be restarted. Do the same procedure for Principal, Mirror and Witness instances.
Now go to Database Properties window and click on Start Mirroring button which starts your mirroring session.
No comments:
Post a Comment