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
I know I’m a little late posting this as I’m sure you’ve already downloaded the free Ebook and read through it, but if you haven’t be sure to go grab a copy.
It’s got some good stuff in it.
SQL Server Sprawl
Includes a link to 2 free chapters from SQL Server 2008 Administration in Action. Also has a link to another option of scanning your network for SQL Servers using SQL Ping.
SSMS Projects and Solutions
Buck Woody talks about some options for using the Project features in SSMS.
Auditing your Database Server
Colin Stasiuk covers auditing options in SQL Server 2008 as well as previous options.
Microsoft SQL Server Team Blogs
Kevin Kline covers the topics of Blogs from the Microsoft SQL Server Team and also provides some useful links to other resources.
SQL Server Consolidation Tips
Thomas LaRock writes his third tip about SQL Server Consolidation tips. Part 1 and Part 2 are also linked.
SSIS Configuration Files
Jamie Thomson covers a tip on editing the .dtsConfig file. I prefer to use a table to store my configurations and I highly recommend looking at this method.
Document your Environment
Jonathan Kehayias covers the importance of documentation. I would love to see more examples of this on the blogs as well.
I’m Certified, Now What?
Steve Jones covers the topic of what comes after becoming certified.
Elegance of Database Design
Andy Leonard continues to cover the topic of Database Design.
Q&A with Louis Davidson and Paul Nielsen on Database Design
Schema design is important, and these two SQL Server MVPs discuss the need for Normalization.
Free eBook from Quest Software
Marlon Ribunal finds a great eBook offering from Quest Software.