Comparison of the results outlined
This database platform in the comparison of two results in building a database application, using SQL Server 2005 (code-named "Yukon") combined with Visual Studio for development than the use of DB2 UDB 8.2 (code-named "Stinger") combined with Visual Studio have a significant advantage. In the development, debugging and deployment of database solutions, these advantages will translate into time and money in savings. In this article highlight the SQL Server 2005 compared with the DB2 UDB 8.2 on. NET support for database objects to a wide range of more. In addition, you will find in building and managing database objects, SQL Server 2005 and Visual Studio integrated level than DB2 UDB 8.2 and Visual Studio integration more closely. In this article, you will also see the SQL Server 2005 development platform provided by the relational database in addition, there are many other features that more than DB2 UDB 8.2.
Preface
In the past, IT development technologies into a programming language, product configuration and data manipulation environment, three relatively independent expertise. To the integrated use of these skills often require relatively independent of technical expertise and a lot of manpower. Now, with SQL Server 2005 and Visual Studio 2005, we have a unified development environment, integrated programming model which provides a total solution, inclusive of client database applications, server management tools and server-side database objects Construction. On tools and frameworks such functional improvement will allow developers and customers can benefit from it, because it would have application availability, performance, security and scalability to bring a comprehensive upgrade.
SQL Server 2005 and DB2 8.2 on Visual Studio environment and database data provider integration will simplify and improve the application development process. They provide to enhance productivity, build and deploy applications tools, which will be the development and application to the program management lead to better performance. SQL Server 2005 and DB2 UDB 8.2 on. NET Framework integration has brought a more efficient and more flexible database application development environment, this has been more efficient than the previous version of the implementation of a more robust database solution, it have a better ease of use and scalable. Through the use of integrated. NET environment, database developers can use the SQL code impossible to achieve before the results obtained. By using the. NET Framework, developers write code to have more complex logic, more suitable for solving computational problems, and to access to external systems and network resources, as. NET languages such as Visual Basic, C # and C + +, are fully object-oriented programming language, with as encapsulation, inheritance and polymorphism such object-oriented programming features. They also have many that do not exist in the SQL language features, such as arrays, structured exception handling, collection and so on.
Today, Microsoft. NET provides the most advanced and efficient to build and integrate database applications environment. In this article, we will compare the SQL Server 2005 and DB2 UDB 8.2, respectively provided. NET integration level. In order to fully illustrate the difference, we will do a detailed technical presentation to show the difference using the SQL Server 2005 and DB2 UDB 8.2 for a. NET stored procedures in the specific steps.
Comparison of core technology
Although SQL Server 2005 and DB2 UDB 8.2 are integrated. NET Framework and Visual Studio, but the extent of their integration has significant differences. The following table lists this on their. NET integration degree of comparison.
Data provider
SQL Server 2005 and DB2 UDB 8.2 are set up. NET data provider makes. NET client program can access the database platform. These "natural" data providers and data-based OLE DB provider than the server application will result in better performance and scalability. Both data providers have very similar functions, can perform basic ADO.NET objects, including Connection, Command, DataReader, DataSet, and DataAdapter. But they have a key difference: SQL Server. NET Data Provider is available in two models, one for the client application, one for the server-side applications. This is for the server-side application development is particularly important because the SQL Server server-side. NET data provider is a resident in the memory of the program that do not like the data provider as the client to consider the restrictions of network traffic, therefore, the server side. NET data provider can focus on. NET database objects for better performance. In addition, the server-side data provider also opened a group on the server side code is only suitable for functions, such as server-side cursor. For client application data to provide an open procedure in the System.Data.SqlClient namespace function, while the server-side data provider functions as an open space in System.Data.Sqlserver name. In DB2 UDB 8.2, only single IBM.Data.DB2 namespace. DB2. NET data provider uses DB2Context object to create in the memory of the database connection.
On the server side, they also have similarities. DB2 and SQL Server support the use of. NET language to build applications and the subsequent deployment of the server side. Apart from this basic concept, the two database platforms. NET integration has a very different level. DB2 UDB 8.2 supports the creation. NET stored procedures and. NET user-defined function. However, Visual Studio IDE support only create DB2 UDB 8.2. NET stored procedures, DB2 UDB 8.2. NET functions must be manually created. In contrast, SQL Server 2005 对. NET's support to a wide range of more. And, like DB2, SQL Server supports the creation. NET stored procedures and. NET user-defined function. In addition, SQL Server also supports. NET triggers,. NET user-defined type (UDT), and. NET user-defined aggregate. All of these objects are fully integrated into creating Visual Studio 2005 IDE in the. Can use. NET language build stored procedures and functions of the database developers is certainly good news, which will allow them to achieve a more complex business logic and function features without having to standard SQL functionality is limited. This undoubtedly is. NET and database integration the key, of course, use. NET language to create a trigger, user defined types and user-defined aggregate useful. Use. NET language to create a trigger to trigger the code to make more fully encapsulated business logic, while also perform some additional operations, such as access to external resources and records operation log. Use. NET language to create user-defined types to allow database developers to extend the system any original data types, these user-defined types can have their own attributes and operator independent, which allows developers to seamlessly extend existing data types , when in use and the original data type, have their own operators and aggregation. Similarly, the use of. NET to create user-defined aggregation enables developers to create custom aggregations used in the original data type or user-defined data types.
In addition to these basic. NET functions in. NET Framework and their database server integration are also major differences. The next section, we will be more in-depth discussion. NET integration details.
. NET Framework integration
Microsoft ®. NET is used to information, human resources, systems and equipment associated with a set of Microsoft software technology. . NET Framework is to build and run the next generation of software applications and Web Service components necessary for Windows.
. NET Framework
◆ support more than 20 kinds of different programming languages.
◆ Management of a large number of "pipeline" - help improve the efficiency of software development and allowing developers to focus more energy on the core business logic code.
◆ make build, deploy and manage a secure, robust and high performance applications easier than ever to achieve.
. NET Framework by the common language runtime (CLR) and the uniform layer composed of a collection of libraries. . NET CLR functions include the operation of an integrated language service, mandatory security and memory, process and thread management. In the language of integration, CLR defines a common type system (CTS), which describes the crossing of all. NET language, the basic data types and operations on those data types. . NET Framework provides a number of classes set for developers to apply to their application, these classes set covers a lot of aspects, including the I / O, network, text processing, data access, encryption, XML processing, Web Service and so on. This allows developers to be able to focus their efforts on building business logic rather than buried in the "pipeline" in the code, as they can. NET Framework classes focus to find. SQL Server 2005 and IBM DB2 UDB 8.2 on. NET CLR integration makes it possible to use any. NET languages (including C #, Visual Basic, C + + and J #) to develop database objects.
These two different database platform and. NET framework of the integrated approach is completely different. SQL Server 2005 database engine will be places on the CLR within the process, which means running the database engine and. NET runtime only a separate operating system processes. In contrast, DB2 UDB 8.2 and. NET Framework is integrated with "-process" model. Figure 1 on different database implementations CLR has been described clearly.
Integrated model of meaning
SQL Server 2005 integration. NET runtime when using the "process of" model "-process" model compared to some very clear advantages. First, the internal enable CLR integration in SQL Server can process in a different way of controlling the operation of CLR. Memory management, garbage collection, thread support the core functions will be controlled by the SQL Server host, rather than. NET's default settings and operation. This is important because the SQL Server database engine to better study from the perspective of the overall system requirements, making it optimized based on actual memory and thread management. Finally, the "in-process" model of SQL Server 2005 CLR integration to create a more robust and get better scalable solution.
For example, consider a heavy burden on the database instance, to respond to many concurrent requests, the situation has been going on for a very long time. SQL Server can automatically and intelligently in data storage and program logic (for example,. NET stored procedures) balance between the allocation of memory. When the system has some of the burden of changes in nature - for example, relatively more of the requested use of the program more logical - SQL Server will again automatically be adjusted. In this way, the system performance to meet the actual needs based on the scalability and reliability of continuous optimization. Because DB2 UDB v8.2 with "-process" model of integration. NET runtime, so DB2 can not provide this type of machine resources and performance optimization of dynamic equilibrium.
SQL Server 2005 by using the CLR hosting API to implement dynamic optimization, these API only. NET Framework version 2.0 there, and in the. NET Framework prior versions do not. Inevitable result, SQL Server 2005 need to be integrated. NET Framework 2.0 and DB2 integration is. NET Framework 1.1, integrated natural low level.
. NET 2.0 and. NET 1.1 difference
Use. NET Framework 2.0 and SQL Server 2005 combined with Visual Studio 2005 and use. NET Framework 1.1 combined with DB2 UDB 8.2, compared with some significant advantages. . NET Framework 2.0 and previous versions, with significantly enhanced features:
◆ to improve performance and loading time
◆ support the generic (the same class and method for the value of different data types, like running, thereby increasing code reuse)
◆ support "editing continue to run" (Edit-and-Continue), developers can modify the code in the implementation process, without having to stop and restart the debugging session.
◆ A new Data Protection API (DPAPI), so applications can encrypt certain sensitive information, such as connection string or block of memory.
◆ authentication flow through the new NegotiateStream and SslStream class allows you to use Kerberos or SSL to achieve client and server-side secure channels.
◆ COM interoperability improvements will make. NET applications to call existing COM object has the better performance and reliability.
◆ Improved I / O performance with the support of the GZIP data compression
◆ 64-bit application compatibility
Deployment. NET logic to SQL Server
Actual use of each database platform. NET object has a very different way is the. In SQL Server 2005, a new SQL Server database objects assembly (Assembly) is deployed. NET objects (such as triggers or stored procedures) the smallest unit of the assembly group is deployed. NET smallest logical unit. In order to create CLR database objects, you must first use the Visual Studio 2005 to create a DLL. Then this DLL into SQL Server as a database program object. These steps can use the command line compiler and the CREATE ASSEMBLY command manually to achieve, or as part of the technical presentation as in see, Visual Studio 2005 can automatically complete the whole process.
SQL Server security in the Assembly
SQL Server 2005 database administrators to provide a new security unit - the assembly. Assembly can be marked as the three security states: SAFE, EXTERNAL, and UNSAFE. These security labels for database administrators to manage and protect all in the database to run the assembly level. NET code. SAFE said the label assembly using only managed code and does not access resources outside the database. EXTERNAL label, said the assembly to use managed code to access external resources, such as network file system or within other non-database resources. UNSAFE label that can include hosting of this assembly and (or) non-managed code, and can access any internal or external resources. UNSAFE assembly created by the database object has system administrator rights can only be performed for users.
Deployment. NET logic to DB2
Although the. NET managed code into the process of DB2 UDB 8.2 and SQL Server 2005 into the process is very similar, but stored in the database object types and location, security and database management process areas, or with some differences. To create DB2 database objects in CLR, you have to first use Visual Studio and use some. NET managed language to create a DLL, the DLL files need to be copied to the DB2 installation path to a special directory, or create a stored procedure or function time to provide the full path to this DLL file. This DLL is not actually a database object and have not been imported into the database table, on the contrary, it is just an operating system file. Then, the DLL will appear in the create database objects (such as stored procedures or functions) in the CREATE command. Part in the technical presentation, you will see the use of Visual Studio 2003 can automatically create a DB2 CLR stored procedure in the entire process. However, creating user-defined function is not the same, you only be done by manual process to create user-defined function.
A database administrator or application developer is usually a need to protect the external program associated with the DB2 DLL assembly, which can limit the operation of the program is running to achieve this step in the creation process of work or function in the CREATE statement EXECUTION CONTROL clause to complete. Effective implementation of the control modes include: SAFE, FILEREAD, FILEWRITE, NETWORK, and UNSAFE. If not specified execution control mode, the default mode is SAFE, which will mean that the CLR procedure can only access by the database administrator control of resources, such resources include all of the tables and the framework for managing the database instance. FILEREAD, FILEWRITE and execution mode NETWORK allows managed code to access the local file system or network resources. UNSAFE execution mode will not be any restrictions on access to resources, marked as UNSAFE execution mode can execute binary code for the procedure. Since DB2 and. NET integration is adopted "-process" model, then use the CREATE statement to create the DB2 CLR procedures and functions must be used FENCED clause, to illustrate. NET logic and database manager process in different Run and do not use shared memory communications. This has resulted in the program logic to transfer data between the database itself, when the "in-process" model compared to a performance barriers.