Sync Database migration

Hi Experts,

I have 3 sync database on SQL2012 want to move 2016 SQL database. May I know the procedure to move the database without downtime.

Parents Reply Children
  • Hi

    I'm assuming two of the databases are for Active Roles Synchronization Services, and the other is for the Collector and Report pack

    Synchronization Service

    Moving the Active Roles Synchronization Services databases is relatively straight forward. Jose and I put something together recently, which is published in the Active Roles knowledge base Steps to move ARS Sync Services DB (existing DB) (308190). It basically boils down to:

    1. Backup configuration file
    2. Backup databases
    3. stop the services
    4. copy the database onto your new SQL servers
    5. Attach the database
    6. Change the connection string.
    7. Restart the services
    8. Test
    9. Backup the new configuration file

    If you use password sync, this wont work during the migration from the old server to the one one (whilst the services are down).

    There are similar ways to do this, without need to jump into the registry, via the Configuration Wizard. The steps are pretty much the same from points 1 to 4, however if you were to restart the service (with the old DB detached, you'd get a error message when starting the Synchronization Service console, the error would be:

    On clicking Close, you'd get the below message, click Start Configuration Wizard

    When you get to Step 2 of the Instance Configuration Wizard, select "User an existing configuration"

    On Step 3 click next (you old configuration file will point to the old SQL server

    On Step 4, change the SQL Server, then click Finish

    Then your back to point 8 and 9 (potentially you might need to restart the service.

    Collector and Report Pack

    When you move the database, you'll need want to:

    Open the Active Roles Collector and Report Pack application:

    Select "Collect data from the network"

    Click Specify on the Configure Connection page

    Change the value in SQL Server (Servers DNS name), click ok

    Continue through the wizard click Next (setting options where required, potentially including setting up a scheduled task)

    Once the wizard is complete, the connection for the collector is done, 

    However the SSRS instance needs to have the DB changed (you may decide to deploy to a new 2016 SSRS instance and decommission the old instance. But for this I'm just going to tell you how to change the reference to the collector database.

    If you open your SSRS webpage, IE

    Open the Active Roles folder, then select SharedDataSources

    Click on the "Active Roles 7.x.x Report Data" entry

    Go to the Connection String value within the Properties page, and change the server name to your new server

    Click Test Connection, the apply

    Finally test

    Hope this helps, and I haven't missed anything