Exercise 5: Database Case Study

Case A: Microsoft SQL server
1. Investigate the SQL Server 2008 architecture available from the Dreamspark website.

The pre-requisite of SQL Server 2008 Express:
-> Maximum data size of 4GB per database.
-> Single physical CPU, multiple cores
-> Uses only 1GB of RAM
-> SQL Server Agent service unavailable

SQL Server 2008 aims to make data management self-tuning, self organizing, and self maintaining with SQL Server Always On technology to provide near-zero downtime. It also includes support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. Multimedia data can be stored as BLOBs (binary large objects). It can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial as well as perform search, query, analysis, sharing and synchronization across all data types.
SQL Server 2008 also takes the advantage of Hyper-V in Windows Server 2008 which support for more virtual systems per physical host, leading to potential cost reduction. The Hyper-V Migration allows to move a virtual machine between two host servers without any interruption of service.
SQL Server 2008 offers customers scalability to meet the demands of growing data warehouse.


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

SQL Server 2008 Express is a freely downloadable and distributable version of Microsoft’s SQL Server relational database management system. Before installing SQL Server 2008 Express, you have to get the following pre-requisites ready:

-> .Net Framework 2.0 SP2
-> Windows Installer 4.5
-> Windows PowerShell 1.0
-> Visual Studio 2008 SP1

SQL Server 2008 Express is a scaled down, free edition which includes the core database engine. While there are no limitations on the number of databases or users supported. It comprises a dataset specifically targeted for embedded and smaller-scale applications which limits to using only one processor, 1GB memory and 4GB of database file. These technical restrictions make it undesirable for large-scale deployment and help to govern the workload during concurrent access. The entire database is stored in a single .mdf file. Two additional editions provide superset of features not in the original Express Edition. The first is SQL Server Express with Tools, which includes SQL Server management Studio Basic. The other one is SQL Server Express with Advanced Services which adds full-text search capability and reporting services.

After installing and basic configuration, it runs pretty smooth on my dual-core notebook. The basic features such as creation of database, tables , creating entries and performing queries is not much difference from previous version. The GUI and dashboard would be familiar if you are using the previous version of SQL Server. We can get quick insights into the server instances and applications with tools and wizards and easy management setup and reveal versioning information. It provides superior insight into resource utilization and policy violations which helps to identify potential problems and maintain system healthiness.

It accelerate deployments and upgrades through a new concept, a single unit of deployment, that packages database schema (database, table, stored procedures) with deployment requirements and can be extracted from existing applications or created in Microsoft Visual Studio which helps to reduce deployment trial and error.