Migrating from “MSSQL Server” To Another SQL Server

Question

I have an application that requires this older version of "MSSQL Server". Is there any chance I could seamlessly upgrade the DBMS to another (preferably free) product?

It's having alot of "Out of hard drive space" messages and the application that uses it is crashing more and more.

Thanks for the advice!

Answer 1

Besides the comment from GregD, it maybe the host that is out of space.

You could update your question with the following information:

• SQL Server Version?
• Host HDD Partitioning and drive space?
• Size of the SQL Server data files and log files?
• Maintenance plans that you have?

It may just be that you need to truncate your log file if you are using a simple recovery method.

As for moving to a more modern SQL version, you will need to setup a test lab first then try the application against SQL Expr 2005 or 2008. I would also put the database into compatibility mode to ensure even more compatibility.

You may be in a position where you cant put the app against another version as it may be using stored procedures or views that only exist in the MSSQL Server version that it is linked to.

If you can't upgrade the database and you are indeed having resource issues you would need to investigate the following:

• Upgrade the system by adding more HDD space
• Add a small SAN to the site and attach to the system. Move the SQL Database to the SAN for improved performance
• Upgrade the system by replacing the metal that it runs on
• Consider consolidating the service by thinking about Virtualisation if excessive IO/Memory is not required

After you have done analysis and have some factual data in front of you can you then start making recommendations to the appropriate persons on how best to move forward and ensure this resource is available for the foreseeable future.

Answer 2

So, if you never back up an SQL Server it uses space endlessly as the transaction logs grow and grow. Have you tried backing it up? A backup causes a checkpoint, which lets SQL server know it doesn't need to keep certain transaction log files.

You might need to back it up to a remote system if the drive is too full. Remember, the backup will be written by the Windows account that SQL is running as, not your user account - so that user needs to have access to the network share you are attempting to back up to.

For your reference: http://technet.microsoft.com/en-us/library/ms189085.aspx

There are other reasons for MSSQL file growth, of course, including insertion of data. ;) If that's not it however, you can look at things like transactions that aren't ever committed.

The answer to your stated question about changing from MSSQL to some other platform is that it depends. If you are referring to an app that already supports multiple DB back ends, then it can be as easy as dumping your data to some universal format, then importing it in to the db platform you prefer, then switching your app to use your preferred DB - but in most cases it is not this simple. In most cases, the application is tightly coupled to the RDBMS platform and it's not as simple to "change back ends" without re-writing significant parts of the code.

Answer 3

Depending on the complexity of your app, you could update it to use SQL Server 2008 Express, which is free.

Answer 4

You could probably use SQuirreL java SQL client to do a export of the db and then import it into another db. Its a little bit of a learning curve to do that though... i'd say the transaction log/backup idea is much better...