UPDATE: This article was originally written prior to the release of CentreStack version 9.5.5409.41172. With the release of version 9.5.5409.41172, the default all-in-one database engine is now MySQL Community Edition therefore this article may not be applicable if CentreStack was originally deployed using version 9.5.5409.41172 or later. For more information, please refer to this article:
MySQL Community Edition is the Default Database
In some cases it may be necessary to migrate the CentreStack database from the local Microsoft SQL Server Express Edition instance to an external Microsoft SQL Server (Standard or Enterprise edition). Reasons for migration could include some of the following:
- Overcoming the 10 GB database limitation of SQL Express Edition.
- Fault tolerance
The instructions assume that you already have a working external SQL Server. It is important that SQL Authentication is enabled on the external SQL Server in the SQL Server's security properties:
- Sign into (via RDP or console) the CentreStack server as user with administrator privileges.
- Install SQL Server Management Studio on the CentreStack server. The latest version may be used as it is backward compatible with the version of SQL Server 2012 Express on the CentreStack server.
- Stop the IIS Web Server on the CentreStack server. Start an elevated command prompt and execute:
net stop GCMon
- Run SQL Server Management Studio
- In the Connect to Server dialog, in the Server name drop-down, select <Browse for more...>
- In the Local Servers tab, expand the Database Engine node, then select the %COMPUTERNAME%\CENTRESTACK instance:
- In the Connect to Server dialog, leave the Authentication as Windows Authentication, then click the Connect button.
- Expand the databases node, and locate the user database. By default, CentreStack creates the user database.
- Right click on the user database and click Properties from the context menu.
- Click the Files node, then maximize the Database Properties window:
- Make note of the Path of the database files. By default, the Data file for the user database is: "C:\Program Files\Microsoft SQL Server\MSSQL11.CENTRESTACK\MSSQL\DATA\user.mdf" and the Log file is: "C:\Program Files\Microsoft SQL Server\MSSQL11.CENTRESTACK\MSSQL\DATA\user_log.ldf.
- Close the Database Properties dialog by clicking the Cancel button.
- Right-click on the user database, click Tasks, then click Detach...:
- Enable the Drop Connections and Update Statistics options, then click the OK button:
- After the user database is detached, move the files to the external SQL server in an appropriate directory. For best performance, it's recommended to put the database file and the log file on separate volumes, if possible.
- You may also want to rename the files, if you plan to rename the database. For the purposes of this document user.mdf was be copied to centrestack.mdf and user_log.ldf was copied to centrestack_log.ldf, and the database will be attached as centrestack at attach time.
- Connect SQL Server Management Studio to the external SQL server using an account with sysadmin privileges (using either a Windows account or a SQL account like 'sa')
- Right click on the Databases node and select Attach...
- In the Databases to attach section, click the Add button, then navigate to the location where the centrestack.mdf file was copied to.
- Select the centrestack.mdf file then click the OK button:
- Initially the database details section will show the user* file names:
- Click each ellipses button to update each Original File Name such that the Data file is centrestack.mdf and the Log file is centrestack_log.mdf:
- Click the OK button in the Attach Databases dialog.
- You should see the database listed under the Databases node.
- Create a new SQL user:
- In the General property sheet:
- Login name: centrestack
- Some complex password
- Set the Enforce password policy appropriate for your organization
- Set the Default database to the name of the recently attached database (user or centrestack):
- In the User Mapping tab, select the centrestack database and enable the db_owner option then click the OK button:
- In the General property sheet:
- On Centrestack server, run "C:\Program Files (x86)\Gladinet Cloud Enterprise\ChangeDBSettings.exe" as Administrator. In the tool, type in the new SQL database information and click the Test Connection button:
- If the Test Connection button is updated to Test Connection - True, then click the OK button:
- Click the Cancel button to close the tool.
- Run these commands in an elevated command prompt to start the CentreStack service and the IIS Web Server:
net start GCMon iisreset /start
- In a web browser on the CentreStack server navigate to: http://localhost/portal/loginpage.aspx
- If you add a new user in the CentreStack portal, you should see a new record created in the xaf_User table:
SELECT TOP (10) *
ORDER BY Created DESC