One of the problems we often encounter is information overload. There’s no shortage of great articles, examples, podcasts and webcasts on every conceivable technology. Microsoft’s products are no different.
Thus, the following is comprised of numerous sources easy to find online – there’s no special insight here. Rather, I’ve just simply tried to distill a product to its highest level overview. Hope it helps, and as usual it’s covered by my ‘well duh‘ disclaimer.
Anyway, on to today’s topic: SQL Server 2008
History of SQL Server
SQL Server has been around for a while, with version 6 coming along in the early ’90s.
Version 6.5 and 7.0 followed. SQL 2000 and SQL 2005 are the most common versions in use now.
SQL Server 2008 has just entered its Release Candidate cycle, which means it may be released in the next few months.
Editions
There’s a number of editions, the main ones being:
SQL Server Standard, Enterprise & Developer
SQL Server Web & Workgroup
SQL Express (available Free)
SQL Compact Edition (available Free)
(These support a variety of operating systems and chip sets)
Components
You may have seen the following helpful diagram at some point – it is a good overview of the main components in SQL Server 2008:
Database Engine
The main storage and query engine, supporting security, tables, views, stored procedures, replication and general management of data. SQL Agent also hangs off the database engine, allowing jobs or tasks to be scheduled.
Analysis Services
As the name implies, Analysis services are about providing in depth functions for analysing data.
There’s two main parts to Analysis Services. Multi-dimensional data is where structures are built that package up data, often from multiple sources, into a single aggregated model. Often referred to as ‘cubes‘ due to their multi-dimensional format. These structures are then also used in other packages (eg PerformancePoint) and reporting tools.
Data Mining is about interrogating vast volumes of data, often to provide predictive models and trends.
Reporting Services
Reporting services provide presentation and programming tools for delivering reports. Various formats, scheduling and exporting options are provided.
Integration Services
Integration services provide a platform for efficiently moving data in and out of SQL Server. Numerous formats and conversions between them can be managed. The integration tasks are coded as ‘packages’, and GUI tools are provided to help build them.
Service Broker
Provides the database engine with the capability to build messaging and queuing applications. Allows distributed applications to be built easily (since it supports transactions).
Replication
Replication facilitates data being distributed between different databases, and synchronised at regular intervals (sometimes close to real time). Replication can function over LANs, WANS, and the internet.
What about Notification Services?
Notifications services was a component of SQL Server 2005 that allowed messages to be sent to subscribers. It was a way of deploying apps that could send notifications in huge volumes. However it was dropped from SQL Server 2008.
Tools
SQL Server Management Studio
Key function: Managing
This is the main application for managing and administering the servers & services, databases, stored procedures, security, replication, resource governor and more.
Business Intelligence Development Studio
Key function: Developing
This is where you build applications (ie think Solutions and Projects) for any of the main services, including Analysis, Reporting and Integration Services. For example, you’d use this for building Analysis cubes, or an Integration Services package (eg here’s one I did earlier :-))
SQL Server Profiler
Key function: Debugging
This is used for capturing and analysing activity. The tool allows almost any communication item in SQL Server to be captured, everything from queries, stored procedures, result sets, handshaking, and more. This is usually the first port of call when debugging SQL performance for example.
Other tools
There’s also various command line tools as well as configuration tools (although apparently not the Surface Area Configuration tool I mentioned recently).
Other important stuff
Microsoft has an overall vision for providing data everywhere, whether that’s on the desktop, web, mobile device or in the data centre. Here’s a nice little piccy that puts it in context ->
Microsoft Sync Framework
Part of the strategy for making data available anywhere, is having the means to synchronise it back with the main data store. This is where the Microsoft Sync Framework fits in. It’s not yet released (currently CTP2) and handles more than just database synchronisation ( but we are only interested in the database part for now).
The diagrams below show two manifestations of this. The first uses the internet for connecting back to a main storage server. The second adds to this by allowing the devices to synchronise between themselves as well as the main server (again over the internet).
The main point is that this supports an ‘occasionally connected‘ architecture.
SQL Server Data Services
Plus Microsoft has a SQL Server Data Services concept coming (it’s currently in limited Beta), which provides on-demand data storage, all hosted by Microsoft.
Closing comment
Yet again Microsoft marketing has chosen to hit us with the ‘actionable’ adjective. I still smile every time I see it – here’s why.
