A bit of nostalgia today. I decided to install a VMware instance with Windows Server 2003 x86 and SQL Server 2000 x86. I will use it to prepare script for my co-workers in the new project where we shall migrate work in a project where 6 system are running on this configuration. It’s over 4 year since I worked with SQL Server 2000 on regular basis, and I need a servere where I can try out thing on my own. This VM instance can also be used my collegues for testing legacy systems.
Microsoft support and liftcycle
Microsoft does not support SQL Server 2000 anymore. The “Extended Support” expired April 9th 2013. Windows Server 2003 is also in “Extended Support” and will expire July 14th 2015. Here is a summary of the lifecycle for Windows Server 2003 and SQL Server 2000.
- Mainstream Support: 8-APR-2008
- Extended Support: 9-APR-2013
- Mainstream Support: 13-JUL-2010
- Extended Support: 14-JUL-2015
It is not decided what SQL Server version we shall use in the target enviroment, but there are several options. The first option is to migrate the server “as-is”. This mean create a new server instance in the target enviroment with Windows Server 2003 and SQL Server 2000. This option involve less risk for the project, and the customer can use current licences for OS and DB. But the customer will have to upgrade the system in the near future if they want support from Microsoft if something happens.
A second option, is to install a new server with a newer version of Windows Server and and SQL Server and run the database in “Compability Level”. The different versions of SQL server supports 3 version back. This can be archieved by using the “ALTER DATABASE Compatibility Level” command. This options work pretty well, and I have used it for several systems where the customer does not want to take the risk of fully upgrading the application.
The third option is to perform a full upgrade of the server architecture and fully convert the application to a newer SQL Server version and take advantage of new fatures. This often require more time to complete, but the end-users and database administrators will most likely get a better application experience in their daily work.
SQL Server is a very I/O intensive application. As a result we should seperate the data and log files for the SQL server. I haven’t described any pre-installation tasks as I did for the “SQL Server 2012 installation” post which included tasks such as definition of service account, measuring disk performance and serveral other tasks that is important when you install SQL Server 2012.
The first “real” screen let you choose on which server the installation should be performed. I have never installed SQL Server 2000 with other than “Local Server”, and have no intention to try out the other two options 🙂
The second screen let you create a new database instance, upgrade an extisting or advanced installation with clustering. I have chosen a standard new default installation for this case.
The next screen is just a where you put your name or department together with the company name that owns the licence to the SQL Server.
The next screen let you choose how detailed the installation will be.. You can choose to only install client tools that let you connect to a SQL Server 2000 server instance through a common GUI interface such as Enterprise Manager and Query Analyser. The second choice is the full installation. The third choice is just drivers and APIs/SDKs for connection to a SQL Server through other development tools such as Visual Studio.
The following screen let you decide whether to install on the default instance or to create a new named instance. The most common option is to choose the default instance. But an example, is to create two instances on the same server – one for test and one for development.
The next few screens shows the different “Size” options – typical, Minimum and Custom, all with different disk space requirement.
If you select the custom installation, the next screen let you choose which components you want to install, including extra components as documentation, examples and development tools.
The next screen let you define service account for the different components on SQL Server 2000. It is normal to create one account for each service, but it is possible to use the local system account for installation purpose, and define the service accounts when configuring the server properties in the post-install tasks. This is the case for this post, but the service accounts are defined in similar manner as for SQL Server 2012.
The next screen let you define how clients should be able to connect to the SQL Server, which supports 2 modes – Windows Authentication and Mixed Mode. This work equally as the other versions (for example SQL Server 2012). When you choose Mixed Mode, you will define a password for the sa system administrator accounts.
The next screen defines the server collation that determine the character set that is commonly used on the server and how string should be sorted and how different datatypes should be displayed to the clients.
The next screen defined the licence mode for the SQL Server. It is possible to choose “Per Seat” (client) or “Processor Licence” (Pr. CPU/Core).
When the installation is completed, you will have to install some service packs since the SQL Server is pretty old. In my case, the SP2 was included on the installation media, and I have to install the SP3 and SP4 manually. Here is a link for latest service packs for SQL Server 2000.
Finally done…just some configuration, setting up backup and maintenance tasks, and I’m ready for my last crusade with SQL Server 2000. This will probably last for a while, but hopefully, the customer will see the need for an instant upgrade project when the current migration project is completed.