Submit a ticketCall us

Announcing NCM 7.7
With NCM 7.7, you can examine the rules that make up an access control list for a Cisco ASA device. Then you can apply filters to display only rules that meet the specified criteria, order the rules by line number or by the hit count, and much more.
See new features and improvements.

Home > Success Center > Database Performance Analyzer (DPA) > Move a DPA repository on SQL Server to Azure SQL

Move a DPA repository on SQL Server to Azure SQL

Updated February 14, 2017

Overview

This article explains how to move a DPA repository from an on-premises SQL Server instance to an Azure SQL instance in the cloud.

If you need to move a SQL Server, Oracle, or MySQL repository, see the following:

Prerequisites

This article assumes that the target Azure SQL database instance is not created yet.

Make sure that:

  • The SQL Server database instance is compatible with the Azure SQL database version
  • Microsoft SQL Server Management Studio is compatible with the Azure SQL database version

For more information:

Environment

  • DPA 11.0 and later

Steps

  1. In DPA, click Stop All Monitors in the upper right.
  2. Shut down DPA.
    • Windows: Stop the IgnitePI service.
    • Linux: Run shutdown.sh in the DPA installation directory. Ensure there are no crontab entries that will restart DPA.
  3. Open Microsoft SQL Server Management Studio, and connect to the SQL Server instance.
  4. Right-click the database you want to move, and click Tasks > Deploy Database to Microsoft Azure SQL database.
    1. Connect to the Azure SQL database.
    2. Specify a new database name.
    3. Specify the Azure SQL database settings. See DPA Requirements for the supported Azure SQL database tiers.
    4. Review the summary and finish the wizard.
  5. On the DPA server, open the repo.properties file in a text editor.
    • Windows: This file is located at DPA_install_dir\iwc\tomcat\ignite_config\idc
    • Linux: This file is located at DPA_home/iwc/tomcat/ignite_config/idc
  6. Edit the values for the following lines to point to the new repository:
    repo.databaseType=Azure SQL Database
    repo.databaseName=
    repo.host=
    repo.user=
    repo.password=
    
  7. Start DPA.
    • Windows: Start the IgnitePI service.
    • Linux: Run startup.sh in the DPA installation directory.
  8. In DPA, make sure the server connects to the new Azure SQL repository database.
  9. Review the status of the database monitors, and start or stop them accordingly.
  10. Review the DPA log for errors.
  11. Wait for 10 minutes, and drill down into each monitored database instance.
  12. Make sure that you see data from the last 10 minutes for the database instances.

Troubleshooting

The database compatibility level must be set to 130 (for V12 template). See the following KB article from Microsoft:

Delete all unnecessary users.

  • Delete all users with Windows authentication.
  • Disable or delete the guest user. When you this user, it will be created again but with a disabled login.
  • If on Startup you see errors for permissions not grated on a table and or schema it could be your database tables are in dbo or ignite for the schema  for example [select V from CONPRM where P = ?]; SQL state [S0005]; error code [229]; The SELECT permission was denied on the object 'CONPRM', database 'Dpa', schema 'dbo'.;  

Migration Wizard alternative

You can use other tools for migration, such as the SQL Database Migration Wizard (SQLAzureMW).

  1. In DPA, click Stop All Monitors in the upper right.
  2. Shut down DPA.
    • Windows: Stop the IgnitePI service.
    • Linux: Run shutdown.sh in the DPA installation directory. Ensure there are no crontab entries that will restart DPA.
  3. Open the SQLAzureMW application.
  4. Select the Analyze/Migrate action.
    1. Select Target Server as the SQL database latest service version.
    2. Connect to your on-premises SQL Server.
    3. Select the database instance you want to move.
    4. Select the objects you want to move, such as schemas and tables.
    5. Click Run Analysis. A SQL script is generated.
  5. Connect to the Azure SQL Server.
    1. Create a new database. See DPA Requirements for the supported Azure SQL database tiers.
    2. Click Run Migration. The SQL script is applied.
    3. Finish the wizard.
  6. Connect to the Azure SQL database directly.
  7. Create a DB user which will be used for the connection. DPA databases can have the objects in the [dbo] Schema or the [ignite] schema check the schema of the tables in your installation when granting the permissions below. 
    CREATE USER <USER> WITH PASSWORD=N'<PASSWORD>', DEFAULT_SCHEMA = [ignite];
    ALTER ROLE db_owner ADD member <USER>;
    
  8. On the DPA server, open the repo.properties file in a text editor.
    • Windows: This file is located at DPA_install_dir\iwc\tomcat\ignite_config\idc
    • Linux: This file is located at DPA_home/iwc/tomcat/ignite_config/idc
  9. Edit the values for the following lines to point to the new repository:
    repo.databaseType=Azure SQL Database
    repo.databaseName=
    repo.host=
    repo.user=
    repo.password=
    
  10. Start DPA.
    • Windows: Start the IgnitePI service.
    • Linux: Run startup.sh in the DPA installation directory.
  11. In DPA, make sure the server connects to the new Azure SQL repository database.
  12. Review the status of the database monitors, and start or stop them accordingly.
  13. Review the DPA log for errors.
  14. Wait for 10 minutes, and drill down into each monitored database instance.
  15. Make sure that you see data from the last 10 minutes for the database instances.
Last modified
10:42, 22 Sep 2017

Tags

Classifications

Public