Sql-server – an advantage or disadvantage for not upgrading database server

sql serversql-server-2000upgrade

I have a legacy database server. The person who wrote it is long gone. There is no documentation at all just some comments in the code. Luckily, it is not that complicate but if I want to fix it (requests from users), it takes time to investigate all related database objects (tables, views, or stored procedures).

Recently, I received suggestions from our IT departments person that I should upgrade our database to a newer database version (2005/2008). Right now, it is SQL Server 2000. He said it will have better performance and security. I think if I upgrade to newer version, our old box (hardware) may not powerful enough for new database engine. Currently, according to users, the response time is acceptable.

I try to think all pro(s) and con(s). I prefer for not upgrade but I want to make sure that I don't miss anything.

Not upgrade

Better ROI (not pay for everything)

Upgrade

Pay for new server/license
Redo some works to get the same functionality
Better performance/security

Question is: What should I do? Upgrade or Not upgrade our database server.

Best Answer

If you don't:

it will be unmaintainable at some point because of OS, version, patch, whatever
hardware failure may force an upgrade at the wrong time

What you gain:

older code can be simplified with new constructs (eg ROW_NUMBER)
far better error handling (TRY/CATCH)
engine improvements: most queries will run quicker on a later version

MS don't support it anymore either

Related Solutions

Sql-server – Does installing SQL Server Database Engine and Reporting Services in Azure require two virtual machines

To purely answer your question:

If you set up two different VMs one running the SQL Server and the other one hosting the SQL Server Reporting Services, you would be charged twice the per-hour price of a Virtual Machine, but only once for your the SQL Server License.

Best Practice

I guess the "best practice" answer would be to rely on SQL Azure and SQL Azure Reporting Services, since they provide licensing, installation, maintenance, redundancy, scalability and security built-in.

But since this doesn't seem to be an option to you, here is what I would do based on my experience:

Put the database that hosts the data the reports are built on in SQL Azure. There you can restrict access to the database to only other Windows Azure components (Websites, Hosted Services, VMs) or a given IP-Range
Setup the SQL Server Reporting Services on a Virtual Machine (not a Hosted Service), because Hosted Services lack data persistence
Host your web application on Azure Websites (they are for free at the moment). You can later switch to Hosted Services if you need more performance.

Hope this answer helps.

Sql-server – How to attach an .mdf file that was not properly detached

It sounds like the database is corrupt. Before you do anything further, make a copy of the files, and only work with copies.

You might try the following, in case the problem is isolated to the log:

CREATE DATABASE [GRMStemp]
  ON (FILENAME = 'c:\wherever\MDF_File.mdf')
  FOR ATTACH_REBUILD_LOG;

If that fails, there is a forum post on MSDN by Paul Randal which describes a process that might work. Basically:

on your 2008 instance, create a database with a data file of the exact same size (I wouldn't bother trying to put a 2000 instance anywhere)
shutdown SQL Server
rename the new database's mdf file, and paste the old one in its place
bring up the server and let the database attempt to be recovered and then go into suspect mode
put the database into emergency mode with ALTER DATABASE GRMStemp SET EMERGENCY;
run DBCC CHECKDB (GRMStemp, REPAIR_ALLOW_DATA_LOSS); which will rebuild the log and run full repair

BTW If the client has no idea what was in the database, and wasn't responsible enough to maintain proper backups, how important could the data be?

Related Question