Author Archives: Wes Brown

About Wes Brown

Wes Brown is Vice President at Cantata Health and maintains his blog at http://www.sqlserverio.com. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.

What I’ve Read and Recommend to Others – Joe Celko Edition

I’ve known Joe for a number of years and have a lot of respect for his experience and knowledge around relational database design and the SQL language. Joe is a prolific writer and has been writing about technology since I was in grade school with articles going back into the early 80’s. Not to mention his ten years serving on the ANSI board for SQL standards. I would put Joe’s books between the purely academic text on relational and set based theory and the more popular books out today that don’t always cover some of the dryer materials that newcomers to SQL may find to hard to digest at the start of their career. If you have been working with SQL for a while and want to take the next step Joe’s books are generally the way to go. Joe’s style is humorous at times and completely unflinching at others. When it comes to things that he thinks is the right way to model and develop using the SQL language, and that is against what popular or easy methods call for, he makes his feelings known on the subject.

Joe Celko’s SQL for Smarties: Advanced SQL Programming Third Edition (The Morgan Kaufmann Series in Data Management Systems)

Now in its third edition this book covers, in detail and depth, what many other texts leave behind. This isn’t a beginners book. This isn’t a text for the dabbler in SQL.

Joe suggest at least a years worth of experience and I would qualify that as a years worth of solid 40 hour a week kind of experience. If you have mastered third normal form and want to take it to the next level this will be one of the text’s I’ll always point you to.

For those who are squeamish about a little math, get over it. I hear people say relational database work isn’t math and they are flat wrong. Just because you may not understand the math doesn’t mean it doesn’t govern every aspect of your relational world.

If you want to take a real peak behind the curtain of relational theory and cover some advanced data modeling this is as good as it gets.

Joe Celko’s SQL Puzzles and Answers, Second Edition, Second Edition (The Morgan Kaufmann Series in Data Management Systems)

I liked this book because it allows you to see how many different ways there are to solve the same problem using SQL. Some of them are very interesting in the approach. It will show you how to think in other ways through the eyes of others. Any time you can get a look into how others solve issues you only build up your own problem solving skills. Plus, for a SQL geek like me I like taking the Pepsi challenge and see how I stack up.

Joe Celko’s Trees and Hierarchies in SQL for Smarties, (The Morgan Kaufmann Series in Data Management Systems)

This is one of my favorite books that Joe has ever written. It covers one of the more complicated modeling issues you will come across, and come across regularly. It explains clearly trees and hierarchies, how they are the same and how they are different. Now that SQL Server 2008 has a hierarchy function don’t think it does away with this book! Joe covers lots of different methods and what works best in different situations.

Joe Celko’s Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL (The Morgan Kaufmann Series in Data Management Systems)

Joe takes one of the common issues new, and some intermediate, users of SQL have a hard time overcoming, sets. I would say most of us that have a background in SQL probably came from a traditional programming back ground and that means iterative thinking. Loops, lots and lots of loops. Having come from that kind of background I still consider the day I finally got functional over procedural was the day I really started down the road of being a SQL developer.

This book can be a heard read if you are locked into procedural mindset, but if you are trying to break out and really get past the same old mistakes we all make this will help you along your path.

Joe Celko’s SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems)

Here we are again, Joe tackles a subject that gets glossed over in many other texts if it gets touched on at all. One of the keys of this book is helping you develop a consistent and predictable way to get the data in and out of your system. Even though this book is on style part of that is understanding set based architecture. Even if you understand the math behind normalization that doesn’t mean you understand the data that goes into it. Even if you don’t agree with everything in this book it will help you focus on the data, consistency and improve you as a SQL developer.

Joe Celko’s Analytics and OLAP in SQL (The Morgan Kaufmann Series in Data Management Systems)

The concept of this particular work is to help transition the online high transaction, high volume database developer over to the analytical side of data aggregation and warehousing. Some folks think because they understand databases in an OLTP environment they can move easily in to OLAP. I’m here to testify that isn’t as easy as it sounds. Joe covers the concepts and some of the newer SQL syntax available in the ANSI-99 standard. Not all of it is available in SQL Server but it is a solid introduction to data warehousing and how to put your general SQL skills to use in the OLAP world.
This book is based on materials in SQL for Smarties. It expands on some of the topics covered there.

I hope you enjoy these books as much as I have, over the years I have relied on Joe and others like him to build my own knowledge base and skills. I also have tried to share that knowledge like Joe has for so many years to others who want it.

Product Review: SQL Pretty Printer

SQL Pretty Printer for SQL Server Management Studio $39.95 single user $99.95 site license

having coding standards is a must for solid quality code. There are lots of articles on the subject like this one on simple talk. Having standards is one thing, getting everyone on board is another, that’s where code formatters or beautifiers come in. I’ve used code beautifiers for may other languages over the years and have written a couple to help enforce coding standards. One of my pet peeves working with the default tools that ship with SQL Server is the lack of any kind of code formatter. When you work with more than one developer you will get code that is ether hard to read or formatted to their specific taste. That by its self isn’t a huge issue but when you are dealing with hundreds or thousands of stored procedures and other code bits written in T-SQL that you now have to dig into in can be a time sink. This is only compounded in an emergency where you are having to quickly look at a piece of code and figure out what exactly is going wrong. To add insult to injury SQL Server will also help mangle the code for you and good. Heaven forbid you should script out a complicated view and watch about 400 lines of code squeezed into say 10. There are some options out there to help with this, I had one criterion that had to be met though, it must integrate with SQL Server Management Studio. This one requirement stripped down the number of candidates very quickly. If you need to format a smallish piece of code there are several online formatters, the authors of SQL Pretty Printer have one at http://www.dpriver.com/pp/sqlformat.htm.

So, with that requirement I found a couple at the time that met the requirements Red Gate SQL Refactor and you guessed it SQL Pretty Printer. SQL Refactor offers a lot more than code formatting and has one feature I really liked that expands wild cards into column listings, but they want $369.00 for it, per user. I also had a problem that after the trial expired I uninstalled and it left grayed out menu items in SSMS that now just annoy me, and I don’t know what to do other than a reinstall of SSMS to fix it. After looking at SQL Refactor I loaded up SQL Pretty Printer. It doesn’t offer some of SQL Refactor’s options but it does reformat the code, it also will convert your statements to work with C# and VB.Net. It will also grab sql statements out of those two languages and put them back as standard T-SQL After using it for a couple of weeks I really grew to like it, it was quick and easy CTRL-K-CTRL-H and *POOF* code was formatted, well most of the time we will get to that.

It has several customizations that control the formatting its pretty straight forward through the options dialog.

With the preview window its pretty quick to get the formatting the way you like it. The things I like about SQL Pretty Printer: Simple install and direct integration into SSMS/VS. This is just a huge time saver for me, there is a stand alone client that has more features but using SSMS all day not having to flip applications, cut and paste to format is a real win. Fire and forget. Once you have it setup it just blends into the system a keystroke and everything is formatted, or just the text you select. Inexpensive, Since the site licenses is low enough getting it installed on every developers machine isn’t a barrier giving everyone the ability to use the same code formatting standards.

The issues I have:

Formatting fails to render. Sometimes a block of code I have selected doesn’t format, instead it disappears. Yeah, it sounds bad but the CTRL-Z undo works so I haven’t lost anything. I will be submitting a bug report.

Un-parse-able code doesn’t format.If it isn’t a valid SQL statement it won’t attempt to format it. Not a huge deal, but I like to format as I go if possible.

Needs more formatting options. It’s hard to please everyone but you can start with me :). I would like to see more options for code style how things rap and split lines.

It’s simplicity and affordability make it a win in my book. If someone comes up with a better formatter at a cheaper price I’d love to see it.

Wes

Fusion-IO Update

I’ve done all the testing I can do for now. There were some unexpected developments that I will cover in a second article.

Later this week I’ll post all my initial findings with the cards.

The second post will be a follow on and some general observations about the SSD market as it stands right now.

All I can say is I’ve never worked with a hardware vendor as passionate about the products, and more importantly the customer they sell them to, than Fusion-IO period.

My Virtual Lab setup Hyper-V 2008 and Virtual Server 2005 R2

One of the things I’ve maintained over the years is a lab to do everything I need. From testing new software, proof of concept designs, benchmarking or testing new builds of SQL Server.
This has always been a tall order and now that I’m back developing software part-time I need a solid, yet cheap, lab to do mostly functional resting in.
To give you an idea of how tall an order testing software to run on SQL Server has come let’s take a small trip down memory lane.

Once upon a time…… I was the product manager for Litespeed at Imceda, life was simpler then. We had a small support matrix.

SQL Server 7
SQL Server 2000 32bit Itanium

Windows NT 4
Windows 2000 Itanium
Windows 2003 32bit, Itanium, 64

Clustering on all platforms except Itanium

Multiply that by the growing feature set and it added up to hundreds of tests that needed to be executed for any built that would see the light of day.So, we setup several machines some virtual to handle the functional testing, some physical to do performance testing. We had automated test harnesses to do some of it but there was still quite a bit of manual work to be done.

Fast forward to today and this is what my test matrix looks like:

SQL Server 2000 32bit Itanium
SQL Server 2005 32bit 64bit Itanium
SQL Server 2008 32bit 64bit Itanium
SQL Server 2008 R2 32bit 64bit Itanium

Windows 2000 32bit Itanium
Windows 2003 32bit 64bit Itanium
Windows 2003 R2 32bit 64bit Itanium
Windows 2008 32bit 64bit Itanium
Windows 2008 R2 32bit 64bit Itanium

Clustering on all platforms except Itanium

Yikes! This is getting huge. This is starting to feel like testing for Oracle and Unix platforms with the only exception you can’t pick and choose what to test. If it is under support by Microsoft you are pretty much stuck supporting it. To my great fortune computers and virtualization has come along way. Almost all desktop processors have multiple cores and support hardware acceleration ether Intel’s VT-x or AMDV. Memory sizes has grown from 2GB to 8GB or better. Hard disks have gotten ALOT larger to hold all the images needed. Oddly, network speeds have stayed at 1Gb/sec but at least it has gotten cheaper. With all that in mind I should be able to build a test environment to handle quite a bit on a shoestring budget.

Lets take a look at the hardware mix I’ve got.

I started building out machines peace meal like all good home grown labs go. My first cut were Intel Q6600 based machines. Hard to beat them for the quad core goodness and all around performance. All four of these machines have multiple terabytes of hard disk space on hardware raid controllers and 8GB of ram. My second round was also Intel but were smaller using E6750 dual core processors as the base. Fewer hard disks but still 8GB of ram on these two. My third pass I switched gears and went with AMD 810 AM3 based machines. These two also have 8GB of ram and enough space to handle whatever I need them too. Last in this stack is an older AMD 5600+ is used for overflow and testing Windows 2000 on Virtual Server 2005. It has 4GB of ram, still has AMDV and two cores enough to get some work done!

Round them out are a few odd balls and support machines.

P4 3.6Ghz is used as a client load generator.
E6750 with 4GB of ram is used also as a client load generator.
AMD 4800+ acts as my controlling server managing all the images and deployments as well as acting as a Virtual Server 2005 for Windows 2000 as well. 4GB and two cores means its up to the task of functional testing when needed.

You will notice I don’t have any Itanium in the mix. I haven’t had a need for it yet as 100% of all our customers and potential customers aren’t running it. eBay is a quick solution for that though.

That’s the basic hardware setup now moving on to the software stack.

As a startup business I’ve joined the Bizspark and Empower for ISV’s. If you are staring a software or software as service business focused on Microsoft technologies and aren’t using these great programs you are putting yourself at a serious disadvantage. That made the choice of VMware or Hyper-V a no brainer for me. But, just to be fair there were other reasons than cost involved in that decision. I can hear some of you now telling me that VMware ESXi 3.5 is F R E E!!!!! And you would be right. The problem is the hardware it will, or more importantly, won’t run on. Remember this thing is hobbled together and will have a wide mix of hardware that needs to be supported. They also offer VMware for windows (GSX) for free as well. I agree that in general it is superior to Virtual Server 2005 another piece of software comes into play Microsoft Virtual Systems Center. It does manage ESX but I couldn’t figure out if it supported GSX and it does support VS2005 and Hyper-V out of the gate (surprise, surprise) With the hardware stack and now the software stack in place we have to work to get this hodgepodge group of servers installed and configured. As I mentioned before, Hyper-V was pretty much the only choice if I was going to manage it centrally and support my hardware mix. That doesn’t mean it was all plug-n-play so to speak.

Some pros and cons of Hyper-V Server 2008

Pros:

Its Free.
It supports VT-x and AMDV.
It supports 32bit and 64bit clients.
You can load drivers for pretty much any hardware Windows Server 2008 x64 supports.

Cons:

It requires x64 support. (Some people really didn’t know)
It requires the disable execution bit to be turned on in bios(not all motherboard manufacturers have the function exposed)
You must have a processor with VT-x or AMDV. If you don’t the OS still installs but the hyper-v services will simply not start.
It is a core installation so no GUI to speak of, if you are a hard core Windows person and has to have the GUI tools you need to load the regular version of Windows 2008 and install the Hyper-V role.
Older clients can run really, really slow. Windows 2000 and Windows XP are prime examples and aren’t listed on the compatibility list there for the need for Virtual Server 2005 R2.

Things to know about Virtual Server 2005 R2. It is also free. Host OS is 32bit support only Windows 2000 or 2003 (no R2). Client wise only up to Windows 2003 32bit is supported but it does run them well enough for what we need.

Installing Hyper-V Sever 2008

The install is pretty simple, it is a Windows 2008 install. If you need to load drivers so the OS can see the disks you can do it with ease even from USB. After the install configuring everything can get tricky simply because you don’t have the GUI to do all the stuff you normally would do post install.

Installing Drivers

If you aren’t prompted to load your drivers by the hardware detection wizard, aka the hardware was already in the machine, there are some simple and not so simple steps that you can take.

The simplest way:

Locate the drivers you need for ether Windows Server 2008 x64 or Vista x64.
Run the installer from the command prompt.
I found that the Intel INF updater runs out of the box just fine for example.

Slightly harder way:

If the installer won’t run you will need to locate the driver files sys and inf and use the command line pnputil tool to install them.
pnputil –i –a <path to the inf and driver>
Some drivers are rapped in cab files and you may need to extract them before putting them on your server.
run driverquery and locate the driver you just installed, may not be so easy if there is a ton of drivers on the machine.
reboot.
check to see if the device is now working (nic card, sata/sas controller etc)
If the Hyper-V server is on the network you can use the compmgmt.msc to remotely view services and stuff on the server if the firewall is setup to allow it too.

The HARDEST way:

I found this method on the internet and haven’t verified it but I thought it really showed what some people can do when they think outside the box.

Here’s how I did it, the hard way. But maybe it will help someone else.
1. I built a Lab server, with Windows Deployment Services.
2. Installed Microsoft Deployment Toolkit 2008,
http://www.microsoft.com/downloads/details.aspx?familyid=3bd8561f-77ac-4400-a0c1-fe871c461a89&displaylang=en&tm
2. Installed the latest AIK Kit: Windows Automated Installation Kit (Windows AIK)
http://www.microsoft.com/downloads/details.aspx?familyid=94BB6E34-D890-4932-81A5-5B50C657DE08&displaylang=en
3. Loaded the Hyper-V 2008 Server ISO.
4. Installed all Intel Drivers I will need for old and current builds.
5. Used the Guidelines in Microsoft® Deployment Toolkit 2008
Getting Started Guide, from the Microsoft Deployment Toolkit.
6. Built x64bit Image with LAN Drivers installed.
7. Built a Bootable USB.
8. Booted Server with USB, Did a network install from Windows Deployment Services.
9 Rebooted Server and was able to use NIC Card.

Some follow on links for driver management.
Server Core and Drivers
Technet forums: Core NIC Driver installation
Managing Services and Hardware in Server Core

Specific uncommon things to my setup

The next thing on my list is at least one of my installs isn’t joined to the domain. Why would you do that you may ask? Easy, this is the machine that actually hosts the domain controller. I will warn you this isn’t a supported Microsoft configuration. they always recommend having at least one physical domain controller. But since this is a test lab I didn’t want to dedicate a machine to just a DC role. Use a fixed IP, no DHCP if the DC hosts the DHCP you will be stuck with a “chicken and the egg” problem. I personally disable the firewall all together instead of just managing openings since all of these already sit behind a hardware firewall. Issuing this command accomplishes that: netsh advfirewall set allprofiles state off

If the server isn’t a part of the domain managing it requires a few tweaks. John Howard has resolved pretty much all of them in this blog post.
http://blogs.technet.com/jhoward/archive/2008/03/28/part-1-hyper-v-remote-management-you-do-not-have-the-requested-permission-to-complete-this-task-contact-the-administrator-of-the-authorization-policy-for-the-computer-computername.aspx
http://code.msdn.microsoft.com/HVRemote

I also ran into an issue where my Windows 7 RC build couldn’t access the hyper-v server and this post fixed that.
http://blog.mpecsinc.ca/2009/06/hyper-v-error-access-denied-unable-to.html

Lastly, Sense I am running VS2005 R2 and Hyper-v I have a need to transfer images back and forth these three posts cover that topic as well.
http://blogs.msdn.com/virtual_pc_guy/archive/2009/02/03/virtual-server-to-hyper-v-migration-guide.aspx
http://www.groovypost.com/howto/microsoft/windows-server/migrate-microsoft-virtual-server-2005-r2-vm-to-windows-server-2008-hyper-v/

hyper-v to virtual server
http://cosier.wordpress.com/2009/04/17/migrating-from-hyper-v-to-virtual-server/

As a side effect of setting up this lab and working with SQL Server 2008 and Exchange 2007 SP1 I’m learning how to use Powershell.
As things settle in I’ll be talking more about actually managing the VM’s with System Center and Powershell.

Oh, just a quick shot of the magic closet. Yes, in the lower right hand corner is an AC unit. No, you don’t want to know what my electricity bill is.

Cannot generate SSPI context…

Out of all the problems you can have with SQL Server troubleshooting connectivity issues can be the most challenging. When you factor in the complexities of Active Directory and SQL Server’s interaction with it fixing SSPI errors can be down right baffling.

At my company we are moving onto new hardware and along the way standardizing on SQL Server 2005 x64.Since this is all happening on new hardware I have the luxury of doing most of the work before we flip the switch. We had one migration under our belt and the second one was looking good when the SSPI came and decided to make sure I spent all my Saturday working.

I ran down my list of things to check:

Date out of sync with domain more than 30 minutes – Nope.

Bad DNS entry – Nope.

Miss configured service account – Nope.

Log on locally with Windows account – Yep.

Now I was stating to get frustrated and kind of worried that a roll back may be called for. I did what all good panicked DBA’s do, I searched the Internet. Most of it I had already tried and some of it just didn’t apply. It’s not often a solid web search is such a complete strike out. When all else fails, step back look at the problem as a whole and start from the beginning.

What do we know?

We can’t establish a trusted login between SQL Server and the domain.

The OS and domain are just fine. You can log in locally with a domain account and you can remotely access other server resources with a domain account.

SQL Server services start up just fine under a domain account.

You can log in locally to SQL Server but not remotely.

SQL authentication works just fine.

That pretty much leaves a configuration issue somewhere. Since we had set this server up it initially had a different name and IP address that would be changed to the old server name and IP address. There are alot of known problems with other parts of SQL Server like reporting services when you do this kind of rename but generally SQL Server is just fine.

You drop the old SQL Server name

sp_dropserver old_name sp_addserver new_name, local

OR

sp_dropserver [old_nameinstancename] sp_addserver [new_nameinstancename], local

Verify everything with these two queries

SELECT @@Servername SELECT * FROM [servername].msdb.dbo.sysjobs

If you have a problem with the @@Servername you get back nothing or the wrong server name. The four part select should return data if not you usually get this linked server is not configured for blah blah blah. Which means you may have forgotten the ,local part. After checking all of these things off again, we still had the issue! Now I really was stumped. I didn’t have a lot of other choices and time was running out. I just started going through EVERYTHING that was network related in the SQL Server configuration. Eventually, I saw the issue. The server IP was correct but the IP in the SQL Server Network Configuration was wrong! I’ve done a ton of renames like this and hadn’t encountered this particular setting not changing when the server IP changed. I reset it to the new server IP and just like magic SQL Server could authenticate to the domain. There was much cheering from my peers, ok not really but I was cheering. The server was up and everyone was able to get back to doing other things as the database faded into the background once again. I just keep telling myself that SQL Server one of the easiest RDMS’s on the market.