Buck makes a quick mention of the "green" efforts at Microsoft in his blog, and points us to the Environment site that Microsoft has created to show off what they are doing for a cause that is important to all of us.  There are several tidbits in here that can help you do your part, as well.  Please pass the word along.

SQL Server's new logo has been published:

 

Courtesy of Wesley.  And I have taken a screen shot of the new splash screen for Management Studio:

 

As you may know, I have been heavily involved in testing a lot of the new features in SQL Server 2008, and am likely as excited as anybody about its release.  Since I work at a fairly progressive company, I spoke with two of my superiors today - independently - and I was surprised at the results.  My proposal was, for the project that we are currently working on, that we hit the ground running by deploying SQL Server 2008 when we are ready to launch.  Based, of course, on successful testing and adequate performance of RC0 (when we get it) in the meantime, and that the product ships on time.

My immediate superior was all for it.  Having attended one of my presentations on the new features, he knew about some of the benefits we would enjoy pretty much right out of the box -- page/row/backup compression, filtered indexes, date columns, change data capture, the list goes on.  And he is all for deploying the next CTP to our QA environment for serious functionality / load testing and analysis, with the intention of being on the "early adopter" side of the curve when the product ships.

His boss, however, is a lot more cautious.  Not only is he uninterested in deploying SQL Server 2008 right away; he is not even interested in looking at it until SP1 is out, and tested, and has about a month of serious market penetration.  Which, by rough calculations, based on the TPC benchmark publication date and the new servicing model, should be sometime in February or March of next year.

All of this stemming from the long-standing tradition of never installing a dot-oh release of a Microsoft product.  Personally, I found the RTM of SQL Server 2005 a hell of a lot more stable than SP2. (And SP1, IIRC, did little in terms of "fixing" anything except that they finished the database mirroring functionality.)  Service packs in SQL Server 2000 don't exactly give us great confidence, either. YMMV.

What I was hoping for was a balanced response, somewhere in the middle.  Like, okay, we won't deploy the day it is out, but we will perform all the necessary tests, including upgrade scenarios, and consider it within a few months of release.  For me, 9 months is a LONG time to wait (and no, it has nothing to do with having children :-)).  But are you facing similar superstition?  Do you feel that way yourself?  I am curious how others are progressing in the "let's upgrade" battle.  Tell me your stories!  Do you align with my boss' boss?  Or do you have any suggestions for changing his mind?

A frequently asked question that surfaced again today is, "how do I see when my data has been accessed last?"  SQL Server does not track this information for you.  SELECT triggers still do not exist.  Third party tools are expensive and can incur unexpected overhead.  And people continue to be reluctant or unable to constrain table access via stored procedures, which could otherwise perform simple logging.  Even in cases where all table access is via stored procedures, it can be quite cumbersome to modify all the stored procedures to perform logging.

SQL Server 2008 will offer Server Auditing for all actions, and this can be logged to a file, or to the Windows Application or Security Log.  You can do something as narrow as record when a specific login queries AdventureWorks.Person.Address.City, and as wide as recording information about every query against every database on the entire instance.  Here is a quick sample that audits all select queries against Person.Address in the AdventureWorks sample database:

USE master; GO CREATE SERVER AUDIT Test_Server_Audit     TO FILE ( FILEPATH = 'C:\Audits\' ); GO ALTER SERVER AUDIT Test_Server_Audit     WITH (STATE = ON); GO USE AdventureWorks; GO CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit     FOR SERVER AUDIT Test_Server_Audit     ADD (SELECT ON Person.Address BY PUBLIC)     WITH (STATE = ON); GO SELECT *     FROM Person.Address; GO SELECT *     FROM fn_get_audit_file('C:\Audits\*', NULL, NULL); GO USE AdventureWorks; GO ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit     WITH (STATE = OFF); GO DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit; GO USE master; GO ALTER SERVER AUDIT Test_Server_Audit     WITH (STATE = OFF); GO DROP SERVER AUDIT Test_Server_Audit; GO

For those of us who don't want to wait for SQL Server 2008 and cannot use stored procedures to log select activity, there is another answer: the DMV sys.dm_db_index_usage_stats, introduced in SQL Server 2005.  By showing the last read and write to a table, this DMV allows us to answer the questions we couldn't before:

• when was database x accessed last?
• when was table y accessed last?

We can answer the question about access to a database simply by aggregating the data in the DMV to the database level:

USE AdventureWorks; GO SET ANSI_WARNINGS OFF; SET NOCOUNT ON; GO WITH agg AS (     SELECT         last_user_seek,         last_user_scan,         last_user_lookup,         last_user_update     FROM         sys.dm_db_index_usage_stats     WHERE         database_id = DB_ID() ) SELECT     last_read = MAX(last_read),     last_write = MAX(last_write) FROM (     SELECT last_user_seek, NULL FROM agg     UNION ALL     SELECT last_user_scan, NULL FROM agg     UNION ALL     SELECT last_user_lookup, NULL FROM agg     UNION ALL     SELECT NULL, last_user_update FROM agg ) AS x (last_read, last_write);

Switching focus to each table is accomplished by adding the object name to the GROUP BY (and as Jerry pointed out, this will require SP2 to use OBJECT_SCHEMA_NAME(), otherwise you can join against sys.tables and sys.schemas):

USE AdventureWorks; GO SET ANSI_WARNINGS OFF; SET NOCOUNT ON; GO WITH agg AS (     SELECT         [object_id],         last_user_seek,         last_user_scan,         last_user_lookup,         last_user_update     FROM         sys.dm_db_index_usage_stats     WHERE         database_id = DB_ID() ) SELECT     [Schema] = OBJECT_SCHEMA_NAME([object_id]),     [Table_Or_View] = OBJECT_NAME([object_id]),     last_read = MAX(last_read),     last_write = MAX(last_write) FROM (     SELECT [object_id], last_user_seek, NULL FROM agg     UNION ALL     SELECT [object_id], last_user_scan, NULL FROM agg     UNION ALL     SELECT [object_id], last_user_lookup, NULL FROM agg     UNION ALL     SELECT [object_id], NULL, last_user_update FROM agg ) AS x ([object_id], last_read, last_write) GROUP BY     OBJECT_SCHEMA_NAME([object_id]),     OBJECT_NAME([object_id]) ORDER BY 1,2;

One word of note is that sometimes an UPDATE can look like a simultaneous read and write.  For example:

USE AdventureWorks; GO UPDATE Person.Address SET City = City + ''; GO SELECT *     FROM sys.dm_db_index_usage_stats     WHERE database_id = DB_ID()     AND index_id = 1     AND [object_id] = OBJECT_ID('Person.Address'); GO

See that for index_id 1, last_user_scan and last_user_update are identical and fairly recent.

Another note is that unless a view is indexed, you cannot reliably track access to a view -- instead the references to the underlying tables are updated in the DMV.

UPDATE - Mike C# and dave ballantyne brought up a great point that applies to all DMVs: the values do not survive a SQL Server restart, or detach/attach, or even Auto-Close. So, if you restart your server and then want to see when something was last accessed, all objects will either be NULL or very recent. One way to work around this is to create a SQL Server Agent job that polls the DMV periodically, and stores a snapshot of the data. This way you can have a running history of "last access" and maybe roll it up once per day (or whatever granularity is suitable).

Even when SQL Server 2008 is released, auditing of some kind will be required if you want more information, such as a history of who ran which queries.  And if you are looking for more details about information that has been added, updated or deleted, you are going to want to look into the Change Tracking and/or Change Data Capture features.  But in the meantime, this DMV provides a quicker and much lighter-weight approach to at least determining when your data was accessed last.

In a recent blog post, Dancho Danchev mis-labeled a recent IIS vulnerability as a "massive SQL injection attack."

Let's be honest here.  Yes, this alert needs attention.  But this is not a new SQL injection vulnerability.  It is simply an exploit in IIS that lets malicious users access your source code.  If your database is already open to SQL injection attacks by anyone who can access the file system on your web servers, then yes, SQL injection is just waiting for the next vulnerability to your file system.  However, if you protect your database server(s) from SQL injection in the first place, then no IIS vulnerability will magically become known as a SQL injection attack.

Never mind that half the IIS servers in the world probably don't even connect to SQL Server, and of the remainder, not all are vulnerable to SQL injection.  The ones that are vulnerable are that way because the web developers and/or DBAs have been sloppy and allowed for practices that help make SQL injection possible.

Call it what it is; don't sensationalize it.  And instead of trying to create panic, provide a little education!  How do you prevent an IIS vulnerability from becoming a SQL injection attack?  There are plenty of things you can do.  Some of them are pretty obvious, or have been discussed previously, but I'll recap the ones on my list:

• Do not expose your SQL Server to the Internet directly
  While in some cases you can't avoid this (shared database servers at a hosting provider, for example), if your server-side code yields a public address, or enough information that the public address can be easily determined, then you are opening yourself up.  All someone needs is read access to your config file or ASP page in order to obtain credentials to access your SQL Server from anywhere.  Talk to your network administrator about keeping SQL Server behind your firewall.
  
  
• Make your passwords strong
  Ideally, your applications will use Windows authentication, but if you must use mixed authentication modes, then make sure your SQL Authentication passwords are "strong" passwords.  It is very hard to be completely immune to a dictionary attack, but you can make it much more difficult by using a 16-character password with mixed case and alphanumerics, like '$QL$erver_r0ck$!', as opposed to an "easier" password like 'tweetybird.'
  
  
• Follow the principle of least privilege
  Do not use sa as the login in the connection strings for your application.  Use a low-privileged user that can only execute (certain) stored procedures.  There is no reason someone should be able to add a query like "SELECT * FROM sys.objects" to your server-side code, or launch extended procedures like xp_cmdshell, or drop objects, because that user should not have sufficient access to do so... the application user should not be sa or db_owner.  Lock down your applications, and only give them the rights they need.
  
  Similarly, do not use a domain administrator or otherwise privileged user as the service account.  This would mean that anything that runs under the context of SQL Server has free reign over your server or even entire network, using a variety of tools like extended procedures.
  
  
• Always use stored procedures, or at least parameterized statements
  If you build ad hoc SQL in your applications, then you are asking for SQL injection attacks, and I strongly suggest you become familiar with using stored procedures or parameterized queries.  Otherwise, all input becomes suspect, since it is very easy to use comments or semi-colons to change the meaning of queries or to append additional queries to be executed.  With a query that uses strongly typed parameters, however, this technique becomes fruitless.  This does not mean something like:
  
  

  sql = "EXEC dbo.foo @param1 = '" & Request.QueryString("bar") & "'" conn.execute(sql)

  
  This is still vulnerable to SQL injection, because I can now call the page using ?bar=';drop table blat;--
  
  Instead you should use a command object and pass the inputs to parameters.  (This also prevents you from having to escape apostrophes in names like O'Hagan, delimit date literals correctly, etc.)
  

• Use TRY/CATCH to return more generic error messages
  In order to prevent revealing your database structure, do not let errors like foreign key violations or other errors bubble up to the application.  This just gives your potential attacker more information about your database structure than they need to have.  Instead use error handling to say "That user does not exist" instead of the default error message SQL Server provides -- which gives specific table and column information back to the user.  If you are using ASP.Net, then you can make sure that you turn CustomErrorsMode to "On" or "RemoteOnly" and set compilation debug to "false"...
  
  
• Do not store passwords in your Users table
  A lot of web applications store usernames and passwords so that their users can log in to the application.  Instead of storing a password in plain text, which can then be read easily by anyone who manages to gain read access to the Users table, store a hash of the password (using MD5 or something similar).  When the user attempts to login (hopefully via SSL), you use the same technique to hash their entry and compare the hashed values, instead of a clear text comparison.  Even if the user has read access to the stored procedure that implements the hash, all they can do with it is try and try and try... they cannot reverse engineer the data if you use a proper hashing technique.
  

Just wanted to post a brief warning about expecting to be able to roll back a cumulative update or hotfix by reverting to a restore point.  In short: don't do it!  These two features are *NOT* designed to work together.  There are various complications with this and other methods of removing a cumulative update.  Hopefully you won't be in a situation where you need to remove a cumulative update, but if you do, I will go over a few things you should be aware of.

Binaries vs. Uninstall

System restore is not necessarily going to remove binaries from locations on your disk that Windows isn't deemed to "own."  So what you may end up with in some scenarios is a system that has a cumulative update (partially) installed, but since the program is no longer registered in add/remove programs, there is no longer the ability to remove it correctly.  Eventually this may lead to a wipe and reinstall.  I strongly recommend using the CU uninstaller utility in Add/Remove Programs instead of trying to "roll back" like you might do with shareware and less complex, non-service-type applications.

Removing "old" setup files

You may be very anal about cleaning up old files from your system that are no longer needed.  Lets say you install SP2 on SQL Server 2005, then you install CU5.  If you install CU6, you may think it is safe to blow away the CU5 files.  Hold on, tiger!  If you need to uninstall CU6, it is going to hang at some point, and leave you in a bit of a pickle.  What happens is it rolls back to SP2 (or whatever release/SP level you were at before applying any CUs) and then tries to apply CU5.  If it can't find the install files (because you deleted them!) then you will be stuck at SP2 and I am not sure currently if the uninstall will fail and rollback, or if it will succeed and leave you at SP2 instead of CU5.  I have heard that you might get the big hourglass in this scenario, and if so, you may eventually kill it -- potentially leaving your system in a bad state.  So, my recommendation here is, leave those installer files there.  This should be a very last resort for reclaiming disk space, and with a little foresight, you should run the installers from a system other than C:.

Cluster Scenario

Personally, I would highly recommend testing CU functionality on a throw-away cluster (e.g. virtualized) and doing your best to not have to remove a CU once it has been baked into an important cluster.  I have successfully removed a CU from one cluster in the past, but I am sure it is not a very highly tested scenario.  Both Geoff Hiten and I have had cases where attempting to install an SP or CU onto a cluster took on one node but not the other, and this made it impossible to back out the installation *or* to supercede it with the next SP or CU.  Geoff found a work-around after a very lengthy process; I gave up on MS Support after several months, and resigned to rebuilding the cluster from the ground up.

Summary

In a session in Seattle today, we were told that there would be a KB article forthcoming on how to deal with rolling back SP and CU installs.  When I hear about it, I will post something.

While playing with the new Policy-Based Management (PBM) features of SQL Server 2008 the other day, I came across a really annoying syntax implementation that is going to trip up a lot of people unless it is fixed.  We all know how DATEADD works:

SELECT DATEADD(DAY, 1, GETDATE());

Sadly, because an expression for a condition is validated using C# and not T-SQL, the syntax needs to be slightly different:

SELECT DATEADD('DAY', 1, GETDATE());

Note the single quotes around 'DAY'... this syntax, obviously, will not work in T-SQL anytime soon:

Msg 1023, Level 15, State 1, Line 1 Invalid parameter 1 specified for dateadd.

So, imagine I am creating and testing a big WHERE clause in a query window, and once I have it right, I want to copy and paste it into the expression editor for a condition, because I want to use that WHERE clause to enforce or monitor some policy.  Now I need to save two versions of it; the original T-SQL version (should I need to modify it later), and the condition-compatible version -- after meticulously adding single quotes by hand (or writing my own parser that will add them for me).  For example, would you really want to be going through large expressions like this, and "correcting" them?

DATEADD('DAY',1-DATEPART(DW,GETDATE()),DATEDIFF('DAY',0,DATEADD('MINUTE',DATEDIFF('DAY',0,GETDATE()),GETDATE())))

And this, only if I know in advance that the DATEADD syntax needs to be different (it is not the only function affected, by the way -- DATEPART() and DATEDIFF() have the same restrictions).  Note that because I forgot to put single quotes around 'DW', I will get an error message.  The error message that I receive is far less than helpful, and is the same regardless of where or how often I forgot single quotes, or even if I left out one of the parentheses:

Error parsing 'DATEADD('DAY',1-DATEPART(DW,GETDATE()),DATEDIFF('DAY',0,DATEADD('MINUTE',DATEDIFF('DAY',0,GETDATE()),GETDATE())))'. Make sure string constants are enclosed in single quotes and facet properties are prefixed with '@' sign.

It would be great to have a helpful addendum, indicating at least the first syntax error encountered, for example "Incorrect syntax near 'DW'"...

I espoused about much of this on Connect, of course:

PBM : Expressions like DATEADD require inconsistent and unintuitive syntax

If you think consistency is important, please consider voting. Unfortunately, due to time constraints around the looming RTM date, it is unlikely this will be fixed. But one can hope.

There are some other cases in the past where some decision was made on a developer's computer somewhere, and by the time the decision came into question, it was too late to correct.  Well, I have three examples that come to mind immediately, and one of them was, in fact, changed at the last minute.

DATETIME2
This data type covers pretty much everything, doesn't it?  Why not be consistent with the INT or CHAR types (BIGDATETIME or DATETIME(MAX))?  Do you envision the need for, say, EVENBIGGERDATETIME?  Yet the name implies that they are leaving room for a higher-scale or higher-precision date/time data type (DATETIME3?).  Or maybe the name was chosen by an employee that was recruited from Oracle.

TIMESTAMP
This was a very unfortunate naming blunder, since the data type is equivalent to ROWVERSION and has nothing to do with date or time at all.  But the implication given by name alone leads a lot of people to add a TIMESTAMP column to their table, only later to seek help in the forums, asking how to display their TIMESTAMP values as DATETIME, or perform a WHERE clause to filter by DATETIME.  We have asked repeatedly that this alias for ROWVERSION be marked as deprecated, and yet in sys.types in SQL Server 2008, TIMESTAMP appears, but ROWVERSION does not.  :-(

DATE and TIME as CLR types
When SQL Server 2005 was in beta, the SQL team thought it would be great to use DATE and TIME as a way to demonstrate the power of the new CLR types.  Unfortunately, the new types did not play well with the other DATETIME data types, the new SSMS GUI, or built-in functions like DATEADD and DATEDIFF.  Thankfully, a rather large group of us were so vocal in our complaints, that before ship date, they agreed to cut the feature until they could get it right.  There are still a few issues with the types as implemented in SQL Server 2008, but trust me, we are in much, much, much better shape now.