SQL Server 2008 Interview Preparation Guide

A comprehensive solution for encryption and key management is provided by SQL Server 2008. The growing need for greater information security within the data centers of enterprises is managed by security keys. This could be done by excellent support given by SQL Server 2008, by supporting third-party keys management and hardware security module products.

Declarative Management Framework is a system for managing the instances of SQL Server 2008. It is a policy based system. The database security can be tighten with polity management, automated administration and explicit administration. A policy can be designed for prohibiting the usage of unauthorized applications and the naming conventions on the database are implied for developers.

Various tasks are planned by many DBAs and backing up databases, reviewing events logs, scanning for improper / unauthorized object creations, long running query SPIDs killing are being performed. Lot of tactical and reactionary items on the list of tasks can keep the conscientious DBA busy.

SQL Server will enforce to define the policies by using Management Studio and select certain servers for enforcing the policy. These can be monitored from SSMS, which acts as a central console.

The demands of regulatory compliance and overall data privacy concern are enabled by encryption in organizations. Searching for encrypted data by using fuzzy searches or range includes secure data from unauthorized users, and data encryption. The existing applications need not be changed by using this concept.

CPUs can dynamically be added to a running system, by using Hot Add CPU feature. New hardware can be added physically and online hardware partitioning logically. A virtualization layer can be used to add this feature virtually.

Hot Add CPU feature allows a database for scaling on demand for extending memory resources added online, The CPU resources can be supported for SQL Server 2008 on hardware platforms that supports, without the need of application downtime

The following are the requirements for Hot Add CPU:

1. Hardware which supports Hot Add CPU
2. Windows Server 2008 Datacenter server of 64-bit or Windows Server 2008 Enterprise Edition for the Itanium-Based System operating system.
3. SQL Server Enterprise.

Data base mirroring in SQL Server 2008 is enhanced by:

Automatic Page Repair: The principal is enabled and mirror computers for recovering transparently from 823 and 824 errors on data pages, with a request for a fresh copy of the page that is corrupted from the mirroring partner.
Improved Performance: The outgoing log stream is compressed by SQL Server 2008 for minimizing the network bandwidth that is required by database mirroring.
Enhanced Supportability: Additional performance counters for enabling more granular accounting of the time, which is spent across the different stages of DBMS log processing. Dynamic Management Views and extensions of the existing views are included, for exposing additional information that is concerned to mirroring sessions.

Windows PowerShell is a new and powerful scripting shell which can be used by developers and administrators for automating server administration and deployment of the application. The PowerShell supports complex logic than the Transact-SQL scripts, which provides the SQL Server administrators the power to build robust administration scripts. Other Microsoft products can be administered by PowerShell. In a nut shell, PowerShell is a common scripting language across different servers.

There are two Windows PowerShell snap-ins provided by SQL Server provides:

SQL Server provider: It enables the navigation mechanism similar to the file system paths. Paths that are similar to file system paths can be built. The process is associated with SQL Server management object model and the other nodes are object model classes based.
Set of cmdlets: These are the commands which are utilized in PowerShell scripts for specifying an action of SQL Server. The actions such as running sqlcmd script which contains Transact-SQL or XQuery statements, are supported by cmdlets.

Extended Events are the enhanced event handling system in SQL Server. It is an architecture that is highly scalable, highly configurable. It allows the DBAs for collecting the required information, could it be little or much, for problem identification or problem trouble shooting.

Data collection which can output to an Event Tracing for Windows target is one of the key features of Extended Events. This allows the correlating data with the data collected from the corresponding operating system with the help of Event Tracing for Windows. Even the wait events could be correlated with the kernel EWT data. This process is done within a single view for isolating the waits for a specific reason.

The events are handled by an engine which is a services and objects collection which allows defining, process and managing event sessions, event data and event sessions respectively.

Location based data can be seamlessly consumed and extended by the enterprises with the comprehensive support of spatial data types.

Geometry data type: This data type supports a flat 2D surface with points of XY coordinates. The points could be on line string, on lines and also mark the polygon edges. There are certain methods like STintersects, STarea, STDistance and STTouch which uses geometry data types.

Geography data type: The similar methods of geometry data type are used in this data type. But the type reflects on curved 2D surfaces. The ST* functions are utilized but the results in the curvature.

DECLARE @gmtry geometry;
SET @gmtry = geometry::STGeomFromText('POINT (3 4)', 0);
DECLARE @grphy geography;
SET @grphy = geography::STGeomFromText('POINT (3 4)', 4326);
Certain observations need to be considered. They are:

- A polygon is tried which is larger than a hemisphere, an ArgumentException is thrown.
- If the returned result is larger than a hemisphere, then a NULL value is returned.

A compression backup is smaller than uncompressed backup. The backup speed significantly increases because it needs less disk space and I/O operations. The CPU usage is increased and the increased and the additional CPU that is consumed by the process of compression would impact the operations that are running concurrently.

The following processes can be isolated for obtaining a good picture

- Physical disk counters

- Device throughput bytes / second counter of SQL Server Backup Device object

- Backup/Restore throughput / second counter of SQL Server Databases object.

A column with an optimized storage for null values is known as sparse column. Sparse columns reduce the storage space needs for null values. In a scenario of saving 20 percent to 40 percent of storage space, sparse columns can be considered. They are created using CREATE TABLE or ALTER TABLE statements. Sparse columns can be used with

- Column sets: The statements INSERT, DELETE, UPDATE could be referred the sparse columns by name. The same an also be combined into a single XML Column. This is a column set.

- Filtered index: As there are several null value rows in sparse columns, they are appropriate for filtered indexes. The filtered index on a sparse column can accommodate only the rows / tuples which populate values. The index created with filtered index is small and more efficient index.

A large number of user defined properties can be accessed by using sparse columns and filtered indexes enabled applications such as Share Point Services of windows are efficiently store and access.