SQL Server 2000

An optional extra for your hosting account is one or more SQL Server databases, SQL Server is highly recommended if you are running a database back end for your web site.

For additional security our SQL Servers listen on a non standard port (5252) this must be taken into account when connecting to your database (see below for information).

Your database login / user will have full db_owner privileges over your database allowing you to read and write data to the database, create new objects (tables, stored procedures etc.) and to import and export data via DTS.

Extended functionality such as replication, SQLXML, full text search and SQL Mail is not suitable to a shared hosting environment and will not be available for use with your database. Database logins will be set to 'British English' by default but can be changed to any other setting on request.

Managing your database(s)

Enterprise manager is one of a number of free tools from Microsoft which allow you to administer SQL databases - it is supplied as part of MS SQL Server software package.

If you don't own a copy of SQL server you can download a trial version from Microsoft, the trial version of SQL Server itself will time out however Enterprise Manager can be used indefinitely - you can also choose to install the management tools ONLY during the trial SQL server install. Alternatively you might choose to purchase the developers version of SQL Server, or many of the books available on SQL Server include the trial on the included CD.

Microsoft also produce a free, cut down version of SQL Server known as MSDE (Microsoft Desktop Engine) - you may find this useful for learning and developing your database locally before taking the step to a full MS SQL Server database for your site, click here for more information on MSDE.

Connecting to your database using Enterprise Manager

Start Enterprise Manager on your local machine, once started you'll see in the left hand panel a 'Microsoft SQL Servers' node icon, click on this icon - if this is the first time you've used Enterprise manager nothing will be listed, if you already have connections on your PC they will be listed here.

Right click on the 'Microsoft SQL Servers' node icon and from the menu select 'New SQL Server Group' then in the pop up box enter a name for the group e.g. dotnetted.co.uk, leave 'Top Level Group' selected and click OK.

You'll now see an icon for the group you created under the 'Microsoft SQL Servers' node, select and right click on your new group icon and from the menu select 'New SQL Server Registration' then in the pop up box enter :

Server : *.dotnetted.co.uk,5252
Connection : set as 'Use SQL Server Authentication'
Login name : your account username (as used for FTP)
Password : your account password (as used for FTP)

(* your database server name can be found by logging into your MyDotnetted account)

If you wish your login and password to be entered automatically when you connect leave the 'Always Prompt For Login Name And Password' box unticked - if you are not the only user of your PC and would prefer to restrict access tick this box and you will be prompted for the password each time you connect.

Your connection is now set up and when you click OK Enterprise Manager will attempt to connect to your database. Please note that the first time you connect it will take Enterprise Manager some time to list all databases, possibly up to a couple of minutes on a slow connection. Once connected you'll see a number of folders listed in the left panel under the server node, to access your database open up the 'databases' folder and scroll to your own database. It may take a few minutes for all databases to be listed, unfortunately in SQL Server 2000 it is impossible to restrict the view to your database only and you will be able to view all the databases on the server, however you will only have permissions to interact with your own database.

Connecting to your database from your ASP / ASP.NET pages

Our recommended method of connecting to your SQL database is via an ole db 'DSNless' connection string, the connection string you need to use is as follows :

"Provider=SQLOLEDB; Data Source=*.dotnetted.co.uk,5252; User ID=*username*; Password=*password*; Initial Catalog=*database name*; Network Library=dbmssocn;"

(* These details can be found by logging into your MyDotNetted account)

So for example if your details were as follows :

SQL Server name : db1.dotnetted.co.uk
SQL database name : db_jsmith
SQL database login : jonsmith
SQL database password : abcdwxyz

Your correct connection string would be :

"Provider=SQLOLEDB; Data Source=db1.dotnetted.co.uk,5252; User ID=jonsmith; Password=abcdwxyz; Initial Catalog=db_jsmith; Network Library=dbmssocn;"

(The string should all be on one line )

Note : If your connection string is to reside within your web.config file you should remove the initial "Provider=SQLOLEDB; " from the above connection string as this is not required and will error.

Uploading your database to the server

If you have an existing SQL database that you wish to upload for use with your account you can upload it using DTS (data transformation services).

To do this connect to the server via Enterprise Manager as normal, right click on your database, select 'All Tasks' then 'Import Data' - this will start the import export wizard.

To transfer your data you just need to follow the various steps of the wizard, first it will ask for the source for the transfer, which will be your local SQL database for which you'll need to provide the server name, database name and login details next you'll be asked for the destination details for which you'd provide the details of your DotNetted database.

Once you've completed all of the import / export wizard steps your database will then be transferred and the wizard will inform you of the progress during the transfer.

Downloading your database / backing up your database

To take a backup of your database you again use DTS and the import / export wizard but just reverse the transfer, setting the source as your DotNetted database and the destination as your local database.

DTS is very flexible regards data sources and if you wish to backup a copy of your database but don't run SQL Server locally you can instead choose to download the data into a number of other database formats including Access, Paradox, Excel or even a .csv text file.

'unable to copy object' error when transferring databases

When transferring your data between SQL Servers you may receive 'unable to copy object' errors, this is most likely to occur when transferring stored procedures but may also occur with other objects - to overcome this try the following :

When completing the import / export wizard after entering the details of both servers and selecting 'Copy objects and data between SQL servers' on the next screen where it "Select Objects to Copy" untick the 'Use Default Options' box.

The 'Options' button will now come live, click on this and untick the boxes for 'Copy database users and roles' and 'Copy object-level permissions'. Now continue with the DTS wizard.

Also ensure that your local SQL Server is running the latest service pack which is SP3 at the time of writing. It is possible that certain stored procedures may still fail to transfer even with the above settings, if this is the case you need to transfer everything that you can and recreate the missing stored procedures on your DotNetted database by copying and pasting them from the source database.

Guest user

Do not enable the 'Guest' user on your database, or if uploading a database ensure that the Guest user does not have access to the database on your local machine - enabling the Guest user will allow access to anybody who has Enterprise Manager access to the database server to view the contents of your database.

Can I send DotNetted my SQL Server database backup to restore ?

Sorry, this is not possible - the users and object ownership in your database will not match that set up on the database on the DotNetted server and you'll be unable to access your database once uploaded. Database transfers must be completed via DTS as described above.