Microsoft SQL Server 2008 Architecture
Atomic – A transaction must complete in its entirety or the transaction is rolled back
Consistenty – A transaction cannot break the rules of the database i.e. an update to a record cannot break those rules
Isolation – A transaction must run in isolation of other transaction i.e. other transactions must not be able to view the changes until the transaction is complete.
Durable – Transactions must persist hardware failure e.g. all transactions are written to the transaction before they are committed by the database software.
By default MS SQL will use implicit transactions; implicit transactions follow the ACID properties
described above i.e. Atomic, Consistent, Isolation and durable.
If you want to run multiple transactions and benefit from ACID you need to use explicit transactions.
Explicit transactions start with BEGIN TRANSACTION and end with COMMIT TRANSACTION OR ROLLBACK TRANSACTION
SQL Server network interface and SQL Server native client has replace MDAC in SQL Server 2005 and onwards. Network protocols available to SNI are:
Shared memory can only be used when you’re connecting to SQL server locally i.e. the same computer.
Shared memory is tried first when you connect locally.
The preferred method of connecting when client and server are different computers; TCP 1433 for the default instance, UDP 1434 is used if there is more than one instance. The browser service will redirect the client
to use the correct TCP port.
Named pipes is more commonly used on the LAN; though TCP/IP probably is more common. Named pipes uses TCP 445 and requires an alias to be configured.
The virtual interface adapter requires an alias and specialised hardware at both ends.
Tabular Data Stream (TDS)
Every network protocol configured for use by SQL Server has a TDS endpoint. The TDS endpoint is responsible for managing
end to end communication between the client and server i.e. it encapsulates and decapsulates TDS packets.
There is also a TDS endpoint for the Direct Administrative Connection (DAC); the DAC endpoint can be connected to by specifying ADMIN:[ServerName]\[Instance] via SSMS or sqlcmd.
Once the packet has been identified the protocol layer send the packet to the command parser at the relational engine layer.
The command parser checks the syntax of the T-SQL; if the syntax is invalid the command parser sends any errors back to the protocol layer to send to the client, if the syntax is valid then the T-SQL is hashed and checked against the plan cache to find a existing plan, if one isn’t found then a query plan is created. The plan cache is part of the buffer pool.
In order for SQL server to create a query plan; the command parser creates a query tree which is passed to the query optimiser. The query optimiser will find the most efficient plan i.e. the plan which executes quickly and is quick to generate. The query optimser has multiple optimisation phases; the pre-optimisation phase is used for trivial queries i.e. those with no joins. The next phases depend on the structure of the query.