SQL server 2008

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.

Spatial data is stored by using Geometry and Geography data types that are introduced in SQL Server 2008.

Geometry data type is created as follows:

CREATE TABLE SpatialTable

( id int IDENTITY (1,1),

GeomCol1 geometry,

GeomCol2 AS GeomCol1.STAsText() );

GO

The data into the geometry data column is persisted by using the following INSERT command

INSERT INTO SpatialTable (GeomCol1)

VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

The data in the geometry data columns is queried by using the following DECLARE and SELECT statements:

DECLARE @geom1 geometry;

DECLARE @geom2 geometry;

DECLARE @result geometry;

SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;

SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;

SELECT @result = @geom1.STIntersection(@geom2);

SELECT @result.STAsText();

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.

Change Data Capture is a feature that is used for tracking the changes on a table. The process involves in steps.

Step 1 – Creation of a database

The database name is MyDataBase

USE [master]

GO

/*** Object: Database [MyDataBase] ***/

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MyDataBase')

DROP DATABASE [MyDataBase]

GO

USE [master]

GO

/*** Object: Database [MyDataBase] ***/

CREATE DATABASE [MyDataBase]

GO

Step 2 - Creation of a table in MyDataBase database

USE [MyDataBase]

GO

/*** Object: Table [dbo].[MyTable] ***/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')

AND type in (N'U'))

DROP TABLE [dbo].[MyTable]

GO

USE [MyDataBase]

GO

CREATE TABLE [dbo].[MyTable](

[ID] [int] NOT NULL,

[Name] [varchar](100) NULL,

CONSTRAINT [MyTable_PK] PRIMARY KEY

GO

Step 3 - Enabling Change Data Capture feature.

The Transact SQL command enables the Change Data Capture feature.

After enabling the Change Data Capture, a schema along with objects is created.

USE [MyDataBase]

GO

EXEC sys.sp_cdc_enable_db_change_data_capture

GO

Using the cdc the columns, tables, history of ddl commands, index columns and time mappings are created as follows:

cdc.captured_columns

cdc.change_tables

cdc.ddl_history

cdc.index_columns

cdc.lsn_time_mapping