Once you decide whether a SQL Diagnostic Manager Migration or Recovery is best for your environment, create a plan so that when the time comes, you are prepared and get SQL Diagnostic Manager up and running as quickly as possible.
Creating a Migration Plan
A migration plan details moving the SQL Diagnostic Manager Repository and Services to another SQL Server instance, thereby replacing the original components. You can use a migration plan to respond to an immediate maintenance need. Use the procedures and guidelines in this document to implement or modify your migration plan.
Creating a Recovery Plan
A disaster recovery plan details the steps to remedy unexpected outages to make sure you can continue monitoring SQL Server activity and performance metrics. This document addresses disaster recovery best-practices for establishing a new Repository.
When you implement SQL Diagnostic Manager in your production SQL Server environment, consider preparing a disaster recovery plan to minimize audit data loss should the SQL Diagnostic Manager Repository become unavailable. Use the procedures and guidelines covered in this document to implement or modify your disaster recovery plan.
Understanding the Repository Database
The SQL Diagnostic Manager Repository consists of a SQL Server database named, by default, SQLdmRepository. This database contains the following information:
- Performance metrics and statistics collected from your monitored SQL Server instances
- Configuration settings such as the connection information for the SQL Diagnostic Manager services and alert notification rules
- Alert messages
By default, the Repository database uses the simple recovery model. When this setting is enabled, SQL Server does not maintain the transaction logs for the database. Likewise, any existing transaction logs are not included in backup data. If your corporate policies require transaction log backups, consider changing the recovery model to full so that transaction logs are maintained and archived.
Understanding SQL Diagnostic Manager services
SQL Diagnostic Manager has two centralized services, the Management Service and the Collection Service. These two services reside on the same computer.
The Management Service performs the following primary functions:
- Provides real-time data to the SQL Diagnostic Manager Console
- Receives historical data from the Collection Service for storage in the Repository
- Raises alerts and sends alert notifications
The Collection Service performs on-demand and scheduled collection from the monitored SQL Server instances.
Recovery and Migration Best Practices
Verify the Configuration of the Target SQL Server
When identifying the new SQL Server instance that you want to host the Repository and Services, make sure this instance meets or exceeds the product requirements as well as these specific requirements.
- You can dedicate the target instance to hosting the SQL Diagnostic Manager only
- The target instance runs the same version of SQL Server software that is currently running on the existing SQL Diagnostic Manager computer
Back up the Repository Database
Use a tool such as IDERA SQL Safe to perform a full backup of the Repository database. If you changed the default recovery model to full, make sure your backup includes all transaction logs.
Identify how often to backup the Repository database
The frequency at which you backup the Repository database depends on the following factors:
- How often your alert settings change
- How often your SQL Server environment changes as you add new servers and databases or remove older servers and database
- How much performance data you collect in a given time period
- How much risk you are willing to incur
The backup frequency should reflect your maintenance needs and allow you to meet future monitoring requirements.
Schedule routine backups of the Repository database
After you identify the appropriate backup frequency for your monitoring needs, use a tool such as IDERA SQL Safe to schedule routine backups of the Repository database. If you changed the default recovery model to full, make sure your backup includes all transaction logs.
Review disaster recovery guidelines
Make sure your recovery strategy includes plans to reinstate the original computer once it is repaired. Consider the following guidelines:
- To minimize data loss, plan to back up the Repository database on the temporary Repository computer immediately before reinstating the original computer.
- Use the procedures in this Technical Solution to reinstate the Repository on the original computer and configure the Management Service.
- To verify all components were reinstated correctly, test your implementation.
- Uninstall the components you previously implemented on the temporary computer.