From Oracle to SQL Server

[a_bertrand]

Since a couple of weeks I'm working on a big project:
Migrating all our Oracle data and applications to SQL Server.

This was triggered by a huge number of reasons which simply staked up over time... and time came to do the switch. As advantage of the switch: we will have a true DB cluster with automatic fail-over where the computing group wasn't able to provide us the same on Oracle (yes it's doable, but it's a LOT more expensive).

We will also have a lot better integration with visual studio, and as this is my main tool for the development it will saves me load of time.

However migrating from one DB to the other is not a piece of cake, even in today world with technologies like LINQ. Why? Because unless you develop without using any of the DB features (not even sequence which can be used as the auto_increment on Oracle), views or stored procedure, well... you will have to do some manual work to port those things from one DB to the other. Of course porting the data can be painful / long as well.

Finally, some softs of mine are from 2008, where I didn't had access to LINQ for Oracle, which means, I developed with the old Database classes. There I had to even rewrite the code to use LINQ to SQL.

Anyhow, the first soft migration is done (quite some left to do), and I can tell you, we will have a much better service with SQL Server for a much cheaper price (we don't need to pay any additional license as we have a volume license with Microsoft). Even performance wise MS SQL seems faster.

Lesson: avoid Oracle if possible

[Chris]

Did Oracle go to paid mode? I read somewhere that they were going to make the switch...

[a_bertrand]

Oracle was NEVER free. We had to pay around 10'000$ per year the license and with that no cluster.

[Chris]

But MySQL is still free right? So is CentOS and a lot of other Linux distributions.

MS SQL claim they can handle more requests, it might be slightly true, that would be more down to the hardware you are running on. And there are solutions like Memcahced which are also free to help speed up your data requests.

[a_bertrand]

MySQL and Oracle DB are 2 different software. Completely different. MySQL is a fun little DB, but remains in my eyes a toy and I would not use it here for my work. Also it lacks a lot of the features I would require to work with.

Oracle Database on the other side is big old boy, offering loads of different features and setup.

[Jackolantern]

Most devs I know don't have very good things to say about Oracle. I have never used it myself, so I will reserve judgement. But I have heard it is quite expensive for what it offers.

[a_bertrand]

It's extremely expensive (now) for what it offers. Also, the installation of the clients, server, and tools are ALWAYS a pain. However if you have it running, then I would say the DB is quite solid, it offers pretty good performances too. However everything starts to be really dated, out-priced and the support is not up to par anymore.

SQL Server may seems at first yet another toy from Microsoft (as there is many from them too), however I must admit I'm surprised by how well it is, how easy it is to setup a true fail-over cluster or even have replications for load balancing. Stored procedure / functions can be written either in TSQL or Managed languages like .NET. Sadly no LINQ is available while you are in a stored procedure / function.

On the other side, there is GREAT debug options even for SQL queries, as well as incredible analyzer tools directly integrated within Visual Studio. You can for example detect that your query doesn't use an index, it tells you what to do to improve your table design and therefore improve your performances.

BTW SQL Server starts free and offers nearly the same options as the payed versions. So I would strongly suggest anyone working on windows and ideally using .NET to try it.

[Jackolantern]

However if you have it running, then I would say the DB is quite solid, it offers pretty good performances too.

Wouldn't this have to be a given for an expensive DB solution? A db that is unstable or has poor performance shouldn't be sold for $10, much less thousands

As far as SQL Server goes, I have used the lower-grade editions before, and it is definitely quite capable. Its integration with Visual Studio is very nice, even though you can get nearly the same integration with almost any other DB provided you install the adapters (of course I just mean in the IDE; LINQ and other actual language and platform features is another matter). Visual Studio is sometimes too smart for itself and forgets it is supposed to be on Microsoft's team lol.

I know that as far as the major commercial DBs go (Oracle, SQL Server, DB2), it is on the lower end of the price scale, but still holds-up well.

[a_bertrand]

The migration finally has been done for the main software, around 110 tables have been migrated, 20 views and 30 functions. Overall it was quite painful, but I'm sure it will pay off.

Performances wise, we should be still about the same, even if now the disk is not anymore locally on the machine, but instead over the network via iSCSI. The servers are also MUCH smaller

Thanks to LINQ the work on the code side was really small, otherwise it would have been mission impossible.

[Jackolantern]

Good to hear it went well! Maybe after a few months, a nice write-up on the differences and your feelings towards both would be good since you would have a nice perspective of using both RDBMSs for the same database.