As a mainstream commercial database product, SQL Server,
Oracle's dedication has never been resting, from enterprise applications, running efficiency, scalability, high reliability, operational safety to overall cost, ease of use, two products room Features Almost all are not the topic of discussion and the focus of debate. As the center of the running environment, the database is often in the location of the comparison center in the solution. Therefore, in order to exempt the program advantage, the discussion of the camps of both sides often is not only limited to the database product itself, and the development technology (especially the development language) is integrated.
In several projects involved in the author, due to the consideration of policy and development, you need to use multiple database products to integrate, in addition to the RDB of the historical stage, often need to use SQL Server and Oracle at the same time. In addition, SAS, Access and other data products will often be used in conjunction with SQL Server, ORACLE. Through several years of use, the author gradually recognizes the choice criteria for the database to best use the most suitable product in the appropriate location. This article is trying to make a brief comparison of two database products through versions, running features, and integration. The results of comparison are not the purpose but want to be referred to and reference to the reader's architectural design.
From the version, I started joining enterprise-level development from the version, and the two combinations of the opponent should be SQL Server 2000 vs.Oracle 9i and SQL Server 2005 vs. Oracle 10g (2nd).
SQL Server 2000 vs.Oracle 9i Due to the adjustment of the development strategy until the SQL Server 2000 version, Microsoft is supported for its built-in XML, and the corresponding characteristics provided by Oracle's Oracle 8i are sluggish. However, from the case of the technical solution, the Treatment of Internet-based XML does not appear in the case of falling, mainly Microsoft provides support from the application (intermediate layer or client) level by providing various XML COM development libraries, Make up the gap between SQL Server 7 and Oracle 8i in this area. SQL Server 2000 and Oracle 9i have non-ordinary strategic significance for Microsoft and Oracle, respectively. From Microsoft, SQL Server 2000 is a cornerstone in the field of enterprise development, through DTC coordination and COM adhesion, ensuring that SQL Server 2000 can connect with other major Windows Server into a transaction, for it The competition for the original IBM and Oracle provides technical guarantees; Oracle 9i is also Oracle completely breaks through the database products, and directly expands directly to a transformation of the application server product line through its own advantages.
SQL Server 2005 vs. Oracle 10g (2nd) With the launch of SQL Server 2005 and Oracle 10g, the competition of two products from the simple database itself, directly into two sets of data processing mode competition. However, the scope of the actual conflict of competition is shrinking, this phenomenon is mainly from the development environment of each product extension. --.NET Framework and J2EE. A big name of Oracle 10G is this "G", and the computing resources through the grid are summarized as a calculated whole, and then all resources can be used through a portal. This consideration of Oracle is not only technically, but also the need for competition, because through the grid, you can provide data calculation services to Windows NT, IBM AIX, Other UNIX and Linux systems, directly across Microsoft and IBM's technical body advantage. However, experienced SQL Server 2005 seems to be more commendable in many components, in many components, the "Integration Service Service Broker" is the combination of "Integration Service Service Broker", because this is Microsoft through its client advantage, will be multi-database, data warehouse Abstract and transparent packages of vendors.
The overall characteristics are more than the combat value of the Dragon Ball, which is constantly chasing all kinds of assessment data that you catch up. In fact, the overall differences in the two products are not large, or there is no obvious score of the database products. Below is a comparison of some of the main features other than DBMS: ◆ As a recognized database manufacturer, Oracle walks is a multi-platform compatible path, while SQL Server is deeply rooted on the Widows platform; Oracle's products can run in the mainstream Operating system platforms provide support for the VMS environment after merged RDB, while SQL Server only supports Windows operating systems. ◆ Everyone is universally discussed, the throughput problem, in fact, is discussed on two product transaction isolation, storage control problem; as for the different feelings in the product use, mainly with the configuration of hardware, operating system platforms, and DBA (or There is no configuration, and the factory default configuration is used. ◆ In operation, Oracle has quickly made up the gap between the SQL Server due to the support of various Java GUI support. However, according to the author's experience, senior DBA is most frequently used or "console accumulated script", except for them, except for them, ease of use cannot be counted as a problem. ◆ In the continuity of the product, the Oracle has a pulse, while the SQL Server has provided a lot of upgrade tools on both versions of SQL Server 2000 and SQL Server 2005, but in implementation, many content are overwritten. ◆ In terms of client support, SQL Server has ADO, OLE DB, DAO, ODBC, and newly joined ADO.NET, Native Client support; Oracle has JDBC, ODBC, OLE DB, OCI support, and provides .NET version Oracle Client Provider. On the data container, ADO.NET provides DataSet to extend a single relationship into a group of associated relationship memory databases, although there is little impact on the architecture, but can provide more choices than Java for application design. Especially in the processing of cascaded data is more efficient. ◆ Oracle already has a relatively perfect Java support in 9i, and SQL Server has increased support for CLR until the 2005 version. However, as the universal law in the industry "later is always advanced, the earlier is always mature", this SQL Server 2005 basically implements a "full version of Hibernate", not just a stored procedure, trigger, view But the entire SQL Server 2005 environment objective support. ◆ Internationalization, the support of both parties is very complete, difficult to divide. ◆ For mobile devices, both parties have mobile device version of the product, which can be embedded in these smart devices, but because the intersections of the facing device type are small, they can only be considered each other. ◆ For space, Oracle provides a complete 2-D, 3-D data development platform in 10G, while there is no corresponding product in SQL Server 2005. ◆ In terms of human language recognition, SQL Server 2005 and Oracle 10G basically do not do more extensions here. ◆ Through enhanced Reporting Service and Notification Service, SQL Server 2005 and Oracle 10G are divided into autumn in autumn in both reports and notifications.
Project integration believes this topic will be part of the controversial, general discussion often develop from the technical type of the project, such as corporate core business system, data transfer system, OA, risk management system, embedding and mobile project, space system , Multimedia information, etc. Here, the author only introduces only the project cost size of the "Database Development Tool Data Access Layer Logic Development Database Server", because the complexity of the project is different, the cost has a large difference, so the author's classification is not continuous. of. Top 20K ~ 500K Items The following figure is a typical deployment level of this scale system, with a virtual node represents a possible duplex, Stand Alone, Read-Only DB. Believe in this scale, SQL Server 2000, SQL Server 2005, Oracle 9i, Oracle 10G Enterprise is not your ideal choice. Even if your application logic is very simple, all data access layer logic also uses only JDBC or ADO.NET standard libraries (or simple packages only), and the author also recommends that you consider the following two integration: integrated "SQL Server 2000 MSDE SQL Server Client VS.NET 2003 (or VS 6, VS 2005), "At the same time, in order to further save costs, improve the underlying software quality, you preferably integrate MS DAAB (MS Data Access Application Block). The reason for choosing DAAB instead of more comprehensive Enterprise Library is because the databases in this integration project use only SQL Server 2000 MSDE without including Oracle, DB2. So, select a simple and efficient DAAB, which can save both the development of self-encapsulation data access layers, and can also greatly reduce the design, developer learning the "Factory Object Builder" implementation mode of the DB part in Enterprise Library. ◆ From a cost perspective, the integration is only required to pay the MSDE license and a fee of SQL Server Client (for management and maintenance). ◆ From the functional point of view, in addition to "SQL-92 T-SQL", stored procedures, triggers, physical and non-material views, you can use copy, link server, DTS, SQL JOB, AD integration certification , Parallel query, DTC service, etc., combined with VS 2003's various designers and public code blocks, I believe that you can fully construct a transparent data access layer in an environment of a distributed heterogeneous data source. ◆ From a performance perspective, a single node is for a system with 200 Online OLTP users, the amount of single ticket transaction data is less than four tables, and more than four tables of the ticket trading table is related to the reasonable configuration. It can be loaded at all. ◆ If your online OLTP number is less than 40, the author recommends that you can completely evaluate the PC installed Windows 2000 Pro or Windows XP Pro, without using Windows 2000 Server or Windows 2003 Server.
Integrated 2 "Oracle 9i Standard Edition Eclipse (or JBuilder, JDeveloper". In addition, it is best to simplify the time and cost of development, it is best to increase Hibernate - the reason is similar to integration. ◆ From a cost perspective, the integration is online, and it is necessary to pay for the Oracle Standard Edition. ◆ From functional point of view, in addition to "SQL-92 PL / SQL", stored procedures, triggers, physical and non-infinitive views, DBMS_ * Processing package, outside of synonyms, you can also copy, dbms_job, sql * loader, rman , IMP / EXP and other tools meet most of your development needs. In addition, according to experience, the author recommends that you combine DBMS_JOB simple extension hibernate to complete data access blocks with the Schedule feature, so that your system can be synchronized or asynchronous, can be processed in batches. ◆ For run platforms, this program has more choices: including Linux, FreeBSD, mainstream UNIX, Windows NT can be. Remove code writing problems, overall throughput efficiency in a specific operational environment is largely determined by hardware, storage, and Oracle administrators' personal capabilities and experience. Other instructions To make this low-input system, a single node achieves such throughput sizes, you must pay attention to the following aspects in the application architecture and development: ◆ Use JDBC, COM or SQL Server sharing connection, as low as possible Use it to release it early. ◆ Moderate, moderately use stored procedures, strictly control the use of all kinds of constraints. ◆ Plan a good batch operation and time period of OLTP operation. ◆ Consider simplifying application logic and deployment by copying, Job these system built-in features. ◆ Consider the cost of backup recovery parts in this scale project. ◆ The distributed trading of the library needs to be done with COM and EJB. ◆ SQL Server 2005 Express and Oracle 10G XE technology are more advanced solutions, by embedding VS 2005 Integration Service project and JDeveloper's Oracle 10G Workflow BPEL project, and quickly packaging to become a web service to complete different Interaction between platforms.
1M ~ 10M project level project covers the scope of today's major functional projects, the content involved from OLTP to OLAP, small-scale DM, full system or industry OA, more comprehensive e-commerce site, legacy The system integrates, small-scale logistics, ordinary ERP or CRM ... In this scale, two products need to further increase from simple DBMS to platforms, historical records, platform, distributed computing platform The deployment environment also expands from a single data center to "multi-point data 1 ~ 2 (or even more possible) centers; from storage, generally need to introduce RAID with disk, tape, according to data Information content, design small-scale HA and hierarchical backup strategy; for EAI or B2B data exchange items, in order to ensure the order of data, it is often necessary to introduce queues in different database nodes; between business processing, general or More or less to extend the flow of hard-coded implementations into a separate workflow engine to overall, the process data of the workflow is also best to open a separate, a special database for frequent short transactions. In general, the data part of this type of system is as follows: In this environment, the author recommends that you should consider the irrelevant data provider on the architecture, unless your system is only designed for a particular user of a particular user, otherwise To ensure the versatility of this scale, you need to complete a data access layer that is not related to database vendors. At this level, in addition to including queries, SP, TRIGGER, DML, you need to provide minimal providing mechanisms: ◆ Buffering of data, mainly used to save common business and technical parameters, XSD (or other Schema information), XSLT (Or other data mapping information), express resolution result, system configuration information, etc. ◆ The canable JOB control. ◆ Various Brokder that supports synchronous or asynchronous processing simultaneously. ◆ According to the product characteristics, it is also necessary to consider adding the Ochestration of the workflow as an option to this data layer. ◆ If it is developed in the .NET platform, in order to save development time, the author guarantees the quality of data access layer code, I recommend that you integrate full Enterprise Library. Product selection is recommended to use new SQL Server 2005 Enterprise and Oracle 10G Enterprise to complete the SQL Server 2005 Express and Oracle 10G XE of specific nodes. The reason why it is recommended to choose a newer product, mainly to consider the relative will be relatively long, for technical upgrade, the author recommends the choice of new products; on the other hand, the author is recommended to adopt " Enterprise Edition Free Version "Method, the free version of the non-OLTP, OLAP, and DM is divided into the free version of the inexpensive server, which can greatly reduce the load of the primary node, and take advantage of the computing power of each point. I can share the operating risk of the system to different nodes, and the three can save the primary server configuration to the overall cost. Since this scale business project is generally divided into three levels from headquarters, branch institutions and business sites. According to the general practice, branches have certain specific business contents, whether it is law enforcement scale, business process conventions, special technical needs, regional positive policies, etc. Therefore, under the premise of the main business data concentration, business parameters and technical controls that embody the characteristics often have different different, and the author will always refer to these customizes as "local food ticket" in time. For these data, if the type and quantity are not large, the update is not very frequent, the network delay can meet the processing requirements, it can be concentrated in a data center, but if the data is very large, the update is frequent, including expressions or expressions The batch rule control of the group is preferably designed to strip these "local grain tickets" to each branch treatment.
◆ For the case of hybrid headquarters data and "local grain ticket" in branch data, you can complete the need for the headquarters database to synchronize data to the branch database through the copy of SQL Server or Oracle's one-way "push" mode. ◆ If there are two (or even more) centers, you need to provide a single transparent database node by two-way replication. If you need a real-time backup in the same city or in different places, copy it to the backup machine to "push" mode by run. ◆ If you reduce the load of the system during the day, you can complete the backup and synchronization of the evening bulk data through the Integration Service or Oracle IMPORT / EXPORT. The next page Figure 3 is a hierarchical deployment frame mode that is often used in this level of items, where the database products have been updated to SQL Server 2005 and Oracle 10G series. Other instructions According to the author's experience, in this level of project, the RESULTSET that uses the data source. Net Dataset and Java can be easily constructed to build a database manufacturer, which will open you. Range. In this environment, developers should pay more attention to the control of distributed transactions, and DBA is more focused on transaction isolation control of different data objects, and through the appropriate transaction model, the same business logic can be used for such systems. Provide higher concurrency and proper consistency.
The items 50M ~ 2B items are briefly introduced to the characteristics of such system data before the project: ◆ This level of project is basically the industry's core business system, and many of the "Gold" series . ◆ There is always an integration of legacy system, and the transition is even in parallel. ◆ Most of the host, small machine, and PC servers, generally in 200 or more. ◆ A wide range of operating system environments, including all kinds of UNIX, Linux, Windows NT, Windows desktop operating system. In addition, it is possible to face the old system such as VMS. ◆ In addition to OLTP and OLAP, there is a need to provide more complete DM support with the purpose of risk management. ◆ The internal EAI and the external B2B require a thousand heads. ◆ There is a wide range of people, and there are many types of development platforms. ◆ The influence of policy and vision planning, data manufacturers have clear requirements. Since the content of this type of project is complicated, the author only tries to introduce from the perspective of integration. According to a 1B project experience involved in the author, such a system must include the following sections in addition to the function of the data access section mentioned in the previous level: ◆ A real-time data exchange platform, this exchange platform is To provide data exchange for internal and old systems, but also completed data exchange between multiple partners. ◆ A data distribution platform based on the "Publication-Coordination-Reservation" structure, often including other surrounding projects, often requires the backbone business system to extract information with the backbone business system, in order to reduce such system to the backbone The impact of the business system, it is best to take "one extraction and multi-point distribution" to distribute these predetermined information asynchronously to other systems. ◆ A comprehensive operation monitoring and management platform. The relationship between a few is as follows: Since SQL Server 2005 has built-in CLR support, various data objects themselves have supported object-oriented development. For Oracle 10g, J2EE also supports access to various components by object-oriented methods. However, there is some problem with a data access library that is unrelated to each provider. Although Oracle 10G provides designer and development libraries for .NET Framework, SQL Server 2005 does not provide rich development to Java developers. The library, therefore, if you want to design a Java version of the public data access library, you can only stick to SQL calls, and you cannot really pack an access library for data objects. Since the Integration Service and Oracle BEPL of SQL Server 2005 can be easily packaged to Web Service, so for the exchange of system boundary parts, other peripheral systems can be used, other peripheral systems All kinds of systems of partners perform interconnection and minimize coupling between systems. Summary After the development of SQL Server 2005 and Oracle 10g, the two products have far exceeded the range of DBMS, so the author is only analyzed and introduced for the integration of several sizes. Introduction to the layout and function segmentation, the content is not discussed for performance, security, and scalability, but if you have selected a product, performance, security, scalability ... Content reference in SQL Server Books ONLINE and Oracle Online Documentation have been introduced.
This article has been published in "Programmer" magazine in January 2006, please indicate the source.
