How to Create Tables/Move Data to SQL Server?

Question: I have created MS SQL database using Database Manager in Hosting Controller. How do I create tables or move my existing local MS SQL database along with its data, to any of my servers in cluster?

Solution:
This article outlines steps to move local MS SQL database to any of your server in the cluster.

Steps are as follows:

1. First of all you will create a database on any of the database servers in your cluster.
2. Then you will take backup of the local database using Database Manager.

Note: HC allows you to take backups for the databases, but only those which are created using the panel Database Manager. Here the backup is required for an existing local database and that can be done using Database Manager.

    * Lastly, you will restore this database using Database Manager.

Step 1: Create MS SQL Database

First step is database creation on any of the server in your cluster having Database Server role.

To create database, follow the steps below:

   1. Login into HC as ‘Reseller/Webadmin’. (Host can not create Database).
   2. Click Hosting Services menu.
   3. Click Add Database under Manage Database sub-section.
   4. Enter the following information on Add Database page:

          * Database Type: Identifies type of database. Either:
            [MS-SQL Server] - set database type to MS SQL.
            [MySQL] - set database type to MySQL.
          * Associated Website: Specifies website you want to link the database.
          * Database Name: Unique name identifying the database in MS SQL server.
          * Database Login: Unique name identifying the login name for newly created database. HC creates this new login in MS SQL and assign rights to the database.
          * Password: Password for security and authentication of database.
          * Confirm Password: Specifies re-entering the password confirmation of database.
          * Click Add Database button.

Step 2: Create Backup of local MS SQL database

SQL database backup creates a backup of your local database which can later be restored on any server in your cluster.

To create backup, follow the steps below:

   1. Open SQL Server Enterprise Manager. Expand a server group, and then expand a server.
   2. Expand Databases, right-click the database, point to All Tasks, and then click Backup Database.
   3. In the Name box, type the backup set name. Optionally, in Description, type a description of the backup set.
   4. Under Backup, click Database - complete.
   5. Under Destination, click Tape or Disk, and then specify a backup destination. If no backup destination appears, click Add to add an existing destination or to create a new one. Click OK button.
   6. Upload this database backup to /db folder of your website.

Note: This is the same website which you have entered as Associated Website in HC during creating the database in first step.

Step 3: Restore the MS SQL backup on any of HC server

To restore MS SQL database, follow the steps below:

   1. Login into HC as ‘Reseller/Webadmin’. (Host can not restore Database).
   2. Click Hosting Services menu.
   3. Click on Manage Database, by default you will be displayed your created Databases through HC.
   4. Select the database you created in Step 1 and click on Restore button. Browse to the /db folder and select the database backup created and uploaded to /db folder in Step 2 above. Click Select File button.
   5. The local database will be restored in the HC created database.

The above three steps will restore all the tables, procedures, view etc along with their data to remote MS SQL server on hosting server.

The following procedure guides you how to create tables in remote MS SQL server if you don't have database backup or this is a fresh database.

To create new tables in MS SQL server, follow the steps below:

   1. Follow the Step 1 above to create a database in MS SQL server.
   2. Create tables in MS SQL server programmatically using following format.

          * CREATE TABLE tablename (columnName datatype [primary key][NOT] NULL [,]).
          * CREATE [unique/clustered] INDEX indexname ON tablename(columnName(s)).