Most of the Windows Enterprise servers that exist rely on a non volatile high performance data store. SQL Server is generally that beast and we are now seeing SQL Server 2005 as the standard platform (certainly in my experience). This is no exception for Windows based BPM software like Metastorm, K2 and Biztalk so here is a beginners nutshell guide to SQL Server 2005 (no fluff, just the facts)…
First off, some statistics and history… SQL Server 2005 has been tested to a 1,000,000 transactions per minute level and it is said that there is a 7% performance advantage over Oracle 10g with a 37% cost reduction. The Microsoft SQL Server series started in 1988 when they teamed up with Cybase to create SQL Server 3.0. In 1995 the first Microsoft only GUI was created in the form of SQL Server 6.0 (for NT 3.5). In 1999 the code became 100% Microsoft in version 7.0. Following that in 2000, SQL Server 2000 hit the shelves introducing DTS (transformation services) which allows executable packages to be created for data transformation. SQL 2000 also introduced Analysis Services. So this brings us to 2005, SQL Server 2005 came to fruition as did a much updated DTS (now named SQL Server Integration Services), Service Broker (a new MSMQ), upgraded Reporting Services and all based on the .NET Framework. The old Enterprise Manager and Query Analyser become one in a new integrated management environment called Management Studio.
.NET to SQL Server 2005 is what Java is to Oracle. It opens up development to SQL Server through referencing .NET SQL Server Assemblies (unlike the assemblies of SQL 2000 that had to be C++ compiled). This allows classes to be built to extend the core functionality of SQL Server. SQL Server is also written on .NET so utilizes .NET components.
SQL Server Parts and Tools
SQL Server installs quite a lot of software for managing and maintaining your databases. The software can be categorized into 4 main area’s : Management, Development, Components and Configuration.
Management – These tools include the SQL Management Studio, Configuration Manager and SQLCMD (sql command line access that replaces the old OSQL which allows a secure connection to be made to the engine and queries submitted).
Development – SQL Management Studio and the Business Intelligence Studio (known as BIDS) which is used for Integration Services, Reporting Services and Analysis Services.
Components – The software components installed with SQL 2005 include the SQL Database Engine, Analysis Services engine (for data warehousing and cube manipulation), Integration Services (ETL tool), Reporting Services, Notification Services (send anything to any platform), Service Broker and SMO (the new SQL-DMO) which are the objects used in SQL programming.
Configuration – SAC (Surface Area Configuration) used for reducing security risk on the SQL Server (switching connections and features on and off as required), SQL Server Configuration Manager for configuring the servers access protocols, Notification Services command shell and the settings configuration for Reporting Services.
We’ll take a further look at these area’s when we look at server level configuration.
SQL Server Service
Each instance of SQL Server installed has its own SQL Service that runs in the background (yes this does mean that different versions of SQL Server can be installed side by side). The service acts as a middle tier between the installed components (listed above) and the management interfaces. When an action occurs in the management studio, it contacts the SQL Service which in turn interacts with the engine / component. When you register a server in Management Studio you are adding the relevant service to connect to.