Get started Bring yourself up to speed with our introductory content.

Upgrading to SQL Server 2005 FAQs

We've compiled a list of frequently asked questions to address your SQL Server 2005 upgrade issues, including working with DTS packages, copying databases, creating passwords and installing different SQL Server versions on one machine.

Taking the leap from SQL Server 2000 to SQL Server 2005 means entering a whole new world of database management. We've compiled a list of frequently asked questions to address your SQL Server 2005 upgrade issues, including working with DTS packages, copying databases, creating passwords and installing different SQL Server versions on one machine.

Frequently Asked Questions:

UPGRADING TO SQL SERVER 2005

  1. Will SQL Server 2000 DTS packages work in SQL Server 2005?
  2. How do I fix encryption failures when upgrading to SQL Server 2005?
  3. What is the best way to copy databases for a SQL Server 2005 upgrade?
  4. How do I tune SQL Server query performance after an upgrade?
  5. How does failover clustering work in SQL 2005?
  6. Can different versions of SQL Server be installed on one machine?
  7. Is it possible to call an extended stored procedure in SQL Server 2005?
  8. How do I create a username and password for a SQL Server database?
  9. Can I run SQL 2000 and SQL 2005 instances at the same time?
  10. Could you provide examples of SQL Server 2005 upgrade issues?
  11. What stored procedure concurrency problems arise in SQL Server 2005?
  12. Can SQL Server 2005 databases be migrated back to SQL Server 2000?

1. Will SQL Server 2000 DTS packages work in SQL Server 2005?

DTS is on its way out in SQL Server 2005 and will be replaced by a brand-new Extract, Transform and Load (ETL) engine called SQL Server Integration Services (SSIS). SSIS provides a more powerful, flexible and better performing foundation for building ETL solutions than DTS, but this may leave many database administrators wondering what to do with their DTS packages.

The good news is, although they are no longer editable in SQL Server 2005, DTS packages created in SQL Server 2000 can still be run. To ease the transition, SQL Server 2005 provides an upgrade wizard for helping DBAs transition packages into the SSIS framework. However, not all components can be upgraded. ActiveX transforms, for instance, present a challenge for the upgrade wizard, and may not be able to be migrated.

While this means that DBAs will eventually have to re-write these packages using SSIS, doing so will probably be much easier than using DTS, thanks to the functionality that SSIS provides.
— Adam Machanic, SQL Server 2005 Expert

Return to upgrading to SQL Server 2005 FAQs

2. How do I fix encryption failures when upgrading to SQL Server 2005?

We recently upgraded from SQL 2000 to SQL 2005 on WINDOWS 2003. The entire upgrade process has run pretty smoothly aside from encryption. We have ASP.NET 1.x apps that connect to an internal server but fails when encrypted. We have tried all of the steps available on the MS KB and still have not resolved the issue.

In "How to: Enable Encrypted Connections to the Database Engine" by Microsoft, we find the following: To configure the server to accept encrypted connections:

  1. In SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, right-click Protocols for , and then selectProperties.
  2. In the Protocols for Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.
  3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
  4. Restart the SQL Server service.

The problem is when we get to Step 2, there are no certificates available in the dropdown box. This problem is solely the result of installing SQL Server 2005. The apps were successfully encrypting data connections prior to the installation. All internal and non-encrypted connections work correctly. What can I do to get certificates to appear in the Certificate tab of the Properties dialog box?

The blog post SQL Protocols by Xinwei Hong of the SQL Server Protocols team should help you resolve the problem. The post lists several criteria required for certificates to be properly loaded by SQL Server 2005.
— Adam Machanic, SQL Server 2005 Expert

Return to upgrading to SQL Server 2005 FAQs

3. What is the best way to copy databases for a SQL Server 2005 upgrade?

I am trying to migrate from SQL Server 2000 32-bit with close to 300 databases, to SQL Server 2005 64-bit. I have tried the "Copy Database" feature but it fails to copy any database over. I also need all of the logins moved over. I am open to any suggestions.

I generally find that the Copy Database Wizard is slow and inefficient compared with other methods of moving data. It is best used for cases when you need to do a very quick and dirty transfer of a small amount of data between a couple of servers, and don't want to take the time to do a backup or detach the database.

If you need to move 300 databases, I think you'll discover that the fastest way is to use sp_detach_db on the SQL Server 2000 instance to detach each database and sp_attach_db to re-attach the databases on the SQL Server 2005 instance. Doing so also has the benefit of preserving your database users.

However, even with that method you'll still have to get the logins into the SQL Server 2005 instance. To access a KB article, which includes a script that should help you with that task, see this Microsoft support page.

Note that you might have to re-map the logins to database users once you're done. To do so, use the sp_change_users_login system stored procedure.
— Adam Machanic, SQL Server 2005 Expert

Return to upgrading to SQL Server 2005 FAQs

4. How do I tune SQL Server query performance after an upgrade?

We migrated our SQL Server 2000 10 GB database to SQL Server 2005. Few queries run slower than when in SQL Server 2000. Earlier we were getting results in 10 secs now it's taking about 30 minutes. The execution query plans in SQL Server 2000 and SQL 2005 are different. SQL 2000 is using indexes whereas SQL 2005 is using table scans of the table. Also the sequence of tables Nested Loop/Inner Join has changed. Since this is a live application, amending the query is difficult. What suggestions can you offer to tune query performance in SQL Server 2005?

You need to troubleshoot the issue as a new problem, unrelated to the upgrade. Indexes need to be rebuilt and statistics updated. Sometimes after a SQL Server upgrade (from SQL 7 to SQL 2000, or SQL 2000 to SQL 2005) the indexes need to be rebuilt and the statistics updated. I always recommend using the UPDATE STATISTICS command instead of the sp_updatestats, as the UPDATE STATISTICS command gives you access to more options than the sp_updatestats procedure.
—Denny Cherry, Performance Tuning Expert

Return to upgrading to SQL Server 2005 FAQs

5. How does failover clustering work in SQL 2005?

How does failover clustering work in SQL 2005? And how does it compare with the failure support in Oracle and DB2?

The failover clustering functionality in SQL Server 2005 has been extended in various ways.

First of all, SQL Server 2000 running on Windows 2000 Datacenter was limited to a four-server cluster. SQL Server 2005 running on Windows 2003 Server can now support up to eight nodes (depending on the edition of Windows 2003.)

Failover is now also supported for a wider variety of services. Analysis Services, Notification Services, replication, and SQL Server Agent are all cluster-aware in SQL Server 2005. This is a major factor for those organizations that utilize these features and need to maintain high availability!

But the biggest improvement in terms of database availability is not from the failover clustering side at all. A new feature called "Database Mirroring" will prove to be a much more interesting way of maintaining uptime.

This feature can be thought of as a more real-time form of log shipping. Transactions are broadcast continuously from the active node to a waiting database on another server that is constantly in a state of recovery. When or if the active node goes down, the mirror can take over almost instantly. This is a huge improvement over the lag -- sometimes several minutes -- that can be experienced with failover clusters.

More information on this and other features of interest to DBAs, can be found in the TechNet article, Overview of SQL Server 2005 Beta 2 for the DBA.
— Adam Machanic, SQL Server 2005 Expert

Return to upgrading to SQL Server 2005 FAQs

6. Can different versions of SQL Server be installed on one machine?

Can you install two different versions of SQL Server on the same box?

Yes you can. While you theoretically can install them in either order, I'd always suggest installing the software in the order it was released: SQL Server 2000 first, then SQL Server 2005.
—Greg Low, Development Expert

Return to upgrading to SQL Server 2005 FAQs

7. Is it possible to call an extended stored procedure in SQL Server 2005?

I'm having a problem calling an extended stored procedure (specifically xp_cmdshell) from java, using the JDBC for SQL Server 2005. By using the JDBC for SQL Server 2000 Service Pack 3, I can call it like a normal stored procedure. With SQL Server 2005, I get the following:

om.microsoft.sqlserver.jdbc.SQLServerException: The request for procedure 'xp_cmdshell' failed because 'xp_cmdshell' is a procedure object.

It is a SQL Server 2000 server. I want to use the new 2005 JDBC because it does not have problems with cloned connections in manual transaction mode. I had problems with the selectMethod=cursor property on my webserver, it just didn't seem to take. It worked fine from my local webserver during development, but for some reason doesn't work when running on the live webserver. Is it even possible to call this extended stored procedure?<.p>

Yes, by default, it's disabled in 2k5 as it's not considered safe. You can enable it using the Surface Area Configuration Tool if you think it's appropriate.
—Greg Low, Development Expert

Return to upgrading to SQL Server 2005 FAQs

8. How do I create a username and password for a SQL Server database?

I have created a SQL Server 2005 database and I plan to protect it with a username and password. I'm unclear as to how to do this. How can I make a security system for my new database with its security objects?

Here is a great article by Steve Jones that will get you started with SQL users and logins: Beginning SQL Server -- Logins and Users.
—Steven Andres, Security Expert

Return to upgrading to SQL Server 2005 FAQs

9. Can I run SQL 2000 and SQL 2005 instances at the same time?

Can you run SQL 2000 and SQL 2005 instances on the same server? Are there any problems associated with the two instances running together?

Yes, they run together. The only issue I've seen is where 2k is installed after 2k5, there have been some issues with shared dll's being replaced by older ones. Installing another instance of 2k5 seems to have always fixed that.
—Greg Low, Development Expert

Return to upgrading to SQL Server 2005 FAQs

10. Could you provide examples of SQL Server 2005 upgrade issues?

What possible problems are faced when migrating from SQL Server 2000 to SQL Server 2005? What are all the precautions that should be taken during migration?

There are many issues that you can run into when doing a migration – and it would be far too difficult to explain them all in this space. Luckily, Microsoft has released a tool to help with the transition. You can read all about the upgrading in my article "Reduce upgrade unknowns with the SQL Server 2005 Upgrade Advisor."

For more information on migration, I invite you to listen to my webcast Upgrading to SQL Server 2005: What you must know where I share many tips to help ensure a smooth changeover to the new version.
—Adam Machanic, SQL Server 2005 Expert

Return to upgrading to SQL Server 2005 FAQs

11. What stored procedure concurrency problems arise in SQL Server 2005?

I'm having a concurrency problem in SQL Server 2005. There are a number of free seats on the bus that I sell tickets to. Before inserting a sold ticket I need to check whether there are any free seats left. My stored procedure does something like this:

CREATE PROCEDURE add_ticket -- parameters DECLARE free_seats int BEGIN TRANSACTION SELECT free_seats = COUNT(*) FROM tickets WHERE seat_is_not_taken IF free_seats <> 0 INSERT INTO tickets VALUES(...) -- some other statements END TRANSACTION

The problem is that two processes can read the amount of free tickets concurrently and both save a ticket, even if there are no free seats left. I need a way to block processes from reading the amount of free tickets while other processes running the add_ticket procedure have not yet inserted a new ticket. SET TRANSACTION ISOLATION LEVEL does not help in this situation, am I right?

You are correct; a higher isolation level would not help ensure that multiple readers did not read the same rows simultaneously. However, there are several ways you could make this work. For instance, you could assign each seat a unique identifier (meaning, a unique key – not necessarily a GUID) and create a table for seats that have already been taken. Put a UNIQUE constraint on the table and you will be guaranteed that no seat is inserted twice.

That said, I think a more interesting option might be to employ SQL Service Broker. You could set up a conversation for each bus, and store the conversation handles in a table that can be referenced by readers before doing the RECEIVE. That way, the readers can filter appropriately. Drop a message into the queue for each seat on the bus. The readers can then simply RECEIVE the messages as needed (in the process, reserving seats on the bus). Service Broker will ensure that no message is received twice, meaning that you will no longer have any concurrency problems.
—Adam Machanic, SQL Server 2005 Expert

Return to upgrading to SQL Server 2005 FAQs

12. Can SQL Server 2005 databases be migrated back to SQL Server 2000?

During the process of migrating to SQL Server 2005, many development organizations will still be forced to support SQL Server 2000 installations. As a result, a common requirement is the ability to reverse-migrate: move databases back from SQL Server 2005 to SQL Server 2000.

Unfortunately, this is not as easy. Backed-up databases from SQL Server 2005 instances cannot be restored in SQL Server 2000 instances. Nor can detached SQL Server 2005 databases be reattached to SQL Server 2000.

Because backup/restore and detach/attach are not supported, the only methods available for moving data from SQL Server 2005 installations to SQL Server 2000 installations are based on copying data. Data and schemas can be scripted using a tool such as Red Gate's SQL Packager and scripts can be applied on the SQL Server 2000 installations. Another option is to use tools such as DTS or SSIS, both of which include data copying wizards.

However, copying data and schema is a potentially troublesome experience. SQL Server 2005 supports many features that are not present in SQL Server 2000 and can cause problems during cross-migration. It's important to be cautious when attempting to develop solutions for both platforms, and my recommendation is that development should be done using SQL Server 2000 and the code and data migrated forward to SQL Server 2005, rather than the other way around.
—Adam Machanic, SQL Server 2005 Expert

Return to upgrading to SQL Server 2005 FAQs

Didn't find what you were looking for?

Pose a question to anyone of our SQL Server experts.

You can also browse our SQL Server Topics section for more advice.

This was last published in September 2007

Dig Deeper on SQL Server Migration Strategies and Planning

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close