AI Docs

Migrating to a new SQL instance | v8.4+

2 min read

certero logo_cropped_png-01 1.png

This article is intended for organizations that are running Certero v8.4+ and have their SQL provisioned on a separate server to the Certero application.

Preparing the SQL Instance

Before the new SQL instance can be added to Certero Provisioning, the authentication between the Certero server and the SQL instance needs to be in place.

Please modify the SQL statements below and run them against the new SQL instance.

CREATE LOGIN [DOMAIN\SERVERNAME$]
FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\SERVERNAME$]
GO

Once this has been completed login to Certero Provisioning and select the SQL Instance tab at the top of the screen. Click the plus icon + near the top right of the screen to open the New SQL Instance properties.

In the Name field, enter the new SQL instance name in the format SERVERNAME\INSTANCENAME for a named instance, or SERVERNAME for the default instance. TAB to Description to verify the authentication has been configured correctly, you will see a green tick, then click Save .

Perform a backup

Select the Tenants tab at the top of the screen and on the actions menu

Next to the tenant name select the Backup option. Enter a description (e.g. Pre Migration backup) and click Submit to perform a backup.

Moving your instance of Certero to the new SQL Instance

Again, select the Tenants tab at the top of the screen and on the actions menu

Next to the tenant name select the Configure option. On the next page, select the Database tab near the top of the screen and use the drop-down to select the new SQL Instance and then click Save . Click OK to confirm your request to migrate the Certero databases to the new SQL instance.

Once the migration is completed the required SQL server role can be changed to dbcreator. Again modify the SQL statement below and run it against the new SQL instance.

ALTER SERVER ROLE [dbcreator] ADD MEMBER [DOMAIN\SERVERNAME$]
GO
ALTER SERVER ROLE [sysadmin] DROP MEMBER [DOMAIN\SERVERNAME$]
GO