Steps to migrate ADManager Plus database from PostgreSQL or MySQL to Microsoft SQL

Description: Here is the guide to migrate ADManager Plus database from PostgreSQL or MySQL to Microsoft SQL in ADManager Plus.

By default, ADManager Plus comes bundled with PostgreSQL database. To move the database from PostgreSQL or MySQL to Microsoft SQL, set up the prerequisites first and then initiate the migration.

Prerequisites to initiate migration

1. Configuring the Microsoft SQL server
2. Providing permissions to the Microsoft SQL instance
3. Enabling communication with the Microsoft instance
4. Opening UDP and TCP ports (applicable only if the firewall is enabled in the Microsoft SQL Server computer)
5. Moving the database

Microsoft SQL supported versions

The following versions of Microsoft SQL are supported:

• SQL Server 2005
• SQL Server 2008
• SQL Server 2008 R2
• SQL Server 2012
• SQL Server 2014
• SQL Server 2016
• SQL Server 2017
• SQL Server 2019

A. Configuring the Microsoft SQL server

1. Open the SQL Server Configuration Manager on the computer running the Microsoft SQL instance (to which the database has to be moved).
2. In the left pane, click SQL Server Services and ensure that the SQL Server browser is running.
3. In the left pane, click SQL Server Network Configuration > Select Protocols for MSSQLServer for the given instance > Right-click TCP/IP> Enable.

   Note: The given instance refers to the Microsoft SQL instance to which the database has to be moved.

4. In the left pane, click SQL Native Client Configuration > Select Client Protocols for the given instance > Right-click TCP/IP > Enable.
5. Restart the SQL Server Service.

B. Providing permissions to the Microsoft SQL instance

The user account associated with ADManager Plus must have access and appropriate permissions to the Microsoft SQL instance (to which the database has to be moved).

Note: If SQL Server Authentication is used to move the database, access and permissions to the Microsoft SQL instance are automatically provided. However, when Windows Authentication is used, access and permissions have to be granted, as explained below.

1. To grant access: Log into Microsoft SQL Server Management Studio with an account that has been assigned the sysadmin role > Select the server instance to which you will be migrating > Security > Logins. Check whether the user running ADManager Plus is on the list and follow the steps below:
   • If the user is already listed, proceed to B(ii).
   • If the user is not listed, right-click Logins > New Login > Create a new login by providing a new user name and password > Proceed to B(ii).
2. To grant permissions: Right-click on the user > Properties > Server Roles > Check whether the user has been assigned the sysadmin role and follow the steps below:
   • If yes, proceed to section C.
   • If not, select sysadmin in the checkbox > click OK > Proceed to section C.

   Note: To grant the user only the minimum permissions required instead of a sysadmin role, follow the two steps below.

   1. Right-click on the user > Properties > User Mapping > Select db_datareader, db_datawriter, db_ddladmin in the checkbox > click OK.
   2. Right-click on the database > Properties > Permissions > Provide Execute permission for the user and click OK.

   Also, execute the below query in the database:

   Right-click on the database > New Query > GRANT CONTROL ON CERTIFICATE::[ZOHO_CERT] TO [newly_created_user]

C. Enabling communication with the Microsoft SQL server

If ADManager Plus and the Microsoft SQL instance are running on different computers, download and install SQL Native Client, Command Line Utilities, and ODBC Driver on the computer on which ADManager Plus is running.

Microsoft SQL Server version	Command line utilities (cmdlnutils)	Native client (ncli)	ODBC driver (odbc)
2008	https://www.microsoft.com/en-in/download/details.aspx?id=44272	https://www.microsoft.com/en-in/download/details.aspx?id=44272	Not needed
2012	https://www.microsoft.com/en-in/download/details.aspx?id=43351	https://www.microsoft.com/en-in/download/details.aspx?id=50402	Not needed
2014	https://www.microsoft.com/en-US/download/details.aspx?id=53164	Not needed	https://www.microsoft.com/en-in/download/details.aspx?id=36434
2016, 2017, and 2019	https://www.microsoft.com/en-us/download/details.aspx?id=56833	Not needed	https://www.microsoft.com/en-us/download/details.aspx?id=56833

Note: Native client, command line utilities, and ODBC driver version has to be the same as the Microsoft SQL version (to which the database has to be moved).

Copy the following files from the Microsoft SQL server installation folder to the ADManager Plus bin folder:

• bcp.exe- <MSSQL Installation Dir>\MSSQL Server\Client SDK\ODBC\?<version>\Tools\Binn\bcp.exe
• bcp.rll- <MSSQL Installation Dir>\MS SQL Server\Client SDK\ODBC\<version>\Tools\Binn\Resources\1033\bcp.rll

D. Opening UDP and TCP ports (applicable only if the firewall is enabled in the Microsoft SQL server computer)

• UDP port number is 1434.
• To find the TCP port number, open SQL Server Configuration Manager on the computer where the Microsoft SQL instance (to which the database has to be moved) resides. Go to SQL Server Network Configuration > Protocols for MSSQLServer > Right-click TCP/IP > Properties > IP Addresses > IPALL > TCP Port Number.
• Open the UDP and TCP ports under firewall settings.

E. Moving the database

• Stop ADManager Plus.
• Invoke (ADManager Plus Home)\bin\ChangeDB.bat in command prompt. Make sure that the user running the command prompt is same as the one running ADManager Plus.

  

• A database configuration wizard will pop-up > Select server type as Microsoft SQL > Select the Host Name, Instance Name, and Database Name.

  Note: The name of the Microsoft SQL server instance has to be entered manually in case you have not chosen the Auto Discover option.

  Tip: To learn how to create an SSL certificate in Microsoft SQL server, follow the step 1 found in this link.

• Select the Authentication type. If you have selected Windows Authentication, the credentials are automatically taken. If you have selected SQL Server Authentication, enter the corresponding credentials.

  Note: If SQL Server Authentication is used to move the database, access and permissions to the Microsoft SQL instance are automatically provided. However, when Windows Authentication is used, access and permissions have to be granted, as explained in section B under pre-requisites.

• Click Test Connection to check whether the credentials are correct.
• Click Save.

The migration procedure will start, and it will take a few minutes to complete. Once the migration is successful, you will get the following screen.



 

Select a language to translate the contents of this web page: