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

The original version of this article described a technique of detaching the SQL Express database and copying it and attaching it to SQL Server. This process is no longer necessary and is not recommended because CentreStack 9.5.5409.41172 and later ships with a Migrate Database feature which will also update the schema of the 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:

• Performance/Scalability
• Overcoming the 10 GB database limitation of SQL Express Edition.
• Fault tolerance

Requirements

• The instructions assume that you already have a working external SQL Server.
• The TCP/IP Protocol must be enabled in SQL Server Configuration Manager:
  
• The SQL Server must be listening on TCP port 1433. Run this PowerShell command in an elevated PowerShell session to test if the process named sqlservr is listening on TCP port 1433. Paste this as one command, line breaks and all:
  

  ((Get-NetTCPConnection | 
   Where {$_.LocalPort -eq 1433 -and `
   $_.RemoteAddress -eq '0.0.0.0'}).OwningProcess `
  -eq (Get-Process | Where Name -eq sqlservr).Id)

  
  If the result is True, then you can be sure SQL Server is listening on TCP 1433.
• SQL Authentication must be enabled on the SQL Server in the SQL Server's security properties:
  
• Download and install Microsoft SQL Server Management Studio on the CentreStack server. See this link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

Recommendation

It is recommended that you manually create a database and a SQL user account (not a Windows account) in SQL Server prior to the database migration to follow the "principle of least privilege". This is not a requirement - the CentreStack server could sign into the SQL Server as the sa account in order to create and later access the database, but using the sa account is against the principle of least privilege.

Create the Database

1. Log in the Microsoft SQL Server Management Studio as a user with the sysadmin role.
2. Right click on the Databases node in Object Explorer's tree pane and click New Database...
3. The Database name is arbitrary but we'll use csmain in this example:
   
   If necessary, change the Path for the database file and transaction log file to a directory of your choosing if the server defaults aren't sufficient, then click the OK button.
4. Expand the Security node in the Object Explorer tree pane
5. Right click on the Logins node and select New Login...:
   
6. In the Login - New dialog:
   1. In the Login name text box use csuser (although this is arbitrary), enable SQL Server authentication and optionally disable the Enforce password policy option:
      
   2. Click the Server Roles node, then enable the dbcreator server role. NOTE: This step is a temporary change which is required at the time of this writing. A future release of CentreStack may eliminate this requirement. After the database is migrated the dbcreator role will be removed from the csuser account.
      
   3. Click on the User Mapping node then select the database that was created (in this case csmain) then select db_owner in the Database role membership section of the dialog:
      
   4. Click the OK button in the Login - New dialog to create the user.
7. Run Microsoft SQL Server Management Studio on the CentreStack server and connect to the remote SQL server:\
8. You should see that the account has access to the csmain database created for CentreStack:
   

Migration Instructions

1. Upgrade CentreStack to 9.5.5409.41172 or later.
2. In an interactive session on CentreStack (console or RDP), open a web browser and navigate to http://localhost.
3. Sign in as the cluster admin.
4. Navigate to: http://localhost/management/MigrateDatabase.aspx
5. Click on the drop down and select the SQL Server option:
   
6. Fill in all four text boxes with the appropriate data for the remove Microsoft SQL Server database then click the MIGRATE button:
   
7. I you "pre-created" the database (as recommend above), you will see this warning. Click CONTINUE:
   
8. When the database is successfully migrated the following message will be displayed:
   
9. Navigate to http://localhost and sign in as cluster administrator.
10. Run SQL Server Management Studio on the CentreStack server and sign into the remote SQL Server as csuser. 
11. Execute this query to verify that the most recent sign on of the cluster administrator is recorded in the new SQL database on the external SQL Server:
    

    SELECT TOP 10
     [UserEmail]
     ,[FullName]
     ,[Action]
     ,[Trace]
     ,[TimeStamp]
     FROM [csmain].[dbo].[xaf_Audit]
     ORDER BY TimeStamp DESC

12. After the database has been migrated remove the dbcreator permission from the csuser account:
    
13. On the CentreStack server, stop and disable these services as they will no longer be needed:
    SQL Server (CENTRESTACK)
    SQL Server Agent (CENTRESTACK)
    SQL Server Browser
    SQL Server VSS Writer