Microsoft SQL Server 2008 PowerShell

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 Microsoft 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.

Below are two Windows PowerShell snap-ins provided by Microsoft 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 Microsoft 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 Microsoft SQL Server. The actions such as running sqlcmd script which contains Transact-SQL or XQuery statements, are supported by cmdlets.

Microsoft SQL Server 2008 Declarative Management Framework

Declarative Management Framework is a system for managing the instances of Microsoft 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.

Microsoft 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.

Microsoft SQL Server 2008 Extended Events

Extended Events are the enhanced event handling system in Microsoft 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.

Microsoft SQL Server 2008 Filestream Storage

The complexity of application development reduces the managing unstructured data cost. The manageability is increased by extending various capabilities which are available only to relational data to non-relational data.

There are 2 new capabilities in Microsoft SQL Server 2008 for persisting BLOB data.

1. FILESTREAM
2. Remote BLOB storage

FILESTREAM

It is an attribute that can be set on a ‘varbinary’ column for data persistence on the file streams. This enables the benefits from fast streaming capabilities and storage capabilities.

Remote BLOB storage

It is a client-side API which reduces the application building complexity and relies on an external persistence for BLOBs and a database for relational data. SQL Server 2008 will also continue the support for standard BLOB type through the data type ‘varbinary’.

Compression Backup in Microsoft SQL Server 2008

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.

1. Physical disk counters
2. Device throughput bytes / second counter of SQL Server Backup Device object
3. Backup/Restore throughput / second counter of SQL Server Databases object.