Wednesday, June 15, 2011

Beginning SQL #1 - SQL Server

I took a SQL class last month to prepare for exam 70-432, which I hope to take later in the summer. One of the best ways for me to remember what I have learned is to write it down so I have decided to use my blog as the medium for that for the next few months. There were six classes that covered nine chapters and I should be able to get a couple of posts from each chapter.

SQL 2008 Server Services
SQL Server 2008 is made of seven main services. Not all of these services are required and some aren't even installed unless you do a full install. Below is a list of these services and a brief description of each one.

SQL Server Database Engine Service - This is the primary service for SQL Server and it is responsible for hosting the databases, managing data storage and access and generating indexes. The default instance of SQL server is always named MSSQLSERVER. Named Instances are called by what name you chose when you installed them.

SQL Server Agent Service - The Agent Service is responsible for job handling. If it is not running your scheduled jobs will not run. If you nightly maintenance plans and backup jobs are not running it is a good idea to make sure this service is running as by default it is disabled. The Agent Service is also responsible for managing replication, log shipping and database mirroring.

SQL Server Browser - The SQL Server Browser is the service that allows you to have more than one instance of SQL installed on a machine. The Browser service listens for incoming requests for SQL Server and provides information on the instances of SQL Server installed on the server. The Browser service is what enables you to install different versions of SQL server on the same machine. The Browser Service usually starts automatically. It won't start automatically if you only install the default instance during a new install.

SQL Server Analysis Service - The Analysis Service (SSAS) adds data mining and OLAP functionality to SQL Server. It allows you to use MDX (Mutlidimensional Expression) queries to analyze cube data and DMX (Data Mining expression) queries for data mining.

SQL Server Integration Service - The Integration Service (SSIS) is used to combine data from disparate sources. You can use Business Intelligence Design Studio (BIDS) to build ETL (Extract, Transform and Load) packages that can access a variety of formats such as: Excel, XML, Oracle, OLE-DB data sources and of course SQL Server.

SQL Server Reporting Service - The Reporting Service (SSRS) enables reporting functionality with SQL Server. You can create reports using Visual Studio, BIDS or the Report Builder that is included with the installation. The reports are administered via a web interface but can accessed using the Microsoft Office Suite or Sharepoint.  You can configure SSRS 2008 R2 to work with Sharepoint 2010 during install by configuring it for Sharepoint integrated mode.

Full Text Search Service - This is a indexing service for searching for text in certain SQL columns. It is similar to using the like operator in a query but has performance advanatages. The service works with columms of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary or varbinary(max). This service is not enabled by default and for it to work a full text index must be created on each table that you want to use it with.

That's it for this week. Next week I will take a look at the six System Databases in SQL Server.
Hasta Luego,