What is Microsoft SQL Server 2008 Express?

As always, the Express edition of any SQL Server is available free and is meant for trial use. The Microsoft SQL Server Express is not only free to use; you could distribute any application using the database freely to your customers. The Microsoft Server 2008 download is available for download in 32 bit as well as 64 bit versions. The three download modules available are for 32-bit environments, 64-bit environments and a version that will work in either version. Depending on what version kind of processors you are, targeting your application for, you could use the suitable package to download. Though limited in capabilities compared to the other versions meant for enterprise use, the Microsoft SQL Server 2008 Express version provides necessary features of data protection as well as a reliable data management platform for simpler web applications, embedded systems and local data stores.

Installation is straightforward, simply run the appropriate setup program. After installation, it is required that you register your SQL Server Express. It is completely free, but the registration will help you in getting in touch with customer service as well as the community. If you are planning to re-distribute the Microsoft SQL Server 2008 express with any applications, you will need to sign up for free re-distribution rights in addition. If you would like Microsoft to get details of bugs you face, you could agree to send the details automatically to the company. There is no requirement that this is a precondition for the use of the Microsoft SQL Server 2008 Express in any way. You can also access developer tools and sample databases. These can be very handy while developing applications of your own. Microsoft SQL Server 2008 express with tools is also a free edition but with graphical management tools included, SQL Server 2008 Management Studio Basic. This tool should make it easier to develop your data-driven applications for the web or your desktop. This has two versions of the download module; one is for 32-bit systems and the other for 64-bit environments.

For either Microsoft SQL Server 2008 Express editions, the minimum hardware requirements are as follows:

  • 32-bit environments need a processor that works at 1 GHz, 2 GHz is recommended by Microsoft. 512 MB of main memory (1 GB recommended) and 1 GB of HDD space is needed.
  • 64-bit systems call for 1.4 GHz, but preferable 2 GHz processor, 512 MB, but 1 GB main memory and 1 GB of HDD
  • In either configuration, single processor is supported not multi-core.
  • OS environments compatible with the product are XP, Vista and later versions. It is compatible with Server 2003 and 2008 too.

The Microsoft SQL Server 2008 Express editions, in general, have some limitations to restrict it use in small systems. You can have a maximum size of 4GB for the Microsoft SQL Server 2008 Express edition. This is a limit per database as users could access databases that are connected together. SQL Server agent service is not available with the SQL Server Express 2008 Express editions. For more info, visit MS SQL 2008 Tutorial page.

Microsoft SQL 2008 Express



Top 3 features in SQL Server 2008 R2

SQL Server 2008 R2 has quite a few new features even though it was an intermediate release. Here are the top 3 features in our opinion.

Master Data Services

With Master Data Services (MDS) in SQL Server 2008 R2, you have a single source of corporate data. Large organizations face the problem of multiple data sources where the same data is defined and used in various locations. SQL Server 2008 R2’s new MDS feature provides a central portal or hub from which administrators can create and update attributes and hierarchies, with the goal of ensuring data consistency across the enterprise.

You also have the ability to do version control of data for auditing or corporate restructuring purposes.  This means that every change made to data and can be reversed back to its original form if needed be. With Master Data Services in SQL Server 2008, you can deliver faster and accurate results across the enterprise. You can direct stewardship to ensure data quality. Enforce business rules and manage supporting domains in a central stewardship portal to create, edit, and update master data, including members and hierarchies. More information on MDS is available here:

PowerPivot with Excel

PowerPivot for Office 2010 allows you to work with large amounts of data in Excel by interacting directly with a SQL Server 2008 database. With PowerPivot your Excel workbook (.xslx) files can contain large, related datasets that you can use with PivotTables and PivotCharts in a worksheet. The window is provided through the PowerPivot for Excel add-in that you download from the web. Here is the Add-in link for PowerPivot that you can use with SQL Server 2008.


You use PowerPivot to import large or small amounts of data from a wide range of external data sources, including relational or multidimensional databases, public data feeds, SQL Server 2008 Reporting Services reports, or Office documents. After you import the data, you can build relationships between different data so that you can work with the data as a whole. In addition to the separate work area, the add-in also provides a local data processor that performs rapid calculations on large amounts of compressed data. Datasets that you import into the PowerPivot window can exceed the one million row limit in Excel worksheets.

Related to this you also have PowerPivot for SharePoint. With this configuration, you can provide the capabilities of PowerPivot workbooks with SQL Server 2008 and then deploy this to Microsoft SharePoint. As such you will be able to share the data and finding across the whole enterprise.

Report Builder 3.0

Report Builder 3.0 is a report authoring tool in SQL Server 2008 that makes creating reports easier just like Microsoft Office environment. It has features like sparklines, databars and visual data indicators. With SQL Server 2008 Report Builder 3.0, you can accelerate report creation, collaboration, and consistency by enabling users to create reusable reports. Shared components make it quick and easy to assemble comprehensive business reports in a professional-looking format. On the best feature is the Report Part feature where you can take an existing report and designate report items and data regions to save and reuse in other reports.  This can amount to a huge time savings for developing new reports based on an existing report.  Other customers say that they like the improved SharePoint integration and the performance improvements in SharePoint.  Further information on Report Builder 3.0 in SQL Server 2008 can be found here:


For Hands-on SQL Server 2008 Tutorials and advance TSQL scripts, visit this site:


What is Microsoft SQL Server?

SQL Server is a Database Management System (RDBMS) from Microsoft. It is also known as MS SQL. SQL Server is an Enterprise level database application just like Oracle, DB2 or MySQL. There are several different versions of MS SQL, the most common ones are SQL Server 2008 and SQL Server 2012. If you want to try a free SQL Server version, please visit this Microsoft site.


Historically speaking, SQL Server was developed as collaboration between Microsoft and Sybase. SQL Server version 4.2 was available in 1993. The first version of MS SQL developed completely by Microsoft was SQL 6.0 and then SQL 6.5. In 1998, Microsoft came up with SQL Server 7 followed by SQL 2000. Microsoft pretty much built the next version MS SQL 2005 from the ground up. Next version of Microsoft SQL Server 2008 was available for production in 2008. The latest product SQL Server 2012 link is on the market now.

SQL Server is a massive product with lots of moving parts and pieces. In is post, we are going to look at a handful of the important ones in the order of their importance. If you are interested in learning MS SQL, please visit our partner sites at SQL Server 2008 Tutorial, SQL Azure Tutorial and SQL Server 2012 Tutorial.

What is SQL Server Management Studio (SSMS)?

SQL Server Management Studio is by far the most important tool in SQL Server. It is used for administration in addition to development of objects like MS SQL databases, tables, stored procedures, logins and SQL jobs. In order to launch SSMS you can follow this path:

Programs > Microsoft SQL Server 2008 > SQL Server Management Studio

Here is a screen shot of What SQL Server Management Studio looks like on our computer, by the way we are using MS SQL 2008 R2 version:


If you are a visual person like me, you can watch FREE SQL Server tutorial along with sample videos on SQL Server at this location.

Important components within SSMS are:

Databases: Using this icon/folder, you can create objects like MS SQL databases, tables, views, stored procedures, functions, triggers and constraints. You will be spending most of your time in MS SQL right here, guaranteed!

Security: Within this folder, you have the ability to create new logins (for access to MSSQL Server). You can also create server level roles (similar to Windows groups), and setup security audits.

Server Objects: Here you can create backup devices, endpoints (point of entry to a SQL Server), Linked Servers (non SQL providers that can interact with MS SQL).

Replication: This is the ability of SQL to synchronize data between a primary and a secondary server(s). With replication, you can configure items like publishers, subscribers, publications, articles, etc.

Management: You can perform SQL Server management tasks like data-tier applications, policy management, setup maintenance plans; look at SQL Server logs, setup Database mail and configure Full Text Search.

SQL Server Agent: Vital component in SQL 2008 component for database administration. You can manage SQL instance, create jobs, alerts, operators, proxy accounts and also review SQL Server agent logs.

A visual representation of the MS SQL Agent is as follows.


SSQL Server Configuration Manager (SSCM)

This is another vital management tool for SQL Server. You can control SQL Server services, Server protocols; limit Client level protocols, client aliases and other surface area configuration pieces. You can get o SSCM by doing the following:

Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configure Manager

I have included a screen shot of SSCM from our MSSQL server:


Here are the available options under SSCM:

SQL Server Services: With this, you can control items like MSSQL Server service (database engine), SQL Agent service, SQL Server Integrations service (SSIS) and SQL Server Reporting service (SSRS).

SQL Server Network Configuration: Here you can configure protocols (network communication standards) for connection to Microsoft SQL including TCP/IP, Named Pipes and Shared Memory

SQL Native Client Configuration: Just like on the server side, you can configure protocols at the client side. In addition you can setup alias to connect to the SQL Server.


Import and Export data:

Using the SQL Server import and export wizard (SSIEW), you can move data to and from SQL Server. Microsoft SQL supports a number of different providers like Oracle, IBM DB2, flat file (.txt), Office applications, OleDB and ODBC compliant drivers. SSIEW walks you through all the necessary steps and at the end will create an Integration Services (SSIS) package. We will cover Integration Services under Business Intelligence Development Studio.

What are SQL Server Performance tools?

It’s just a fact of life, sooner or later your SQL Server database will grow and grow and run into performance issues. It may be a combination of higher volume of data, poor database design or inefficient Transact SQL queries. In order to solve the problem, SQL Server provides you the following two tools right out of the box:

SQL Profiler:

SQL Profiler can capture every single SQL Command, stored procedure and database process that you are running on SQL Server. In a sense, you create a trace which stores a chronological list of every event on SQL Server. You can then open the trace and review it later for performance tuning and query analysis.

Database engine tuning advisor (DETA)

This next performance tuning tool is also an excellent utility that can help you find not only missing indexes but show you how to improve existing indexes in SQL Server. First you have to either create a SQL Profiler trace or a TSQL script, next you need to be loaded it up into database engine tuning advisor which will then make its recommendations.

What is SQL Server Business Intelligence Development Studio (BIDS)?

When you install SQL Server on your box, you will also be given Business Intelligence Development Studio. This is a cut down version of Visual Studio and allows you to create projects on Integration Services (SSIS), Reporting services (SSRS) and Analysis Services (SSAS). You can launch BIDS by doing this:

Programs > Microsoft SQL Server 2008 > SQL Server Business Intelligence Development Studio

Like we mentioned in the Import and Export data section, SQL Server Integration Services is an ETL (Extract, Transform and Load) Tool that can move data back and for between MSSQL and other data providers. Our trainers cover a basic way to create a SQL Server at Integration package in this sample training video.

Here is what a SSIS package looks like in BIDS. We are loading a flat file (.txt) into SQL Server.


SQL Server Reporting Service (SSRS) is a full blown reporting solution from Microsoft. SSRS uses RDL (Report Definition Language) and XML to create and manage reports. You can use BIDS to create a report. You can deploy it to the Reporting Server which will process reports and handle end-user transaction. It has the ability to allow users to not only pull SQL reports but also push reports on demand at a fixed schedule.

Here is video on SSRS from YouTube:


This is what an SSRS report looks like in BIDS.


SQL Server Analysis Services (SSAS) is part of the business Intelligence set of tools from Microsoft. SSAS will let you perform Online Analytical Processing (OLAP) which lets you create multi-dimensional cubes. SSAS also enables its users to do Data Mining which lets you analyze massive data by different attributes. This will uncover patterns and useful information that may not be readily available from an online transactional Processing (OLTP) system.

Miscellaneous topics on MS SQL:

What else is there in MSSQL? you ask. Well a lot! SQL Server has many more components and features that we have not even mentioned. Here are a few important ones:

SQL Server Failover Clustering: This provides High Availability support for the entire SQL Server instance. First you create Windows Server Failover cluster and then add one or more nodes to it.

Database Mirroring: This is a High availability solution in SQL Server at the database level. In case of a database failure, the database is switched from the Principal (active) to the Mirror Server. In this manner, there is minimal interruption from the data consumer point of view. You can take a look at this Database Mirroring video on YouTube.

SQL Replication: This is the ability to synchronize data between different MSSQL databases. This is a popular option for mobile Sales professionals who are on the road and can sync with the master database on as needed basis. You can setup on of these types: Snapshot, Merge and Transactional replication.

Transact SQL (TSQL): This is the flavor of Structured Query Language (SQL) for Microsoft. You can manipulate your data using commands like SELECT * FROM TABLE. Our partner has more than 60 TSQL scripts to help you be a better SQL DBA or SQL Developer!

Sqlcmd Utility: Sqlcmd is a command line utility in SQL Server. You can use it to execute TSQL statements, stored procedures and other script files from the command prompt. This is useful when you use batch files or run automated processes like setting up a new server.

Services: Using Windows Services manager, you have ability to Start and Stop MS SQL Services. You can get to this by either typing in services.msc in the Start menu or by following this path

Control Panel > Administrative Tools > Services

SQL Server Related Links:

-SQL Server Home Page
-SQL Server 2012 Training
-SQL Server Central – Excellent resource on MSSQL topis
-How to implement Security in MS SQL 2008?