SQL Server is Microsoft’s relational Database Management System (RDBMS) like other RDBMS Oracle, SYBASE, MySQL and DB2. Microsoft has released to compete against competitors like Oracle Database (DB) and MySQL. SQL Server supports ANSI SQL Like all major RBDMS, the standard SQL language. However, SQL Server also contains T-SQL, its own SQL implementation. SQL Server Management Studio (SSMS) (previously known as Enterprise Manager- SQL2000 and previous versions) is SQL Server’s main interface tool, and it supports 32-bit and 64-bit environments. SQL Server was released in various editions like Enterprise: Designed for large enterprises with complex data requirements, data warehousing and Web-enabled databases. Has all the features of SQL Server and its license pricing is the most expensive. Standard: Targeted toward small and medium organizations. Also supports e-commerce and data warehousing. Workgroup: For small organizations. No size or user limits and may be used as the backend database for small Web servers or branch offices. Express: Free for distribution. Has the fewest number of features and limits database size and users.
SQL Server also includes more add-on services however these are not important for the operation of the database system, they provide value added services on top of the core Database Management System. These services either run as a part of some SQL Server component or out-of-process as Windows Service and presents their own API to control and interact with them.
SQL Server Replication Services are used by SQL Server to replicate and synchronize database objects, either in entirety or a subset of the objects present, across replication agents, which might be other database servers across the network, or database caches on the client side. Replication follows a publisher/subscriber model, i.e., the changes are sent out by one database server (“publisher”) and are received by others (“subscribers”).
The SQL Server Analysis Services, or SSAS, is a multidimensional analysis tool that features Online Analytical Processing, powerful data mining capabilities, and deeper dimensions to business information within a relational database. Multidimensional analysis is an OLAP technique that produces the ability to analyze large quantities of data by storing data in axes and cells instead of the traditional relational two-dimensional view in rows and columns. SSAS places predictive analytic capabilities in the hands of information workers by creating an instant connection to backend data using familiar applications such as Microsoft Excel and SharePoint for analysis, visual presentation and collaboration.
Microsoft has come up with its own reporting service; along with SQL server database to introduce the Microsoft SQL Server Reporting services also known as SSRS.
SSRS solution is a web based solution in which the report can be shared among different customers, stake holders and top management on a web based platform. SSRS is a comprehensive reporting platform whereby reports are stored on a centralized web server and because of that the deployment is quite simple.
This innovative approach that Microsoft introduces in SSRS enables users to create report with lesser efforts.
SQL Server Integration Services is most advanced tool and is used to integrate data from different data sources like Oracle, SQL, or Sybase. It is used for the Extraction Transform and Load capabilities for SQL Server for data warehousing needs. It includes tools Business Intelligence Studio to build data extraction workflows integration various functionality such as extracting data from various sources, querying data, transforming data including aggregating, duplication and merging data, and then loading the transformed data onto other sources, mail notifications.
Used inside an instance, it is used to provide an asynchronous programming environment. For cross instance applications, Service Broker communicates over TCP/IP and allows the different components to be synchronized together, via exchange of messages. The Service Broker, which runs as a part of the database engine, provides a reliable messaging and message queuing platform for SQL Server applications.
Full Text Search Service
SQL Server Full Text Search service is a specialized indexing and querying service for unstructured text stored in SQL Server databases. The full text search index can be created on any column with character based text data. It allows for words to be searched for in the text columns. While it can be performed with the SQL LIKE operator, using SQL Server Full Text Search service can be more efficient.
Notification Services is a framework for making rich but scalable event and subscription-matching applications. Developers use Notification Services to build alerting applications that generate and deliver alerts for events (e.g., an alert that tells a customer that an out-of-stock item is available for order). Typically, events generate notifications that match criteria from user-generated subscriptions. MSN Alerts uses Notification Services to generate several million notifications per day. Notification Services is part of the Microsoft SQL Server Business Intelligence (BI) platform. When you look at Microsoft’s three BI pillars–Integrate, Analyze, Report–you see that Notification Services is an important feature for the Reporting pillar.