Right vs. better
If you’re building a Microsoft .NET network and you have an Oracle database, you should migrate it to SQL Server. It is not a question of which is better but of which one is the right tool for you. In the case of a .NET architecture, the answer to both questions is a .NET server. In this article, we explore the reasons why there may be an Oracle server in your network, what it will take to move it to SQL Server, and what you will gain by the move.
Seek the Oracle
If there is an Oracle server on your network, you’ll need to look at why it’s there—who uses it, what applications reference it, what applications are running on it, and so on.
Who uses it?
One of the first things you’ll find out if you just unplug the server is who uses it. That’s a bad method, though, so you’ll probably want to find out who the users are in some other way.
Network administrators may have monitoring or logging processes that use it. Developers may be developing applications based on the current server. Managers may be drawing reports from its data or making enterprise decisions based on an Oracle back-end. Any user can, potentially, be anywhere in the world. You must consider all these possibilities when determining who will be affected by the migration away from Oracle and onto SQL Server.
What applications reference it?
After you ask the users, “Who uses Oracle?” and all of them tell you, “Not me,” you’ll want to look at the logs to see what workstations are accessing it. When you check these logs, you’ll probably find that more than just workstations access the database: Other servers access it too.
Make note of the servers that are accessing the database and then find out which specific applications on those servers are accessing it. You can probably determine those applications by comparing the data stored in the tables to the applications that are running on the server.
What applications are running on it?
Now that you know what users and external applications are accessing the Oracle database, you need to find out what applications are running on the database server itself. These applications could be in the form of stored procedures in a database (with accompanying triggers, custom data types, and security settings), or they could be stand-alone applications that don’t run within Oracle. Particularly, you should take note of any Oracle developer tools that have been added onto the server.
Migrating to SQL Server
You shouldn’t just unplug Oracle and plug in SQL Server an hour later; a critical server requires a more graceful transition. That’s why the process is called migration—it doesn’t happen all at once. If you follow a few simple steps, the transition can go over without any snags.
Local and external applications
To migrate applications, follow these steps:
1. Plug in a new SQL Server.
2. Create “devices” and build tables for use by the applications.
3. Take the application offline by disallowing applications’ access to it.
4. Copy the current data from Oracle to SQL Server.
5. Point all the applications at the new database.
6. Allow applications access to the new data tables and devices
SQL considerations There’s one major “gotcha” in moving between SQL Server and Oracle: They speak different dialects of SQL—PL/SQL (Oracle) and Transact-SQL (Microsoft).
For the most part, if you can use one, you can use the other. However, I keep a copy of The SQL Programmer’s Reference handy because it defines all the SQL functions, operators, statements, and so on—and it shows which DBMSs support which features. Of course, SQL issues wouldn’t be such a big deal if both of these American vendors of SQL products complied with the American standard for SQL (ANSI-SQL).
You could run into a couple of snags if you try to run SQL for one kind of server on the other kind:
Depending on the versions of the two servers that you’re dealing with, other migration issues regarding SQL syntax are almost certain to crop up.
A business case against Oracle
At the risk of starting an emotional fight, there is a business case to be made for selecting the Microsoft product over the Oracle one. Oracle spends a lot of resources fighting Microsoft in court and the press. From an economic perspective, it is not very productive. In addition, Oracle has only one core product, “Oracle”—the company is a one-trick pony. Add to that a complex sales process, and you have a company with three strikes against it. As healthy as the company seems to be, and as popular as its product is, those are three very strong indicators that the company could be a has-been in only a few years. It has happened before to stronger companies.
What you gain by migrating to SQL Server
The first, most important thing you gain is uniformity in your network architecture. You don’t have UNIX people managing Windows systems or vice versa. Management tools built for the DBMS will work well with those for the NOS.
You also gain native support for your .NET applications. The .NET architecture does not demand that you use SQL Server, but it’s the default. The ODBC Drivers for Oracle are good, but they’re just one more potential point of failure.
You have a lower cost with SQL Server than with Oracle in a .NET network. When you add the purchase of your Win2K Server licenses and your VS.NET and Office licenses, you’ll probably get it cheaper than if you bought it stand-alone.
Even if you buy a stand-alone copy of SQL Server, it’ll probably be cheaper than Oracle. Until recently, getting a price for an Oracle license was like pulling teeth. I remember asking one rep “How much?” and he actually said, “What can you afford?”
Summary
If you’re building a .NET network, it makes sense to use SQL Server as your DBMS because it is a core component of Microsoft’s suite of .NET server applications. Migrating from one platform to the other is an important process that must be handled gracefully. When you’ve made the switch, you’ll have a uniform, efficient, easily managed, and reliable .NET foundation on which to build.