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.
24 February 2005
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)