SQL Server 2008: Self-Destructing Databases

Using SQL Server 2008?  There’s a little something you should know about to avoid losing data.  Earlier, I wrote:

Certain previously-innocent actions create new (often empty) database files, without warning, and (by default) in the Program Files folder.  If you lose these files, your database goes straight to the bit bucket.  So, if you attach your 15GB database by copying the .MDF and .LDF files, enable full-text indexing, detach your database, and take your 15GB home with you, you’re now the proud owner of 15 GB of uselessness.  Your database is gone forever because you didn’t also grab that 2 KB file SQL Server generated automatically and placed in your Program Files folder.  Whoops.

Well, here’s a bit more detail.

When working with databases in SQL Server 2008, it’s very easy to unknowingly create additional database files.  A great example of this is working with full-text indexing.  In SQL Server 2005, full-text indexes were saved outside of the standard database files, in a separate directory.  Losing this directory wasn’t too big a deal, because re-indexing a database is usually a trivial process.  Even if the full-text files were lost, it was easy to restore or attach the database itself and then recreate the full-text index.

In SQL Server 2008, full-text indexes are now stored in the database itself.  This is a great improvement for managing databases; it means the full-text index is part of the actual database itself, and is included in backups.  Wonderful!  The only problem is that the index is actually placed in a separate data file.  SQL Server experts fully understand that one SQL Server database can contain several database files; there are lots of great reasons for doing this.  In fact, putting the full-text data in a separate file is probably a good architectural decision.  But people working with more simple SQL Server deployments might not realize this.  To these people, a SQL Server database consists of an .MDF file (the data), an .LDF file (the log), and perhaps an extra folder of full-text data.  The only file that was really needed to save or copy the database was the .MDF file; the log and full-text index could easily be recreated.  Now, though, the database file containing the full-text index will be in a separate .NDF file, placed by default in the Program Files folder, and the primary .MDF file is entirely useless without this new .NDF file.

So, what’s the point of all this?  Know the following:

• Before copying a SQL Server 2008 database’s files, make sure you know exactly which files are used to store the database, and make sure you copy all of them.
• Making an SQL Server backup, copying the backup file, and restoring from this backup is a much safer way of saving and moving databases.
• Now, more than ever, it’s important to specify locations for all database files.  Program Files is not a good place to be saving data.

Overall, this is a good change, but you really have to know what’s going on.  There’s nothing complicated here, but if you’re not aware of these changes, they can easily catch you by surprise.

Internet Explorer: Why It Crashes And How To Fix It

Ah, Internet Explorer… the browser everyone loves to hate but can’t live without.

Why the hatred?  Well, it’s by Microsoft.  That alone is enough for some people.  It’s slow.  Not that it’s really that bad, it’s just that pretty much every other browser out there is faster.  It’s a pain in the ass to develop for: IE6, IE7, IE8, Quirks Mode, IE7 Standards Mode, IE8 Standards Mode, Compatibility View… I’m a developer, and I can’t even keep track of all the different modes IE renders in.  And of course, none of them are exactly right.

But it does have a few things going for it.  Amazingly, it’s probably the most secure browser out there right now – not because it was developed particularly well, but because after years upon years of bugs and security flaws, most problems have been found and fixed.  It has all the features a reasonable person would use, and (starting with IE8), has a great set of developer tools as well, nicely tucked away until you need them.  Oh… and it’s installed on every damn Windows PC on the planet.  That also helps.

So if it’s so well-tested and secure, why the inevitable crashes?  Simple: third party code.  Say what you will about Microsoft, but Microsoft code is a hell of a lot better than random third party code.  Microsoft has a vested interest in making their code rock-solid: they want it to work.  When it doesn’t, people get pissed off and go buy Macs.  Third party developers don’t have quite the same incentive: when third party code crashes, people usually still blame Microsoft, especially when it’s not obvious that third party code is running.

Want proof?  Read this entry on the IE team’s blog.  The key piece of information is that pretty graph.  The blue bars represent crashes caused by third party code – ActiveX controls, toolbars, and other plug-ins.  The red bars represent crashes IE is responsible for.  A mere 17 bugs are responsible for half of all IE crashes.  The top four, and 12 of the top 17, are caused by third party code.  Microsoft itself is only responsible for about 20% of these crashes.

While Microsoft can’t say this outright, there are plenty of clues pointing to the biggest culprits here: Adobe Flash and Adobe Reader.  These are two of the most common plug-ins out there, and they’re horribly written: just have a look at how much memory and CPU these programs use to perform relatively simple operations.  Flash, particularly, is absolutely fucking ridiculous for this.  There’s also an endless stream of security vulnerabilities caused by these plug-ins.  As I write this, there’s yet another flaw in Adobe Reader that allows a your system to become infected with a virus simply by opening a PDF file (or clicking on a PDF link) – and yes, viruses exploiting this are spreading like Australian wildfire.  Adobe won’t have a patch out for weeks at the earliest, despite this guy publishing his own patch without even having access to the source code.

So, next time IE crashes, don’t blame Microsoft.  Blame Adobe.  Want to fix it?  Here are some good starting points:

• Use Foxit Reader instead of Adobe Reader.  It’s faster, it’s smaller, it’s probably more secure (or at least less targeted), and it doesn’t crash IE. Kill Adobe Reader.
• Avoid Flash if at all possible.  Or, keep IE ‘clean’ and use Firefox for websites that require Flash.
• Keep your computer up to date.  Seems obvious, but so many people don’t do this, even those who know they should.  Run IE7 and check Windows Update.  When IE8 is out, get it.  Run the latest service pack for your OS.
• Uninstall or disable as many plug-ins as possible – preferably all of them.  Under later versions of IE, you can do this under Tools, Manage Add-ons.  Look on your Start menu for “Internet Explorer (No Add-ons)”.
• Running Windows 7?  Keep in mind you’re running a beta version of IE on top of a beta version of Windows.  Crashes come with the territory.  That said, Microsoft just released some updates that take care of quite a few problems; make sure you have these installed.

Hopefully this helps.

Google Chrome on Windows 7

Google Chrome is the Notepad of browsers.  It lacks even the most basic features available on all other modern browsers.  There’s no support for RSS, bookmark and history features are rudimentary at best, and there are very few options to configure.  There is no way of managing third party add-ins.  It’s not even possible to disable JavaScript.

But sometimes, simplicity is exactly what you want.  There’s no denying that Google Chrome is beautiful, and it’s fast, too.  It’s full of nice touches, like the ability to drag tabs into new windows and a status bar that gets out of your way when it’s not needed.  All style, little substance, and great attention to detail: if only it were grossly overpriced, I’d swear it was an Apple product.

I really don’t recommend Chrome for most people: there are just too many problems and shortcomings.  It feels like a proof of concept, not an finished product.  Overall market share would seem to agree with me.  But for those who have been won over by Google’s elegant design, or for those who like to run a variety of browsers, you’re going to get a nasty little surprise when you try Chrome on Windows 7:

The Program Compatibility Assistant does a very good job of warning users when there is a problem.  When is says “This program has known compatibility issues”, it’s a good idea to listen.  Sometimes the problems are minor and easily ignored.  Sometimes the problems will destroy your system.  But the problems are always there.

In this case, nothing will break your system.  But Chrome won’t work.  Instead, it displays a snarky “Aw, Snap!” error message when you try to browse to a site and notes that “Something went wrong while displaying this webpage.”  If you massage it a bit, you can get other types of errors and crashes, but it still won’t work.

Now, I know Google likes simplicity, but when things go sour, simplicity is a horrible idea.  Not only does Google Chrome fuck up, but it doesn't even have the courtesy to explain what happened.  If you loan a friend your car for the weekend, you want him to take care of all the little details for you.  You don’t need to hear about exactly what routes he drove, where he bought gas, and so on.  But if he returns your car as a mangled heap of steel and blood, you damn well want a bit more of an explanation than “Something went wrong” and a cutesy sad face.

I have plenty more I could rant about, but perhaps for now I’ll just shut the hell up and tell you how to fix this.  It’s simple, really: you just have to run chrome.exe like this:

chrome.exe -in-process-plugins

Note that the only space is before the first hyphen; this is one argument, not three.  I’m assuming this argument tells Chrome to run plug-ins – such as Adobe Flash – in the same process as the browser.  This is not a great design as far as security is concerned, so hopefully Google will fix this soon.

This helps on the command line, but to actually make Chrome work when you start it normally, you’ll have to change the properties of its shortcut.  Right click on the Google Chrome link on your desktop or Start menu and click Properties.  For the taskbar shortcut, right-clicking opens a jump list; just right-click on the Google Chrome link in that jump list to find your Properties option.  Then, add “ -in-process-plugins” to the end of the text in the Target textbox.  You’ll have to do this for each shortcut.

When you run Chrome, you’ll still get the compatibility warning.  In fact, you’ll get the warning each time you open a new tab in Chrome.  To get rid of this warning, just check the “Don’t show this message again” checkbox and click “Run Program”.

If you want Chrome to work as your default browser, you have even more work to do.  It looks like some people have had limited success by following the instructions at http://www.sevenforums.com/software/930-chrome-chromium-windows-7-64bit.html, but not without problems.  Also, Google Chrome installs on a per-user basis, and this is a per-machine fix, so if you use multiple profiles, things will get messy.  Really, I strongly recommend against using Chrome as your default browser.

You might also have trouble getting Flash to work.  If so, browse to http://get.adobe.com/flashplayer/ and follow the instructions there.  The installation is a bit wonky; you may have better luck you if save the Flash setup file, close Chrome, and then run the setup file you just downloaded.

Or, you could ignore all this bullshit and just click Internet Explorer.

Num Lock on at Boot

“Who the hell cares about Num Lock being enabled by default?”  That was my first thought when I was first asked to make this happen. I mean, seriously, who cares, right?

Well, I work with a lot of accountants, and guess what, they do. I had never really thought this was an issue, but with the number pad for accountants being the equivalent of Burger King to a Sumo wrestler, it matters.

A lot of people out there seem to think that they have this already figured out. A lot of people are already screaming – it’s a BIOS setting…It’s a BIOS setting…It’s a BIOS setting….FUCK.. I hear you all already, but it’s not just the BIOS.  There is a little more to it than that.

In fact, you’ll notice on computer where the BIOS setting is enabled, that the Num Lock Key actually stays on right up until the point where Windows takes over. Then it will flash a couple of times, and Poof…it’s gone.

The following tutorial will show you how to change that default behaviour in the windows registry and you will be fast on your way to have your very own auto-enabled Num Lock ….whooo..wow….

The following fix involves modifying the windows registry, so the usual advice about backing up your registry applies. IF you don’t know how to modify the registry, maybe ask a buddy to do the following for you as you could end up with more than just a Num Lock key not working if you mess up. But, that being said, this registry entry is a snap and I am sure that with a little confidence you will have this finished in no time at all.

Your first step involves opening up the Windows registry.

Simply push the Windows key and R at the same time, and type the word regedit in the box. This will start up the windows registry editor.

Then migrate to the HKEY_USERS\.DEFAULT\Control Panel\Keyboard\InitialKeyboardIndicators key, right click on it and select “modify”.

Finally, change the value from 0 to 2 and voila, you are done. Close the key, and the registry and reboot to check it out.

Now, I know some of you don’t like registry changes, so there is an alternate way to do this through scripting.

set up a script by using notepad with the following, name it “numlock.vbs” and save it into your startup file.

set WshShell = CreateObject("WScript.Shell")
WshShell.SendKeys "{NUMLOCK}"

All of the above works on Windows 2000/XP/2003 but is untested in Windows Vista and will not work in Windows 7 – Windows 7 must be configured with the same settings, but under the HKEY Current Users key. .

Enjoy!

SQL Server 2008: Betrayal and Broken Dreams

I was excited when SQL Server 2008 first came out.  A new version!  While it didn’t promise to change the world (“New Form Of Energy” marketing campaign aside), it did offer a few significant upgrades:

• Compression, for tables, indexes, and backups, is available.  No more zipping backups!
• New Spatial data types. No more weird math converting latitude and longitude to distances!
• Full text indexes are (finally) saved in database files.  That means they’ll be included in backups, and moving databases should be much easier: no more rebuilding full text indexes all the time!
• New FILESTREAM data type.  Store large binary streams in the file system, but let SQL Server manage it.  Finally, using SQL Server as a storage engine for serious content is feasible.  This is basically the best of both words… almost a developers-only version of WinFS.
• XML Improvements.  If you work with XML columns, there’s some candy here for you.
• Reporting Services can export to Word files.  Finally!  I’ll avoid the obvious criticism that this should have been there from day one and just be grateful we have it now.  Exporting to Excel is better, too.
• Reporting Services no longer requires IIS.  Thank God!  But you can still do it this way if you want.
• Evolutionary improvements to all the management and development tools.
• Another few dozen bullet points that I’m sure matter to some people.  But Partition Aligned Indexed Views just don’t make me hard these days.

So, we should be happy, right?  New toys to play with, improvements right across the board, and all that?

HELL, NO.

There are at least three huge problems with this new release.  Each one of them has fucked me over good and hard when I least expected it.  If you can get around these problems, it’s a great release.  Don’t get me wrong: I’m not saying it’s all bad.  I’m not saying you should run from it like Yersinia pestis.  I’m not saying I’d rather fondle stinky hooker feet all night than wrestle with this piece of shit for just one more hour.  And I’m sure as hell not saying any competing product is better.  It’s a definite step forward.  But why, oh, why, Microsoft, must you bust my balls so?  I just wanted to love you!

I have too much hatred for one entry.  I must pace myself, and let my rage out over several days.  Only this manner will I truly be at peace.  So for now, know the unholy triad of monstrosities lurking in this delightful new offering by gist alone:

1. Installation is a horror of over-engineering, redundancy (the bad kind), and practical humour.  Allocate 6-8 hours.  And pray to Zeus you have a fast Internet connection, because you’re gonna need it!
2. SQL Server 2008 is completely backwards-incompatible with SQL Server 2005.  You can move up, but not down.  Moving a database from SQL2008 to SQL2005 is impossible.  There is no way to accomplish this task; you must rebuild your database.  Attaching your SQL2005 database to SQL2008 will bork it instantly and without warning.  If you have SQL Server 2008, and your client has SQL Server 2005, you might as well be running MySQL.
3. Certain previously-innocent actions create new (often empty) database files, without warning, and (by default) in the Program Files folder.  If you lose these files, your database goes straight to the bit bucket.  So, if you attach your 15GB database by copying the .MDF and .LDF files, enable full-text indexing, detach your database, and take your 15GB home with you, you’re now the proud owner of 15 GB of uselessness.  Your database is gone forever because you didn’t also grab that 2 KB file SQL Server generated automatically and placed in your Program Files folder.  Whoops.

I’ll have more to say about these three issues later, including the best ways to work around them.  So long as you know what you’re getting into, nothing here is all that bad.  But please, Microsoft, don’t ever, ever do this again.  Neither my heart nor my balls could take it.