In order to publish data from a local SQL Server database to a live SQL Server database hosted under your web hosting package, you will first need to do two things:
-
Create a SQL Server database and Database User under your hosting package. This can be done by logging into your Plesk web site control panel and selecting Databases >> Add New Database >> SQL Server
-
Have a copy of SQL Server Management Studio 2008 installed on your local computer. If you do not already have a copy, the Express version can be downloaded by clicking here.
SQL Server 2005 Database Users
Please note that if you are connecting to a live SQL Server 2005 database (applies to hosting packages hosted on Windows Server WinSrv106), you will need to connect to this using SQL Server Management Studio 2005. This can be downloaded by clicking here. You may also need to download the SQL Server Management Studio 2005 Toolkit and install this in order to access the Import/Export wizard. This can be downloaded by clicking here.
From there you can then proceed to access your local SQL Server database.
Publishing your data
For this tutorial we will be using the 2008 wizard to export data from your local SQL Server database to a remote Microsoft SQL Server database.
To begin with, navigate to 'Start > All Programs > Microsoft SQL Server 2008'. Once logged in, right-click your database and select "Tasks" / "Export Data". The wizard will then launch as per below.
In order to proceed with the wizard click 'Next > '.
You will then need to select 'SQL Server Native Client' as your desired data source.
You will also need to enter the following details:
-
Server name - this is the location of your local Microsoft SQL Server installation, in most situations this will be 'localhost\SQLEXPRESS' based on the usage of Microsoft SQL Server Express Edition in a local development environment
-
Windows Authentication or SQL Server Authentication - as the database server is local to the computer that you are using, you will be able to simply select Windows authentication as you are already logged into that SQL Server
-
Database - you will need to select the database that you wish to export to the remote Microsoft SQL Server.
After that you will then need to select a destination for the data that you are importing.
As we are going to be exporting the data to a remote Microsoft SQL Server, the details you enter this time around will be slightly different. Although the destination type will remain the same, you may wish to alter the following:
-
Server name - the server name is going to be different for the remote Microsoft SQL Server, you should refer to your Welcome Email for the exact server details you should be using
-
Authentication - as this is going to be done over an internet connection you will only be able to use SQL Server Authentication using SQL Server user accounts that you have setup on the remote server - in this case you should use the SQL Server database user account that you have setup in your Plesk control panel
-
Database - your remote database's name will be the name of the database you created using the Plesk control panel (via Plesk >> Databases >> Add New Database >> SQL Server).
The "Export Wizard" will also give you the option to import either all of the data stored in your data source or to run a SQL query on your data source in order to extract a select amount of data.
Depending on the option that you have chosen, the wizard will then show you the data that you are about to import. Simply click 'Next' to confirm the import.
Once the data you wish to import has been imported, the wizard should then confirm this for you.
It is then simply a case of clicking 'Finish' to ensure that all of the options that you have selected are carried out correctly. The wizard will complete by running through all your options one by one so you can see them being completed individually.
To exit the wizard simply click 'Close'.
After that it is a good idea to verify that the data has been imported correctly. In order to do this simply open a new connection to the Remote database using Microsoft SQL Server Management Studio. Full details on this can be found by clicking here.
