Migrating Your SQL Server to a Standalone Database Server

Knowledgebase version 7.0 and later.

Follow the steps below to migrate your SQL Server from the Application Server to its own server:

Important Note 1: This article is intended for the use of a network administrator with good working knowledge of the technologies used in a modern network.

Important Note 2: Prior to Installation of the Knowledgebase Databases on a Microsoft SQL 2005 machine you must grant the user who is performing the installation Create Database permissions within the SQL Server Management Studio. Open the SQL Server Management Studio and right click on the SQL Server, go to Properties and then select Permissions from the Server Properties window. Find the user who is performing the install and grant these permissions.

Important Note 3: You cannot restore a SQL 2000 Data Base to a SQL 2005 Server. SQL 2005 is not backwards compatible with SQL 2000. You will have to upgrade your SQL 2000 machine to SQL 2005 if you are migrating from SQL 2000 to SQL 2005.

1. Make sure that your current installation of Knowledgebase has all the appropriate patches and updates before performing the rest of the migration.
   • Consult the support site support.moxiesoft.com to verify that you are up to date.
     
2. Install SQL server on the new machine.
   • Verify that both machines are SQL 2005, as SQL 2005 is not backward compatible with any previous version of SQL. 
   • Also, verify that the target server has all relevant security, OS, and application patch updates from Microsoft prior to install.
     
3. On the old machine back up the Knowledgebase Databases. They are: ecrmAdmin, ecrmKB, ecrmLogs, ecrmReport, ecrmUser, ecrmImport. These will be used to restore the Databases on the new machine.
   
4. On the new SQL server you will need to create a user account for use by the Knowledgebase.
   • You do this in the SQL Enterprise manager by opening up Microsoft SQL Servers selecting SQL Server Group then Local. Select Security and finally, Logins.
   • Create the account as follows:
   1. User name: kbadmin
   2. Password: Contact Moxie Software Product Support for this password.
   • It is imperative that you create this account exactly as instructed and do not change the password. Please grant this account Sysadmin permissions.
     
5. Install the Databases using the Database setup from the installation files. Please refer to the Installation Guide for your version of the Knowledgebase application.
   
   • This is very important to note: You must upgrade the Knowledgebase databases to the current production version that is currently installed and running your original source SQL server. For example, if your current version is 7.0.1 then you must install the database portion of the Knowledgebase version 7.0.1 on the new destination SQL box and then you can then restore from the backup files.
     
     In the event that you are also upgrading your Knowledgebase software, you do not need to install and upgrade from version 7.0. You can start at version 8.0 and upgrade to version 8.0.1 and then restore.
     
6. Move a copy of the Knowledgebase Database backup to the new server.
   • Once they are on the new server begin the process of restoring the Databases.
     
7. When the restore process is complete go back to the Application server and begin the process of pointing the Knowledgebase to the new server.
   • You can accomplish this by running both the Admin and Portal configuration utilities such as you did on initial Knowledgebase setup.
   • This time you will only run the Database connections tab.
   • Enter the IP of the new SQL server and run the utility. This will create the DSN’s pointed to the new server.
     
8. You will need to configure the new server to accept Remote connections to the Databases.
   • The following is an Microsoft Knowledgebase article explaining how to accomplish this:
     • http://support.microsoft.com/kb/914277.
   • It is important to remember: You will need to stop and re-start the SQL server when you are done making these changes.
     

Important Note: This article is intended for the use of an experienced network administrator.