Monthly Archives: June 2010

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.

The Price of Convenience is Non-Ownership and Loss of Privacy.

This is as far as I ever plan to stray from writing purely technical posts. It just strikes so close to home. I am completely conflicted on this subject. As a user of all of these things I rail against limits imposed on me by the greedy companies and the governments that serve them. As a business man and developer, I really want to get paid and get upset when people assume that using my work has value to them but not in a way worth paying for. Most of us don’t remember not having a phone. We were connected, to our neighbors, friends and for the right price, the world. Some of us in rural areas had to make compromises and shared our phone line with others, the ever favorite “Party Line”. It was a line but not much of a party. What you could rely on though is at some point you listened in on your neighbor and they in turn listened in on you. We gave up some privacy for the awesome ability to call someone whenever we felt like it. Things have come a long way. Now, you are expected to be accessible via cell phone pretty much 24×7 and with that comes all the wonderful data services so people also expect you to be reachable via email, chat or whatever you like. More and more we have a tether to each over like never before in our history. If I can’t reach out and touch you, something must be wrong! My mom didn’t panic if she called the house and I didn’t answer. I was out side running around and knew to check in every so often.Now, If I get an email, don’t answer it within minutes I also get a text message checking up on me. If that goes unheeded the phone rings, that may escalate to all my friends or co-workers phones ringing assuming one of them is near by me. If that all comes up a draw the worst is assumed. Long gone is the pleasure of just dropping out for a few days, or even hours without someone noticing your absence. We are on the cusp of a permanently connected society, for those lucky enough to afford it in the beginning, and later mandated for those that need watching. The second effect of all this technology is more and more things becoming non-physical in nature. With this loss also goes the traditional methods of controlling them, and by that extension creating scarcity and demand for those items. It also means that the physical thing has less value compared to the idea behind it. From the beginning of software, and digital only creations, had to balance the desire to protect them and extract value from them. This has been at odds with peoples desire to share those items freely like they would anything else.

So we tried to restrict the digital goods. To me Software As A Service has been around since day one, just not your ability to enforce the concept completely. This idea is now being transferred to every thing we “use”. I once owned books, now I have a right under the license to read it but not share it, I can’t resell it or even give it away. Fair use is going to go away, because in a world where anything can be duplicated for free the item has no value, listening to the song has value, reading the book has value so whenever you “loan” that book or song to someone it has inherently lost its value. Mainly because the notion of “loaning” something by duplicating it strips away scarcity all together. Everyone had to put in a key to unlock something that you have already purchased. The physical item has zero value, the use and ideas behind them are the commodity. But, this fell well short when we became “mostly on”. The amount of people now hammering away at the old key system meant it fell in hours or minutes compared to the days or months before.

This gave rise to our favorite worst solution, Digital Rights Management. This misguided concept was an attempt to make the digital artifact, music, books and other entertainment, act just like the old physical things, but with more restrictions of course. Tighter control equals more profit.But, this very quickly showed a flaw, if the company in question went under or simply wished to stop supporting it you lost the use of it. All digital equated to non-ownership.This was just a stop gap until a better solution, total control, came along. Total control isn’t really possible unless everyone is “always on”. Once that is archived you have a since of freedom again, I can loan someone a book or a song control is transferred to them I loose the use of it but retain “ownership” of it.

Or do I?

Ever since we started keying software the concept of licensing use, not actual ownership of a thing became real, even though that license may be a perpetual one ownership is never implied. I don’t own a copy of Windows, I have the right to use it under terms that I have been dictated to by the owner of the work in question. If I violate these one sided terms I loose the privilege, and my money by the way, to use the software. Just as if I had purchased a pirated copy, my money would be forfeit unless I could get it back from the scoundrels that sold the software to me. These concepts aren’t new, again they just weren’t really enforceable at the single person level. The companies and governments would target larger organizations who were making illegal copies for the sole purpose of making money for next to nothing. The closer we get to always on we in essence become the organization of thieves, not selling to other people, just simply denying the owner of the work to make any money off of the item. Always on can now give us the illusion of scarcity and bring back the things we use to do with physical only items. These two things, persistent personal connection and a purely digital object has come together in the perfect storm. The buzz this week has been the news that Amazon has total control of items you purchased via their store and whisper net to transfer to your Kindle. The thing that to me has really struck home all the ideas of license, ownership and your rights to a thing come into clear focus. Amazon shouldn’t have sold you the ability to read a digital representation of an artists ideas, you know a book.Is that a problem? Nope, they simply reach into your Kindle, remove the offending material, refund your money for the loss of the privilege to use this item and poof, things are all better. People weren’t happy with this at all, this wasn’t like they broke the terms of service, or even that Amazon went out of business and the DRM they use is now off line. Amazon simply made some mistakes, they sold you the use of something they had bought the right to use but the person who sold them the rights didn’t have it to sell. Amazon won’t do it again though, honest.

Wow, this isn’t going to get any clearer or easer as time marches on. What will we do when we can manufacture things directly in our home, the physical things we need like chairs, TV’s or computers. If someone screwed up could they reach in and dissolve that item, refunding your money? Another little blurb that has been making the rounds is Starcraft2 not having a “LAN” mode you can only play it through their service called battle.net. It’s just like an MMO without the massively part. But people are up in arms about it. I think it will blow over.

Lastly, the RIAA as stated DRM is not the way. No joke, the enforcers on the music side of things who whole heartedly embraced DRM have now turned their back on it. Oh, they aren’t suing people anymore well, not most people. Why bother with law suits and DRM when you can just reach in and take it back. Eventually, if everything plays out, there simply won’t be a way to get an illegal copy of anything. Always on, also means always accessible. I’m not saying it will happen tomorrow but it will happen. Some people will say that loosing revenue of those who want to enjoy something “off line” will keep this from happening. Hog wash, the gain of revenue by the act of control greatly offsets that loss.

This is the future, and it is now.

As long as a society, we become more and more connected the concept of ownership will slip farther and farther away and be reserved for those things that have true rarity. Just to be a total geek about it, I think Star Trek:TNG has it right. When you can manufacture anything at the push of a button and energy is fundamentally free the need for money really looses its value. The world moves back into a much more pure state of barter for those things that have real value. Do I think this will be the ultimate outcome? Nope, to pull from another great thinker service equals citizenship. At any point in our past when a culture has more consumers than producers and services have as much or more value than goods, it collapses. I don’t think that will happen this time. We have the missing ingredients, the always on connection and the ability to give up privacy, and ultimately, freedom to those that control the use of an item, or enforces the ability to use it. So, until Star Trek zips in and I don’t need money anymore I guess I’ll just have to try and find the balance.

I, for one, welcome our new digital overlords. They tell me we have always been at war with Eastasia.