Managing a SQL Server 2008 DB using SQL Server Management Studio

• Visual Studio 2008 Express, or
• Visual Web Developer 2010 Express, or
• SQL Server Management Studio (SSMS) 2008 Express

Earlier versions of the software listed above are not suitable and will not work. The software listed are available as a free download from Microsoft (the links were correct at the time of writing this article).

Before you Start

Before you can access your SQL Server 2008 database remotely you will need to contact our support team with some information. You do not need to contact them to use the database with our systems, you only need to contact them if you wish to connect from a remote location via Visual Studio or SSMS. This is due to the way the Control Panel sets-up databases, where the set-up requires some manual changes to be made so that your database is accessible via a remote connection.

Please send the following information to support@rackteam.com.

1. The name of the database
2. The UserAccount for this database
3. Your IP Address (for the first time you need to connect - after the first time a firewall rule will exist)

At the moment remote connections to databases are only available to those with a fixed IP but this is constantly under review and will be changed if required.

Instructions are for SQL Server Management Studio

The following instructions are for SQL Server Management Studio 2008.  If you are using later versions of the MS SQL Server or SQL Server Management Studio the process will be very similar and these instructions may be helpful to you.

How to Connect to your Database from a Remote Location

1. Open SQL Server Management Studio 2008 on your computer
2. Enter the following information for your database in the "Connect to Server" window
   • Server Type: Database Engine
   • Server Name: The remote IP address provided in the Control Panel when you created the database. This can vary between different databases.
   • Authentication: SQL Server Authentication
   • Login: The Database Username you created for this database
   • Password: The Password you created for the Database Username
     
     
     
     Once this information has been added and is correct, click on the "Connect" button.
3. You should now be connected to our database server via the remote connection.
   
   If you have not connected please check the following:
   1. You have contacted our support team and we have opened the firewall for your IP address (if relevant)
   2. Check all the details entered on the "Connect to Server" screen are correct
   3. There is not a firewall or router at your location that is stopping the connection being made (a relatively common issue)
   4. Check our monitoring reports for any errors with the SQL Server 2008 Database Servers. Our monitoring reports connect to the server from a remote location (just like your connection)
       
4. When you have connected, your screen should look similar to the one below:
   
   
   
   Click on the "Databases" folder to expand the list of databases. Your database should be listed.
   
   If your database is not listed it means you have not contacted our support team with your Database Name and User Account name.
   
   NOTE: Although you can see some system databases listed you cannot access these databases. If you attempt to access them or manipulate them in any way you will receive an error. These error reports show in the logs which are checked regularly. Attempting to access systems that you do not have permission to access may lead to suspension or cancelation of your hosting account.
5. You have now successfully connected to your database on our servers and can manage it remotely.

How to Upload your Database (or individual tables) from your Remote Location

Now that you have successfully connected to the server you may want to upload or download data from your location to the database server. The instructions that follow are for uploading a database from your location to our database servers. If you wanted to download the data from our database servers to our location, it would be done in a similar manner.

1. Connect to the database or your local computer or server, in the following way:
    
   • From SSMS (that is already open) click on "File" then "Connect Object Explorer". This will provide you with a new "Connect to Server" window as shown below.
   • Server Type: Database Engine
   • Server name: Your Local Server name or IP Address 
   • Authentication: SQL Server Authentication
   • Login: The Database Username for your LOCAL database (or use "Windows Authentication depending upon your setup)
   • Password: The Password for your LOCAL database
     
     
      
     
     
     Click on the "Connect" button and you should connect to your local database.
2. Expand your local database and right-click against it. 
3. Select Tasks | Export Data.  A new Wizard will open
4. Choose "next" on the Wizard
5. A new screen called "SQL Server Import and Export Wizard" will open up as shown below:
   
    
    
6. Complete the "import and export" wizard with the following information
    
   • Data Source: SQL Server Native Client 10.0
   • Server Name: You LOCAL Database Server name or IP Address
   • Authentication: Choose "SQL Server Authentication" or "Windows Authentication" depending upon your set-up
     
     If you select "SQL Server Authentication" then you will need to complete the rest of the boxes
   • Username: Your LOCAL database username
   • Password: Your LOCAL database password
   • Database: From the dropdown list, select the database you want to upload (i.e. the "source" database)
     
     Click on the "Next" button to continue.
     
      
7. Complete the new "SQL Server Import and Export Wizard" as shown below
   
   
    
    
   • Data Source: SQL Server Native Client 10.0
   • Server Name: The remote IP Address that was shown in the Control Panel when you created the database
   • Authentication: Choose "SQL Server Authentication"
   • Username: The "database username" created in HELM for this database
   • Password: The "password" created in HELM for this database user
   • Database: From the dropdown list, select the database you created in HELM for the Username and Password details provided.
     
     
     Click on the "Next" button
8. A new screen will open as shown below
   
   
   
   In this  case we want to "Copy data from one or more tables or views"
   
   Click on the "Next" button
    
9. A list of tables will now be displayed.  Select the tables you want to upload from your local database to our database server
   
   
   
   When you do this, make sure the correct table name is entered in the "destination" list.
   
   If you want to make changes click on the "Edit Mappings" button. This shouldn't be necessary if the destination database (the one on our servers in this case) is empty.
   
   Click on the "Next" button
    
10. You are now ready to upload your data from the new screen.
    
    Click on "Run Immediately" then "Next" to show a summary of what the "import and export" wizard is going to do.
    
    
    
    Click on the "Finish" button to start the upload.
     
11. The "import and export" wizard runs the process and the results are displayed on the next screen.
    
    
    
    Assuming everything has gone correctly (the screen provides a summary) you have now uploaded your database tables from your location to our remote database server.