Versioning

[Jerry]

Dear friends,

In the area of GIS (Geographic Information Systems) there is a feature
known as versioning (long transactions).

This feature allows databases to maintain different versions of data,
in a hierachycal structure, in order to do simulation (what if ...),
historical snapshots, concurrent editing, etc.

Each version can be reconciled with its parent version in any moment
(merge-post changes).

I have recently seen that Oracle supports this feature from version
9i.

I am very interested in knowing if SQL server will support this
feature in future versions. Looking at SQL Server 2005 doc. I haven't
seen any related info.

Thanks,

Jerry

[Erland Sommarskog]

Jerry (to****@hotmail.com) writes:

In the area of GIS (Geographic Information Systems) there is a feature
known as versioning (long transactions).

This feature allows databases to maintain different versions of data,
in a hierachycal structure, in order to do simulation (what if ...),
historical snapshots, concurrent editing, etc.

Each version can be reconciled with its parent version in any moment
(merge-post changes).

I have recently seen that Oracle supports this feature from version
9i.

I am very interested in knowing if SQL server will support this
feature in future versions. Looking at SQL Server 2005 doc. I haven't
seen any related info.

In SQL 2005, there is indeed a new snapshot isolation level which aims
at the scenarios like the one you discuss. Although my reaction from your
description is that the requirements here may go beyond what you normally
have snapshots for - readers don't block writers. I have not looked at
the snapshots capabilities in SQL Server 2005 in much detail yet. My
main understanding of the features is from my days with DEC/Rdb (which
Oracle later acquired, and from which I assumed they got their snapshot
function.) In RDB, the snapshot was a before-image, so that when a
transaction performed an update, the old version was pushed to the
snapshot. I'm not really sure that I can see who this can work with
your scenarios, assuming that you actually want to look at all these
versions.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jerry]

Thanks Erland for your response.

I'll read about SQL 2005 snapshots to see if thef fit our needs.

As mentiones we would use them for:
* Create historical persistent versions (monthly, per year).
* Create proposed data version, which in some cases (where aproved)
would merge to the main or "nominal" version of the DB.

You can read an abstract about Oracle's implementation at:
http://www.oracle.com/technology/pro...mgr/index.html

Jerry

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

Jerry (to****@hotmail.com) writes:

In the area of GIS (Geographic Information Systems) there is a feature
known as versioning (long transactions).

This feature allows databases to maintain different versions of data,
in a hierachycal structure, in order to do simulation (what if ...),
historical snapshots, concurrent editing, etc.

Each version can be reconciled with its parent version in any moment
(merge-post changes).

I have recently seen that Oracle supports this feature from version
9i.

I am very interested in knowing if SQL server will support this
feature in future versions. Looking at SQL Server 2005 doc. I haven't
seen any related info.

In SQL 2005, there is indeed a new snapshot isolation level which aims
at the scenarios like the one you discuss. Although my reaction from your
description is that the requirements here may go beyond what you normally
have snapshots for - readers don't block writers. I have not looked at
the snapshots capabilities in SQL Server 2005 in much detail yet. My
main understanding of the features is from my days with DEC/Rdb (which
Oracle later acquired, and from which I assumed they got their snapshot
function.) In RDB, the snapshot was a before-image, so that when a
transaction performed an update, the old version was pushed to the
snapshot. I'm not really sure that I can see who this can work with
your scenarios, assuming that you actually want to look at all these
versions.

[Jerry]

Thanks Erland for your response.

I'll read about SQL 2005 snapshots to see if thef fit our needs.

As mentioned we would use them for:
* Create historical persistent versions (monthly, per year).
* Create proposed data version, which in some cases (where aproved)
would merge to the main or "nominal" version of the DB.

You can read an abstract about Oracle's implementation at:
http://www.oracle.com/technology/pro...mgr/index.html

Jerry

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

Jerry (to****@hotmail.com) writes:

In the area of GIS (Geographic Information Systems) there is a feature
known as versioning (long transactions).

This feature allows databases to maintain different versions of data,
in a hierachycal structure, in order to do simulation (what if ...),
historical snapshots, concurrent editing, etc.

Each version can be reconciled with its parent version in any moment
(merge-post changes).

I have recently seen that Oracle supports this feature from version
9i.

I am very interested in knowing if SQL server will support this
feature in future versions. Looking at SQL Server 2005 doc. I haven't
seen any related info.

In SQL 2005, there is indeed a new snapshot isolation level which aims
at the scenarios like the one you discuss. Although my reaction from your
description is that the requirements here may go beyond what you normally
have snapshots for - readers don't block writers. I have not looked at
the snapshots capabilities in SQL Server 2005 in much detail yet. My
main understanding of the features is from my days with DEC/Rdb (which
Oracle later acquired, and from which I assumed they got their snapshot
function.) In RDB, the snapshot was a before-image, so that when a
transaction performed an update, the old version was pushed to the
snapshot. I'm not really sure that I can see who this can work with
your scenarios, assuming that you actually want to look at all these
versions.

[Jerry]

to****@hotmail.com (Jerry) wrote in message news:<6c**************************@posting.google. com>...

Thanks Erland for your response.

I'll read about SQL 2005 snapshots to see if thef fit our needs.

As mentioned we would use them for:
* Create historical persistent versions (monthly, per year).
* Create proposed data version, which in some cases (where aproved)
would merge to the main or "nominal" version of the DB.

You can read an abstract about Oracle's implementation at:
http://www.oracle.com/technology/pro...mgr/index.html

Jerry

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

Jerry (to****@hotmail.com) writes:

In the area of GIS (Geographic Information Systems) there is a feature
known as versioning (long transactions).

This feature allows databases to maintain different versions of data,
in a hierachycal structure, in order to do simulation (what if ...),
historical snapshots, concurrent editing, etc.

Each version can be reconciled with its parent version in any moment
(merge-post changes).

I have recently seen that Oracle supports this feature from version
9i.

I am very interested in knowing if SQL server will support this
feature in future versions. Looking at SQL Server 2005 doc. I haven't
seen any related info.

In SQL 2005, there is indeed a new snapshot isolation level which aims
at the scenarios like the one you discuss. Although my reaction from your
description is that the requirements here may go beyond what you normally
have snapshots for - readers don't block writers. I have not looked at
the snapshots capabilities in SQL Server 2005 in much detail yet. My
main understanding of the features is from my days with DEC/Rdb (which
Oracle later acquired, and from which I assumed they got their snapshot
function.) In RDB, the snapshot was a before-image, so that when a
transaction performed an update, the old version was pushed to the
snapshot. I'm not really sure that I can see who this can work with
your scenarios, assuming that you actually want to look at all these
versions.

I've been reading the SQL Server 2005 Beta 2 Snapshot Isolation
documentantion. As far as I understand, this new isolation level gets
the benefits of both "READ COMMITED" (data consistency accross
transactions) and "READ UNCOMMITED" (performance avoiding readers
block writers).

But, if I'm not wrong, you cannot create persistent versioned data
sets, in order to deal with "long transactions" (a set of traditional
transactions that don't affect the main version of the database) or
historical versions, that Oracle's Workspace Manager does achieve.

I hope Microsoft takes care about this powerful feature and includes
it on incomming versions of SQL Server.

[Erland Sommarskog]

Jerry (to****@hotmail.com) writes:

I've been reading the SQL Server 2005 Beta 2 Snapshot Isolation
documentantion. As far as I understand, this new isolation level gets
the benefits of both "READ COMMITED" (data consistency accross
transactions) and "READ UNCOMMITED" (performance avoiding readers
block writers).

But, if I'm not wrong, you cannot create persistent versioned data
sets, in order to deal with "long transactions" (a set of traditional
transactions that don't affect the main version of the database) or
historical versions, that Oracle's Workspace Manager does achieve.

I hope Microsoft takes care about this powerful feature and includes
it on incomming versions of SQL Server.

I assume that you read
http://www.microsoft.com/technet/pro...5/sql05b.mspx?

But there might be more possibilities. Since we last spoke, I have watched
a web cast on backup/restore enhancements in SQL 2005. There is a new
feature call "Database snapshot". This permits you to take an instant
backup of the database, which is basically empty initially, but as pages
are modified, the originals are copied to the snapshot. You can then
restore that snapshot easily. You can have several active snapshots at
the same time.

Since this works on the entire database it may not be fine-grained enough,
though.

I have not found that much public information, but there is a short
passage about it on
http://www.microsoft.com/technet/pro...n/sqlydba.mspx.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp