Migrate SQL Sever 2000 to PostgreSQL

Forums › IT Pro and developers › Migrate SQL Sever 2000 to PostgreSQL

8d52797c436

264 posts

Ultimate Geek
Inactive user

#42087 29-Sep-2009 09:11

Hi All,

At work we are moving from our Windows VB.Net/ASP.Net application with an SQL Server 2000 db to a Linux Mono environment with a PosgreSQL db. The problem is we have a ton of tables, stored procedures and data that we need to migrate from the SQL Server 2000 db to the PostgreWSQL db. We tried buying a cheap app to do this for us, but it's not working, so I was wondering if anyone has tried this before, what their results were, and if they have any recommendations for how we might do this.

Thanks in advance.

Ragnor

8287 posts

Uber Geek
+1 received by user: 591

Trusted

#259287 29-Sep-2009 10:00

I would be concerned that the application might be tightly coupled to the database platform, this is fairly common especially for older applications. What sort of data access layer is used in the application, is it using ADO.NET datasets/data readers or is it using an ORM (Object relational mapper) tool? Is alot of business logic written into stored proceedures or is it in code?

Basically changing the database platform for an existing application is often a bad idea unless you are rewriting it or doing significant manual development work to resolve differences and make sure it all works. There are many differences between in syntax and features and automated solutions will not get it right.

I presume you're trying to get rid of an old windows server from your environment, can you run this server and database in a VM or something for the rest of the life of the application (until you redo it on your new platform)?

8d52797c436

264 posts

Ultimate Geek
Inactive user

#259343 29-Sep-2009 12:52

Hi Ragnor,

Basically our plan at the moment is to move from being Windows dependant to being able to run on Ubuntu on EC2 as we need the scalability that the cloud offers as we move into overseas markets.

Our application uses ADO.Net with datasets and datareaders, which it is possible to use NpgSQL as an alternative when moving to Mono. A lot of the business logic is in stored procedures, of which we have a few hundred.

We are just trying to do a proof of concept at the moment for a presentation showing that our system can be moved to the cloud without increasing the cost significantly (A server running windows on the cloud costs approx. $1.14US per hour compared with $0.11US per hour for Ubuntu), so it isnt really possible for us to run the old db in a VM as far as I can tell.

We'll be doing a lot of manual work to test that everything is working. The data itself does not need to be moved across, just the schema, functions and stored procedures. It looks like it's going to be a huge amount of work, but it will still work out cheaper than moving our db from sql server 2000 to sql server 2008 and having to pay the huge amounts to run a windows based site on EC2.

wazzageek

1096 posts

Uber Geek
+1 received by user: 110

ID Verified

Trusted

Lifetime subscriber

#259345 29-Sep-2009 13:00

I've heard of solutions involving linked tables (think ODBC driver back to postgres / db of choice, table linked to ODBC presentation).

Of course, in this solution, I would suggest that speed and reliability would be quite ... poor.

This might help with a direction, but YMMV.

nate

6473 posts

Uber Geek
+1 received by user: 458

Retired Mod

Trusted

Lifetime subscriber

#259488 29-Sep-2009 20:15

Ragnor: Basically changing the database platform for an existing application is often a bad idea unless you are rewriting it or doing significant manual development work to resolve differences and make sure it all works. There are many differences between in syntax and features and automated solutions will not get it right.

+1. A program won't be able to get this right, you'd be best to rewrite from scratch in the new environment.

freitasm

BDFL - Memuneh
80955 posts

Uber Geek
+1 received by user: 41741

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#259493 29-Sep-2009 20:22

What would worry me most in this scenario is the amount of unit testing, integration testing and regression testing required to certify the new database provides the application with 100% functionality as before.

How much this testing is going to cost? Do you have the resources (people, staging server, proper testing tools) available for this?

Referral links: Quic Broadband (free setup code: R587125ERQ6VE) | Samsung | AliExpress | Wise | Sharesies

Support Geekzone by subscribing (browse ads-free), or making a one-off or recurring donation through PressPatron.

Ragnor

8287 posts

Uber Geek
+1 received by user: 591

Trusted

#259499 29-Sep-2009 20:41

I kind of agree if the application is small enough functionality-wise and you really want to move to a FOSS platform + cloud based infrastructure you should just rewrite it in rails or zend or something and make use of features of the DB and application platforms.

Unless you're google, facebook or digg you probably don't really need a cloud based solution though.

Alternatively if you want examples of the Microsoft platform handling very http high requests per second for a database backed web application have a look at Stackoverflow.com (they used only 2 servers originally).

http://highscalability.com/stack-overflow-architecture
http://www.codinghorror.com/blog/archives/001195.html

Even more alternatively here's an article on scaling asp.net mvc using ServerPath/GoGrid which is apparently much cheaper than Amazon EC
http://www.emadibrahim.com/2009/08/05/startup-asp-net-mvc-cloud-scale-deployment/

Geekzone

Want to support Geekzone and browse the site without the ads? Subscribe to Geekzone now (monthly, annual and lifetime options).

8d52797c436

264 posts

Ultimate Geek
Inactive user

#259500 29-Sep-2009 20:42

Yeah, it is really worrying. In terms of the database I am actually leaning towards having it manually migrated. In terms of the code, a lot of things I have read have said that it shouldnt be too difficult to migrate from VB.Net/ASP.Net to Mono, but even that I think will be a massive task.

I just have to hope we can get a basic version going as soon as possible, or I may have to talk to my boss about sticking with Windows for now (although the long term costs of Windows are incredibly high). We were looking at getting a few students to work for us over summer to work on the conversion, but the problem is we dont want to invest a huge amount into this project when we still dont have the contracts. I'll see if one of the other guys in the office is able to dedicate a bit of time to manually migrating the db over, but it seems it's a bigger job than the code port.