Occasionally you might encounter the need to move your CE databases from the existing Microsoft SQL Server to another SQL Server. For example if you perform a 'from-scratch' SQL upgrade next to the existing, working one.
Database migration prerequisites
Please note that in order to migrate the CE databases from one database environment to another, you will require database administration skills and privileges for attaching and detaching fiiles as well as sufficient Windows credentials to actually copy or move the files accross servers.
Database migration network prerequisites
Make sure your new Microsoft SQL Server service is accessible by the CE systems over the network exactly the same way as the existing (old) one was. This means all network connectivity parameters like routing, firewall rules, packet tagging, etc.
- Generate a 'user script' containing the database Users information only for both the ACESystem and ACEReport databases in the old (existing) database server.
Save the script into either an SQL Query Window in Microsoft SQL Server Management Studio application, or save it into a file.
Make sure you only script the User information, no other details should be scripted.
- Detach the ACESystem and ACEReport database in the old database server.
- Find the database and transaction log files in the old server and copy (or move) them into the proper directory on the new database server.
- Attach these databases into the new database server.
- The database user accounts are going to be travelling with the files, but you need to erase and recreate them on the new database server to have them work properly. First erase these accounts from the ACESystem and ACEReport databases in the new database system.
- Run each script generated in step 1. for their respective database in the new database server – this regenerates the proper user accounts used in the old database system.
Run the following 2 cmdlets on CE core server with local administrator permission
Set-CESystemDatabaseProperties -Fqdn [FQDN of SQL Server host B] -DatabaseName ACESystem -Login ACESystem -Password [ACESystem’s SQL password] -Timeout 120
Set-CEReportDatabaseProperties -Fqdn [FQDN of SQL Server host B] -DatabaseName ACEReport -Login ACEReport -Password [ACEReport’s SQL password] -Timeout 120
Restart CE using the Server Manager tool