Pricing  Get Quote
 
 
Knowledge Base
Home » Knowledge Base

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

  1. Open SQL Server Configuration Manager on the computer running the MS SQL instance that the database and/or data will be moved to.
  2. In the left pane, click SQL Server Services to ensure that the SQL Server Browser is running.

    3. Configuring the MS SQL server

  3. 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.

  4. Enable TCP/IP.

    5. Configuring the MS SQL server

  5. 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.

  1. To grant access, login to Microsoft SQL Server Management Studio with an account that has been assigned the sysadmin role.

    2. 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.

    • Right click the database → New Query.
    • Enter the following query.

      GRANT CONTROL ON CERTIFICATE::[ZOHO_CERT] TO [newly_created_user]

  2. 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.
  3. 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.

SQL Server version Command Line Utilities (cmdlnutils) Files to be download for CmdLnUtils Native Client (ncli) File to be download for Native client ODBC Driver (odbc) File to be downloaded for ODBC Driver
2008 https://www.microsoft.com/en-in/download/details.aspx?id=44272 SqlCmdLnUtils.msi https://www.microsoft.com/en-in/download/details.aspx?id=44272 sqlncli.msi Not Needed Not Needed
2012 64 bit: http://go.microsoft.com/fwlink/?LinkID=239650&clcid=0x409
32 bit: http://go.microsoft.com/fwlink/?LinkID=239649&clcid=0x409
Ref: https://www.microsoft.com/en-us/download/details.aspx?id=29065		 The previous column contains the download link. https://www.microsoft.com/en-in/download/details.aspx?id=50402 sqlncli.msi Not Needed Not Needed
2014 https://www.microsoft.com/en-US/download/details.aspx?id=53164 MsSqlCmdLnUtils.msi Not Needed Not Needed https://www.microsoft.com/en-in/download/details.aspx?id=36434 msodbcsql.msi
2016 https://www.microsoft.com/en-us/download/details.aspx?id=56833 MsSqlCmdLnUtils.msi Not Needed Not Needed https://www.microsoft.com/en-us/download/details.aspx?id=56833 msodbcsql.msi
2017 https://www.microsoft.com/en-us/download/details.aspx?id=53591 MsSqlCmdLnUtils.msi Not Needed Not Needed https://www.microsoft.com/en-us/download/details.aspx?id=53339 msodbcsql.msi
2019 64 bit: https://go.microsoft.com/fwlink/?linkid=2230791
32 bit: https://go.microsoft.com/fwlink/?linkid=2231320		 MsSqlCmdLnUtils.msi Not Needed Not Needed 64 bit: https://go.microsoft.com/fwlink/?linkid=2223304
32 bit: https://go.microsoft.com/fwlink/?linkid=2223303		 msodbcsql.msi

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)

  1. UDP port number is 1434.
  2. 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.
  3. Open the UDP and TCP ports under firewall settings.

5. Move database and/or data

  1. Stop SharePoint Manager Plus.
  2. 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.

    3. Move database and/or data

  3. 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

 

Request Support

Need further assistance? Fill this form, and we'll contact you rightaway.