How to migrate SharePoint Manager Plus database from PostgreSQL to MS SQL
By default, SharePoint Manager Plus comes bundled with the PostgreSQL database. This page 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.
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.
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.
Note: To grant the user only the minimum permission required instead of sysadmin role, follow the 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.
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
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.
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.
Database Configuration wizard will pop-up. Select server type as MS SQL and Select the Host Name, Instance Name, and Database Name.
Note: The name of the MS SQL Server Instance has to be entered manually in case you have not chosen the Auto Discover option.
New to ADSelfService Plus?
Related Articles
How to move SharePoint Manager Plus to a new server
Points to be noted before moving SharePoint Manager Plus to a new location: Do not uninstall SharePoint Manager Plus from the current server until the installation on the new server works perfectly. If the operating system on the new server is ...How to granularly delegate SharePoint activities to non-administrative users
SharePoint Manager Plus offers non-invasive delegation where you can granularly delegate activities to non-administrative users. By assigning a role to a technician, they can perform only the activities associated with that particular role.SharePoint ...How to audit SharePoint document changes
Users perform various modifications to documents like creation, deletion, moving, renaming, checking-in, downloading, and more. With the audit logs generated by SharePoint Manager Plus, no action goes unnoticed. Steps to generate a document audit ...How to check user and group permissions
When permission inheritance breaks, it paves the way to make SharePoint more secure. However, it's also a hindrance to administration as it often demands you look up and ensure that each user or group has access to only what is necessary. Looking up ...How to copy users between groups
Instead of adding one user at a time into a SharePoint group, you can directly copy users from one SharePoint group to another across different site collections. Steps to copy users between groups: Click on the Management tab. Under Groups ...