What does integration of .NET Framework mean for
SQL Server 2005?
enables us to execute C# or VB.NET code in the DBMS to take advantage of the
.NET functionality. This feature gives more flexibility in writing complex
stored procedures, functions, and triggers that can be written in .net
What is SSIS?
According to Microsoft SQL Server Integration Services,
"(SSIS) is an effective set of tools for both the traditional demands of
ETL operations, as well as for the evolving needs of general purpose data
integration." In short, it is the next version of DTS (Data Transformation
Services). ETL stands for Extract, Transform and Loading. In short it is a data
migration tool that is flexible, fast, and has scalable architecture that
enables effective data integration in current business environments.
What is MARS?
In previous versions of SQL Server, applications had to
process or cancel all result sets from one batch before it could execute any
other batch on that connection. SQL Server 2005 introduces a new connection
attribute that allows applications to have more than one pending request per
connection, and in particular, to have more than one active default result set
per connection. Multiple Active Result Sets (MARS) is the ability to have more
than one pending request under a given SQL Server connection. MARS is a
programming model enhancement that allows multiple requests to interleave in
the server. We need to note that it is not a parallel execution in the server.
However, it may benefit us with some performance benefits if used correctly. By
default, this feature is not set in SQL Server 2005.
What are the Security Enhancements in SQL Server
SQL Server 2005 enables administrators to manage permissions
at a granular level.
In the new SQL Server 2005, we can specify a context under which
statements in a module can execute.
SQL Server 2005 clustering supports Kerberos authentication
against a SQL Server 2005 virtual server.
Administrators can specify Microsoft Windows-style policies on
standard logins so that a consistent policy is applied across all accounts in
SQL Server 2005 supports encryption capabilities within the
database itself, fully integrated with a key management infrastructure. By
default, client-server communications are encrypted.
What is new with the Reporting services in SQL
SQL Server 2005 Reporting Services is a key component of SQL
Server 2005 that provides customers with an enterprise-capable reporting
platform. This comprehensive environment is used for authoring, managing, and
delivering reports to the entire organization. SQL Server 2005 reporting
services have some major changes when compared with the previous version.
Changes to the core functionality of the Reporting services in
the design of the report, processing, and interactivity
Better Integration with other components – Enhanced integration
with other components within SQL Server 2005 like SSIS, SSAS and SQL Server
Report Builder – A new reporting tool that enables business users
to create their own reports
What is OLAP?
Online Analytical Processing (OLAP) allows us to access
aggregated and organized data from business data sources, such as data
warehouses, in a multidimensional structure called a cube. The arrangement of
data into cubes avoids a limitation of relational databases which are not well
suited for near instantaneous analysis of large amounts of data. OLAP cubes can
be thought of as extensions to the two-dimensional array of a spreadsheet.
What is Data Mining?
According to MSDN Data, mining is "the process of
extracting valid, authentic, and actionable information from large
databases." Microsoft data mining tools are different from traditional
data mining applications in significant ways. Data Mining is a platform for
developing intelligent applications, not a stand-alone application. You can
build custom applications that are intelligent because the data mining models
are easily accessible to the outside world. Further, the model is extensible so
that third parties can add custom algorithms to support particular mining
What is new with the Analysis Services (SSAS) in
SQL Server 2005?
SQL Server 2005 Analysis Services (SSAS) delivers online
analytical processing (OLAP) and data mining functionality through a
combination of server and client technologies, further reinforced through the
use of a specialized development and management environment coupled with a
well-defined object model for designing, creating, deploying, and maintaining
business intelligence applications. The server component of Analysis Services
is implemented as a Microsoft Windows service. Clients communicate with
Analysis Services using the public standard XML for Analysis (XMLA), a
SOAP-based protocol. Let us see the enhancements of made to SSAS.
Supports up to 16 instances of Analysis Services Service.
As discussed above, the Analysis Services service fully
implements the XML for Analysis (XMLA) 1.1 specification. All
communication with an instance of Analysis Services is handled through XMLA
commands in SOAP messages.
Uses the Proactive caching.
What is Information Schema in SQL Sever 2005?
Information Schema is the part of the SQL- 92 standard which
exposes the metadata of the database. In SQL server, a set
of views are created in each of the databases which exposes the metadata of the
database. The information schema is kept in a separate schema - information
schema - which exists in all databases, but which is not included in the search
path by default. For more information regarding Information schema please read
What is Full Text Search? How does it get
implemented in SQL server 2005?
Full-text search allows fast and flexible indexing for
keyword-based query of text data stored in a Microsoft SQL Server
database. In contrast to the LIKE predicate which only works on character
patterns, full-text queries perform linguistic searches against this data, by
operating on words and phrases based on rules of a particular language.
What is integration of Microsoft Office System
The integration with Microsoft Office system means the
Table Analysis Tools for Excel: Provides an easy-to-use add-in
that leverages SQL Server 2005 Data Mining behind the scenes to perform
powerful end user analysis on spreadsheet data.
Data Mining Client for Excel: Offers a full data mining model
development lifecycle directly within Excel 2007.
Data Mining Templates for Visio: Enables powerful rendering and
sharing of mining models as annotatable Visio 2007 drawings.
What is the support of Web Services in SQL
With this feature the database engine can be directly
exposed as a web service without a middle tier or even an IIS. This will enable
the user to directly call a stored procedure by calling a web method. This
feature is designed with well-known standards such as SOAP 1.2, WSDL 1.1, and
HTTP. With this new feature we can now connect to SQL Server not only with TDS-
Tabular data stream (a binary protocol for connecting to SQL Server 2005) but
also over SOAP/ HTTP.
What is OLTP?
Online Transaction Processing (OLTP) relational databases
are optimal for managing changing data. When several users are performing
transactions at the same time, OLTP databases are designed to let transactional
applications write only the data needed to handle a single transaction as
quickly as possible.
What is Snapshot in SQL Server 2005?
A database snapshot is a read-only, static view of a
database, the source database. Each database snapshot is transaction-consistent
with the source database as it existed at the time of the snapshot's creation.
What is snapshot isolation in SQL Server 2005?
SQL Server 2005 introduces a new "snapshot"
isolation level that is intended to enhance concurrency for online transaction
processing (OLTP) applications. In prior versions of SQL Server, concurrency
was based solely on locking, which can cause blocking and deadlocking problems
for some applications. Snapshot isolation depends on enhancements to row
versioning and is intended to improve performance by avoiding reader-writer
What is Database Partitioning in SQL Server
SQL Server 2005 provides a new capability for the
partitioning of tables across file groups in a database. Partitioning a
database improves performance and simplifies maintenance. By splitting a large
table into smaller, individual tables, queries accessing only a fraction of the
data can run faster because there is less data to scan.
What is SQL Server Agent?
SQL Server Agent is a Microsoft Windows service that
executes scheduled administrative tasks called jobs. SQL Server Agent uses SQL
Server to store job information. Jobs contain one or more job steps. We
generally schedule the backups on the production databases using the SQL server
agent. In SQL Server 2005 we have roles created for using SQL Server agents.
SQL Server Agent for SQL Server 2005 provides a more robust
security design than earlier versions of SQL Server. This improved design gives
system administrators the flexibility they need to manage their Agent service.
What is Replication? What is the need to have
the replication? What are the enhancements made to SQL Server 2005 related to
"Replication is a set of technologies for copying and
distributing data and database objects from one database to another and then
synchronizing between databases to maintain consistency.” In short, replication
is all about having multiple copies of the same database. We need replication
when we need to distribute data to and from different locations. Generally we
have a master copy of data. There will be multiple slaves (Clients) located at
various locations which need to be replicated. We use replication for a variety
of reasons. Load balancing is sharing the data among a number of servers and
distributing the query load. Offline processing is one of the main reasons. In
this scenario we need to modify the data on the database that is not connected
to the network. The last reason may be to have a back-up to the database in
case of failure to the existing database. Let us see the enhancements of SQL
server 2005 database related to replication.
Database Mirroring – Database Mirroring is moving the
transactions of database from one SQL Server database to another SQL server
database on a different SQL Server.
Replication Management topology (RMO) – RMO is a new construct in
SQL Server 2005. It is a .NET Framework library that provides a set of common
language runtime classes for configuring, managing, and scripting replication,
and for synchronizing Subscribers.
What are Business Logic Handlers?
Business logic handlers are written in managed code and
allow us to execute custom business logic during the merge synchronization. We
can invoke the business logic handler in case of non-conflicting data changes.
Business logic handler can perform one of the following three actions.
Apply Custom Data
What are different variants of SQL Server 2005?
There are different variants of SQL Server 2005 commercially
Express – Free and only for one user
Enterprise – 5 users apart from server
Workgroup – 10 users apart from server
Standard – 25 users apart from server
What are Various Service packs available for SQL
As of now there are two service packs available for the SQL
Service Pack 1 – Has major changes or enhancements to SQL Server
2005 in Analysis Services, Data Programmability, SSIS, and reporting services.
Service Pack 2 – Unlike Service Pack 2, this service pack enables
SQL Server 2005 customers to take advantage of the enhancements within Windows
Vista and the 2007 Office system.
What are the New Data types introduced in SQL
SQL Server 2005 has added some new data types to its
existing data types.
XML Data type
As we can see, the new term MAX has been introduced in SQL
Server 2005. This new specifier expands the storage capabilities of the varchar,
nvarchar, and varbinary data types. Varchar(max), nvarchar(max), and varbinary(max)
are collectively called large-value data types.
Does SQL Server 2005 support SMTP?
SQL Server 2005 now supports sending E-mail from the
database. It is called as database mail and it uses DatabaseMail90.exe. Gone
are the days when we were using a third party component for this. Receiving an
e-mail was not supported in the previous versions of SQL Server.
What is SQL Management Object is SQL Server
These are collection of objects that are made for programming
all aspects of managing Microsoft SQL Server 2005. SMO is a .NET based object
model. It comes with SQL Server 2005 as a .Net assembly named
Microsoft.SqlServer.Smo.dll. We can use these objects for connecting to a
database, calling methods of the database that returns a table, using
transactions, transferring data, scheduling administrative tasks, etc. The best
part about SMO is that most of it can also be used with SQL server 2000.
What is SQL Service Broker in SQL Server 2005?
SQL Service broker is a new technology introduced in SQL
Server 2005 for building database-intensive distributed applications. Basically,
service broker has been built for developing applications that consist of
individual components which are loosely coupled. Service broker supports asynchronous yet reliable messages that are passed between the components. These messages
are called conversations.