SQL 2005 introduces database snapshots. You create a point-in-time snapshot that coexists on the server with the original database. It actually uses the same tables until the tables change, at which point, the original data pages (only the affected pages) are copied to the snapshot file. This is implemented through the use of 'sparse files', an artifact of the NTFS. BOL gives a nice overview (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ f838de94-3ae7-49f7-9dbf-52ed30bcff81.htm if you have SQL 2005 BOL installed).
Based on some recent involvement with versioning test data and maintaining reporting data, I think this is going to be a nice feature. We can now maintain a pristine set of test data, right on the server with the database, and restore to that version after each round of testing. While testing is occurring, we can compare the original data in the snapshot with the data the tests modify. In SQL2K, this can be achieved by restoring backup files and maintaing a second database for comparisons, however, the overhead goes down considerably by having the two entities coexist.
One limitation I came up against was trying to maintain several snapshots for a single database (take a snapshot, make a change, take a snapshot, etc.). When it's time to roll back to a snapshot, only the snapshot to which you want to rollback can exist. You must first drop all the other snapshots for that database. It's nice having serval snapshot points from which to choose, however, it's frustrating to not be able to keep the other snapshot points (other than by taking backups of the source database in conjunction with the snapshots, which is how you get to do the whole snapshot thing in SQL2K, anyway).
Snapshots must be created and dropped using T-SQL. You create a snapshot with the create database statement using the AS SNAPSHOT <snapshot_name> clause. You rollback to a snapshot with the RESTORE DATABASE command using the FROM DATABASE_SNAPSHOT <snapshot_name> clause. DROP DATABASE <snapshot_name> drops the snapshot.
Snapshots can probably also be used for lightweight reporting. Take a month-end (quarter-end, year-end, etc.) snapshot and users have a read-only, point-in-time, picture of the database against which they can do their reporting (snapshot permissions can never be changed so, be sure any reporting specific permissions are in place before the snapshot is taken). I'm guessing you would still have the same old issues of your reporting database fighting for resources with your transactional database (especially since the two would actually be sharing some of the same data pages). I'd say use this technique judiciously.
09 March 2005
03 March 2005
Light Auditing Using OUTPUT
Had a brief conversation at work today about the inserted and deleted tables that are available in sql 2000 triggers. The availability of these "tables" for sql 2005 has been expanded with the OUTPUT clause.
Any values that are inserted or deleted as the result of INSERT, UPDATE, or DELETE statements are available through this clause (the values as they exist BEFORE any triggers fire).
This is a handy way to enforce application level auditing without having to worry about maintaining triggers (and disabling them for non-audited, administrative udpates).
An example would really make this more clear. Since I don't want to clutter this up with sample code, I've put some code here(no longer available; 20060313), in case you have a sql 2005 instance on which you would like to run it. If you don't have an instance on which to run it, the query results are included at the end of the file. You can walk through the code to see how they were produced.
The Microsoft SQL Server Management Studio (still couldn't remember the whole thing) doesn't highlight OUTPUT as a keyword. Though it was rather ironic that one of the new features hadn't made it into the code editor, yet. Also noticed that NOCOUNT doesn't highlight like it does in query analyzer. Guess it's just a beta :-)
On a quick, unrelated note, table variables still don't support named constraints. For example,
DECLARE @tableVariable TABLE (pk int identity( 1, 1 )
CONSTRAINT [PK__tableVariable__pk] PRIMARY KEY CLUSTERED )
is not a valid sql statement while
DECLARE @tableVariable TABLE (pk int identity( 1, 1 ) PRIMARY KEY CLUSTERED )
and
CREATE TABLE tableName (pk int identity( 1, 1 )
CONSTRAINT [PK__tableName__pk] PRIMARY KEY CLUSTERED )
both are.
Sorry about the code pollution.
Any values that are inserted or deleted as the result of INSERT, UPDATE, or DELETE statements are available through this clause (the values as they exist BEFORE any triggers fire).
This is a handy way to enforce application level auditing without having to worry about maintaining triggers (and disabling them for non-audited, administrative udpates).
An example would really make this more clear. Since I don't want to clutter this up with sample code, I've put some code here(no longer available; 20060313), in case you have a sql 2005 instance on which you would like to run it. If you don't have an instance on which to run it, the query results are included at the end of the file. You can walk through the code to see how they were produced.
The Microsoft SQL Server Management Studio (still couldn't remember the whole thing) doesn't highlight OUTPUT as a keyword. Though it was rather ironic that one of the new features hadn't made it into the code editor, yet. Also noticed that NOCOUNT doesn't highlight like it does in query analyzer. Guess it's just a beta :-)
On a quick, unrelated note, table variables still don't support named constraints. For example,
DECLARE @tableVariable TABLE (pk int identity( 1, 1 )
CONSTRAINT [PK__tableVariable__pk] PRIMARY KEY CLUSTERED )
is not a valid sql statement while
DECLARE @tableVariable TABLE (pk int identity( 1, 1 ) PRIMARY KEY CLUSTERED )
and
CREATE TABLE tableName (pk int identity( 1, 1 )
CONSTRAINT [PK__tableName__pk] PRIMARY KEY CLUSTERED )
both are.
Sorry about the code pollution.
Subscribe to:
Posts (Atom)