Database Fundamentals in SQL Server 2008 p2

This lesson will provide you with an overview of the rich set of features and benefits that SQL Server can bring to your organization. You will gain an understanding of the components that are provided by SQL Server 2008. Finally, this lesson will summarize the typical roles and responsibilities of IT professional careers around SQL Server technologies. SQL Server is an example of a technology that has uses a client/server architecture.

Introducing SQL Server 2008 in the Enterprise

What Is a Client/Server Architecture?

The client/server architecture consists of client, server, and network components.

The client is the requester of services while the server is the provider of database services. You can design an application in client/server architecture and the users can access the designed application from client computers.

To help the users access the application, you need to install the client application on the client computer. You also need to install the server-side application on the server that has server software, such as Microsoft SQL Server 2000 or SQL Server 2008.

The user can access SQL Server 2008 by using Named Pipes or Multi-protocol Net-Libraries and share Windows NT. SQL Server 2008 grants access to the server based on Windows NT user or group security identifiers (SIDs).

In Microsoft Access there is only file sharing whereas in client/server architecture there is sharing of processes. When the client sends a request for a service or data to the server, the server responds to the request.

You can categorize client/server applications as multi-tier architecture based on how the business logic is distributed and whether the maximum processing takes place on the client or the server.

You can find several benefits in using client/server architecture. Firstly, you can ensure data integrity because data is stored in a central location.

In addition, you can define business and security rules on the server and enforce these rules equally among all users.

Besides this, you can optimize network traffic because an RDBMS performs effective querying and returns only the data that an application requires.

Therefore, the application workload is shared between the client and the server, thus providing a better performance.

Moreover, client/server architecture allows better scalability of the processes.

When scaling, you can do vertical and horizontal scaling up, that is, you can either increase the configurations of the server for better


What Is Microsoft SQL Server 2008?

Basically, SQL Server 2008 is an RDBMS. However, it is an enterprise-class data platform with integrated Business Intelligence (BI) tools that stores, manages, manipulates, and presents data. You can provide secure multiuser access to your data by using the client/server architecture of SQL Server 2008. Even if there are several can be, the solution is highly available and scalable.

SQL Server 2008 is designed to meet the exploding data needs of many organizations. SQL Server provides a solution that organizations can use to store and manage many types of data, including XML, e-mail, time/calendar, file, document, and geospatial data. It also provides a rich set of services to search, query, analyze, report, and integrate data.

You can use the components of SQL Server 2008 such as Microsoft SQL Server 2008 Analysis Services (SSAS), Microsoft SQL Server 2008 Reporting Services (SSRS), Microsoft SQL Server 2008 Integration Services (SSIS), Replication Services, and Notification Services to provide BI and enterprise data solutions. You can also take advantage of the new features available to SQL Server 2008 to add greater value to your organization’s data needs.


SQL Server Components

SQL Server 2008 also consists of a series of components that allows IT professionals to manage all aspects of database administration, development, and business intelligence development. The following list below is designed to provide an overview of the components purpose within SQL Server.

Relational Database Engine

Relational Database Engine provides a highly secure environment to store, retrieve, and modify data in relational, Extensible Markup Language (XML) and unstructured formats. Besides data management, the database engine enables you to set up security and manage the databases.

SQL Server Component Description
SQL Server Agent SQL Server Agent is a separate service that is used to automate repetitive tasks. Besides setting up job schedules, notifications and alerts can also be set up to alert a database administrator about problems or the successful completion of a job. Furthermore, you can set up alerts to monitor the general status of SQL Server and its databases.The SQL Server database engine must be running for the SQL Server agent to run. There are also tools to monitor the job history of the previously completed jobs.
Transact-SQL (T-SQL) Transact–Structured Query Language (T-SQL) is the primary programming language that is used against SQL Server. Database developers primarily use it query and modify data and objects within the database such as tables, views, and stored procedures.Database administrators will typically use the graphical user interface to manage the day-to-day operation of SQL Server. T-SQL provides useful code that not only allows you to retrieve information about objects that have been created by a database developer, but also to set up administrative tasks, such as backup programmatically.

Understanding T-SQL will benefit any SQL Server professional. This is because the T-SQL programming language is more flexible than the equivalent options that are presented in the GUI.

SQL Server Integration Services (SSIS) SSIS is the service that is responsible for the movement and integration of data from a single or multiple data sources. It can be used for simple data transfers from one system to another. It can also deal with complex data movement that can be used to populate entire database systems such as a data warehouse.SSIS is referred to as an Extract, Transform and Load (ETL) tool. The popularity of SSIS is due to its ability to transform or change data during the data movement, providing consistency to the data to meet business requirements.
SQL Server Analysis Services (SSAS) SSAS is the service that allows the creation of multidimensional structures that contains data aggregated together from other data sources. This multidimensional structure is referred to as a cube. Each intersection of axes within the cube is represented by the aggregation of data at that point. This ultimately delivers Online Analytical Processing (OLAP) from a central point, rather than having to establish this aggregated data from numerous data sources, hindering performance. Data warehouses are typically used as the source to an OLAP database.Furthermore, you can take advantage of the information already in the cube through SSAS data mining. Data mining allows SSAS to identify patterns in the data, which may not be so obvious, by using built-in data mining algorithms. Data mining can also use relational source to pick trend as well as using cubes as a source.
SQL Server Reporting Services (SSRS) SSRS is the reporting tool that can query a wide range of data sources, such as relational and OLAP, and present the information in a more digestible manner. It consists of a developer tool known as Report Designer to provide the professional report writer the ability to create compelling, visual reports. It also has an end-user tool known as the Report Builder that enables business users to create their own ad hoc reports.Visualization comes in the form of charts and gauges. It also involves presenting data in a standard table format. SSRS also provides the feature to automatically deliver reports through a wide range of formats.
Full Text Search Now integrated into the database engine in SQL Server 2008, Full text search enables fast and flexible indexing for keyword-based queries of free text data stored in a SQL Server database.
Service Broker Service Broker is a queuing mechanism that can be used for reliable, transactional, and message-based communication between software services. These services can be a part of applications within a single database or distributed applications.

The service broker service is a part of the database engine and is useful when informational messages from an application may not be immediately available to a backend database. This type of communication is known as asynchronous communication. The messages are stored in a queue until the receiving service becomes available. Introduced in SQL Server 2005, it was difficult to develop asynchronous applications prior to this. With Service broker, Microsoft provides the infrastructure r to manage asynchronous communication. At the same time, it also reduces the development time for such systems.

Replication Replication allows a copy of the data to be made available on another SQL Server so that the data is brought closer to the user. It also ensures that the data is synchronized.

Although replication can extract a copy of data from a source and load it into a destination, it cannot transform the data.

Replication can be controlled to replicate changes immediately or on a scheduled basis. It also has the ability to merge data together from multiple SQL Servers and reconcile conflicting data.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.