Q&A

Upgrading SQL Server Questions and Answers

1. Can you upgrade SQL Server 2008 SP2 Standard Edition to 2008 R2 Developer Edition?

Ans:

You can’t change the version of the installed instance as far as I know, but you could install a second instance with the Dev edition, or uninstall the Standard edition, install Developer edition and attach the user databases.

2. Does upgrade advisor analyze the remote instances?

Ans:

Upgrade Advisor can analyze remote instances of SQL Server, except for SQL Server Reporting Services. To analyze Reporting Services, Upgrade Advisor must be installed and executed on the report server.

3. Ho to upgrade a SQL Server 2000 to SQL Server 2008?

Ans:

That said, what kind of upgrade are you doing? Are you planning an in-place or side-by-side upgrade? The different approaches will result in different checklists. The safest approach is the side-by-side upgrade. You can do this either by using a backup and restore or dettach/attach of the database files. I’d suggest using the backup & restore as the safer approach. Here are the things I’d do:

• Run Upgrade Analysis tool from Microsoft. Address any issues raised there, first.
• Identify DTS packages. These must be migrated by hand, unless you buy PragmaticWorks excellent software. Rebuild the DTS packages as SSIS.
• Script out all SQL Agent jobs.
• Script out all security
• Backup the systems and validate the backups (preferably by restoring them to another system)
• Run the security script on the new system
• Run the restore on the new system.
• Validate the databases by running DBCC
• Manually update all statistics
• Run the SQL Agent script

4. Have you ever prepared a checklist while performing an upgrade?

Ans:

Yes Of course!!!

• Identify which databases need to be upgraded (ie are still on older versions of SQL Server)
• Of those databases, which are not supported on more recent versions of SQL Server?  This is one for the appropriate vendor – so do we have contact details for the vendor?
• Of those non-supported databases, if this is just because the vendor hasn’t tried it, are we able to try and will they support is in our efforts?
• Identify maintenance routines and scheduled jobs associated with database
• Identify dependencies upon older technologies (eg DTS rather than SSIS), and work out an upgrade path for these
• What applications depend upon the database?
• What UserIDs are required?
• How do we configure the application to point to a new database?
• What else needs to be changed?  (eg middleware servers)
• Are parts of the database subject to Replication?
• Is the database part of a Log Shipping routine?
• Is the database part of a Mirror set?
• What’s the recovery plan for the database?
• What’s the backup plan for the database?
• Are there any SSRS jobs relating to this database?
  • What are they?
  • Where are they?
  • How do we migrate these across?
  • What else depends upon those reports?
• …and similarly, are there any OLAP / SSAS dependencies?

It might also be worth thinking about the amount of data in the database:

• How much data have we got?
• How fast is the database growing?
• For how long do we need to retain this data?
• Can we archive anything off to improve performance?

Of course, all the above forms part of your database documentation, so it should be easily to hand, right?

The other things to work out include:

• How do we test this thing to ensure the migration is successful?
• How do we rollback if it isn’t successful?
• Point of contact for the supplier / vendor / development team
• Point of contact for the customer(s) / user(s)

5. Can you detach a SQL Server 2005 database and attach it to a SQL Server 2008 server?

Ans:

Yes. SQL Server 2005 databases are compatible with SQL Server 2008. However, that attaching a SQL Server 2005 database to SQL Server 2008 automatically upgrades the SQL Server 2005 database to a SQL Server 2008 database and the database is then no longer usable by the SQL Server 2005 installation.

6. Can you detach a SQL Server 2008 database and attach it to a SQL Server 2005 server?
Ans:

No. The only way to move a SQL Server 2008 database to a SQL Server 2005 server is by transferring the data using a method such as Data Transformation Services (Import/Export),SSIS, bcp, or use of a query between linked servers.

7. How long will it take to upgrade my SQL Server databases?

Ans:

Many factors affect the amount of time needed to upgrade SQL Server Databases. Depending on the complexity of each database, Size of databases, the hardware platform, number of processors, disk subsystem, and amount of RAM plays a significant part in the amount of time required for the upgrade. Selecting “data validation” during the setup increases the amount of time needed to perform the upgrade by a factor of two. Some typical times for the upgrade process are:

8. When you upgrade a SQL Server, the upgrade wizard seems to stop responding and fails. Why?

Ans:

If applications or services have open ODBC connections to the SQL Server 2005 server during the conversion process, they may not allow the SQL Server to shut down completely. The conversion process will not proceed on to the next step if it does not receive verification that the SQL Server has been completely stopped.

9. “I’m trying to restore a 25 GB database backup taken from a Windows 2003/SQL 2005 machine to a Windows 2008/SQL 2008 machine in the Amazon EC2 cloud, using a .bak file and the SQL Management Studio. SQL Management Studio reports the restore reaches 100% complete, and then just hangs indefinitely (24+ hours) using a lot of CPU, until I restart the SQL Server service. Upon restart, SQL again uses a lot of CPU activity for what seems to be an indefinite amount of time, but the DB never comes online.”

Ans:

The database is in the process of being upgraded from SQL 2005 to SQL 2008 when you kill it. Check the ERRORLOG in SQL Server and you should see that the database restore is complete and that the database is being upgraded.

This process is normally very quick, but it can take a while to perform depending on the database, especially if you have a lot of pending transactions in the database which much be rolled forward or backward before the database can be upgraded.

9. How to rollback the upgrade?

Ans:

If the legacy SQL Server instance is replaced by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complex and time-consuming, whereas in a side-by-side upgrade the legacy instance remains available if a rollback is needed.

10. What are the different ways in upgrading to a higher version?

Ans:

There are two ways:

In-Place Upgrade: Installs 2008 and overwrite on the 2005 Server.

Side-by-side Upgrade: A new instance will be installed and moves the databases.

11. Give some examples when we consider Side-by-side and In-Place?

Ans:

Pros & Cons: In-Place

Pros

• Easier, mostly automated
• Generally fast overall process
• Requires no additional hardware
• Applications remain pointing to same server/database name

Cons

• Less granular control over upgrade process
• Instance remains offline during part of upgrade
• Not best practice for all components
• Complex rollback strategy
• Not recommended for SSAS

Pros & Cons: Side-by-side:

Pros

• More granular control over the upgrade process
• Original database left unchanged; allows for testing of new database
• Single occurrence of database downtime
• Relatively straightforward rollback strategy

Cons:

• Usually require additional hardware
• Server/database name changes
• Not practical for VLDB unless utilizing SAN(Beware of “loss of quick roll-back)

12 What are the parameters should be considered while choosing the upgrade process?

Ans:

Components: A certain upgrade strategy might not be possible because the component does not support it. For example, there is no in-place upgrade for SSIS from SQL Server 2000; Microsoft recommends that you upgrade most SQL Server 2000 SSAS components.

Versions and Editions: The in-place upgrade strategy does not support all paths between versions and editions. For example, to upgrade a SQL Server 2000 Enterprise Edition instance to SQL Server 2008 Standard Edition, you must perform a side-by-side upgrade because SQL Server Setup does not support an in-place upgrade path.

Partial upgrading: To transition only a few databases on a server to SQL Server 2008 and leave the rest on the legacy version, you must use a side-by-side upgrade.

Upgrading over time: To transition databases gradually, a few databases at a time, from a legacy instance to SQL Server 2008, you can only use a side-by-side upgrade.

Effect on applications: If your organization requires minimal disturbance to the existing applications and users, you may want to choose an in-place upgrade if possible.

Availability: Both an in-place upgrade and a side-by-side upgrade require that the databases be unavailable for a certain amount of time. The amount of downtime required depends primarily on the size of the data sets. At first, it might seem that an in-place upgrade would be faster than a side-by-side upgrade because the data is not transferred from one server to another. However, an in-place upgrade also requires time for the installation of SQL Server 2008. In a side-by-side upgrade, SQL Server 2008 is already installed on another instance. If the data transfer proceeds quickly and few changes are needed on the new instance, a side-by-side upgrade might be faster than an in-place upgrade.

13. We have upgraded databases from SQL Server 2000 to SQL Server 2008 and now the upgrade hits the production. Unfortunately a part of application is not supporting SQL Server 2008. Do we need to Rollback entire process to SQL 2000? Is that the only solution? If it is the only way! Since the databases at production transactions are being running and the data has been updated. Please assist us.

Ans

However, after the upgraded SQL Server 2008 instance goes into production and starts capturing new data, there will come a point in time when enough new data has been captured that a rollback is no longer realistic. For an in-place upgrade, if you encounter problems after the system is in production, making adjustments or “patches” to the new application would be a better option than attempting a rollback. For a side-by-side upgrade, you could employ SSIS to transfer new data from the SQL Server 2008 instance to the legacy SQL Server 2000 to bring it current. Depending on the complexity of the data, this could be a difficult process.

14. Can you list out some of the compatibility options while upgrading?

Ans:

For example we goanna upgrade SQL 2000 to SQL 2008:

• Some features do not exist anymore
• Examples: undocumented system stored procedures, DUMP, LOAD, sp_addalias,
• Some are deprecated, Will be removed in future versions of SQL Server
• Examples: SQL Mail, COMPUTE BY, Remote Servers, backup passwords,…
• Some features behave differently
• Example: Access to catalog views (new security on system views)
• Some editions have different features
• Example: Express has no SQL Server Agent

15. What are the different tools available while upgrading from SQL 2000 to SQL 2008?

Ans:

Primary Tools:

• SQL Server 2008 Upgrade Advisor
• DTS xChange

Secondary Tools

• Microsoft® Assessment and Planning Toolkit 3.2
• SQL Server 2008 Upgrade Assistant
• SQL Server Best Practices Analyzer
• System Configuration Checker
• SQL Server Profiler
• SQL Server: Deprecated Features Object Counter
• Other tools

16. Error while upgrading to SQL server 2005 from Sql server 2000.

“while upgrading my sqlserver 2000 with version 8.0.2039 to SQLserver 2005, msxml6.msi failed to upgrade and while trying to uninstall it is try to look the msi file which missing from the path”

Ans:

MSXML 6 services installed in my server is a lower version of SP2 msxml6-KB954459 and not allowing the upgrade, because setup trying to upgrade MSXML6.MSI and it could not locate the file in the previous installation and Of course it got missed.

So, we have worked out here to download the same version from Microsoft website and then extracted the MSXML6.MSI file to some location and then we tried to un-install the MSXMl service and it went successfully.

Later, again we have installed the msxml6-KB954459-enu-x86.exe and upgrade of SQLserver 2000 to 2005 went smooth without issues and applied latest servicepack 4 successfully.

17. How to Upgrade to SQL SERVER 2005?

Ans:

• We can directly upgrade instances of SQL Server 2000 Service Pack 3 (SP3) or later, and instances of SQL Server 7.0 SP4 or later, to SQL Server 2005. We can perform most upgrade operations through Setup
• Before running Setup to upgrade to SQL Server 2005, we should first review system requirements and update Matrix
• Before beginning an upgrade
• All SQL Server database files will have to be backed up.
• Appropriate DBCC commands should be run to ensure consistent state.
• SQL Server System databases will have to be configured with autogrow setting to ensure that they will have adequate disk space.
• All startup procedures will have to be disabled; else they will block the process of upgrading.
• Replication log should be emptied and Replication will have to be stopped.

• Another factor that needs to be taken into account while preparing for an upgrade, are the features that have been deprecated in SQL Server 2005.
• Once all the above has been done, SQL Server 2005 setup has to be run and the instance installed.
• The upgrade Advisor does not get installed automatically when SQL Server is installed. It has to be installed separately.
• After reviewing system requirements and upgrade Matrix, run SQL Server Upgrade Advisor to analyze the instances of SQL Server 2000 and SQL Server 7.0.
• Upgrade Advisor produces lists of issues that we must fix before or after upgrading. SQL Server Setup will detect blocking issues that will prevent us from upgrading to SQL Server 2005 (The table alias should not be used in order by clause)
• Thereafter, move user databases to the instance by using backup and restore or detach and attach functionalities in SQL Server 2005. Then register the server, repopulate full text catalogs, update the statistics and run Surface Area Configuration tool. Change the compatibility level to 90 from 80

18. What are the issues u faced in sql server upgrade?

Ans:

Common causes

• SQL Server or the machine running the upgrade loses its network connection.
• The database in which you were working has run out of log or data space.
• You are not allowed to perform an update to a table.
• The database is corrupted.
• The database is not available (still in recovery.) It may be unavailable if the upgrade program begins to work before SQL Server finishes performing recovery after startup.
• Unable to restart the server

Can identify the issue from the upgrade log files and resolve the issues and rerun the upgrade advisor

19. What is the sequence to install service packs or hotfixes on an instance of SQL Server that is part of Log Shipping/Database Mirroring/Replication/Failover Clustering environment?

Ans:

When an instance of SQL Server is configured as part of Log Shipping, Database Mirroring, Replication, or Failover Clustering environment, it is important to install service packs or hotfixes in a correct sequence otherwise we may get unexpected issues.

Log Shipping:

There is no required sequence to apply a service pack or hotfix for Primary, Secondary and Monitor servers in a Log Shipping environment. The following is my preferable to apply service pack or hotfix:

• Apply the service pack or hotfix on the Monitor server.
• Apply the service pack or hotfix on the all Secondary servers.
• Apply the service pack or hotfix on the Primary server.

Database Mirroring:

If you install service packs or hotfixes on servers in a database mirroring environment, you need to determine the role of the servers. If there are many mirroring sessions configured on the server, you need to determine all possible roles that could be. For instance, if the server is acting as a mirror server for any database mirroring session, update the server as the mirror role for all mirroring sessions. To do this, follow these steps:

• If a witness server is configured in the database mirroring session, disable the automatic failover during the update process. To do this, remove the witness server from the mirroring session.
• If the safety level of the database mirroring session is OFF (asynchronous mode), change the safety level to FULL (this is required in step 3).
• Make sure all database mirroring sessions to be in Synchronous mode and synchronized.
• Pause the database mirroring sessions that are present on the server.
• Install the service pack or hotfix on the mirror server.
• Resume the database mirroring sessions.
• Perform manual failover (all the mirroring sessions on this principal server) to the mirror server so that mirroring server assumes the principal role.
• Pause the database mirroring sessions as step 4.
• Install the service pack or hotfix on the new mirror server (previous principal server).
• Resume the database mirroring sessions.
• If you changed the safety level in step 2, change the safety level back to OFF.
• If the database mirroring session has a witness server, undo the changes made in step 1.

Replication:

In a replication environment, there is no preferable sequence to apply service pack or hotfix for non-bidirectional replication typology. However, for bi-directional replication typology such as merge typology or transactional replication with updateable subscriptions, you must upgrade Distributor, Publisher, and Subscribers in the following order:

• Apply the service pack or hotfix on the Distributor server.
• Apply the service pack or hotfix on the Publisher server.
• Apply the service pack or hotfix on the Subscriber server.

 Failover Clustering:

In SQL Server 2005, if you want to install a service pack or hotfix, you must install the setup on the Active node (node that currently runs SQL Server services). When running the setup which will launch simultaneously “remote silence” on all passive nodes.

However, in SQL Server 2008 and SQL Server 2008 R2, the service pack or hotfix deployment is changed to reduce the downtime. Now, you must install the service pack or hotfix on the passive node first. To do this, following these steps:

• Apply the service pack or hotfix on the passive node (or all passive nodes if you have more than one).
• Reboot the passive node.
• Failover the SQL Server failover cluster to the passive node (and the passive node becomes active now).
• Apply the service pack or hotfix on the new passive node (previous active node).
• Reboot the passive node.