If you are building a Microsoft .NET network and the backend is running an Oracle database, then you should migrate the backend to SQL Server. The core of this question is not to compare database performance but to find the tool that is best for you. To answer these two questions under the .NET architecture, there is only one answer, and that is .NET Server. In this article, we first explore why you have an Oracle server in your network, then discuss how to migrate it to SQL Server, and finally explain the pros and cons of this move.
Oracle in your system
If you have an Oracle server on your network, you need to figure out why you need it – who is using it, what applications are using it, what applications are running on it, etc.
Who is using it?
First you should find out who is using the Oracle server. Otherwise, rushing to move the server before you have an answer is likely to lead to a big mistake. Of course, if you really want to do this, it is a quick way to find the database user. But we still advise you not to do it.
Network administrators may have procedures in place to monitor or log Oracle usage. Developers may want to use current servers to develop applications. Managers may need to generate analytical reports based on data stored in the database or use the Oracle backend to make business decisions. And the users of the database may be all over the world. You must consider all of these possibilities when determining which users will be affected by the migration process from Oracle to SQL Server.
What applications will use it?
Now suppose you ask all the users one by one to find out who is using Oracle? And their answer is exactly no, then you should check the log file to understand which workstations are accessing the database. When you check these log files, you may find that not only the workstation is accessing the database, but other servers are also accessing the database.
Okay, pick up a pen and jot down the servers that are accessing the database, and then find out what specific applications those servers are accessing the database from. Such applications can be identified by comparing the data stored in the data table with the applications running on the server.
What applications are running on the Oracle server?
Now that you know the users and external applications that access the database, you now need to find out what applications are running on the database server itself. These applications may be database stored procedures (and corresponding triggers, custom data types, security settings, etc.) or they may be independent applications that do not run within Oracle. You should especially pay attention to the Oracle development tools added to the server.
Migrating to SQL Server
You should never be tempted to immediately unplug your Oracle server and install SQL Server. You must think twice before migrating critical servers. Why is this process specifically named migration? And it’s not because migration doesn’t always happen suddenly. If you take some simple and reasonable steps, the migration process can be achieved without any obstacles.
To migrate applications tonative applications and external applications,
take the following steps:
1. Install a new SQL Server in the network.
2. Create the "devices" and data tables used by the application.
3. Prohibit the application from accessing the database and make the application offline.
4. Copy the current data from Oracle to SQL Server.
5. Point all applications to the new database.
6. Allow applications to access new data in data tables and devices.
There is a fatal problem whenconsidering migrating SQL
between SQL Server and Oracle: they speak two different SQL dialects, SQL-PL/SQL (Oracle) and Transact-SQL (Microsoft).
In most cases, if you can use one SQL language, you can probably use the other SQL language. Taking into account SQL functions, operators, statements, etc., I specially compiled a SQL Programmer's Reference. This information shows the features supported by various DBMSs. Of course, if these American suppliers of SQL products honestly abide by the American standard SQL (ANSI-SQL), why would there be such a big problem!
In addition, you may also encounter the following problems:
Oracle's dual table - on SQL Server, you may encounter statements such as select 'x';. On Oracle, this statement must be converted into select 'x' from dual;. dual is a system table generated by Oracle. In addition to this SQL syntax issue, you cannot copy this table to your SQL Server because it is an Oracle system table.
Truncation -- Both DBMS support the FLOOR and ROUND functions, but Oracle also has an additional TRUNC function. If your Oracle system uses the TRUNC function, you will need to re-edit the involved parts - you may have to think of changing it to the FLOOE or ROUND function.
Concatenation-SQL Server 7 does not support the ANSI || connection method, but SQL Server 2000 does. Both databases now use the plus sign (+) to represent connections, but it is better to use this symbol for arithmetic operations!
It depends on the specific versions of the two servers you are using. SQL language migration issues will almost always pop up suddenly and unexpectedly.
The business case for abandoning Oracle
This database migration is not a simple emotional game, and choosing Microsoft products over Oracle is supported by the business case. Oracle has devoted considerable resources to defeating Microsoft in court and propaganda battles. However, from an economic point of view, Oracle is not more cost-effective. In addition, Oracle only has one core product, which is still less confident than Microsoft. In addition to the complicated sales process, you'd better find a more impressive company to provide support. The company should be healthy and prosperous, and its products should be generally popular. It should ensure that the company is very strong for at least a few years before encountering more powerful opponents.
What can you gain by migrating to SQL Server?
First, you get a system that is highly consistent with your network architecture. You don't need a dedicated person to manage your UNIX system. Management tools tailored for DBMS work optimally with those network operating system tools.
Secondly, the system implements built-in support for .NET applications. The .NET architecture does not require you to use SQL Server, but this server is the default database. ODBC drivers for Oracle are certainly good, but they are always a possible point of failure.
Third, the cost of using Oracle under the .NET network is higher than using SQL Server. You may get discounts when you purchase more Win2K server licenses as well as VS.NET and Office licenses.
Finally, even if you buy a SQL Server system separately, it may be cheaper than Oracle. These days, haggling over an Oracle license can feel like pulling teeth. I remember asking a sales rep the price and he actually said, "Can you afford it?"
Summary
If you are building a .NET network, it makes sense to use SQL Server as your DBMS because it is Microsoft .NET The core component of the Server application suite. Migrating from one platform to another is an important process that must be carefully considered and planned. Before switching platforms, you should have a unified, effective, easy-to-manage and reliable .NET foundation.