SQL Server review

C++, C#, Java, PHP, ect...
Post Reply
User avatar
a_bertrand
Posts: 1537
Joined: Mon Feb 25, 2013 1:46 pm

SQL Server review

Post by a_bertrand »

Hi,

As I said a couple of weeks back, I migrated my softwares from Oracle to SQL Server, and I will now here summarize my experience. Keep in mind it's not a flaming contest and I'm not saying everyone should jump on SQL server and dump Oracle.

Anyhow here is how it went:
I was using LinqConnect (from Devart.com) to connect to Oracle and use the LINQ framework, therefore I didn't had to rewrite most of my code as it would have been if SQL statements would have been scattered around. I still had some smallish issues where SQL server do react differently than Oracle, but overall, for a software which is not all that small:
solution.jpg
Having to spend only about 1 week of work on the code for the migration I would say it went very well.

Now how did I proceed? Well, the LINQ provided allowed me to switch to SQL server, and then generate the DDL (SQL create table...) to create the base SQL for the database. Doing it manually for the around 110 different tables, foreign links and all, would have been painful and error prone. Of course the generated SQL have been manually checked and some stuff like the IDs changed. On Oracle I had to have sequences (a feature which gives you a new number each time you call it) and triggers to set it to the new row (triggers are functions you write inside the DB which will be run by the DB at some given time, for example each time you insert a new row in that table), on SQL server you can have the MySQL equivalent of AUTO_INCREMENT if you define a column as IDENTITY, much simpler.

Once the main structure has been ported, I made some small C# code, with 2 LINQ (one for Oracle and the other for SQL Server), and reading on one side while writing on the other. There is pre-made tools which would have allowed me to do it automatically like "dbForge Studio" or others, but doing it via some self done software would allow me to change some stuff as I would want to.

Data migration done, remains to rewrite all the views and stored functions by hand. Here sadly no converters would do the work for me, so that took me around 2 weeks to fully port my DB logic. I can't say T-SQL (the language you need to use on SQL Server for stored procedures and such) is more comfortable than PL/SQL (the language you use with Oracle). Some times T-SQL offers things PL/SQL don't, and some times it's the reverse, overall however I was able to convert all my stuff and get it running.

The SQL server itself as been set as a 2 nodes fail over cluster on the same small machines I was using for IIS (the web server). Sure it's certainly NOT what is recommended but oddly enough, our setup which is about 5x cheaper as the setup we had for Oracle actually runs with nearly the same performances. For the cluster solution we used an iSCSI disk (disk over the network) shared across the 2 nodes.

On Oracle the "experts" here said they didn't had the experience / skills to setup a full fail over solution, while it took us like an afternoon for us to setup a SQL server fail over solution. Really not complex.

Security wise, we decided to limit the accessibility of the "sa" account (root account) to a set of computers, to do so we had to write a trigger and create a small table to define which account is limited on which IP. That took a bit more time, as we had some odd issues like when the SQL server was starting a login would have been triggered yet the tables would not be ready and therefore the trigger and login would fail. We had therefore to dig a bit and put some if inside to detect such conditions. Anyhow after this, I can tell you here again it's safer than what we had before as we can decide per IP who can access the database.

For the backup, we decided to go on the cheap road again, and use what SQL server offers natively. Basically inside SQL server you can defines jobs which will run at specified time, and there run T-SQL or cmd commands. We decided to make a weekly full backup of the different databases and a daily incremental backup the remaining days. That's stored on the iSCSI drive too in a different directory. Then every day there is a "mirror" of that directory to a remote shared drive which ensure us 3 different copies of the same data (live, backup, and copy of the backup in a remote location). Cost? Nothing beside some time for the setup. Yes we already tested the restore ;) Just to give an idea, 1 week is about 17 Gb of data. After 30 days the oldest files will be deleted.

Now I will say what SQL Server gave us as advantages (beside the cluster due to the lack of knowledge of the previous dba, and the price):

Inside the server installation you have a few useful tools coming, like the "SQL Server Profiler" which let you basically sniff what kind of SQL statements are received by the server while you are doing this operation. This let you spot slow queries while they come in, and then with the help of the "execution plans" (I will explain it here after) you can debug your queries and try to improve them. I must admit it takes some times to master this "Server Profiler" as the first time you will get all and nothing out of it.

When you do some complex select (like with multiple joins, conditions and more) the execution plans let you understand what the database engine is actually doing under, if it is using one or more indexes or if actually it's not and it's scanning the whole table. This can be the tool which let you solve slow queries. The exection plans of the query was already available in Oracle, so you could say I didn't gained anything, well sadly what I had so far with Oracle wasn't really all that helpful. In comparison SQL server offers out of the box something which give you a diagram of your query and tell you in case you need an index (it even gives you the full statement to create the missing index):
exec_plan.jpg
I could not talk about SQL server without talking about the integration within Visual Studio which is one of the major points for me. This changes my life, honestly, and just for that maybe the switch would have made sense. Of course it make sense if you use Visual Studio, if not then... no points ;)

For the user point of view of the applications, nothing has been visible. For them, it works as before, and they didn't even saw any difference between the data, the interface or the performances.

For us the developers, it's really the night and day, where Oracle was working, SQL server is actually shining as we have a lot more tools, a lot more options, and the life is overall easier. Again this is due to our development style: we work mainly with Window server and Visual Studio. For the linux integration (as yes we do use linux as well), we wrote some simple web services and the python scripts now pump the data through it. No more need of oracle clients on all the machines, and that's again a lot easier for us.

I would say, I talked enough for a first review, however if you have more specific questions, please ask and I will try to answer you. However overall we are all really happy about the move. In most cases we gained a lot out of it, and for our customers they certainly didn't lost anything.
Creator of Dot World Maker
Mad programmer and annoying composer
User avatar
Jackolantern
Posts: 10893
Joined: Wed Jul 01, 2009 11:00 pm

Re: SQL Server review

Post by Jackolantern »

Thanks for the write-up!

It definitely seems like SQL Server is the solution to use if you are a .NET company, and that should be no surprise (like you said). I believe after Oracle bought Java that really Oracle's database went the other direction, and is at least trying to become the DB to beat if you are a Java company. However, they have a lot of MySQL culture to overcome with Java. I can only imagine that was one of Oracle's main reasons for buying Sun and Java out.
The indelible lord of tl;dr
User avatar
a_bertrand
Posts: 1537
Joined: Mon Feb 25, 2013 1:46 pm

Re: SQL Server review

Post by a_bertrand »

Well.... MySQL is all good and fun, but I would never consider it up to par with either Oracle or SQL Server. Sure it's free, (even if there was some commercial solutions), but sadly you don't have all the features you could expect from a good database. Yes you can make nice applications with it, but you would save a lot of time and efforts using something a bit more professional.

For the Oracle business, I would say, they tend to stagnate, and are not really up to par in many areas. Yes their DBs are solid, and you can trust them, however, installation, maintenance, and even client (driver) installation are ALWAYS a pain. No matter on which platform you use their products it is like doing vodoo and hoping it will work at the end of the day.
Creator of Dot World Maker
Mad programmer and annoying composer
Post Reply

Return to “Coding”