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.
24 February 2005
2K from 2K5
The Microsoft SQL Server Management Studio (had to type it again because I still can't remember its name) can actually manage a SQL Server 2000 instance, as advertised. I tried it out and it seemed to work normally. However, I was unable to connect to the 2K5 instance from the Enterprise Manager (after all these years, I can remember that one, even without typing it out for reference). It gave an error telling me I needed to connect using the MSSMS (consider this a 'synonym' for the new name) or with SMO (SQL Server Management Objects, a scriptable set of objects for managing sql server that live in a DLL somewhere).
Even though I couldn't connect to the 2K5 instance with EM, I was able to connect to it with query analyzer. It's always nice to use a familiar tool when working on a new toy. Speaking of query analyzer, it has shortcut keys you can set up with sql statements. For example, I map the [ctl]+3 key combination to sp_helptext so I can just highight an object name and get the relevant sp_helptext results. I did not see this facility in the MSSMS query window (though you can turn on line numbers, just like basic on a TRS-80; yee-hah!)
Didn't get a chance to do much more playing this time around. The machine I have it installed on switched to Psychic Failover Mode (PFM) and had a network breakdown. It's feeling much better now but, why wait for it to happen in a more permanent manner? I am installing it on another machine where it will live side-by-side with a sql2k instance. I read somewhere that you can do that. If the machine comes back up when it's all said and done, I guess that will be a good sign. It's a dreadfully slow process, the install routine has to spend about 86400 seconds installing the .net 2.0 framework before it actually does anything useful. Microsoft is really keeping up with Sun on this one; .Net will soon have as many versions as Java. As long as we don't get fishing.net and cast.your.net to match java beans and what-not, I guess I can live with it.
I just noticed there are six parentheticals in these three paragraphs (now four paragraphs and seven parentheticals). It appears that staying on topic is not a strong point of SQL2K5. Good thing we can just add more processors and memory.
Even though I couldn't connect to the 2K5 instance with EM, I was able to connect to it with query analyzer. It's always nice to use a familiar tool when working on a new toy. Speaking of query analyzer, it has shortcut keys you can set up with sql statements. For example, I map the [ctl]+3 key combination to sp_helptext so I can just highight an object name and get the relevant sp_helptext results. I did not see this facility in the MSSMS query window (though you can turn on line numbers, just like basic on a TRS-80; yee-hah!)
Didn't get a chance to do much more playing this time around. The machine I have it installed on switched to Psychic Failover Mode (PFM) and had a network breakdown. It's feeling much better now but, why wait for it to happen in a more permanent manner? I am installing it on another machine where it will live side-by-side with a sql2k instance. I read somewhere that you can do that. If the machine comes back up when it's all said and done, I guess that will be a good sign. It's a dreadfully slow process, the install routine has to spend about 86400 seconds installing the .net 2.0 framework before it actually does anything useful. Microsoft is really keeping up with Sun on this one; .Net will soon have as many versions as Java. As long as we don't get fishing.net and cast.your.net to match java beans and what-not, I guess I can live with it.
I just noticed there are six parentheticals in these three paragraphs (now four paragraphs and seven parentheticals). It appears that staying on topic is not a strong point of SQL2K5. Good thing we can just add more processors and memory.
22 February 2005
Couple of useful new features
We can now INSERT INTO EXEC(...) into a table variable. We are no longer limited to using a temp table for this. Sorry, but, I like this new feature very much. Although temporary table storage is rather sloppy (and temporary tables are more sloppy than table variables), catching the results from xp_cmdshell is one seemingly legitimate use for them.
Also stumbled across the WITH EXECUTE AS clause. I was using some kind of stored procedure wizard thing (IDE folks are sure to love it) to make a stored procedure that provides directory listings using the new INSERT INTO EXEC... functionality. It executes the stored procedure (or queue, or trigger, or function) with the specified permissions. I have no idea what a queue is, beyond the obvious, 'it's a queue'; I'll have to dig that one up. At any rate, declare your procedure as normal...
CREATE PROCEDURE <owner>.<procedure>
@parm0 int
@parm1 int
/* with clause comes after parameter list */
WITH EXECUTE AS < CALLER | SELF | OWNER | 'user_name' >
AS ...
When the procedure is executed, the permissions of the specified executor are used when accessing all required base objects.
AS OWNER, the procedure is executed with the permissions of the procedure owner (dbo is common)
AS SELF executes the procedure with the permissions of the user that created the procedure
AS CALLER executes the procedure with the permissions of the process/user who is calling it
AS 'user_name' can be any valid user; the procedure will be executed with that user's permissions.
This will be handy when ownership chaining causes problems. Instead of the caller having to have permissions on the various base tables (messy bit of work, that is), the procedure can be executed as the owner, or any other use with the appropriate permissions. Let's see how long till there is an escalation exploit for this one :-)
Wow! schemas are no longer directly tied to database users (e.g., a table owner no longer has to be a user). Users can be managed without worrying about the effect on the objects they may have owned. I'll have to read up on this one.
Also stumbled across the WITH EXECUTE AS clause. I was using some kind of stored procedure wizard thing (IDE folks are sure to love it) to make a stored procedure that provides directory listings using the new INSERT INTO EXEC... functionality. It executes the stored procedure (or queue, or trigger, or function) with the specified permissions. I have no idea what a queue is, beyond the obvious, 'it's a queue'; I'll have to dig that one up. At any rate, declare your procedure as normal...
CREATE PROCEDURE <owner>.<procedure>
@parm0 int
@parm1 int
/* with clause comes after parameter list */
WITH EXECUTE AS < CALLER | SELF | OWNER | 'user_name' >
AS ...
When the procedure is executed, the permissions of the specified executor are used when accessing all required base objects.
AS OWNER, the procedure is executed with the permissions of the procedure owner (dbo is common)
AS SELF executes the procedure with the permissions of the user that created the procedure
AS CALLER executes the procedure with the permissions of the process/user who is calling it
AS 'user_name' can be any valid user; the procedure will be executed with that user's permissions.
This will be handy when ownership chaining causes problems. Instead of the caller having to have permissions on the various base tables (messy bit of work, that is), the procedure can be executed as the owner, or any other use with the appropriate permissions. Let's see how long till there is an escalation exploit for this one :-)
Wow! schemas are no longer directly tied to database users (e.g., a table owner no longer has to be a user). Users can be managed without worrying about the effect on the objects they may have owned. I'll have to read up on this one.
21 February 2005
The basics work as expected
Did a little fiddling with stored procedures, views, and user-defined functions. CREATE, DROP, EXECUTE, SELECT; all work as expected. Might dig through the documentation sometime to see if there are any changes that aren't intuitively obvious.
CREATE DATABASE seemed to work as expected, too. I did stumble across a little blurb in the help file that said the default filegrowth is now 1MB instead of 10%. Just replacing one potentially inappropriate arbitrary growth rate with another. Disregard the defaults and try to put together good estimates for your initial size and growth rates so you don't have to worry about frequent grows and the resulting file fragmentation.
Also noticed that the checksum() and binary_checksum() functions still ignore trailing spaces, to my continuing dismay. I'll have to dig up ufn_checksum() and see if it works in 2k5.
Microsoft SQL Server Management Studio was the name of the enterprise manager / query analyzer replacement I couldn't remember earlier. Maybe I'll remember it, now that I have typed it. At any rate, it looks like database diagrams may be gone, which probably isn't that great a loss. On the other hand, there are approximately 1,000 million T-SQL templates from which to choose (up from 3, give or take a few).
CREATE DATABASE seemed to work as expected, too. I did stumble across a little blurb in the help file that said the default filegrowth is now 1MB instead of 10%. Just replacing one potentially inappropriate arbitrary growth rate with another. Disregard the defaults and try to put together good estimates for your initial size and growth rates so you don't have to worry about frequent grows and the resulting file fragmentation.
Also noticed that the checksum() and binary_checksum() functions still ignore trailing spaces, to my continuing dismay. I'll have to dig up ufn_checksum() and see if it works in 2k5.
Microsoft SQL Server Management Studio was the name of the enterprise manager / query analyzer replacement I couldn't remember earlier. Maybe I'll remember it, now that I have typed it. At any rate, it looks like database diagrams may be gone, which probably isn't that great a loss. On the other hand, there are approximately 1,000 million T-SQL templates from which to choose (up from 3, give or take a few).
20 February 2005
New Feature: Synonym
Finally have a database-wide alias mechanism for sql server objects. Want a long, descriptive object name? Knock yourself out:
CREATE SYNONYM shortName FOR [Really_Long_Descriptive_Table_Name_That_We_Never_Want_To_Type_Again]
GO
-- you can then use the synonym any time you want to query the monstrously named table
SELECT * FROM shortName -- (not that you would likely see much select * in production...)
It also provides a nice layer of abstraction from the physical database layout. If we decide to change the base table we are selecting from so we can use a different table in the same database or, some table in another database or, even, on another server, we just update our synonym (of course, we still need to have the appropriate columns; can't be entirely out to lunch, here).
Looks like they work on quite a handful of objects, too; ranging from tables and views, through various types of functions and procedures, all the way to CLR Assemblies and functions (haven't got to play with the CLR in SQL, yet).
Handy advantage / possible drawback: The create synonym statement will successfully create synonyms for nonexistent objects. This is a handy feature if you want to create the alias before bothering to create the aliased objects (or drop and recreate an aliased object without worrying about maintaining the alias). However, it's a run-time gotcha if you forget to bother creating the aliased objects. Conversely, you can drop the aliased object without cleaning up the synonym, creating another potential run-time gotcha.
CREATE SYNONYM shortName FOR [Really_Long_Descriptive_Table_Name_That_We_Never_Want_To_Type_Again]
GO
-- you can then use the synonym any time you want to query the monstrously named table
SELECT * FROM shortName -- (not that you would likely see much select * in production...)
It also provides a nice layer of abstraction from the physical database layout. If we decide to change the base table we are selecting from so we can use a different table in the same database or, some table in another database or, even, on another server, we just update our synonym (of course, we still need to have the appropriate columns; can't be entirely out to lunch, here).
Looks like they work on quite a handful of objects, too; ranging from tables and views, through various types of functions and procedures, all the way to CLR Assemblies and functions (haven't got to play with the CLR in SQL, yet).
Handy advantage / possible drawback: The create synonym statement will successfully create synonyms for nonexistent objects. This is a handy feature if you want to create the alias before bothering to create the aliased objects (or drop and recreate an aliased object without worrying about maintaining the alias). However, it's a run-time gotcha if you forget to bother creating the aliased objects. Conversely, you can drop the aliased object without cleaning up the synonym, creating another potential run-time gotcha.
19 February 2005
Primer
Just had my first look at sql server 2005 beta 2. Thought I 'd throw this out there so I'd have a convenient place to note some of the differences it has with sql server 2000.
First blush:
Enterprise Manager is totally reworked. It has a new name that has already escaped me. It seems to look a bit more like the object browser in the old query analyzer than the old enterprise manager. It no longer has the mmc icon, either. I mistakenly opened the sql computer manager on my first attempt.
Query Analyzer is gone. Queries are run in the enterprise manager replacement (somewhat frightening). Fortunately, if you have SP 3 installed on your machine with Query Analyzer, you can connect to a sql server 2005 instance (at least you can with beta 2. hope it holds for release).
Some decent T-SQL enhancements. First one to fall out of the head is that TOP now takes parameters (functions, sql statements, variables, etc). i.e., SELECT TOP (5*3) * FROM sys.sysobjects is allowed instead of SELECT TOP 15 * FROM dbo.sysobjects (inane example, I know).
First blush:
Enterprise Manager is totally reworked. It has a new name that has already escaped me. It seems to look a bit more like the object browser in the old query analyzer than the old enterprise manager. It no longer has the mmc icon, either. I mistakenly opened the sql computer manager on my first attempt.
Query Analyzer is gone. Queries are run in the enterprise manager replacement (somewhat frightening). Fortunately, if you have SP 3 installed on your machine with Query Analyzer, you can connect to a sql server 2005 instance (at least you can with beta 2. hope it holds for release).
Some decent T-SQL enhancements. First one to fall out of the head is that TOP now takes parameters (functions, sql statements, variables, etc). i.e., SELECT TOP (5*3) * FROM sys.sysobjects is allowed instead of SELECT TOP 15 * FROM dbo.sysobjects (inane example, I know).
Subscribe to:
Posts (Atom)