Exercise 5

1. Investigate the SQL Server 2008 architecture available from the Dreamspark website.

Microsoft SQL Server 2008 allows organisations to run mission-critical applications, lowers data infrastructure cost and deliver information to all users. It provides robust security features, reliability and scalability for mission-critical applications.

Transparent Data Encryption
SQL Server 2008 enables encryption of entire databases, data files, and log files, without the need for application changes.

External Key Management
SQL Server 2008 provides a comprehensive solution for encryption and key management.

Enhanced Auditing
SQL Server 2008 improves compliance and security by enabling you to audit activity on your data.

Enhanced Database Mirroring
SQL Server 2008 provides a reliable platform for enhanced database mirroring. New features include:

• Automatic page repair
• Improved performance
• Enhanced supportability

Resource Governor

Resource Governor enables database administrators to define resource limits and priorities for different workloads

Data Compression

SQL Server 2008 allows efficient storage of data.

Backup Compression:

SQL Server 2008 requires less disk space is required to keep backups online allowing backups to run faster.

Performance Data Collection:

SQL Server 2008 provides new monitoring and reporting tools to allow database administrators to help performance tuning.

Streamlined Installation:

SQL Server 2008 provides improvements in installation, setup and configuration architecture.

T-SQL improvements:

There has been improvements in T-SQL allowing users to pass table name as parameters and new data types like DATE and TIME has been added.

Beyond Relational Data:

SQL Server 2008 allows storage of wide variety of non-relational data types. This includes FileStream data type (large binary files), Geographical Information (geodetic spatial data), Hierarchy ID (system type to stire values that represent nodes in a heirarchy tree).

Resource:

http://download.microsoft.com/download/6/9/d/69d1fea7-5b42-437a-b3ba-a4ad13e34ef6/SQL2008_ProductOverview.docx

2. Test out SQL Server Express as a lightweight application development tool. SQL Server Express edition and report your findings.

SQL Server Express is a free edition of Microsoft SQL Server but it has some limitations. It can only work on 1 CPU, consume 1 GB Ram max and can have a database size of 4 GB.

The database can be easily created using Visual Studio or by using Microsoft SQL Server Management Studio.

I have setup a database using Visual Studio 2008 as shown below.

At first, right-clicked on the solution explorer and choose to add a new item and selected the database option.

Then, a SQL Server Express database is added to App_Data folder.

The server explorer looks like below.

As can be seen, there are options to add tables, views, stored procedure, etc as can be seen on SQL Server Management Studio.

Then, right clicked on the table, and chose to add a new table.

As can be seen, I have a table assigned various data-types to different fields. Fields like heirarchy id, date are new to SQL Server 2008. There are other interesting data types like xml and image. The xml data type enables to add a whole xml document or chunks of a xml document. The image field allows uploading image files as binary data. Fields can also be set up to have null values.

To set a primary key for the table, select the field, make sure it does not except null, right click on the field and choose "Set primary Key". To set it to an auto-number, go to the Identity-specification section and set "Is Identity" to "Yes".