Microsoft SQL Server 2008

Hi Friends

Today I am going to talk of MS-SQL Server 2008, a very powerful tool in Database Management.


MS SQL Server 2008 aims to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime. SQL Server 2008 will also include support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects), but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them.

Other new data types include specialized date and time types and a Spatial data type for location-dependent data. Better support for unstructured and semi-structured data is provided using the FILESTREAM data type has been added, which can be used to reference any file stored on the file system. Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system. Such files can be accessed both via Win32 file handling APIs as well as via SQL Server using T-SQL; doing the latter accesses the file data as a binary BLOB. Backing up and restoring the database backs up or restores the referenced files as well. SQL Server 2008 also natively supports hierarchical data, and includes T-SQL constructs to directly deal with them, without using recursive queries.

SQL Server 2008 Architecture

Protocol layer

Protocol layer implements the external interface to SQL Server. All operations that can be invoked on SQL Server are communicated to it via a Microsoft-defined format, called Tabular Data Stream (TDS). TDS is an application layer protocol, used to transfer data between a database server and a client.

Data Storage

The main unit of data storage is a database, which is a collection of tables with typed columns. SQL Server supports different data types, including primary types such as Integer, Float, Decimal, Char (including character strings), Varchar (variable length character strings), binary (for unstructured blobs of data), Text (for textual data) among others. It also allows user-defined composite types (UDTs) to be defined and used.

For physical storage of a table, its rows are divided into a series of partitions (numbered 1 to n).

Buffer Management

SQL Server buffers pages in RAM to minimize disc I/O. Any 8 KB page can be buffered in-memory, and the set of all pages currently buffered is called the buffer cache. The amount of memory available to SQL Server decides how many pages will be cached in memory. The buffer cache is managed by the Buffer Manager. Either reading from or writing to any page copies it to the buffer cache.

Concurrency and Locking

SQL Server allows multiple clients to use the same database concurrently. As such, it needs to control concurrent access to shared data, to ensure data integrity - when multiple clients update the same data, or clients attempt to read data that is in the process of being changed by another client. SQL Server provides two modes of concurrency control: pessimistic concurrency and optimistic concurrency. When pessimistic concurrency control is being used, SQL Server controls concurrent access by using locks. Locks can be either shared or exclusive. Exclusive lock grants the user exclusive access to the data - no other user can access the data as long as the lock is held. Shared locks are used when some data is being read - multiple users can read from data locked with a shared lock, but not acquire an exclusive lock. The latter would have to wait for all shared locks to be released.

The Full-Text Search functionality has been integrated with the database engine, which simplifies management and improves performance.


Pic showing SQL Server Full Text Search Service Architecture

SQL Server includes better compression features, which also helps in improving scalability. It also includes Resource Governor that allows reserving resources for certain users or workflows. It also includes capabilities for transparent encryption of data as well as compression of backups. SQL Server 2008 supports the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be build around the Entity Data Model.

On the management side, SQL Server 2008 includes the Declarative Management Framework which allows configuring policies and constraints, on the entire database or certain tables, declaratively.

I will be covering some other features of this tool in my coming posts.

Happy Learning!!...

Cheers

Madhu S

MS SQL Server

Hi Friends
Today I will be discussing Q&A on SQL Server.

Q. What is Cascade and Restrict when we use DROP table in SQL SERVER?
Ans. When we are using Drop table in SQL the syntax is simple. Drop table table_name(CASCADE / RESTRICT). We use cascade to drop table although it have some dependencies just like triggers, strored procedures, primary key,foreign key it will be deleted first. But if we use Restrict an error message is shown on using of DROP if the table have some dependencies.

Q. What is DTS in SQL Server?
Ans. If an organization is big then they might be using multiple options to store data; some people might be using EXCEL some using ACCESS and some of them might be using SQL SERVER or in some other formats also but there a problem arises that how to merge that data into one format. SQL SERVER DTS helps in this problem. It provides a set of tools using which we can customise the database according to our need. DTSRun is a command-prompt
utility used to execute existing DTS packages.

Q. What are the different types of Locks?
Ans. There are three main types of locks in SQL Server.
(1) Shared locks are used for operations that does not allow to change or update data, such as a SELECT statement.
(2) Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
(3) Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

Q. What is the use of DBCC commands?
Ans. DBCC stands for Database Consistency Checker. We use these commands to check the consistency of the databases i.e., maintenance, validation task and status checks. Some of the commands are: DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked. DBCC CHECKALLOC To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

Happy Reading!!...

Cheers

Padhaku Einstein
Contact me at einstein.padhaku@gmail.com

ADO.Net - ActiveX Data Object.Net

Hi Friends
Today I will be discussing Q&A on ADO.Net.

Q. What are Asynchronous Database Commands ?

Ans. When we execute any database, thread that is executing the command waits before the command get fully executed before executing any additional code. Thread is blocked for another process. Asynchronous Database Commands solve this problem when database command is executing, current thread can continue any other process. Thread can execute a no of database commands simultaneously. There are two benefits of using Asynchronous Database Commands.

1) Executing Multiple Database Commands simultaneously improve performance.

2) Since ASP.Net framework uses a limited pool service for request, when anyone request for a page it assigns a thread to handle the request.

Q. Why is ADO.NET serialization slower than ADO ?

Ans. ADO uses binary serialization while ADO.NET uses text based serialization. Since the text takes more space, it takes longer to write it out.

Q. How to get database schema information when connection object is established in ADO.NET ?

Ans. There is a method that helps in getting schema information that is GetSchema() and this method is called in three different ways; first way is when we call this method without any parameter it returns the metadata (table, view, stored procedure, indexes etc). When method is called by passing metadata collection name and filter criteria returns the items from the collection after applying the filter criteria and last way is when we called by passing a metadata collection name returns information about items found in the specified collection.

Q. Which one is better in .Net WebService or Remoting ?

Ans. Both of these are good but have little difference. WebService is helpful where the user who is using WebService doesnot have the .NET plateform. On the otherside for remoting we need .Net plateform on both server side and client side so in both of these remoting is faster then webservice.

Q. What is MARS support in ADO.NET ?

Ans. In First version of ADO.NET we could only do one connection on one result set. But the new feature allows us to do multiple commands on the same connection. Another feature is we can switch back and forth in command objects in connection. MARS means (Multiple Active ResultSets).

Keep Reading!!...

Cheers

Padhaku Einstein
Contact me at einstein.padhaku@gmail.com