This article discusses the necessary preparation required before upgrading an existing Microsoft SQL Server 2000 environment to Microsoft SQL Server 2005.

Introduction

With the release of Microsoft SQL Server 2005, new features have been introduced and existing features have been enhanced to improve performance, security and manageability. Some of these changes may affect existing applications. This article discusses the necessary preparation required to successfully upgrade an existing Microsoft SQL Server 2000 environment to Microsoft SQL Server 2005.

SQL Server Compatibility

The following table summarizes Microsoft SQL Server compatibility with the various version of K2.net 2003.
Note: Versions of K2.net 2003 prior to SP3 do not support Microsoft SQL Server 2005. This is an important factor to consider before upgrading the SQL Server environment.
K2.net 2003 ReleaseSQL Server 2000SQL Server 2005
K2.net 2003 RTM
K2.net 2003 Hotfix1
K2.net 2003 Hotfix2
K2.net 2003 with SP1
K2.net 2003 with SP1a
K2.net 2003 with SP2
K2.net 2003 with SP2a
K2.net 2003 with SP3 (and later)
 
Microsoft SQL Server 2005 Upgrade Advisor
The Upgrade Advisor is a utility that ships with the Microsoft SQL Server 2005 installation. Use this utility to plan the upgrade of an existing SQL environment.
 
The Upgrade Advisor consists of:
  • Upgrade Advisor Analysis Wizard: The wizard examines the existing instances of SQL Server for issues that can prevent the upgrade process or issues that may impact on other applications from functioning correctly after the upgrade. The wizard will prevent the upgrade if any issues were discovered during the examination of the environment.
  • Upgrade Advisor Report Viewer: The report viewer displays the results of the analysis. Use the report to view a list of issues found during the analysis, to manage task associated with the list, and to access documentation that can help resolving the issues.
Consult the Microsoft SQL Server 2005 documentation for more information on installing and working with the Microsoft SQL Server 2005 Upgrade Advisor.
 
Analyzing the K2.net Transaction and Log databases
Discussed in this section are the results that were observed after analyzing the K2.net 2003 (pre SP3) Databases with the Microsoft SQL Server 2005 Upgrade Advisor. Recommendations made in this article are based on the results obtained when using the Upgrade Advisor. This section assumes that the Microsoft SQL Server 2005 Upgrade Advisor has already been installed on the system.
Note: For demonstration purposes, the SQL Server 2005 Upgrade Advisor was configured to analyze the K2.net 2003 databases only.

 
Analyzing K2.net 2003 with SP2a databases
The following image is a screen capture of warnings reported by the Upgrade Advisor related to K2.net:

  

Important: No action is required to correct this condition, upgrading the existing K2.net environment to K2.net 2003 with SP3 will resolve the condition.
Analyzing K2.net 2003 with SP3 databases
K2.net 2003 with SP3 is currently the only version of K2.net 2003 that is compatible or supports SQL Server 2005.

Database changes have been incorporated with SP3 to ensure compatibility with SQL Server 2005. This includes the issues reported by the Upgrade Advisor in the previous section. No issues related to K2.net have been detected by the Upgrade Advisor during the analysis of the K2.net databases.
 
Installation and Upgrade scenarios
The following section highlights some common scenarios encountered when performing K2.net installations as well as upgrading SQL Server environments. Select the scenario that best suits your organization's requirements and follow the recommendations for the scenario.

Scenario 1:
New K2.net 2003 SP3 installation
  • Upgrade Microsoft SQL Server 2000 to Microsoft SQL Server 2005. Refer to the SQL Server 2005 installation documentation for more information
  • Install K2.net 2003 with SP3. Refer to the K2.net 2003 installation documentation for more information
Scenario 2:
Existing K2.net 2003 (pre SP3) installation with Microsoft SQL 2000 as the database server
  • Upgrade K2.net 2003 (pre SP3) to K2.net 2003 with SP3. Refer to the K2.net 2003 installation documentation for more information
  • Upgrade Microsoft SQL Server 2000 to Microsoft SQL Server 2005. Refer to the SQL Server 2005 installation documentation for more information
Important: Stop the K2.net 2003 Server service "Click on Start > Administrative Tools > Services". Locate the K2.net 2003 Server service, right click on the service and then select Stop from the menu.
Note: For the upgrade, change the Startup Type of the K2.net service to "Manual". This ensures that the service does start automatically when the system requires a restart during the upgrade process.
Scenario 3:
Porting existing K2.net 2003 Databases from Microsoft SQL Server 2000 to Microsoft SQL Server 2005.
  • Upgrade K2.net 2003 (pre SP3) to K2.net 2003 with SP3. Refer to the K2.net 2003 installation documentation for more information
  • Do a complete database backup of the existing K2.net Transaction and Log databases on Microsoft SQL Server 2000. Refer to the Microsoft SQL Server documentation for more information
  • Restore the database backups made in the previous step on Microsoft SQL Server 2005. Refer to the Microsoft SQL Server documentation for more information
Important: Stop the K2.net 2003 Server service "Click on Start > Administrative Tools > Services". Locate the K2.net 2003 Server service, right click on the service and then select Stop from the menu.
Update the K2.net 2003 Server database configuration:
  • Open the K2.net Server configuration file (K2Server.config) located in the K2.net Bin directory (default location: ”C:\Program Files\K2.net 2003\Bin”)
  • Locate the “Database ConnectionString” and “LogDatabase ConnectionString” key

--- SQL Authentication ---

 <Database ConnectionString="server=SQLServerName;database=K2;uid=sa;pwd=mypassword;pooling=true;enlist=false;Min Pool Size=5" />
<LogDatabase ConnectionString="server= SQLServerName;database=K2Log;uid=sa;pwd= mypassword;pooling=true;enlist=false;Min Pool Size=20" />

--- Integrated Windows Authentication ---

<Database ConnectionString="server= SQLServerName;database=K2;Integrated Security=SSPI;pooling=true;enlist=false;Min Pool Size=5" />
<LogDatabase ConnectionString="server= SQLServerName;database=K2Log;Integrated Security=SSPI;pooling=true;enlist=false;Min Pool Size=5" />
 
  • Update the connection string information for both the K2.net Transaction and Log database
Update the K2.net 2003 Workspace database configuration:
  • Open the K2.net Workspace configuration file (Web.config) located in the K2.net Workspace Service directory (default location: ”C:\Program Files\K2.net 2003\K2WS\WorkspaceService”)
  • Locate the “DSN” key

--- SQL Authentication ---

<add key="DSN" value="Data Source=SQLServerName; initial catalog=K2Log; User ID=sa;Password=mypassword" />

 

--- Integrated Windows Authentication ---

<add key="DSN" value="Data Source=SQLServerName; initial catalog=K2Log; Integrated Security=SSPI" />
 
  • Update the connection string information for K2.net Log database