SQLServerCentral.com

PASS Summit 2009 Final Thoughts…or What I've Learned From The 2009 Summit

Kendal Van Dyke — Sat, 21 Nov 2009 04:15:00 GMT

Last week I wrote a day by day recount of my PASS Summit experience (Monday, Tuesday, Wednesday, Thursday, Friday). Since then I've been tagged by The Midnight DBAs™ to talk about what I learned from the Summit…so I'm going to use the opportunity to share a few thoughts that didn't fit into my previous posts.

What worked

I rode the Light Link Rail from the airport into downtown for a whopping $2.50. That sure beat spending $40 on a cab!
To cut costs I split a room at the Sheraton. That put me right next to the convention center and lowered the amount to a rate acceptable for my company to cover, plus it gave me the chance to build a professional relationship beyond just chatting in the hallway between sessions. Win-win-win.
Twitter was a hot thing at the Summit this year. Because of the relationships that I formed on Twitter in the months leading up to the Summit I showed up feeling like I already knew 75 people there. It's a lot easier going to a party with people you know than going by yourself. Twitter also proved itself useful at the Summit in two ways:

Entertainment: Reading the comments tagged with #sqlpass during the keynotes (e.g. Tuesday when the rack of servers were about to lift off and Thursday when Dell was…well, I have no idea what Dell was doing on Thursday. I don't think they did either).
Value: Figuring out where people were at or what the nighttime entertainment of choice was.

I knew I wanted to meet as many people as I could so I pre-printed a lot of personal contact cards with URLs for Twitter, LinkedIn, and my personal email address. Exchanging information after a greeting was as easy as handing them my card. Surprisingly there were people who came to the Summit without any business cards to hand out.
Flying the redeye home. It gave me Friday to do some sightseeing around Seattle and sleeping made the long flight home easier.

What I'll do differently next time

Bring a travel umbrella. Although I had a waterproof jacket, that only covered the top half. I was fortunate that it didn't rain more.
Arrive a day earlier. I flew on Monday which made for a loooong day after all the opening activities were done. It would have been nice to arrive on Sunday and adjust to the time zone difference.
I'm not 100% sold on staying at the Sheraton. Sure it's nice to be close by, but it wouldn't have been a big deal to walk an extra couple of blocks for $50 less per night.
Bring a real camera. I used the camera on my iPhone and it was OK, but I would have appreciated having zoom and higher quality pictures (especially at the keynotes). Fortunately other people have been kind enough to post their pictures on Flickr.
Get better contact cards. I printed mine at Staples at the last minute. They worked, but they could have looked much better if I had pre-ordered them from moo.com instead of leaving it to the day before I arrived.
Leave the extension cord at home. I brought a 16' cord with me so I didn't have to camp out for a plug along the outside wall at each session. I didn't even use it once. I either ran off battery power or just put my laptop to sleep and listened to the speaker.

Other random thoughts
Initially I complained about the Summit being in Seattle. "It's prohibitive for people on the east coast to come!" I said. However, after going to the Summit I realize that Seattle is a great location for it. Why? Public transportation into downtown and having everything within walking distance. I'd love for the conference to be in my hometown of Orlando, and we certainly have the facilities for it, but if you want to get anywhere here you need to drive. I don't think that having everyone drive away at the end of the day to get to their hotels, go to dinner, etc. facilities networking. Meeting people and getting the chance to build relationships with them is one of the biggest things I got out of the week. I'm not saying there aren't other cities that could host the Summit, just that I can understand why Seattle works well.

So what now?
Now it's back to the real world and time to put all those great things I learned into action! First off, I'm getting the Summit DVDs and sharing them with my coworkers. I got to go to the Summit, now they get to benefit from it. I'll hold lunch and learns every week and let them pick which sessions to watch. Second, seeing the emphasis that Microsoft is placing on BI reinforced to me that I need to learn SSAS and BI, and fast! I've already started identifying ways that my company can benefit from it; now it's time to put my head down and figure this stuff out. Finally, I'm already looking forward to the next Summit in 2010. You can bet that I'm going to do everything I can to make it back next year and make it even better!

Jason Massie, Michelle Ufford, and Aaron Nelson - tag, you're it!

Should DBAs have local administrator rights?

K. Brian Kelley — Fri, 20 Nov 2009 19:26:00 GMT

This was actually spurred by a post from Ted Krueger (@onpnt), which led to a short, but hearty, discussion on Twitter. He was discussing removing access from a local server admin to the SQL Server. My discussion was in regards to the fact that this isn't a successful preventative control. A system administrator who knew what he or she was doing can bypass the lack of access and still get access to SQL Server. I've gone into how previously, so I won't rekindle that debate here. But that does raise another question, and another controversy.

Should DBAs have local administrative rights on their SQL Server? DBAs would argue that it makes their jobs easier. Indeed, it does. But server admins and security folks would argue that it makes their jobs harder. I've been on both sides. I'm back as a senior DBA now, but I spent the previous seven years as an infrastructure and security architect. I've seen some crazy stuff done by folks who have admin rights and who don't understand the implications of what they are doing. Notice I was quick not to limit it to DBAs. Case in point, a particular IT pro (not a system administrator type) created a share on a production system where Everyone had read permissions through the share and at the NTFS level Authenticated Users had read rights as well. Meaning anyone on the domain could access that share. And that share periodically contained sensitive information. Internal audit caught it and flagged it immediately. But you get the idea.

The argument against system administrators having rights inside SQL Server is that they don't necessarily have a full understanding of what they are doing. But couldn't that same argument apply in reverse? Yes, yes, it could. When I start talking about GPOs, about where things are in registry, about what services are critical, about NIC configuration, about shares and NTFS permissions, quite a few DBAs start getting that glassy-eyed stare. The same stare you get when you start talking recovery models, rebuilding indexes, securables, and the like when talking to most system administrators. So if the argument applies in one direction (no to sysadmins because of the lack of knowledge), it must apply in the other (no to DBAs because of the lack of knowledge). Meaning DBAs aren't local administrators on the SQL Server (and incidently, neither is the SQL Server or SQL Agent service, since it's a simple matter to privilege escalate using them if you're a DBA). Yeah, I said it. I know it's not popular. But it's the logical argument carried back in the other direction.

Another point that is made to keep system administrators out is separation of duties. Sysadmins shouldn't be touching the SQL Servers because it's not in their job duties. As a matter of fact, to prevent one person from stealing everything, the duties are split and so are the permissions. Now, realistically this doesn't work, but it's a good argument. And if it's a good argument as applied to system adminstrators, it's a good argument when applied to DBAs. Meaning DBAs have the rights to their SQL Servers, but not to the servers themselves. Again, yeah, I said it. And again, I know it's not popular. But again, it's the logical argument carried back in the other direction.

So does this mean we should just forget the whole thing? Or does it mean we should just lock everyone down, start building trenches, and lobbing mustard gas at each other (though that's banned)? Well, it depends. Yeah, I said that, too. It depends on your organization. It depends on the data. It depends on the job functions. It depends on the other controls in place. There are enough factors that you can't give one of those "best practice" answers and move on. You really have to consider each and every situation independently. Is that a cop out? No, that's reality. I know of cases where controls say production DBAs can access a particular server but development DBAs can't. That's even more stringent. But if you're talking HR data or intellectual property, maybe that makes sense for your organization. But if you're going to consider stripping local admins from getting into SQL Server for security reasons, then I would think that if you're serious about security, and not just about building personal fiefdoms that you've got to look at the other side, too. You might come to the conclusion that DBAs need to main admin rights. And if that's fine with your organization, there's nothing wrong with that. Just as long as the question is considered in the first place.

Fundamentals of Storage Systems – Capturing IO Patterns

Wesley Brown — Fri, 20 Nov 2009 16:13:00 GMT

We often take the advice given to us on forums or in articles at face value. Even though the authors almost always say things like “your mileage may vary” or “may not apply to your situation” people still assume it is the gospel. Sometimes it is lack of experience. Other times it is just lack of knowledge on how to verify these things on your own. In this article I’m going to give you a tool to look at what SQL Server is doing at the disk level and allow you to make better decisions on how to configure your underlying disks.

The Basics

There are several things you need to know about how SQL Server accesses the database files and the implications of that before you can construct a proper testing methodology.

http://technet.microsoft.com/en-us/library/cc966500.aspx covers the basics. There are a few things I will highlight here.

ACID and WAL

ACID (Atomicity, Consistency, Isolation, and Durability) is what makes our database reliable. The ability to recover from a catastrophic failure is key to protecting your data.

WAL (Write-Ahead Logging) is how ACID is achieved. Basically, the log record must be flushed to disk before the data file is modified.

Stable Media

Stable media isn’t just the disk drive. A controller with a battery backed cache is also considered stable. Since SQL Server can request blocks as big as 64KB make sure your controller can handle that block size request in cache. Some older controllers only do a 16KB block or smaller.

FUA (Forced Unit Access)

With the requirement of stable media SQL Server creates and opens all files with a specific set of flags. FILE_FLAG_WRITETHROUGH tells the underlying OS not to use write caching that isn’t considered stable media. So, the local disk cache is normally bypassed. Not all hard drives honor the flag though, Some SATA/IDE drives ignore it. Usually, the drive manufacturer provides a tool to turn off write caching. If you are using desktop drives in a mission critical situation be aware of the potential for data loss. FILE_FLAG_NO_BUFFERING tells the OS not to buffer the file ether. At this point the only cache available will be the battery backed or other durable cached on the controller.

File Access

SQL Server uses asynchronous access for data and log files. This allows IO request to queue up and use the IO system as efficiently as possible. The main difference between the two are SQL Server will try and gather writes to the data file into bigger blocks but the log is always written to sequentially.

All of these rules apply to everything but tempdb. Since tempdb is recreated at restart every time recoverability isn’t an issue.

SQL Server data access patterns

Searching around you will find these generalities about SQL Server’s IO patterns

Log Writes

Sequential 512 bytes to 64KB

Data File Read/Writes

8KB

Read ahead – more important to Enterprise Edition

8KB to 125KB

Bulk Insert

8KB to 128KB

Create Database

512 byte – full initialize on log file only.

Backup Sequential Read/Write

1 MB

Restore Sequential Read/Write

64K

DBCC – CHECKDB

Sequential Read 8K – 64K

DBCC – DBREINDEX

(Read Phase) Sequential Read (see Read Ahead)

DBCC – DBREINDEX

(Write Phase) Sequential Write

Any multiple of 8K up to 128K

DBCC – SHOWCONTIG

Sequential Read 8K – 64K

Now that we have an idea of what SQL Server is suppose to be doing its time to verify our assumptions.

Capturing IO activity

There are a few tools that will allow you to capture the file activity at the system level. Process Monitor is a free tool from Microsoft that I will use to collect some base line information. In it’s standard configuration Process Monitor captures a ton of stuff and uses the page file to spool the info to. So, before we begin we need to change the default configuration.

Capturing IO data using process monitor.

Filter to apply

process is sqlservr.exe
Operation is Read
Operation is Write

Columns to choose.

Process Name
PID
PATH
Detail
Date & Time
Time of Day
Relative Time
Duration
TID
Category

Change Backing File.

The maximum number of events it will capture is 199 million. This is enough on my system to capture 12 hours of activity easily. Once we have a good sample you can save it off as an XML file or CSV. Choosing CSV it is pretty easy to import the data into SQL Server using SSIS or your tool of choice.

I import the CSV into a raw table first.

Raw table to import into.

CREATE TABLE [SQLIO].[dbo].[pm_imp] (
  [Process Name]  VARCHAR(12),
  [PID]           SMALLINT,
  [Path]          VARCHAR(255),
  [Detail]        VARCHAR(255),
  [Date & Time]   DATETIME,
  [Time of Day]   VARCHAR(20),
  [Relative Time] VARCHAR(50),
  [Duration]      REAL,
  [TID]           SMALLINT,
  [Category]      VARCHAR(6))

Next I create a cleaner structure with some additional information separated from the detail provided.

SELECT 
  [Process Name]                                                                AS ProcessName,
  PID                                                                           AS ProcessID,
  PATH                                                                          AS DatabaseFilePath,
  Detail,
  [Date & Time]                                                                 AS EventTimeStamp,
  [Time of Day]                                                                 AS TimeOfDay,
  [Relative Time]                                                               AS RelativeTime,
  [Duration],
  TID                                                                           AS ThreadID,
  Category                                                                      AS IOType,
  substring(detail,charindex('Length: ',detail,0) + 8,
            (charindex(', I/O',detail,0) - charindex('Length: ',detail,0) - 8)) AS IOLength,
  CASE reverse(left(reverse(PATH),3)) 
    WHEN 'mdf'
    THEN 'Data'
    WHEN 'ndf'
    THEN 'Data'
    WHEN 'ldf'
    THEN 'Log'
  END AS FileType
INTO   SQLIOData
FROM   
  dbo.pm_imp
WHERE  reverse(left(reverse(PATH),3)) IN ('mdf','ndf','ldf')

Once we have the data cleaned up a bit we can now start doing some analysis on it.

Queries for interesting patterns.

This query gives us our read and write counts.

SELECT   
  count(* )        IOCount,
  IOType
FROM     
  SQLIOData
GROUP BY IOType
ORDER BY count(* ) DESC

This one shows us the size of the IO and what type of operation it is.

SELECT   
  count(* )        IOCount,
  IOLength,
  IOType
FROM     
  SQLIOData
GROUP BY IOLength,IOType
ORDER BY count(* ) DESC

This is a look at activity by file type data or log.

SELECT   
  count(* )        IOCount,
  FileType
FROM     
  SQLIOData
GROUP BY FileType
ORDER BY count(* ) DESC

Since we are capturing the thread id we can see how many IO’s by thread.

SELECT
count(* )        IOCount,
ThreadID
FROM
SQLIOData
GROUP BY ThreadID
ORDER BY count(* ) DESC

We can also look at IO types, sizes and count by file helping you see which ones are hot.

SELECT   
  count(* )        IOCount,
  databasefilepath,
  iotype,
  iolength
FROM     
  SQLIOData
WHERE    databasefilepath LIKE '%filename%'
GROUP BY databasefilepath,
         iotype,
         iolength
HAVING   count(* ) > 10000
ORDER BY databasefilepath,
         count(* ) DESC

Now that we see exactly what our IO patterns are we can make adjustments to the disk subsystem to help scale it up or tune it for a particular pattern.

This is just another tool in your tool belt. This is a supplement to using fn_virtualfilestats to track file usage. I use it to get a better idea of the size of the IO’s being issued.Using these two tools I can see the size of the IO’s in a window of time that is reported by my fn_virtualfilestats capture routine.

Always verify your assumptions, or advice from others.

I’m Missing Data

Steve Jones — Fri, 20 Nov 2009 15:04:00 GMT

One of the things that I thought about doing after the 2008 PASS Summit was to bring someone along with me as a photographer/videographer that would take lots of pictures and video of the event. I thought about bringing my wife, but that didn’t work out and I dropped the ball on that task.

And I regret it.

Coming back, going through some of the images and video I did get made me realize how much I missed. I rarely stopped to take video, I’d forgotten my tripod support, I got distracted, and ended up not getting much in the way of media from the event.

Pat Wright walked around with his camera and got a lot of great shots, but he wasn’t always near me, and I realized that I missed a lot of shots.

So another goal for 2010. I need to carry a video camera and real camera (DSLR or other fast response camera) to events and make sure that I get some footage. I want to get some responses and impressions from people, more shots of the setup, the signs, and people milling around.

And some “manamana” quotes from people as well. I’d like a little Muppet video to come out of 2010.

How to Create a Reporting Services 2005/2008 Template

Patrick LeBlanc — Fri, 20 Nov 2009 14:50:20 GMT

At most large companies one business requirement is that all reports have the same look and feel. This may vary by department, but there is typically some level of standardization amongst the business entities. In most cases there is a header and footer template that needs to be seen on all reports. Often developers I have seen developers start from scratch or copy and paste and existing report. Those days are gone. For all of you still using Reporting Services 2005, don’t worry this method is available to you also. Here are the steps:

1. Create a template report, maybe and .rdl that contains only the header and footer information. These are items that are typically used throughout a company or department.

2. Copy the .rdl file to one of the following directories

(SSRS 2005) - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

(SSRS 2008) - C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

The next time you create a report, right click on the Reports folder in the Solution Explorer of your Reporting Services Project. Then choose Add –> New Item. A dialogue box will open, similar to the one below:

In the above screen shot, my template is named Dashboard Template. Choose that item and click Add. When the report is added to you project it will look exactly like the template you created. Happy Report Writing!

Talk to you soon

Patrick LeBlanc, founder www.TSQLScripts.com and www.SQLLunch.com

MDX, DMX, and XMLA Templates

Adam Jorgensen — Fri, 20 Nov 2009 14:00:00 GMT

One of the most underused features in SSMS is the additional templates that are available for MDX, DMX, and XMLA. Many of you probably use the T-SQL templates to quickly build common statements, but struggle with the syntx for their counterparts in these variation languages.

Fear Not !

If you will do this with me:

Open SSMS and Go to VIEW > TEMPLATE EXPLORER
Select the Analysis Cube Option (This will load the SSAS related Templates)
Browse through the query options in this window
Now we have some options to use these in a couple of cool ways. First if you go to QUERY > SPECIFY VALUES FOR TEMPLATE PARAMETERS you can supply values for the templates, or you can hand code them in.

Your preference. :) These can really save you some time when working with some common MDX queries like moving average and ratios.

Get 25% Off SQL Server E-Learning from Microsoft

Brad M. McGehee — Fri, 20 Nov 2009 09:42:51 GMT

Not all DBAs have the opportunity to attend formal classroom training or attend conferences. While there are many SQL Server learning opportunities, one option you might want to consider is E-Learning from Microsoft. Official Microsoft E-Learning allows you to take many different SQL Server classes, all from your office or home, using your computer and an Internet connection.

Through June 30, 2010 (or until supplies last), Microsoft is offering a 25% discount on the following SQL Server E-Learning classes:

What’s New in Microsoft SQL Server 2008 Database Development
Writing Queries Using Microsoft SQL Server 2005 Transact-SQL
Querying and Modifying Data in Microsoft SQL Server 2008 with Transact-SQL
Designing Microsoft SQL Server 2005 Databases
Designing the Data Tier for Microsoft SQL Server 2005
Implementing and Maintaining SQL Server 2008

These E-Learning classes, plus many more on other Microsoft products, are available at the discounted price. See this link for more information.

Book Review: The Cleaner

Andy Warren — Fri, 20 Nov 2009 06:39:00 GMT

I read The Deceived a few weeks back and enjoyed it, even though it was the 2nd book in the series, and finally got around to reading first the one. Interesting to learn more about the characters, though even here I feel I’m missing book zero. Hopefully at some point the author will take us back in time and show more of the lessons that were learned early on. This book was fun, though I think I liked the Deceived better – maybe that’s only because I read it first, but it felt like in the second book it’s more focused on ‘cleaning’. The story in this one starts with him being attacked at home and then becomes a strange journey to unravel two interlocking puzzles. Some of it a little over the top, some of it pretty interesting, all pretty good fun.

I’ve got him on my list of authors to watch for more.

SQL Job Ownership Article on SSC.Com

Robert Pearl — Thu, 19 Nov 2009 16:03:00 GMT

Today, Please see my featured article appearing on SQLServerCentral.com discussing SQL Agent Job Ownership.

Have you ever had a situation where a DBA or admin leaves the company, his login is disabled, only to discover that his years of service, and of creating sql jobs to run under his account, are now all suddenly failing? Backups, Maintenance Jobs, Packages, etc. 10, 20, 30, 40, 50 servers +.

What is the DBA left behind to do? That is the topic of this article and what you can do to automate and fix the issue accross your SQL Server infrastructure, and how to prevent it from happening in the future, using the scripts provided.

Please read on in Let's Talk Ownership (SQL Jobs)....
http://www.sqlservercentral.com/articles/SQL+Jobs/68764/

Thanks for reading! Hope this is helpful - RP

Licensing For Everyone

Steve Jones — Thu, 19 Nov 2009 15:03:00 GMT

I used to really try not to over-license in the small companies I worked at. It seems I’d spend a few days every quarter or two counting up usage, asking people what they did, looking at logs, and eventually calculating our needs. Then compare the needs to what we’d purchased and I’d price out software. Typically I came to this conclusion.

License everyone.

It didn’t make sense to try and manage Office applications or SQL Server CALs. It was work, and it distracted me from other things I could be doing to make the company more productive. If we were over-licensed by a few hundred dollars a person, it was a waste of my time. And what I found was that we were rarely overlicensed, and as soon as I didn’t give someone Powerpoint or a SQL Server CAL, they’d request it.

I know that licensing is expensive, and not everyone needs all software, but there is a core set of software, and this likely includes SQL Server CALs, that should just be purchased for everyone and then ignored. It’s too hard to do anything else, and it’s likely a waste of time.

I do think that you ought to try and combine SQL Servers where possible, and share the instance with multiple databases, but I don’t think that it’s the same with clients. Get ‘em and forget ‘em.

Maintenance Plan clean up task - not working to plan

James Howard — Thu, 19 Nov 2009 14:27:00 GMT

A colleague of mine who is getting to grips with SQL Server had an issue after creating and implementing a maintenance plan to manage backup files on his local SQL Server instance.

The problem was with the routine maintenance which should have been removing files with a bak extension that are older than two days. He was trying to emulate a plan from one of our staging servers and despite having entered all the details, albeit manually, the job would execute and apparently succeed but there would be no removal of files. And then this morning his hard drive stopped J

I had a look at the plan that had been created and performed a quick test and the issue was exactly as my colleague had reported it. And then it hit me! The value for File Extension had ‘.bak’, and the task was therefore looking for test..bak as it apparently appends its own '.' to the string.

Removing the extra full-stop (or period) resolved the problem, so the entry simply states 'bak' rather than '.bak'.

Having briefly researched (ok, googled) the issue, I note that others have faced similar problems and most of which were resolved by observing the steps below. Hope it saves you some time…

(As posted on http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic24757.aspx by SQL Server MVP Jonathan Kehayias)

- Make sure that you have a '\' at the end of the path, so if the backups are in D:\SQLBackups use D:\SQLBackups\

- Make sure that the extension is bak and not .bak For whatever reason it doesn't like having the . before the bak.

- If you are deleting trn files and bak files, have two separate jobs one to cleanup the bak files and one to clean up the trn files.

- If you backed up databases to separate folders, checking the 'Include first-level subfolders' check box is necessary.

PDC 2009 Laptop Giveaway

Glenn Berry — Thu, 19 Nov 2009 12:08:40 GMT

During Steven Sinofsky’s keynote address at PDC09 today, he started telling a story about how some of the engineers in the Windows Division partnered with Acer to learn what a hardware manufacturer goes through when they design and configure a laptop computer. This story went on for several minutes, and I was thinking, “Ok, I get it, lets move on”, when he surprised everyone and said that all paid attendees would be getting one of these specially configured units. After a few seconds of disbelief, the hall erupted in a very long round of applause!

The unit is based on the new Acer Aspire 1420P, it is a convertible TabletPC with a 1.2GHz Celeron U2300 45nm dual-core processor, Mobile Intel GS45 video, 2GB of DDR3 1066 MHz memory (supports up to 8GB), an 11.6" 1366x768 multi-touch screen, webcam/microphone,, 3G WWAN, WLAN (supporting up to Draft-N), S/PDIF for digital speakers, 3 USB ports, 250GB HDD, card reader, and even an HDMI port. It weighs 3.8 pounds with a six cell battery.

It came with x64 Windows 7 Ultimate, and a pre-beta version of Office 10, along with Microsoft Touch Pack for Windows 7 and Corel paint it! touch. It has a Windows Experience Index score of 3.2, the lowest score being for “Desktop performance for Windows Aero”.

This machine is just slightly larger than my little Toshiba NB205, but it is significantly faster and more useful. Having a screen resolution of 1366x768 is a big deal compared to the net book’s 1024x600 screen resolution, not to mention the units' tablet and touch screen capabilities. I agree with Joe Wilcox that this was a move designed to counter the ever growing sales volume of net books, which are cannibalizing sales of larger notebooks, which hurts HW manufacturers and Microsoft. It is also a bold move by Acer, which is looking to pass up Dell as the #2 manufacturer of PCs.

So far, I have only installed the November CTP of SQL Server 2008 R2, but it is working great so far. My biggest short-term issue is how to get it back home to Denver. I already had my HP Envy 15 and the little Toshiba NB205, so now I have to worry about getting three laptop computers through airport security…

Anniversary at Database Trends & Applications Magazine

kevin_e_kline@yahoo.com — Thu, 19 Nov 2009 07:31:56 GMT

I was happy to hear that the magazine Database Trends and Applications had recently relaunched their website. While I’d been a columnist there for a quite a while, I’d never been able to easily find my own articles nor check to see if they’d generated any interest. DBTA has changed all of that with their relaunch. I was also surprised, upon closer examination, that I’d been writing there for more than a year. Check to see if any of these articles are of interest to you. I’d love to hear your thoughts.

Security as an Afterthought Issue: November 2009

The Big Keep Getting Bigger Issue: October 2009

Getting Up to Speed on the SQL Server Social Media Scene Issue: September 2009

The Ebb and Flow of SQL Server Instrumentation Issue: August 2009

A Look at Upcoming Enhancements in SQL Server 2008 R2 Issue: July 2009

Is it Time for a Professional Code of Ethics for DBAs? Issue: June 2009

The Trouble with Third-Party Applications Issue: May 2009

SQL Server in the Clouds? Issue: April 2009

The Future of Coding for SQL Server, Part 2 Issue: March 2009

The Future of Coding for SQL Server Issue: February 2009

Reaching for Highly Scalable Systems with SQL Server 2008 Issue: January 2009

Long-Term Changes Resulting from Policy-Based Management Issue: December 2008

The New Master’s Certification from Microsoft Issue: October 2008

What to Expect at the PASS 2008 Summit Issue: September 2008

The November article just went live this week. The site traffic is 79% North America, with India and the UK as the next largets readership markets. It was heartening to see a relatively high level of interest in the “Code of Ethics for DBA’s” among other topics.

If you are perceiving a trend or overarching issue in the SQL Server world, I’d love to hear your input. Drop me a note here or use one of the methods available on http://KevinEKline.com/Contact/ to get in touch with me directly.

Thanks,

-Kev

Professional Extrovert, Private Introvert

Andy Warren — Thu, 19 Nov 2009 06:33:00 GMT

I’ve had this on my list of things to talk about someday, a recent post on almost the same subject by Sacha Chua along with my recent experience at the PASS Summit made me decide now was the time to write it!

Professionally I’m outgoing, most of the time. It’s hard to succeed in business if you’re not able to speak to groups, hold your own in meetings, and project some energy/power. I suspect few working with me or meeting me at a conference would consider me shy (though I’m often told that I appear serious – I am!). Some of that comes easily to me, some requires real effort, one of the reasons I put a lot of effort into my networking skills this year.

For the first few days at an event like PASS I’m out and trying to participate as much as I can, talking to old friends and meeting new people, but usually around the 3rd day (which is often day 1 of the event since I typically arrive early) I’m just tired of being outgoing, ready for some quiet time, where quiet time can be a conversation with people I know or just looking out the window for a while. I suspect I’m not the only one like this, and it’s a useful thing to understand – if you’re talking to someone/trying to meet/etc, and they seem a little withdrawn, it might just be networking overload.

As the title suggests, personally I’m more of an introvert, less likely to push to meet people, less likely to join in events where I’m not as comfortable. There are times when I push to be outgoing, but it’s a struggle against genetics as far as I can tell.

Of course most people aren’t going to know this about me, nor will I know how they interact with people either, beyond whatever signals we send. In practice we all do the best we can and try to meet halfway. About all I can see is that understanding my own behavior has taught me to look a little deeper when I run into someone that seems to be too loud, too happy, too withdrawn. It’s rarely rudeness, more often a default behavior which I don’t understand.

Not sure I explained it well, looking forward to your comments!

Impressions of PDC09 Day One

Glenn Berry — Wed, 18 Nov 2009 19:57:54 GMT

Back before I grew up and became a DBA, I used to be a developer. This seems to be a fairly common career path for many DBAs that I have met. At any rate, I can relate to and understand developers somewhat better because I used to be one, and unlike some DBAs, I don’t get cold chills at the thought of using Visual Studio.

I used to go to the Professional Developer’s Conference (PDC) fairly regularly, but the last time I attended one was PDC05. Things have certainly changed quite a bit since then.

From a logistical standpoint, Microsoft has apparently cut back the budget pretty significantly. There is no breakfast served at the conference, and instead of a nice briefcase or backpack for a conference bag, we got what looks like a reusable shopping bag like you might get at the supermarket. I heard quite a few complaints about both of these items today.

On the positive side, I still get a welcome recharge to my inspiration level after seeing the demonstrations during the keynote addresses. Microsoft tends to put a lot of effort into impressive and entertaining demos at PDC compared to what I have seen at PASS, for example. Maybe this is because developers have a lower tolerance for long PowerPoint decks than DBAs do, or maybe it is because Microsoft is such a developer oriented company. At any rate, one of the benefits of going to the PDC is being reminded how cool it can be to write great software, to create something wonderful out of nothing, using your skill and imagination.

The opening day keynotes were all about cloud computing (which Bob Muglia calls the 5th generation of computing), with Windows Azure, SQL Azure, Windows Server App Fabric, etc. It was amazing how hard Microsoft was pushing this idea today. It might be a good idea to start learning more about both Windows Azure and SQL Azure.

One interesting part of the keynote was the appearance of Vivek Kundra, who is the CIO for the U.S. Federal Government, live via satellite. He talked about the Federal government’s efforts to “democratize” information, and he asked the developers to try to come up with new applications that use cloud computing to have a positive impact on the world. The also introduced the contest that NASA/JPL-Caltech and Microsoft is having called the Pathfinder Innovation Challenge.

SQL Lunch Recordings Now Available

Patrick LeBlanc — Wed, 18 Nov 2009 15:59:59 GMT

After several tries I think we finally have it working. If you were unable to attend the last two SQLLunches, go to www.SQLLunch.com and go to the Archived November meetings. They are both available for your viewing. If you have any questions or comments about the SQL Lunch, please send an email to webmaster@sqllunch.com.

Talk to you soon

Patrick LeBlanc, founder TSQLScripts.com and SQLLunch.com

SQLDownSouth

It's Getting Confusing Talking Platforms

Steve Jones — Wed, 18 Nov 2009 15:48:00 GMT

There was a time that Windows ran on multiple architectures. We had the PowerPC, Alpha, and MIPS CPUs in addition to the Intel x86. That ended with Windows NT as it seemed those other architectures didn't generate a lot of sales and Microsoft discontinued the porting of the OS.

For a long time we had different editions of Windows and SQL Server, and it was a fairly simple matrix to determine what level of CPU and memory was supported in each edition. However that's getting confusing again. It was hard enough in Windows 2000/2003 with SQL Server on x86 and Itanium processors to figure out the maximums, but now that we have both Itanium and x64 platforms in addition to x86, and different limits as Windows 2008 grows beyond the limitations of the 32 bit platform, it's becoming confusing again.

I noticed this last week with a simple question. How many CPUs does W2K3 Datacenter Edition support on the 64 bit platform? There was a good argument over the answer of 64. That's because we have Itanium and x64 limits being different.

You'd think this would be simple to determine, but it's not. And as Microsoft continues to shorten product cycles, we'll likely start to have 3 or 4 versions of Windows and SQL Server in many of our environments, making it more and more confusing for everyone.

Attaching a data file without the log file...

James Howard — Wed, 18 Nov 2009 12:55:00 GMT

If you have a SQL data (mdf) file without the corresponding log (ldf) file, you can still attach the database to an instance. Use the sp_attach_single_file_db procedure as demonstrated below:

--To attach a .mdf file without its log file,

--use the following syntax

EXEC sp_attach_single_file_db @dbname = 'YourDBname',

@physname = N'd:\Database\Data\YourDBname.mdf'

For more details, including a list of arguments, please visit:

http://msdn.microsoft.com/en-us/library/ms174385.aspx

PASS Update #19 (SQL Server Standard)

Andy Warren — Wed, 18 Nov 2009 06:05:00 GMT

In a recent blog post Brent Ozar questioned why PASS decided to relaunch the SQL Server Standard given the failure of other content related projects. A good question, so I’ll devote this update to answering that question as best I can and talking about our plans moving forward.

First though, a little history! The Standard actually got started back in 2003. Brian, Steve, and I were thinking on ideas to add value to SQLServerCentral.com and we had in mind a simple printed newsletter, but then Brian started looking at the prices of full color printing and the next thing we decided to try a magazine. Ambitious even then. One thing let to another and we ended up partnering with PASS, a relationship that continued through 2007 when we decided to refocus – and at that time we gave PASS ownership of the magazine. Steve did most of the work during our magazine years along with a great copy editor, but it was always a struggle to get content, I think we were paying $100 for an article at the time. PASS continued it for a while, but announced at the end of 2008 that it would be discontinued due to the rising cost of production. A bit sad to see it end, was good for PASS to have a journal.

Fast forward just a little to the beginning of my term on the Board and one of the things Wayne asked me to do was to see if I could still do something with the Standard in electronic version, in any format that would work. I thought about it some, discussed with a few volunteers and a few advisors, decided to try it as:

One article per issue with a long term goal of 26 issues per year
Increase the rate for content to something close to market value
Have it staffed by volunteers and augmented with staff/contract help
Provide a slow steady delivery of new content to sqlpass.org

What drove those? The first was sustainability. I knew first hand the kind of workload that managing a magazine generates, and I knew it was going to be very hard to get content on an ongoing basis. I also knew that I couldn’t define my portfolio as ‘just’ the magazine, we had other things to look at and clearly Wayne saw it as broader than that. Next was looking to involve volunteers. Back in the print days there was almost no volunteer involvement, and I think it’s good to involve people in PASS, let them earn some ownership. At the same time, it had to be something that a volunteer or three could manage, and something that looked liked a good challenge. It makes sense to have PASS members working on content and doing tech reviews, but not copy edit or layout, we could hire someone as needed for those skills. And finally, paying an interesting amount of money would make it easier to attract authors, because without content it doesn’t work!

But those were all a means to arrive at something I consider more important; giving our members a place to showcase their skills. Writing isn’t as easy as it looks, and there is a lot of difference in writing a blog or short article than a 4000 word article that will be picked apart by a tech editor and copy editor. It also means putting it in front of some very critical peers, so it takes a certain amount of confidence to give it a try. It’s ambitious, but I hope over time that getting published in the Standard is a nice career milestone, something that looks good on a resume and something seen as worth doing. That’s something a professional association should have and encourage.

Brent asked why this would succeed when other projects have failed. The answer is we don’t know yet. I’ve tried to build something that is as minimalist as possible while leaving plenty of room to grow (we can do more issues, and multiple articles per issue if needed), and I have dreams of adding back editorials, advertising, minutes of meetings, and more – if…we can generate the content on a recurring basis. I’ve tried to stack the deck a little, asking Grant Fritchey to take the lead as editor, because I know Grant is passionate and has a voice in the community, and supplemented that with Brad McGehee, another well known voice and a great person to manage the tech editing process. Maybe a better question is why earlier projects failed, and my answer would be a combination of things – a lack of a true champion, asking for donations of effort when other sites pay for the same effort, and no sense of making a difference.

We’re just getting started and we’ll have lessons to learn I’m sure, but I was thrilled that Tom Larock jumped in to write the first article for the revised format. It’s available for free download now (requires login) at http://www.sqlpass.org/LinkClick.aspx?fileticket=bX8_hcbYJ7U%3d&tabid=236&mid=1275, and I encourage you to share it with others.

So you could say the Standard is a dream that we’re trying to materialize. We can grow the next generation of authors, we can add substantive content to sqlpass.org, give our members one more reason to read the Connector to find out about our latest issue, challenge our volunteers to make a tough project succeed.

What’s interesting about a project like this is we can’t make it succeed. If it was just a matter of hard work, well…then I’d make the bet without hesitation! But to make this work requires something tougher than sweat equity, it’s going to take us making the Standard a desirable goal for every author and aspiring author in the SQL community. An interesting project isn’t it?

And finally, don’t think I’m letting you off the hook here. People often ask me how to contribute to PASS, and here’s an easy opportunity – write something for us. No, we don’t take first time writers, but we’ll coach on you how to get to us, and we’ll do what we can to help you through the process. You can earn $500 for doing the work and you’ll earn it, and you’ll have made a positive impact on PASS. It’s coming up on time to write goals for 2010, so why not add this as a goal?

Record of a PSS Call

Grant Fritchey — Tue, 17 Nov 2009 19:36:34 GMT

Not everyone has the opportunity to call Microsoft Premier Support. For those who have not yet had this experience, I’ll document my most recent, non-critical, call. Critical calls are a different critter entirely.

We were experiencing a very odd error on one server in our system. When a particular procedure was run with a particularly large set of data, it would produce an error, but only when called from the application. The exact same error with exactly the same data called from SSMS did not produce an error. We went through a very extensive set of tests and were unable to fix the problem on the server. After moving the production system that was experiencing the issue to a different server, we decided to contact PSS.

8:48 Am, Tuesday: I made the initial call and went through the process of validating my identity, our company, etc. I had to explain everything that we had done, what versions of the software involved everything was, etc. This took 10 minutes. Since I had called early in the day and this was non-critical call, although labeled as important since we are talking about a production system, I had to wait for the MS guys to come in.

9:47 AM, Tuesday: I received an email from PSS asking me for the SQL Server logs. I zipped up the most recent log with the error and sent it in to the guy. This took me about five minutes to get everything together and zip it up for him.

10:53AM, Tuesday: I received my first phone call on the process. I re-explained the issue to the support person. they suggested that we set up a server-side trace and get the query to error out when run from the app, and to run successfully from Management Studio and see what differences there might be, if any, in the basic trace. It took me about 30 minutes to set up the server-side trace, test it, generate the errors and run the query successfully and then zip it all up with sample code and sample data to ship off to MS.

Does it feel like this is going to take a long time? You’re right. It continues.

12:10PM, Tuesday: I get an email back with some initial thoughts. Unfortunately, it looks like we don’t have complete information. I respond in the email, providing more.

12:26PM, Tuesday: Another email back from MS confused about the trace. They had me use the default trace, which captures RPC:Completed and SQL:BatchStarting and SQL:BatchCompleted. They claimed that the app wasn’t run and must have errored before calling the database. But that’s because the app errored which means no RPC:Completed. We didn’t capture RPC:Starting. I pointed this out in a response. Nothing back yet.

1:32PM, Tuesday: I get my response back, am I sending a new trace? Nuts. I knew when I realized what they were looking for that I should have restarted the trace. There’s an hour shot.

2:35PM, Tuesday: I’ve finished rebuilding our trace, retesting and sending everything off for evaluation, again. It took longer for me this time because I was waiting on a developer who was away from his desk. PSS calls require everyone to be available, all the time. When we do critical calls we just jam everyone into the same room. It makes it easier.

6:39PM, Tuesday: The data wasn’t sufficient. It doesn’t show when the error occurs. PSS sends me a new trace template to try out. Unfortunately, I’m not at work any more and what’s more, the developers who’s support I need are long gone. I can’t do anything with this until morning.

DAY 2

8:08AM, Wednesday: I’m setting up the trace to see what they’re having me gather while I wait for the developers to show up. Odin’s all seeing eye! They’re capturing quite a few events here. Just about every single event in “Errors and Warnings.” OLEDB Errors. Auto Stats, Degree of Parallelism, Showplan All and Showplan XML Statistics Profile from “Performance.” Server Memory Change. PreConnect Completed and Preconnect:Starting from Sessions (I’ve never even seen these before). RPC:Completed, RPC:Starting, SP:Completed, SP:Recompile, SP:Starting, SP:StmtCompleted (I knew that one was coming),SP:StmtStarting from “Stored Procedures.” Finally SQL:BatchCompleted, SQL:BatchStarting, SQL:StmtCompleted, SQL:StmtRecompile, SQLStmtStarting from “TSQL.” A full load. This should be fun.

I’m converting it to a script and adding a filter to only capture data from the database I’m having troubles with. I’m also converting to a script for another reason, which I’ll post a little blog post about seperate from this one.

11:11AM, Wednesday: The joys of developing software. The developer I was working with yesterday wasn’t in, so I got a different developer to help out. He didn’t know how to run the code the way the other guy ran it. So it was run in a different way. I captured everything and shipped it off to Microsoft. While waiting for their response, I read through the data gathered by the trace. No error. In fact, everything ran successfully. We did a bunch of tests and found that as long as we were running the big trace, we didn’t get the error. It was almost like the error was caused by having too many resources.

12:00PM, Wedensday: The PSS person comes back and says that the data contained no error. Yep. They don’t have a single suggestion. All the indications are, the faster, more powerful server, is causing the problem.

DAY 3

I get a response back from PSS. They’re asking if I’m suggesting that I’m supposed to run the trace all the time. This is the point where I decide to bail on PSS. I’m leaving the case in a non-closed state, but I’ve been talking to some other resources and have a troubleshooting scheme from those resources that we’re going to try out.

More when I know more.

Cumulative Update 5 For SQL Server 2008 Service Pack 1

Glenn Berry — Tue, 17 Nov 2009 19:36:04 GMT

Microsoft has released Cumulative Update 5 for SQL Server 2008 Service Pack 1. You can go here to request the hotfix from Microsoft. It typically takes just a few minutes for them to send you the download link and password, then you have to download and unzip with the password. This CU is Build 2746, and there are about 35 fixes, including ones for SSMS, database mirroring, SSIS, etc.

They also released CU8 for SQL Server 2008 RTM, which is Build 1823. If you are still on the RTM branch, you should start thinking about getting on the SP1 branch, since Microsoft is going to stop mainstream support for the RTM branch early next year (if memory serves).

SQL Server 2008 iFTS Lessons Learned

Glenn Berry — Tue, 17 Nov 2009 19:12:36 GMT

We have been using SQL Server 2008 Integrated Full Text Search (iFTS) in Production at NewsGator for a little over a year, with very good results. We were able to replace a 3rd party solution that required a lot of attention, maintenance, and extra hardware, with a very simple iFTS implementation that is much easier to operate, and gives better performance on less hardware.

Of course, nothing is perfect, including iFTS. One thing I have learned is that you need to monitor the pending items count on your full text indexes when you have Auto Change Tracking enabled to make sure that everything is running properly. Otherwise, once in a while, you will find that new items are not being added as you expect.

Periodically, you should check the TableFulltextPendingChanges property to see if you see a very high number.

-- Find out how many changes are pending
SELECT OBJECTPROPERTY(OBJECT_ID('CurrentPostFullTextMonday'), 'TableFulltextPendingChanges') 
AS [Monday Full Text Pending Changes];

If you see a number over a few hundred when you run the query above, you may need to start a manual update of the full text index, like you see in the first command below:

-- Start a Manual Update of the Full Text Index
ALTER FULLTEXT INDEX ONdbo.CurrentPostFullTextMonday START UPDATE POPULATION;

A Manual Update of the index can cause a lot of memory and I/O pressure, as the pending changes are incorporated into the full text index.

-- Resume population in case of an error during manual population
ALTER FULLTEXT INDEX ONdbo.CurrentPostFullTextMonday RESUME POPULATION;

I call this process “giving iFTS a good kick”, and fortunately, I don’t have to do it very often.

Workflow is important

Steve Jones — Tue, 17 Nov 2009 15:55:03 GMT

I have a pretty good administrative system here at SQLServerCentral for managing the site. The upgrades we made in 2007 really simplified some of the back end work, and while there are bugs and issues, it has made it easy for people to submit articles, scripts, and questions online. I typically have a queue of work that I dig through on a regular basis to edit articles, schedule things, etc.

However a number of people continue to send me articles through email. That's OK, and it's actually easier for me to edit things that way than with our online editor.

However that leaves me with two different workflows for articles, and no good way to sync them up. Especially as I don't have meta data that allows me to determine where an article came from. Without that, I can't easily feedback changes to someone. If I enter all articles in the system myself, then I struggle to effectively communicate with authors, who might be expecting emails or system messages on their content.

I'm behind again editing things, mostly from offline submissions. I was well caught up before the PASS Summit with my online submissions, but I'd dropped the ball on email submissions and have a few that are a month old.

So this week is article catch up week, to the detriment of other work.

Except writing, of course. I can't forget about that.

Proof

Grant Fritchey — Tue, 17 Nov 2009 11:20:45 GMT

SQL Rockstar tells me that unless there are photo’s, it didn’t happen. Brad McGehee published some pictures from DevConnections. See, I really do try to get people to buy my book… uh, I mean present technical sessions at conferences.

Note the laptop with the styling stickers from:
SQL PASS
SQL Batman (defunct)
SQL Serverpedia
SQL Server Central
Thrive
SQL Server Bible
SQL AgentMan
SQL Rockstar

One of these things is not like the others…

SERVERPROPERTY Function - How can I find the version,edition and build numbers of SQL Server?

James Howard — Tue, 17 Nov 2009 10:55:00 GMT

I can find out the version, edition, and build of my SQL Server by using the SERVERPROPERTY function in SQL Server (the full list of options for this function exists at the foot of this post).

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

This will return results such as:

10.0.2531.0 SP1 Enterprise Edition

You can then reference against the build list in Steve Jones' post to see details of the build you are running

http://www.sqlservercentral.com/articles/SQL+Server+2008/63491/

The SERVERPROPERTY function is useful and provides the following options:

Property	Values returned
BuildClrVersion	Version of the Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server.
BuildClrVersion	Base data type: nvarchar(128)
Collation	Name of the default collation for the server.
	NULL = Input is not valid, or an error.
	Base data type: nvarchar(128)
CollationID	ID of the SQL Server collation.
CollationID	Base data type: int
ComparisonStyle	Windows comparison style of the collation.
ComparisonStyle	Base data type: int
ComputerNamePhysicalNetBIOS	NetBIOS name of the local computer on which the instance of SQL Server is currently running.
	For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster.
	On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property.
	Note:
	If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.
	NULL = Input is not valid, or an error.
	Base data type: nvarchar(128)
Edition	Installed product edition of the instance of SQL Server. Use the value of this property to determine the features and the limits, such as maximum number of CPUs that are supported by the installed product. 64-bit versions of the Database Engine append (64-bit) to the version.
	Returns:
	'Desktop Engine' (Not available for SQL Server.)
	'Developer Edition'
	'Enterprise Edition'
	'Enterprise Evaluation Edition'
	'Personal Edition'(Not available for SQL Server.)
	'Standard Edition'
	'Express Edition'
	'Express Edition with Advanced Services'
	'Workgroup Edition'
	'Windows Embedded SQL'
	Base data type: nvarchar(128)
EditionID	Is an identification number that represents the installed product edition of the instance of SQL Server. Use the value of this property to determine features and limits, such as maximum number of CPUs that are supported by the installed product.
	-1253826760 = Desktop
	-1592396055 = Express
	-1534726760 = Standard
	1333529388 = Workgroup
	1804890536 = Enterprise
	-323382091 = Personal
	-2117995310 = Developer
	610778273 = Enterprise Evaluation
	1044790755 = Windows Embedded SQL
	4161255391 = Express with Advanced Services
	Base data type: int
EngineEdition	Database Engine edition of the instance of SQL Server installed on the server.
	1 = Personal or Desktop Engine (Not available for SQL Server.)
	2 = Standard (This is returned for Standard and Workgroup.)
	3 = Enterprise (This is returned for Enterprise, Enterprise Evaluation, and Developer.)
	4 = Express (This is returned for Express, Express with Advanced Services, and Windows Embedded SQL.)
	Base data type: int
InstanceName	Name of the instance to which the user is connected.
	Returns NULL if the instance name is the default instance, if the input is not valid, or error.
	Base data type: nvarchar(128)
IsClustered	Server instance is configured in a failover cluster.
	1 = Clustered.
	0 = Not Clustered.
	NULL = Input is not valid, or an error.
	Base data type: int
IsFullTextInstalled	The full-text component is installed with the current instance of SQL Server.
	1 = Full-text is installed.
	0 = Full-text is not installed.
	NULL = Input is not valid, or an error.
	Base data type: int
IsIntegratedSecurityOnly	Server is in integrated security mode.
	1 = Integrated security.
	0 = Not integrated security.
	NULL = Input is not valid, or an error.
	Base data type: int
IsSingleUser	Server is in single-user mode.
	1 = Single user.
	0 = Not single user
	NULL = Input is not valid, or an error.
	Base data type: int
LCID	Windows locale identifier (LCID) of the collation.
LCID	Base data type: int
LicenseType	Mode of this instance of SQL Server.
	PER_SEAT = Per Seat mode
	PER_PROCESSOR = Per-processor mode
	DISABLED = Licensing is disabled.
	Base data type: nvarchar(128)
MachineName	Windows computer name on which the server instance is running.
	For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.
	NULL = Input is not valid, or an error.
	Base data type: nvarchar(128)
NumLicenses	Number of client licenses registered for this instance of SQL Server if in Per Seat mode.
	Number of processors licensed for this instance of SQL Server if in per-processor mode.
	Returns NULL when the server is none of these.
	Base data type: int
ProcessID	Process ID of the SQL Server service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance.
	NULL = Input is not valid or an error.
	Base data type: int
ProductVersion	Version of the instance of SQL Server, in the form of 'major.minor.build'.
ProductVersion	Base data type: nvarchar(128)
ProductLevel	Level of the version of the instance of SQL Server.
	Returns one of the following:
	'RTM' = Original release version
	'SPn' = Service pack version
	'CTP', = Community Technology Preview version
	Base data type: nvarchar(128)
ResourceLastUpdateDateTime	Returns the date and time that the Resource database was last updated.
ResourceLastUpdateDateTime	Base data type: datetime
ResourceVersion	Returns the version Resource database.
ResourceVersion	Base data type: nvarchar(128)
ServerName	Both the Windows server and instance information associated with a specified instance of SQL Server.
	NULL = Input is not valid, or an error.
	Base data type: nvarchar(128)
SqlCharSet	The SQL character set ID from the collation ID.
SqlCharSet	Base data type: tinyint
SqlCharSetName	The SQL character set name from the collation.
SqlCharSetName	Base data type: nvarchar(128)
SqlSortOrder	The SQL sort order ID from the collation
SqlSortOrder	Base data type: tinyint
SqlSortOrderName	The SQL sort order name from the collation.
SqlSortOrderName	Base data type: nvarchar(128)
FilestreamShareName	The name of the share used by FILESTREAM.
FilestreamConfiguredLevel	The configured level of FILESTREAM access. For more information, see filestream access level.
FilestreamEffectiveLevel	The effective level of FILESTREAM access. This value can be different than the FilestreamConfiguredLevel if the level has changed and either an instance restart or a computer restart is pending. For more information, see filestream access level. Bottom of Form