Here's a quickie just before we head off to SQL Connections in Orlando (see here for all out pre/post cons and sessions).

On one of the internal MS forums was the question - how can I tell through T-SQL the last time SQL Server restarted (i.e. the current 'uptime')? The answer relies on the fact that all the background tasks that start when SQL Server starts must record a 'login time'.

You can get this from:

SELECT [login_time] FROM sysprocesses WHERE spid = 1;
GO

Or more simply:

SELECT MIN ([login_time]) FROM sysprocesses;
GO

(And this isn't an April Fool...) I'm very pleased to announce that I've been made a SQL Server MVP for 2008. For the eight years or so before leaving the SQL team last August, I was involved a lot with the SQL Server MVPs. It's going to be *really* interesting being on the 'other side of the fence' in the MVP community and be part of the group providing the product feedback instead of the group receiving the product feedback!

As the MVP award is based on community participation, I have to thank all of those who read my blog posts, and those who post questions on the various forums and websites I post on - keep'em coming!

(Redmond, WA: For immediate release worldwide)

Today, in a surprise development that has stunned industry analysts, SQLskills.com announced a new technology for DBAs that will help in the never-ending battle against human-error and unforeseen disasters. The patent-pending Time-Setback technology allows DBAs of SQL Server to literally rewind time and avoid disasters before they happen.

Renowned SQL expert Kimberly Tripp said in an interview earlier today: "This will be a real boost for harried DBAs. All this time I've been going on and on and on about how DBAs should have a comprehensive backup strategy to cope with disasters. Now they can just forget all of that, throw caution to the wind, and rely on a Time-Setback device!"

Asked how the R&D department developed the technology, a spokesman for the company said "We got the idea after reading Harry Potter and The Prisoner of Azkaban, where Hermione is given a Time-Turner device from Professor Dumbledore. We figured there had to be some scientific basis for it, just like all those books that explain how Star Trek and the X-Files are based on real physics too. So, we had a crack at creating it and it worked! I'm not sure the color's quite right though. Maybe we'll change that in V2. Anyway, cool eh?"

A further disclosure, from a major software company, explained that it is in talks with SQLskills.com to purchase almost a thousand of the devices to hand out to developers to "ensure we ship this year and don't have to change the name". The spokesman wouldn't name the product when pressed.

The device will launch on April 1st, 2008, and will be available for immediate delivery. Although the company has only manufactured 4 of the devices, it will use one of them to do just-in-time manufacturing as orders stream in. For further details, please send email to: AprilFools@SQLskills.com

(Redmond, WA: For immediate release worldwide)

Fixed now - thanks so much!

Folks - a couple of people have setup their systems to refresh *all* our category feeds every five minutes - this is putting an undue load on our server (and screws up our server stats). I know I post a lot but not *that* often. I've WHOIS'd the IP addresses - one person is in Brazil and another in Korea - I can't limit how often you do this but I can ban your ISP's IP addresses if you continue to put this load on us - which I don't want to do. Please change your refresh rate to be 60 minutes or just subscribe to a whole blog feed rather than all the category feeds.

Thanks!

Last year I posted on my old blog about the active SQL Server team blogs. I just happened to post on February 14^th and so in every class Kimberly teaches, she makes fun of how romantic I was to post that on Valentine's Day. So what better thing to post this year than an update to that old post!

Again, this is a list of 'active' blogs, not just one-post wonders, or blogs that are inactive but have a ton of fantastic info archived on them. I've grouped them by rough area and updated the list from last year, removing some that have been inactive for many months. I've also added a list of non-SQL team blogs that I follow too. Eventually I'll put this on our blogs page too - http://www.SQLskills.com/blogs.asp.

Enjoy (and Happy Valentine's Day again Kimberly! )

General SQL Server

• Samples team - code samples, for SQL Server 2005 and 2008
• Buck Woody - BOL, Tools
• Customer Advisory team (plus checkout their dedicated site http://sqlcat.com/)
• PSS Escalation Services team - another PSS blog, lots of how-it works info
• PSS Support team
• Euan Garden - SQL, VS, ...
• Dan Jones
• TechNet Magazine SQL Q&A

Compact/Express

• Compact Edition team
• SQL Express team
• Steve Lasker

Data Programmability

• Data Programmability team
• ADO.NET team - replaced the Data Access blog
• SQL Protocols team
• XML team
• Protocols team
• Angel Saenz-Badillos - JDBC / Linq

Storage Engine

• Storage Engine PM team  - my old blog, still going strong thanks to Sunil
• Slava Oks - SQLOS / memory management
• Laurentiu Cristofor - Security
• Raul Garcia - Security
• Security Team

Service Broker

• Roger Wolter - SOA, data management
• Rushi Desai - Service Broker
• Remus Rusanu - Service Broker (inactive now Remus has left - see below for his new blog)

Relational Engine

• Query Processor team (replaces the old Query Optimizer team blog)
• Programmability & API Development team
• CLR Integration team
• Denis Ruckebusch - XML datatype (inactive now he's left MS)
• Lyudmila Fokina - various including index builds (in Russian)
• Michael Rys - XML, XQuery
• Craig Freedman - query processing, execution and plans
• Isaac Kunen - Spatial data

Analysis Services / Data Mining

• Jamie MacLennan 

Reporting Services

• John Gallardo
• Bob Meyers
• Brian Welcker
• Lukasz Pawlowski - RS, Notification Services

Sync Services / Replication

• Synchronizer
• Sync Framework

SSIS / DTS

• SSIS Wiki
• SSIS team

Manageability / Tools

• Manageability team
• Michiel Wories - SMO / WMI samples (inactive)
• Gert Drapers - Data Dude

SQLskills.com Team

• Kimberly Tripp
• Stacia Misner
• Simon Sabin
• Bob Beauchemin
• Conor Cunningham

Select MVPs I Read

• Tony Rogerson
• Kalen Delaney
• Adam Machanic
• Colin Leversuch-Roberts - aka The Grumpy Old DBA
• Remus Rusanu
• Kevin Kline
• Andrew Kelly
• Tibor Karaszi
• Linchi Shea
• Sean McCown

After many reminders (thanks Adam Machanic!) I've added Conor and Simon to the two aggregated feeds over all the SQLskills.com blogs.

There are two feeds:

1. SQL Server 2008 Category Posts
• Provides previous 30 day's posts
• http://pipes.yahoo.com/pipes/pipe.run?Age=30+days+ago&_id=mhCQs_B13BGv3jGgjtzu1g&_render=rss
2. All posts
• Provides previous 20 day's posts
• http://pipes.yahoo.com/pipes/pipe.run?Age=20+days+ago&_id=vv9PBOp13BGAc67kLO2fWQ&_render=rss

The amount of content is really growing as Simon and Conor also seem to not sleep like me

Enjoy!

This has been causing some problems on the various groups and forums over the last few days so I thought I'd repost this from my old Storage Engine blog. The questions have been around attaching 2005 databases to 2000 servers - even databases that are in 80 compat mode - and it doesn't work. Why?

The confusion is between database compatibility level and database version. Here's a quick explanation of the difference.

Database version

The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to. The database version number does not equal the SQL Server version. For example, doing the following:

SELECT @@version;
GO

on one SQL Server instance on my laptop returns:

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Feb 13 2007 23:02:48   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

However, the database version is 611. You can see the database version but if you attach a database from an earlier version of SQL Server, you'll see these numbers in the error log as SQL Server reports what upgrade steps its doing. You can also see by doing the following:

USE master;
GO

SELECT DatabaseProperty ('dbccpagetest', 'version');
GO

Some things to note about database version:

• SQL Server is not up-level compatible. You cannot attach a database that was created on (or has been upgraded to) SQL Server 2005 to any earlier version of SQL Server (also true for trying to attach a 2000 database to 7.0, and so on).
• You cannot attach a database that was created on an earlier version without going through the proper upgrade procedures. Forcibly attaching a database using various hacky methods will result in all kinds of weird errors, and possibly crashes.

Database compatibility level

The database compatibility level determines how certain database behaviors work. For instance, in 90 compatibility, you need to use the OUTER JOIN syntax to do an outer join, whereas in earlier compatibility levels, you can use '*=' and '=*'. Contrary to popular myth, all of the behavioral differences ARE documented - in the Books Online section for sp_dbcmptlevel - the SP used to set the compatibility level.

There are 5 supported compatibility levels support by SQL Server 2005:

60 = SQL Server 6.0

65 = SQL Server 6.5

70 = SQL Server 7.0

80 = SQL Server 2000

90 = SQL Server 2005

You can see the compatibility level of all databases by doing:

SELECT name AS 'DB Name', compatibility_level AS 'Compatibility Level'
FROM master.sys.databases;
GO

Some things to note about compatibility levels:

• A database created on SQL Server 2005 will have a default compatibility level of 90, unless the model database has a different compatibility level, in which case the new database inherits the compatibility level of model.
• New features may work under older compatibility levels but beware of SET options.
• An upgraded database retains its compatibility level. For example, a database that was created on SQL Server 2000, and didn't have its compatibility level altered, will stay in 80 compatibility level when its upgraded to SQL Server 2005.

Summary

This was just a quick - and by no means comprehensive - explanation of the difference between the two terms. Basically, there's no relationship between them.

A bit more traffic on the thread (see previous post here) prompted me to give my thoughts on the many sweeping generalizations that plague the computer industry and make it difficult sometimes to give advice in forums and blogs. I'd like to repost here (with a few tweaks for clarity).

Some examples of questions that breed sweeping generalizations:

• Should you have clustered indexes on all tables? The well-known clustered-index debate as Kimberly likes to call it.
• Should you rebuild or reorganize indexes to remove fragmentation?
• Which high-availabilty solution should you use?

The problem - as with most advice - is that it's extremely hard to make generalizations. This is both because:

1. without lots of evidence many people (quite rightly) don't believe sweeping generalizations as they may have been bitten by one in the past
2. nearly every situation is different so many generalizations are useless

What I'd love to see, (and I tried to do this when at MS, and like to think I do it here or when teaching classes or conferences) is for people to provide the justification for generalizations, plus some idea of the exceptions and the circumstances under which they arise.

As for this case (whether to create multiple files because there are multiple cores/CPUs), I think we've about done this one to death. The sweeping generalizations here are:

1. for non-tempdb you usually don't need multiple files, unless you have a very high-end workload of the specific nature I described in my first post (rare)
2. for tempdb you usually do, as long as your workload merits it on a multi-core/cpu box
3. IO vendors may recommend it for increased IO throughput *on their specific hardware*
4. there exist sweeping generalizations from various sources that dispute all of the above

Unfortunately, you're not going to get a definitive, authoritative answer to a design/strategy question such as this and you'll continue to find contradictions to anything anyone says on the forums, and even MS contradicting itself (sigh).

What I would suggest is the following:
1) go with the majority opinion of responses to questions asked, based on the respondents collective experience with many customers, databases, and workloads
2) do your own testing, on your own hardware, with your own workload and see what works for you (but beware that testing in a vacuum can prove or disprove anything you want - which is why you see so many contradictory statements)

One last thing on MS - it's a very big company, with lots of groups. Anyone can sponsor a whitepaper, write a blog post/MSDN article/technet article and publish it, or reply on a forum as a visible MS person and it has the 'official stamp' of coming from MS. When I was in the product group I was continually dismayed by the misinformation, bad advice, contradictions, and baseless assertions that I saw coming from MS employees who were just trying to be helpful.

Once something's published on the internet, it's *incredibly* hard to undo the damage done. There's a fundamental element of mistrust sometimes on forums and newsgroups which can be wearying when you're trying to help people out. It can be very hard to convince people that someone else's advice isn't the best to follow - I remember several times arguing with people about how CHECKDB works or what a corruption error message means and finally having to resort to 'I wrote that code - I'm afraid you *are* wrong' - which I really hate doing.

Anyway - rant over

It gives me great pleasure to announce two new additions to the SQLskills team - Stacia Misner and Simon Sabin. Stacia's a BI expert who will be working alongside Liz Vitt, and Simon's a developer expert who will be working alongside Bob Beauchemin. Bringing Stacia and Simon on-board really strengthens the capabilities of the SQLskills team as we now have two widely-known and respected Subject Matter Experts in each of the three areas we operate in:

• DBA/ITPro - me and Kimberly
• BI - Liz and Stacia
• Developer - Bob and Simon

Their blogs are at http://www.sqlskills.com/blogs/stacia and http://sqlblogcasts.com/blogs/simons/, respectively (with a SQLskills.com-hosted blog for Simon coming soon) and their bios are below (in their own words).

Welcome!

Stacia Misner

Stacia Misner has over 23 years of experience with improving business practices through technology and has been providing consulting and education services for Microsoft’s business intelligence technologies since 2000. Prior to becoming an independent consultant, she spent nearly six years at Aspirity  (acquired by Hitachi Consulting) as a member of their Microsoft Gold-Certified Business Intelligence practice for which she developed and delivered a variety of BI courses including Microsoft’s SQL Server Accelerator for Business Intelligence, SQL Server 2005 Business Intelligence Ascend, and Business Intelligence Voyage courses.  Stacia has presented at the Professional Association for SQL Server (PASS), Microsoft’s TechEd, and SQL Server Magazine’s SQL Server 2005 roadshows. She has 8 years of experience in business intelligence architecture and implementation, data warehousing, OLAP, ETL, and reporting and analysis, as well as 15 years of experience in technical project management. Stacia is a Microsoft Certified IT Professional – Business Intelligence Developer, and a Microsoft Certified Technology Specialist – SQL Server 2005 Business Intelligence Development.

Stacia Misner is the author of:

• Microsoft SQL Server Express Edition: Start Now! (Microsoft Press, 2007) - a registration benefit when you download SQL Server Express Edition.
• Microsoft SQL Server 2005 Reporting Services Step by Step (Microsoft Press, 2006)
• Microsoft SQL Server 2000 Reporting Services Step by Step (Microsoft Press, 2004)

and co-author of:

• SQL Server 2005 Administrator’s Companion (Microsoft Press, 2006)
• Microsoft SQL Server 2005 Analysis Services Step by Step (Microsoft Press, 2006)
• Business Intelligence: Making Better Decisions Faster (Microsoft Press, 2002)

Simon Sabin

Simon runs his own database consultancy company Onarc Consulting. He specialises in SQL Server focusing on the areas of search, distributed architectures, business intelligence and application development. He has worked with SQL Server since 1998, and has always focused on high-performance, reliable systems.

Simon graduated from Nottingham University in 1996 and joined CMG as a consultant working on many varied systems over 7 years. He has spent the last 2 ½ years working for the largest Internet job board company in the UK developing a passion for search and the discoverability of data.

He was awarded MVP status by Microsoft in 2006, and is a regular speaker at SQL Server events, as well as writing for the SQL Server Central and Simple-Talk.com websites. In 2007 he co-founded SQLBits aimed at delivering SQL Server to the masses with the first free conference held in October 2007 for over 300 professionals.

It's not like me to post on security - that's one of Kimberly's areas - but this is an interesting example of a real-life injection attack

Lots of people have been asking for us to create some aggregate feeds of the various blogs on the SQLskills site - well, now I've done it.

The three new feeds are:

SQLskills BI Team Blog

SQLskills SQL Server 2008 Category Aggregate Feed

SQLskills All Blogs Aggregate Feed

Enjoy!