Steps to migrate SharePoint Manager Plus database from PostgreSQL to MS SQL
Description: By default, SharePoint Manager Plus comes bundled with the PostgreSQL database. This guide will explain how you can migrate SharePoint Manager Plus database from PostgreSQL to MS SQL.
The following versions of MS SQL are supported by SharePoint Manager Plus:
- 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
To move the database and/or data from PostgreSQL to MS SQL, follow the steps below.
1. Configuring the MS SQL server
- Open SQL Server Configuration Manager on the computer running the MS SQL instance that the database and/or data will be moved to.
- In the left pane, click SQL Server Services to ensure that the SQL Server Browser is running.
![Configuring the MS SQL server](https://www.manageengine.com/za/sharepoint-management-reporting/kb/images/configuring-the-ms-sql-server-01.png)
- In the left pane, click SQL Server Network Configuration → Protocols for <the given instance>.
Note: <The given instance> refers to the MS SQL instance that the database and/or data will be moved to.
- Enable TCP/IP.
![Configuring the MS SQL server](https://www.manageengine.com/za/sharepoint-management-reporting/kb/images/configuring-the-ms-sql-server-02.png)
- Restart the SQL Server Service.
2. Providing permissions to the MS SQL instance
The user account associated with SharePoint Manager Plus must have access and appropriate permissions to the MS SQL instance (to which the database and/or data is to be moved).
Note: Access and permissions are automatically provided to the MS SQL instance, if the SQL Server Authentication is used to move the database and/or data. However, when Windows Authentication is used, access and permissions have to be granted.
- To grant access, login to Microsoft SQL Server Management Studio with an account that has been assigned the sysadmin role.
Note: To grant the user only the minimum permission required instead of sysadmin role, follow the two steps given below:
- Right click the User → Properties → User Mapping.
- Select db_datareader, db_datawriter, db_ddladmin in the checkbox and click OK.
- Right click the database → Properties → Permissions.
- Provide Execute permission for the user and click OK.
Also, execute the query below in the database.
- Select the server instance to which you will be migrating → Security → Logins. Check whether the user running SharePoint Manager Plus is on the list.
- If the user is already listed, Proceed to 2(iii).
- If the user is not listed, right click Logins → New Login → Create a new login.
- Enter the login name and mapping details. Click OK.
- To grant permissions, Right click on the User → Properties → Server Roles. Check whether the user has been assigned the sysadmin role. If yes, Proceed to 3. If not, select sysadmin in the checkbox and click OK.
3. Enabling communication with the MS SQL server
If SharePoint Manager Plus and the MS SQL instance are running on different computers, download and install SQL Native Client, Command Line Utilities, and ODBC Driver on the computer on which SharePoint Manager Plus is running.
Note: Native client, command line utilities, and ODBC driver version has to be the same as the MS SQL version (to which the database and/or data is to be moved).
Copy the following two files from the MS SQL server installation folder to the SharePoint Manager Plus bin folder:
- bcp.exe- <MSSQL Installation Dir>\Microsoft SQL Server\Client SDK\ODBC\<version>\Tools\Binn\bcp.exe
- bcp.rll- <MSSQL Installation Dir>\Microsoft SQL Server\Client SDK\ODBC\<version>\Tools\Binn\Resources\1033\bcp.rll
4. Opening UDP and TCP ports (applicable only if the firewall is enabled in the MS SQL Server computer)
- UDP port number is 1434.
- To find the TCP port number, open SQL Server Configuration Manager on the computer where the MS SQL instance to which the database and/or data is to be moved, resides.
- Select SQL Server Network Configuration → Protocols for <instance name>
- Right click on TCP/IP and select Properties.
- In TCP/IP dialog box, go to IP Addresses tab → IPALL → TCP Port Number.
- Open the UDP and TCP ports under firewall settings.
5. Move database and/or data
- Stop SharePoint Manager Plus.
- Invoke (SharePoint Manager Plus Home)\bin\changeDB.bat in Command Prompt. Make sure that the user running the command prompt is the same as the one running SharePoint Manager Plus.
![Move database and/or data](https://www.manageengine.com/za/sharepoint-management-reporting/kb/images/move-database-and-or-data-01.png)
- Database Configuration wizard will pop-up. Select server type as MS SQL and Select the Host Name, Instance Name, and Database Name.
Note: Native client, command line utilities, and ODBC driver version has to be the same as the MS SQL version (to which the database and/or data is to be moved).
![Move database and/or data](https://www.manageengine.com/za/sharepoint-management-reporting/kb/images/move-database-and-or-data-02.png)