Tag Archives | SQL Server 2000

Problems with SQL 2005 Upgrade Advisor

For those of us that are still in situations where we deal primarily with SQL Server 2000, there should come a time when we have to run the Microsoft SQL Server 2005 Upgrade Advisor. Some of you may be going straight to SQL Server 2008 and this issue may be resolved in the SQL Server 2008 Upgrade Advisor.

During the initial run of the wizard, an error will pop up without giving you many details. In my case, I look at the event viewer and see the following:

.Net 2 Runtime Error

.Net 2 Runtime Error

If you browse the internet you’ll find various ideas to fix it. Some suggest removing all .Net versions and reinstalling them. Some suggest not putting on .Net 2 SP1 until you run it. Another suggestion is to change the Aspnet.config file to read <legacyUnhandledExceptionPolicy enabled=”true” />. None of these suggestions worked for me.

The fix that got me working was to copy all of the files from within the \BPA\Bin folder and paste them into \BPA\.

I think I would try this first before going through all that other trouble. If this doesn’t work you can always explore the other options.

Resources:
http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/2a5f460b-8684-4510-8fcb-1e9d9786baff/
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/390e8a17-bd66-4426-b24c-d0a5cc009053
http://www.sqlservercentral.com/Forums/Topic561810-5-1.aspx
http://support.microsoft.com/kb/911816
http://sqllearnings.blogspot.com/2009/04/microsoft-sql-server-2005-upgrade.html
http://devel.virtage.com/2009/01/en-crashing-sql-server-2005-upgrade-advisor-error/

Comments { 1 } Posted on August 5, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication

Install SQL Server from network

Whenever it comes time to install SQL Server to a server it’s usually not a big deal to walk back to the server with the disc or share my drive across the network. I ran into a different situation this weekend when I needed to install several versions on a server while on a vpn connection from home. I can’t switch the discs when needed because I’m over 30  minutes away so I needed to come up with a better way.

The problem arises when I have to deal with a .iso file. I needed a way to get the contents out of the iso and into a folder that I could use for a network install. After a small amount of searching I found that I had a few options. The option from Microsoft (Virtual CD Rom) didn’t play nice with the server permissions. I finally got ISOBuster to work for my needs.

Once I extracted the .iso file to a folder for each of the cd/dvds then it was a very easy process. I’m sure there are multiple ways to do this, but given my situation of being remote and only having the .iso files the solution I found seemed to do the trick.

Comments { 0 } Posted on March 8, 2009 in SQL Server 2000, SQL Server 2005

Tempdb needs a doctor

In the past, we’ve run into issues with contention and the -T1118 flag, hotfix 2040, and multiple tempdb files that led me to go ahead and put this info out there if nothing more than for my future reference.

I ran into a machine today that had the tempdb files in the wrong location. They were also the wrong size and there were not enough of them.

So that presents three problems that had to be solved. The first problem I decided to fix was to get rid of the current files that were sized inappropriately and in the wrong location. No need to double my work.

I’m sure it can just as easily be done by script, but I went into the GUI and deleted each tempdb data file that I didn’t want anymore for the time being. You will get an error message about deleting the file and restarting SQL Server. Once you’ve done that you are left with only one data file and one temp file, even though they may be in the wrong location and the wrong size.

Let’s go ahead and tackle the size issue first. After we’ve just restarted SQL Server lets do something like this:

dbcc shrinkfile (‘tempdev’,1024,TRUNCATEONLY)

Now it’s time to go ahead and move the files to the place we want them:

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘I:\tempdb.mdf’)
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘I:\templog.ldf’)
GO

Now it’s time to add the additional tempdb data files:
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′,
FILENAME = N’I:\tempdb2.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev3′,
FILENAME = N’I:\tempdb3.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev4′,
FILENAME = N’I:\tempdb4.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev5′,
FILENAME = N’I:\tempdb5.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev6′,
FILENAME = N’I:\tempdb6.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev7′,
FILENAME = N’I:\tempdb7.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev8′,
FILENAME = N’I:\tempdb8.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO

This will create 8 files (for our # of processors) that are 1024MB in size and autogrow at 200MB. At this point it probably wouldn’t hurt to restart the SQL Server. We had a few small issues but I’m not sure they were related.

The links that helped me with this are as follows:
http://www.sqlteam.com/article/moving-the-tempdb-database
http://weblogs.sqlteam.com/tarad/archive/2008/05/20/60604.aspx
http://weblogs.sqlteam.com/tarad/archive/2008/08/05/How-to-get-physical-CPU-count-on-a-server.aspx
http://sql-server-performance.com/Community/forums/p/23813/135241.aspx
http://support.microsoft.com/kb/328551

Comments { 2 } Posted on February 18, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication

SQL 2000 Server Wide Configurations

Pinal Dave asks a question about server-wide configuration values regarding 2005 and 2008, but I thought I would add the ones for 2000 in case noone else did. Hopefully I got the pertinent info.

SQL Server 2000 Sys Configures

SQL Server 2000 Sys Configures

Comments { 0 } Posted on February 17, 2009 in SQL Server 2000

Which SQL Server version?

From my understanding, it is only necessary for me to discuss SQL Server versions later than SQL Server 2000. I include SQL Server 2000 because the truth is that many people are still using this product. I know that the shop I work in has mostly SQL Server 2000 instances and a couple of SQL Server 2005 so that is where I’m coming from. Some of the consideration may be out of your control because your shop may not have a SQL Server 2000 server. I hope to present the pros and cons that go with the order in which you choose to begin your journey. I also hope that others will add their opinion to back me up or correct me.

SQL Server 2008
If you aren’t working with a certain SQL Server version at your workplace then I would think that going with SQL Server 2008 would be your best option. It’s the latest and greatest and you will give yourself the most shelf-life for your effort. SQL Server 2008 was released in August of 2008 and you can see a build list here. One negative I noticed is that some of the books (MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 Implementation and Maintenance (PRO-Certification) are still being finalized for this version at the time of this writing. At the bare minimum, I would recommend becoming familiar with this version and go grab the Express Edition.

SQL Server 2005
You still may want to look at this version first if you are breaking into the field. There are tons of resources for SQL Server 2005 and you can focus on laying the ground work for a successful base to your knowledge. SQL Server 2008 does have new features, but I am not sure they are make or break for the beginner DBA. Personally, I have only gone as far as touching SQL Server 2005 and 2000. I am in the process of beginning to test SQL Server 2008, but since it’s not deployed in my work environment I feel that I gain more by focusing on maintaining and upgrading from 2000 to 2005. The amount of resources available to someone starting out with 2005 will be far greater than 2008 at the time of this writing, but there are many variables to add up when making your decision and I can’t account for all of them. One negative aspect that I saw last night would be finding a development version of SQL Server 2005 to start your test lab with. Amazon did not have any copies in stock so you would have to find a reseller if you wanted to use more advanced features that weren’t available in the Express Edition. SQL Server 2005 is up to SP3 so you are past the initial growing pains that come with a product without a service pack. You can grab the Express Edition here.

SQL Server 2000
Support for this product is only available through extended support. If you are currently in a shop that primarily uses SQL Server 2000 then you first step should be learning how to help maintain what is already there. If you are going to be working with a product then it does not make sense to me to only prepare for the future. This is the situation that I inherited so I can speak more clearly on it than the others. When this package fell into my lap we were only using SQL Server 2000. Even though the product life cycle is basically expired, the product still has to be managed by someone. Given the current economy, there’s a pretty good chance that budgets are frozen and upgrades to SQL Server 2005 may not be possible. This does not mean you should not prepare for the future, but it does mean you better become an expert on what your shop is using right now if you want to be the “go-to-guy” where you are at. There are tons of resources out there for SQL Server 2000, and that has helped me a great deal when it came to becomming the person who wants to answer the questions. If your shop is currently using SQL Server 2000 then you should have access to the necessary materials to build a good test lab to begin the learning process. I made the choice to go ahead and go with the 2005 certification track since that is the direction we are going, but I still needed to know how to do the same things in 2000. Having a certification of MCITP Database Administrator in SQL Server 2005 helps give those around me the confidence that I am able to facilitate an upgrade from SQL Server 2000 to 2005.

Summary
The SQL Server version you choose to go with definitely depends on variables that only you know. Whether or not you actually get to touch the SQL Servers at your workplace also play a huge role in what kind of decision you make. I would still download an Express Edition copy of 2005 and 2008 in order to get your feet wet, but you have to focus on what you feel will give you the edge where you work or where you would like to work. It all goes back to establishing what your goal is and then taking action to make it happen. If you want to become a SQL DBA where you work then you have to know if there’s even a possibility of that happening before you expend too much energy going down that path. Pick a target and set some goals to try and hit it. If you never set them then you’ll miss it every time. And by all means, if you want different results then you have to change what you are doing.

Comments { 0 } Posted on February 17, 2009 in SQL Server 2000, SQL Server 2005, SQL Server 2008