The site got hacked and I’ve taken my sweet time and getting things back up. Hopefully, after a little bit of cleanup things will be back to normal. Sorry for the downtime.
This isn’t a SQL related post, but it’s helpful so I’m going to post it.
This post assumes that you are using Firefox and your bookmarks have gotten out of control. It’s time that we do a little cleanup.
The first thing you need to do is download an add-on called Xmarks. That piece of advice along with using Firefox is worth the price of admission alone. Xmarks allows you to sync your bookmarks between multiple computers and is very helpful when you have ever have to rebuild your computer as you can just get them back once you reinstall Xmarks and sync with the server. This doesn’t replace normal backups but it’s a nice safety net and useful feature. It also syncs passwords if you use that feature, but I haven’t seen it fully catch every password in my experience. I guess it’s possible that using Xmarks in the first place might have created more duplicates, but I’ll hold off on saying that until I see what it does after I clean it up.
So at this point we have Firefox with the Xmarks add-on is installed, registered and our bookmarks are synced up with the server.
I have always needed to get my bookmarks in order, and I finally saw a post on Lifehacker that got me going. The sad thing is that it adds a toolbar, but you can always use it for it’s purpose and then get rid of it. The add-on can be downloaded directly from Firefox or by going here or here.
Once you’ve gotten it installed then it’s time to get down to cleaning out your closet. My main purpose was removing duplicates, but it also has a feature to check for bookmarks that return ‘File not Found’, ‘Server Not Found’, and ‘Timeout’. That could probably be a bit more tasking on your network to go hit all those sites but if you are at home that’s not a big deal.
Removing Duplicates
I’ll start off with a couple of negatives that aren’t deal breakers but do add to the time it takes to clean things up. When you click the IB icon and select ‘Check Duplicates in This Toolbar’ the window that it returns is way larger than the screen. You have to drag it to fit the screen size you want to work in. This may not be happening for everyone but it did happen for me.
Once you’re past that point, you’ll find the biggest negative I have found. Let’s say it returns something that looks like this:
Well, you could just click each little check box besides the last one but surely there’s a better way. I’m used to using tab or the down arrow and hitting the space bar (or enter) when I want to do something repetitive like this. Maybe there’s a way that I haven’t found but please add a comment if you do because you could help others with the same problem. Of course, you wouldn’t get to use this brief intro to AutoHotKey that I’m giving. I’m sure there are better intros but you’ll have to suffer through mine for now. AutoHotKey is going to give us the ability to go through this a little faster than clicking every check box, so if you can get around the front-end time of writing the script you’ll save time on clicks in the end. I’ll provide the scripts that I have used as well.
Go ahead and download AutoHotKey. The Installer will work just fine for our purposes. Install the program and accept all the defaults. Feel free to hit the Readme if you want to learn a little more about it.
I think it’s a good idea to go ahead and create a specific folder on your computer for these scripts, because you’ll always know where to find them if you choose to use this program for something else. Call it AHKey or whatever, just remember where you put it. Now fire up notepad or notepad++ (even better) and copy the text below into the document.
;
; AutoHotkey Version: 1.x
; Language: English
; Platform: Win9x/NT
; Author: A.N.Other <myemail@nowhere.com>
;
; Script Function:
; Template script (you can customize this template
; by editing “ShellNew\Template.ahk” in your
; Windows folder)#NoEnv
; Recommended for performance and compatibility
; with future AutoHotkey releases.SendMode Input
; Recommended for new scripts due to its superior
;speed and reliability.SetWorkingDir %A_ScriptDir%
; Ensures a consistent starting directory.
This is how it is suggested that you begin each script so I just went with that. Basically, we want to use this program to simulate a mouse click, mouse move, mouse click, etc. Go ahead and add the following code directly after the SetWorkingDir line.
+2::
MouseClick, left
MouseMove, 0, 24, 0, R
MouseClick, left
return
In English, that means that when I hit shift and the 2 key perform the following actions: Click the left mouse button, move the mouse 24 pixels down and click the left mouse button again. So in my right hand I’m holding the mouse arrow over the first check box. With my left hand I hit Shift-2 and the magic happens. This isn’t that big of a deal until you get to where you have thousands of bookmarks with 2-4 duplicates each, and that was the case for my bookmarks.
You could modify it to do four check boxes like this:
+4::
MouseClick, left
MouseMove, 0, 24, 0, R
MouseClick, left
MouseMove, 0, 24, 0, R
MouseClick, left
MouseMove, 0, 24, 0, R
MouseClick, left
return
So once you’ve saved your files (with the .ahk extension), double click the file to active the script. It will minimize into your system tray so you can go there when you need to close it. You can find ways to change the hot keys and do other things by reading through the documentation and website for AutoHotKey.
Hope that helps someone else out.
Seems that I’m having SQL Server 2008 installs fail on computers that have Visual Studio 2008 with SP1. We’ll see if anyone can reproduce it.
We’ve been running SQL Server 2000 x86 on Windows Server 2003 R2 x64 for some time. While there have been a few issues here and there, monitoring SQL related counters can be troublesome whether you are local or remote.
If you are local to the server when you open up performance monitor you will receive the x64 counters and this won’t help you since your SQL Server is x86. For the 32 bit version just use %SystemRoot%\SysWow64\Perfmon.exe.
If you are remote then you’ll need to make some registry modifications on the server that you are monitoring. You’ll need to modify 2 registry entries to read SysWow64 instead of System32:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\RemoteRegistry]
“ImagePath”=”%SystemRoot%\SysWow64\svchost.exe -k regsvc”
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\RemoteRegistry\Parameters]
“ServiceDll”=”%SystemRoot%\SysWow64\regsvc.dll”
Restart the Remote Registry Service and you should be good to go now.
Resources:
http://www.code-magazine.com/articleprint.aspx?quickid=080015&printmode=true
(Maybe you couldn’t run SQL 2000 under a x64 bit OS at the time of this writing)
http://blogs.msdn.com/edglas/archive/2006/09/06/reading-32-bit-counters-on-a-64-bit-machine.aspx
Jorge Segarra is onto something pretty cool. Head on over to SQL University and check it out. I’m going to run through it myself in the coming weeks because everyone should be trying to polish up on the SQL.
http://midnightdba.itbookworm.com/midnightdba/blog/post/How-do-you-keep-track.aspx
Staying on Track can be hard, Jen McCown outlines a few ideas of how to keep up with tasks.
http://www.red-gate.com/specials/ebooks/protecting_sqlserver_data.htm?utm_source=simpletalk&utm_medium=email&utm_content=ProtectingDataN20090921&utm_campaign=SQLtoolbelt
Another free ebook from redgate. John Magnabosco talks about securing SQL Server Data.
http://weblogs.sqlteam.com/tarad/archive/2009/08/31/DefragmentingRebuilding-Indexes-in-SQL-server-2005-and-2008.aspx
Tara Kizer gives a new script for backups.
http://sqlblog.com/blogs/linchi_shea/archive/2009/09/21/the-transact-sql-prime-directive.aspx
Linchi Shea gives us a vision, or at least some good thoughts on how it should be.
http://www.brentozar.com/archive/2009/09/how-to-deliver-a-killer-tech-presentation/
Brent Ozar gives us presentation tips. Everyone should want to be the best.
http://sqlblog.com/blogs/kevin_kline/archive/2009/09/18/new-post-on-my-sqlmag-blog-free-dmv-monitoring-tools.aspx
Kevin Kline gives us some DMV tools.
While the Microsoft reference may say that you should keep the operating system the same, it seems hard to believe that there aren’t situations where one would decide to bypass this recommendation. If you are sitting on Windows 2003 x86 with SQL Server 2000 x86 and you have the option to go to Windows 2008 x64 with SQL Server 2005 x64 then it might be a good time to move on that. Microsoft Windows 2008 R2 is the first OS to only be offered in a 64-bit version. If you want to be set for a few years, now might be time to go ahead and bite the x64 bullet. But you should also be aware of the downsides that can be associated with this decision. If you have 100 DTS Packages that primarily use Excel connections then you may eventually regret the decision to go with x64. It would also be a good idea to make a decision about going to Windows Server 2008.
RAID Levels can also be something to consider during this time. Kendal Van Dyke has an excellent series on Disk Performance that can be useful when looking at RAID levels. While you will often read that RAID 10 gives you the best bang, you may not have any buck left. Generally, it’s recommended that you stick with RAID 5 for your data files and RAID 1 for your log files. If you are not connected to a SAN and your drives are local then now is the time to get the RAID levels right.
Once you have your OS installed, its time to get it configured so that it works optimally for SQL Server. There are several key fundamentals about SQL Server installations that you will need to understand as you move forward and there are great resources from Brent Ozar and Chad Boyd included in the resource links below. Another item that Brent touches on briefly is that of Disk Partition Alignment. Jimmy May has some excellent information on that, and the SQL CAT team recently published a new whitepaper that is helpful.
Resources:
SQL Server 2005 on a 64-bit Platform
32-bit vs. the 64-bit SQL Server performance surge
Q&A: Making the jump to 64-bit SQL Server 2005
Disk Performance Hands On
SQL Server Setup Checklist – Part 1
Installing Clustered SQL Servers – Outline, Checklists, Document Sheets
Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck
Disk Partition Alignment Best Practices for SQL Server
After reading through the SQL Server 2005 Upgrade Technical Reference Guide, you should have a good idea about which direction you want to move forward. The upgrade path and upgrade method will help you modify your plan so that you have the best chance to succeed. Depending on your environment, your choices may already be made for you. Regardless of whether or not you are doing an In-Place or a Side-by-Side upgrade, it will be very beneficial for you to do some testing before you flip any kind of switch to go live with SQL 2005.
In the best of scenarios, you can find another server that you can use to do testing and simulate what the environment will look like. If you cannot find another server that can be used solely for this purpose, maybe you could look at using virtualization to meet this need. You probably will not be able to simulate the hardware completely, but it should be better than nothing. Setting up a virtual instance is beyond the scope of this article but it should be fairly easy to set that up with resources found online. VMware and Microsoft both have virtualization software that can be used for free.
Scalability Experts has a tool called SQL Server Upgrade Assistant 2005 that can be used to verify how your applications will run against SQL Server 2005 instead of 2000. Even if you do not use this tool, by reading through the User Guide you can develop your own plan for testing and that should help you discover the items you need to address.
In the fourth part of this series, I’ll describe some things you should consider as you are setting up your new server.
Due to constraints such as budgets, staff, and overhead it may only be feasible for a shop to move from SQL Server 2000 to 2005 instead of going straight to SQL Server 2008. There are plenty of resources on the internet that cover the advantages and pitfalls that one might encounter during the leap of faith, but I hope that by documenting my own experience I might help others have an easier transition.
One of the first stops for anyone interested in upgrading should be reading the following papers from Microsoft:
SQL Server 2005 Upgrade Handbook
SQL Server 2005 Upgrade Technical Reference Guide
After you’ve digested nearly 400 pages of information you should at least have a grasp on the initial concepts of what you are about to undertake and possibly even have a rough idea of what your game plan will be. The biggest positive is that we can learn from all of those who have gone before us since we aren’t exactly breaking new ground. SQL Server 2008 has even been out for some time now, and hopefully you have investigated whether or not you should bypass SQL Server 2005. A few resources on that can be found at:
http://blog.scalabilityexperts.com/2008/01/28/upgrade-sql-server-2000-to-2005-or-2008/
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1325303,00.html
http://www.sqlservercentral.com/articles/Administration/3094/
http://sqlblog.com/blogs/denis_gobo/archive/2008/07/09/7753.aspx
In our case, the decision to make a slow move towards SQL Server 2005 was made based on a previous purchase of the software coupled with the later knowledge of the amount of resources required to redo what we had already begun.
Once you’ve decided to go forward with your upgrade it would be wise to download the Microsoft SQL Server 2005 Upgrade Advisor. This product will tell you what issues you could possibly run into. It’s not going to cover everything that could possibly trip you up, but it’s a good starting point in educating yourself on what you have and where you are going.
You may also find that it would be beneficial to run the Microsoft SQL Server Best Practices Analyzer to look for any other issues that may be there if you have an inherited system. You may find that it’s a good time to do a little bit of cleanup before you upgrade and there’s no time like the present. I’ve found this tool useful in tracking down issues such as stored procedures with a sp_ prefix, usage of Select *, and objects calling deprecated built in functions.
In Summary, I think that it would be wise to use this planning time to clean up issues with the current SQL 2000 instance before you move forward if you have the opportunity. While some of the issues you’ll find won’t prevent you from going forward, it seems that while you have your sleeves rolled up and you’re inside the system a little cleanup would reduce future maintenance issues. Of course, if your situation is anything like mine you will run into issues that just have to wait because there’s no way that modifications can be made in a timely manner without sidetracking the upgrade.
This is part one in a series of upgrading from SQL Server 2000 to SQL Server 2005. The first question I want to address is why would you want to upgrade in the first place. It’s important to know why you are doing something if you are going to be able to truly understand what you are doing. Most of my opinion and experience comes from the admin DBA side of the fence so you won’t find a ton of developer jargon thrown around because that’s not a strong point for me. I’m in the process of upgrading from SQL Server 2000 to SQL Server 2005 as well, so I hope I can chronicle the journey and help others if they are in the same situation.
It goes without saying that the biggest reason for upgrading is that the product is basically End of Life. While you can still get extended support through 2013 it would still be wise to start the process of moving forward. There are plenty of resources on the internet about the new features that were introduced or approved upon with SQL Server 2005. Since I am more of an administrator I get a little more warm and fuzzy about Table and Index Partitioning, All Permissions Grantable, Mirroring, Snapshots, Online Restores, and Performance DMV’s. While the new data types can be very useful and I’m sure CLR has its place it is hard for me to get as excited about those items. A full list of features can be found by digging through Microsoft’s full list or an abbreviated Top 30.
For us, SQL Server Integration Services this was one of the biggest hurdles to overcome because of the number of DTS packages that we are dependent upon. Microsoft has completely redone the way that data is moved from the ground up and there is a learning curve. We eventually decided to purchase a few licenses of a third-party piece of software to help with the package transition.
There’s also no more Enterprise Manager, and for the most part that’s a good thing. Visually, it’s much more appealing and it’s worth running a client to access SQL 2000 instances to do most things. This late in the game I’ve even been running a SQL 2008 client because of the added features that it gives me as an administrator.
Coming in part 2, Upgrade Education and Fact Finding.
Resources:
Landing page for SQL Server 2005
Microsoft whitepaper on “Why Upgrade to SQL Server 2005”
FAQ: Why Upgrade to SQL Server 2005 now
What’s new in SQL Server 2005
SQL 2005 Books Online : What’s New
Top 10 new features in SQL Server 2005
Consider what’s new in Microsoft SQL Server 2005
New T-SQL Features in SQL Server 2005 Part 1
New T-SQL Features in SQL Server 2005 Part 2
The Differences Between SQL Server 2000 and 2005
The Differences Between SQL Server 2000 and 2005 – Part 2