Microsoft SQL Server Hosting

Microsoft SQL Server Management Studio Express is the free Microsoft product that allows you to manage your Microsoft SQL Server from any internet connected location, just as long as the manager and server are available. The management console also allows you to configure certain settings whilst on the move meaning that you don’t necessarily need to be at one computer all of your time to manage your database server.

SQL Server Management Studio allows you to connect to your server using both the SQL Server and Windows Authentication login methods, just as long as the private server that you are trying to connect to accepts both. This means that you can allow your clients to login and manage their databases using SQL Server logins that you have created for them, and you can login and manage the whole SQL server using the standard Administrator account that you login to Windows with.

In previous articles, we have discussed the installation of Microsoft SQL Server Management Studio Express, as well as the use of it to configure certain settings of the Microsoft SQL Server that you are using. You should also be aware of the two different types of authentication, as also discussed in previous articles.

This time around we are going to be discussing the use of Microsoft SQL Server Management Studio Express to manage databases that you have created for use with your Microsoft SQL Server. The main ttasks that SQL Server Management Studio allows you to carry out are:

• Manage existing databases
• Create new databases
• Modify databases and created new tables etc. to expand the usage of your database
• Manage certain settings in relation to the Microsoft SQL Server 2005 instance that you are managing.

In order to start using Microsoft SQL Server Management Studio, you will need to navigate to ‘Start > All Programs > Microsoft SQL Server 2005 > Microsoft SQL Server 2005 Management Studio Express’. Once you open the program, you should be presented with the login screen so that you can connect to your Microsoft SQL Server 2005 database server.

If you have enabled the Microsoft SQL Server browser service, as previously discussed, then your Microsoft SQL Server should appear in the ‘Server Name’ drop down box if you are using Microsoft SQL Server 2005 Management Studio on the same machine or network as your instance of Microsoft SQL Server is located on.

If you are running your instance of Microsoft SQL Server 2005 on the same machine or network, then you should be able to use the Windows Authentication login method since this will make use of your active Windows account to connect to SQL Server.

If you are running your instance of Microsoft SQL Server at a remote location, then you should use the SQL Server authentication method sine this will allow you to use the login that you may use in your website’s and scripts to connect to your database server, although this will only allow you to manage the database that the login is directly assigned to.

Once you have successfully logged into Microsoft SQL Management Studio Express, you should be presented with a screen that looks similar to the one below from which you can proceed to manage the part of your database server that you need to.

In order to create a new database, you will want to right-click on the ‘Databases’ folder which is located in the left hand panel, and click ‘New Database’. A window should then popup asking you to enter the name of the database that you want to create. The database will then be automatically created when you click the ‘OK’ button’ near the bottom of the window.

If you need to select any custom options, then you can do so by using the ‘Option’ setting on the left menu which is part of the ‘New Database’ wizard.

To start managing your new database, expand the ‘Databases’ tree on the left hand menu and then expand the tree for your new database by clicking the ‘+’ icon which is located next to it. You should then be presented with some more trees which you can then use to start expanding your new Microsoft SQL Server 2005 database.

The first thing that you will want to create for your new database are tables, since these are where the data that you want to store in your database will be kept. Without them there is nowhere for the data to be stored.

To create a new table, right click on the ‘Tables’ tree and select ‘New Table. A new pane should open in the centre of the screen, in which you should enter the necessary column names for your new database table. You should ensure that the column names are relevant to the data that they are going to be storing so that you don’t get confused when it comes to administrating the database. There are quite a few different data types to choose from when creating a database table, these are:

• Bigint
• Smallint
• Int
• Decimal
• Numeric
• Money
• Smallmoney
• Bit
• Float
• Real
• Datetime
• Char
• Text
• Nvarchar
• Varchar
• Nchar
• Ntext
• Image
• Binary
• Varbinary
• Cursor
• Timestamp.

It is recommended that you create one column to organize all your data, which will automatically assign a unique identifying number to each row of data that is added to your database. This field is also known as the ‘Primary Key’ field.

For our example, we have called our field ‘ID’. To ensure that each row is given a unique id, you will need to ensure that this column is of the ‘int’ data type, and is set as the ‘Primary Key’. You can set a field to be the primary key field by right-clicking on it and selecting ‘Set as Primary Key’.

You will also need to set the (Is Identity) option to ‘Yes’. This will ensure that the identity increases by one each time, ensuring that each row gets a unique identity.

That’s it! You can now create databases and tables for use with your Microsoft SQL Server!