OT: Internet Explorer 8 Multiple Tabs = Multiple Processes

This has nothing to do with SQL Server, but it was something that I noticed today and had to do a bit of research for.  I recently upgraded from IE6 and IE7 on my work machines to IE8 since my Windows 7 Laptop uses IE8 I kind of got used to having some of its features.  By my very nature I tend to over work the common computer by having to many screens open as I jump around from task to task, and problem to problem. At any given point in the day, it is common for my task bar to be scrolling and it is currently set to hold three rows of applications.

I previously made use of FireFox to take advantage of the multiple tabs, but have been using IE8 for the same feature.  (Chrome users, yes I know about it, no I don't care to try it, so please save comments about switching to Chrome :-) )  Today I had once again overworked my desktop to the point that it was having trouble switching screens, and it was especially having trouble switching tabs in IE8.  Curious whether or not a machine inventory (automated asset tracking software) or virus scan had been kicked off mid-day, I opened task manager and found a screen full of iexplore.exe processes. 

Turns out this is by design in IE8:

http://blogs.msdn.com/ie/archive/2008/03/11/ie8-and-loosely-coupled-ie-lcie.aspx

I also happened on this blog:

http://blogs.msdn.com/askie/archive/2009/03/09/opening-a-new-tab-may-launch-a-new-process-with-internet-explorer-8-0.aspx

that shows how you can loosely control the configuration of this.  I am not sure that this feature is actually a benefit to me.  I am certainly using more memory and as shown in my screen shot above CPU from this.  What is really bad is that IE 8 is listed as an Important Update in Windows Update Services.

(CSS) - Updated Support Policy for Virtualization of SQL Server

Microsoft updated the Support Policy for running SQL Server Virtualized today.  The CSS Team Blog has a post that provides a quick overview of what's changed as well as a link to the new policy:

http://blogs.msdn.com/psssql/archive/2009/09/03/another-update-for-the-support-policy-for-virtualization-for-sql-server.aspx

Now I haven't personally looked at the SVVP approved vendor list in a long time but I decided to take a glance today at it, and there are a bunch of new approved platforms:

http://www.windowsservercatalog.com/results.aspx?&bCatID=1521&cpID=0&avc=0&ava=0&avq=0&OR=1&PGS=25

The key topics to make note of in the updated policy which can be found on the following KB Article are listed in the Restrictions and Limitations and include the inclusion of Guest Failover Clustering Support if you are on Windows Server 2008 and meet the requirements of Windows Server 2008 Failover Clustering, and the lack of support for Virtualization Snapshots.

Moving to SQLBlog.com

At the beginning of the week I was contacted by Adam Machanic and asked if I was interested in moving my blog from Blogger to SQLBlog.com which is run by Adam, and Peter Debetta. This was originally something that I thought would be easy to do, but when it came down to it and I had to make a decision it was really hard to do. I've spent the last year blogging on this site, building a reader base, and using the content here to help answer questions on various forums. Currently I get just under 200 hits a day and have over 50 subscribed readers to this blog, so why would I want to move my blog?

Well, the main reason is the extended reach that exists for SQLBlog.com due to the other bloggers who blog there including Kalen Delaney, Kevin Kline, Adam Machanic, Simon Sabin, Peter Debetta, Aaron Bertrand, and numerous others. Now some might call me a sell out, but in reality the whole reason that we blog is to have people read what we write. I've been pretty successful building readership on my own, but by blogging at SQLBlog.com, I can grow my readership even further and faster.

So that being said, if you follow me here, I hope you will continue to follow me on SQLBlog.com. I plan to leave this blog in place since there are thousands of links to its content online and I would hate to orphan those links. You can follow my new posts on the SQLBlog RSS feed for my new blog at:

http://sqlblog.com/blogs/jonathan_kehayias/rss.aspx

The link to the actual blog is:

http://sqlblog.com/blogs/jonathan_kehayias/

Wordle and blog content

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/16/wordle-and-blog-content.aspx

Windows Live Writer 2009 Released

... and yes this blog post was made with the newest non-Beta version of the tool.  I usually just stick to post regarding SQL Server, but this one has nothing to do with SQL Server aside from the fact that I use Windows Live Writer to Blog about SQL.  If you have a blog, are new to blogging, or are planning on blogging in the future, give Live Writer a look test.  It is free, and it connects to most public blogging sites without any real configuration.  All you have to do is install it, point it to your blog, provide your username and password and it is ready to publish anything you type up.

http://download.live.com/writer

Some reasons I use Live Writer:

1. I can start a new blog entry and save it locally when I get ideas that I'd like to blog about but don't have the time to dedicate to writing the entry.
2. It has a Microsoft Word like interface that is familiar.
3. It accepts plugins that allow you to do things like add tag clouds, insert videos, pictures, formatted code (this doesn't work out so hot with blogger.com as I learned a few months back due to CSS issues and overlaps).

You can also manage multiple blogs with a single install of Live Writer.  I think it is one of the better tools I have in my blogging toolbox.

Time to Rant : Applications and Administrator Rights

It is 2009, and I am shocked to find that applications today still show up with a requirement to be a sysadmin on SQL Server, and in the case of one particular application I am helping troubleshoot today, a Local Administrator on the SQL Server machine.  As an application developer myself, I understand the due diligence required to properly design an application to follow proper security best practices, and it isn't hard to do if you do it up front.  Since I am not one to roll over on security I am willing to spend the hours/days to figure out the appropriate rights necessary for an application to work in SQL without being a sysadmin, but I don't know many people who can/will do that much leg work.

So why does the app need to be a sysadmin in the first place?  All to often it is just because that is how it was written, and it makes it easiest.  Often there is absolutely no need to be a sysadmin.  SecurityAdmin and DBCreator roles are initially needed to do the install so that the login can create the database for the app and setup security properly for it.  After this, DBO rights to the database is all that it actually needs, and you can drop the server level roles from the login.

Another place I see that apps need elevated rights is where the application has builtin administrative functions.  One app I have seen has a console that will allow the user to change max server memory for SQL Server from the application.  Thank you no, that is my job, and I don't want/need some application admin playing with my server configuration.  Why do application developers think that this kind of thing is a good idea?  We already have applications to manage SQL Server with.  I think that this is one of the reasons that the quality of applications that are out there has decreased in recent years.  Rather than create a focused application that solves one problem, it seems like application vendors want to do it all, which generally only means one thing, they won't do any of it very good.

So that leaves a question for the community of readers:  What do you do with applications like this?  I know in past discussions on SQL Server Central that some people use virtualized SQL Servers to solve this problem, and isolate the database on its own VM, but is that really a solution?  Now you get server sprawl and added licensing costs because the application, for lack of a better word, sucks?

oPass User Group Presentation

This thread is a place holder for the Presentation Slide Deck and Demo Code from the oPass User Group.  It will be updated with complete information after the Session.

SQL Server 2008 Express SQL Agent won't start

Yep, you read that right.  I did say SQL Server 2008 Express SQL Agent, and so will your services snapin if you install SQL Server 2008 Express, but you won't be able to use it.  Apparently there was some miscommunication/ lack of communication at Microsoft regarding the installation of SQL Agent with SQL Express, and the result is a disable SQL Agent Service that won't start no matter what you try to do.  This is documented in the following connect feedback:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=351806&wa=wsignin1.0

If you attempt to start the service you will receive the following error message:

The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]

and trying to enable the service to start automatically will result in:

The request is not supported. [0x80070032]

This is by design according to the connect response from Microsoft, though it is not the ideal situation and is certain to cause a lot of confusion in the community.  If you want to use the SQL Agent, you need to install one of the pay versions of SQL Server.  You can still however, use sqlcmd.exe, batch files, and the Windows Task Scheduler to replace the SQL Agent free, or for a lot less than the cost of a single license for SQL Standard hope over to the SQL Sentry site, and purchase a copy of SQL Sentry Event Manager.

Now I will be quite honest and say that I haven't yet tried the Event Manager, but SQL Sentry has a great reputation for creating top quality applications and comes highly recommended by colleagues that I trust and respect.

Other Blogs that you should be Watching

I committed to blogging about other SQL blogs in the community after Jason Massie gave me a bump on his blog.  So this week I spent some time reading over some of the blogs in my reader and finding ones that don't have many posts yet, but have demonstrated a sustained interest in blogging somewhat frequently.

TJay Belt's Thoughts - TJay Belt

TJay started blogging consistently before PASS 2008, and has quite an amazing blog roll on his site.  I made a reminder to come back to it and look at what feeds he might have of interest that I don't yet.

StraightPath Solutions - Mike Walsh

I usually don't recommend business based blogs, but Mike Walsh dedicates a good bit of time to helping people on the forums and Newsgroups for free, and he commented on a post here this week so I had to check out his blog the other day anyway.

Facility 9 - Jeremiah Peschka

I know Jeremiah from Twitter, and he is always doing performance tuning it would seem from his Tweets, but his blog covers more than just that. 

If you have a blog you'd like reviewed, send me an email, and I'll add it to my feed reader and see what you are all about.  I think that almost every blog post I see is interesting in one way or another, so even if I don't put you in a post like this, I will be subscribed to your blog.  :-)

PASS Governance Board posts Financial's Information

PASS is a not for profit organization for SQL Server Professionals that is widely known.  For years however, one thing about PASS was that it was not open with a lot of information like its financial statements and minutes from board meetings.  Well that has finally changed, and any member of PASS can login to the PASS site with their username and password and now see this information on the new Governance sub-site:

http://www.sqlpass.org/AboutPASS/Governance.aspx

For me this is a very important thing, especially given the amount of money that is handled by PASS for a given year.  I am not saying anything negative about PASS or implying that anything incorrect has been done by anyone involved with the Board currently or in the past, so please don't try and read into my comments on this post and make them into something they are not.  The level of transparency that the Board is now providing to the community is a good thing, they even posted information going back to 2004 for audited financial's.

This is definitely a step in the right direction, and having the January 2009 meeting minutes available for review is taking it the next step as well.  While I don't run a user group, this kind of information is good for group leaders to be able to see.

Free eBook from RedGate : Dissecting SQL Server Execution Plans (Grant Fritchey)

For the last few months I have been working with Understanding SQL Server Execution Plans at a very basic level to provide information on how to get Execution Plans from SQL, how to read them, how to find the information contained in them, and what to look for as flags of potential areas of concern when performance tuning code.  Last night I was having a chat with Jim McLeod, running some thoughts by him when he mentioned this eBook, so I did a quick search and located a free copy from RedGate which I promptly download from:

http://www.red-gate.com/specials/Grant.htm?utm_content=Grant080623

All I can say is WOW.  This has to be the best reference I have ever seen on Execution Plans in SQL Server.  My hats off to Grant Fritchey for first putting this together, but then for making the concepts simple enough that you really don't have to be a Sr DBA to figure out what is happening.  I'd highly recommend this eBook to anyone who writes or reviews TSQL code.

Error: 5123, Severity: 16, State: 1 when moving TempDB

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/06/error-5123-severity-16-state-1-when-moving-tempdb.aspx

Think you can replace DBCC INPUTBUFFER with the DMV's. Better check again.

I was working on a different blog post this morning when I happened upon this little problem.  I was trying to implement statement level auditing of a database without using a SQL Trace in SQL Server 2005 for SOX Auditing, very similar to what we can do with the Server Audits in SQL Server 2008, when I happened upon a couple of posts on the forums that I had responded to in the past.  The first was a post asking how to do this very thing where I posted to use a SQL Trace, ironic I know, but later a post was made with a link to a "Oriental/Asian" (trying to be politically correct because I can't tell if the characters are Chinese or Japanese, but I suspect Chinese because it is at http://blogs.csdn.net/ which looks more like it should be Chinese) blog that shows how to accomplish the task using DBCC INPUTBUFFER.

That is great, and the code works out perfectly, but I am on SQL 2005, so I should be able to just hit up the DMV's for this information right?  Actually the answer is NO YOU CAN'T.  I should have recalled this from last February when UC from Microsoft replied to the following post:

Alternative to DBCC INPUTBUFFER/sys.dm_exec_sql_text?

I tried to use a number of the DMV's, sys.dm_exec_requests, sys.dm_exec_connections, sys.sysprocesses, and all of them return, the code of the trigger when run inside of the trigger.  I don't get how something this simple was overlooked, especially when the DMV's are supposed to be our replacement for the older code and structures, or are they?  The BOL entry for DBCC INPUTBUFFER doesn't have the standard deprecation announcement that can be found on other items that the DMV's should replace, so it really isn't intended in the current releases at least for the DMV's to be able to replace this functionality.

The good news is that according to UC, Microsoft is aware of this and working to fix it.

Goals for 2009

A lot of people have been blogging lately about their New Years Resolutions and goals for 2009.  I had intended to blog this earlier, but I have been busy working on the Extended Events Manager app that I wrote for the SQL Heroes contest.  I prefer to set goals rather than make resolutions since goals are measurable and resolutions tend to not be accomplished. 

My number one goal for this year is to continue to dedicate a block of time to my family each day, since they are the number one priority in my life, and the older my daughter gets, the faster time seems to go by, especially with a second child due in June.  My second goal is to attend and graduate from the US Army Drill Sergeant Academy to further my military career.

As far as technical and career development my goals would be as follows:

1. Remain consistent in technical blog posts averaging 3 a week for the year.
2. Finish coding on the Extended Events Manager App to make it a one stop shop for creating, altering and consuming Extended Events in SQL Server 2008.
3. Continue to answer posts on the MSDN and SqlServerCentral Forums.
4. Present at least one session at a major event, (ie TechEd, SQLConnections, PASS Summit, etc.)
5. Submit an article for publication at least once a quarter to an online site or magazine publication.
6. Continue to present sessions at SQL Saturday and local Code Camp Events.
7. Complete my MCITP SQL Server 2008 Administrator Certification.
8. Take the Oracle Certified Associate Database Administrator Certification test.

I have a few other goals that I would like to accomplish as well, time permitting, but we'll see how the above eight pan out first.

Another Year Gone - 2008 in Review

It is really amazing to look back on 2008, and see what all I have accomplished both personally as well as professionally.  At the start of 2008, I was completely unknown in the SQL Server Community and online.  I had asked a grand total of 4 questions on the forums and had never once answer someone else's question.  I had never written a blog post, or technical article online.

To recap this year, I have replied over 5000 times to questions on MSDN forums, SQL Server Central Forums, and Microsoft Newsgroups.  I have posted 128 blog posts, written 2 articles for SQL Server Central and 28 articles on the SQL Examples Site on Code Project.  I also did a Live Meeting for the PASS DBA Special Interest Group, and presented at 3 user groups and 4 different conferences this year. 

If you are reading this and you haven't done a presentation, written a blog post, or online article, then hopefully this will motivate you to get your start.  If you think you don't know enough, or that people won't be interested, then try and do the presentation at your local user group first.  You'll be surprised with the outcome and amount of interest that there may be, I can almost guarantee it. 

ISNUMERIC functions differently SQL 2000 to SQL 2005

Did you know that the ISNUMERIC function works differently between SQL Server 2000 and SQL Server 2005?  I didn't realize it until the other day when a post on the forums made me dig into it a little bit.

The difference is documented in the books online for sp_dbcmptlevel.

SQL Server 2000 and SQL Server 2005 Compatibility Level 80
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are significant.

For example, the following SELECT ISNUMERIC('121212,12') query returns 0. This indicates that the string 121212,12 is not numeric.

SQL Server 2005 Compatibility Level 90
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are ignored.

For example, the following SELECT ISNUMERIC('121212,12') query returns 1. This indicate that the string 121212,12 is numeric.

Pretty interesting, only the way to maintain the functionality if you actually expect it to return a zero (0) for commas means that you lose the ability to use things like CROSS APPLY, PIVOT, UNPIVOT, Common Table Expressions (CTE's), SQL CLR, and numerous other features in SQL Server 2005.  The only real way to go around this would be to change code to function differently and test for the commas or do a bulk update of the column to replace commas with a different symbol to force a failure or do an inline REPLACE in the SELECT.

Other Blogs that you should be Watching

Following my blog post from yesterday where I listed the major blogs from the SQL Server Team that you should follow, today, I am going to list some "unknown" blogs that you might consider following.  This post is more of a "pay it forward" type of post since Jason Massie, did a similar post and listed my blog in his list of ten blogs to follow and it tripled traffic to my blog. 

In no particular order:

Aaron Alton is a rising star in the SQL Server Community, and a fellow Moderator in the MSDN SQL Server Forums.  His blog can be found at  http://thehobt.blogspot.com.  Look to find posts similar to mine where the focus is around questions commonly asked on the MSDN forums.

Sankar Reddy is another Moderator from the MSDN SQL Server Forums as well who blogs at http://sankarreddy.spaces.live.com/.  Again his posts should be similar in nature to my own, and follow common problems seen in the community.

Gail Shaw is a fellow MVP, and a consistent top performer on the SQL Server Central forums.  She covered the PASS Summit live from PASS Summit, and is definitely worth following.

Microsoft Security Advisory (961040): Vulnerability in SQL Server Could Allow Remote Code Execution

This has been a hot topic online recently, and Jason Massie brought it up last week, and I have seen it blogged a few different places, but now it has a MSRC Advisory for it, and chances are, that your server is affected and you can't do much about it at the current point in time:

http://www.microsoft.com/technet/security/advisory/961040.mspx

Since these Advisories are constantly updated to reflect the newest information, I am not going to rehash it here, but make sure that you read the Advisory and at least know whether you are affected and what your level of risk is.

Blogs you should be watching

If you are reading this, I hope that you are watching my blog, or that you have subscribed to it in a RSS feed reader.  However, if you haven't or you don't that is no matter, and isn't the real topic of this posting.  The real purpose of this post is to make you aware of a specific set of blogs at Microsoft that you should be following if SQL Server is your primary area of responsibility.

Number one of the list has to be the Microsoft SQL Server Release Services blog.  This is the primary location to find out about new releases happening regarding SQL Server, from major releases, to service packs and cumulative updates, it is announced here.  This is how I stat up to date with when releases occur for SQL Server.

Number two on the list would be the CSS SQL Server Engineers blog for SQL Server.  A few people post here, all from the Customer Service and Support team for SQL Server, but the posts are generally centered around problems that the CSS team is seeing in their day to day work.  You can get a good heads up on solutions to popular problems by simply monitoring this blog.

Number three on the list would be the SQL Server Storage Engine blog.  This is one of my favorite SQL Server Team blogs, mainly because it has to deal with the side of SQL that I deal with primarily.  However, in the grand scheme of things, for most people, it is probably the third most important blog to watch behind the above two.

If you are a developer, and not a DBA, swap the above number three with the following number four which is SQL Programmability & API Development Team Blog.  This blog has pointers about how to use the programmability features of SQL Server, as well as how to performance tune code, and the SQL instance for specific scenarios.

Number five on my list would be the Microsoft SQL ISV Program Management Team blog.  A note here is that if you watch the SQL Programmability & API Development Team Blog as well, you will probably see a bit of overlap since these two teams seem to work hand in hand with each other to solve ISV problems at times.  The ISV team may also solve a problem that the Programmability team finds interesting enough to share with their followers and they will repost it, but there are non-overlapping posts between the two that make both worth following.

The last SQL Team blog to watch is the SQL Server SQLOS team blog.  I place this one last only because it doesn't have the posting frequency that other blogs do. The information on this blog is just as important, it just isn't updated with any regular frequency.  However, if an update is posted, you probably would be good to know about it.

That's all folks.  If you are not following the above blogs, I highly recommend it.

Service Broker External Activator Documentation

In October, the SQL Server 2008 Feature Pack became available and included in it a new External Activator for Service Broker.  Documentation is lack luster in the Books Online, but documentation actually comes with the installation that you would probably never find unless you knew about it.  Thanks to The Bit Bucket (Greg Low) from SQL Down Under who pointed out that documentation ships with the External Activator in the C:\Program Files\Service Broker folder.  You can find a Word document called SSBEA.docx in this path that has the documentation.

To download the Feature Pack see:

Download details: Microsoft SQL Server 2008 Feature Pack, October 2008