Log Shipping in Microsoft SQL Server

Log shipping in Microsoft SQL Server is process used to automatically taking backup of the database and transaction files from one Microsoft SQL Server and then restoring them on a backup server. 

Log shipping keeps the two Microsoft SQL Server instances in sync with each other. If production server fails, then users simply need to be pointed to the backup server. 

Log shipping process consists of following three steps :

1. Backup transaction logs of the Production server.
2. Copy these logs on the backup server.
3. Restore the log on backup server.

Types of Temporary Table in Microsoft SQL Server

There are two types of temporary tables in Microsoft SQL Server. These are: 

1. Local temporary table
2. Global temporary table. 

Local Temporary Table

Local temporary table is available only to the current Db connection for current user and are cleared when connection is closed. Local temporary table can not be shared among multiple users.

Global Temporary Table

Global temporary table is available to any connection once created. They are cleared when the last connection is closed. Global temporary table can be shared by multiple user sessions.

Temporary tables are used to allow short term use of data in Microsoft SQL Server.

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.