SQL Server vs. mysql database

Microsoft SQL Server 2008 is a database platform that trumps MySQL in all important business areas. It delivers a large resource network, industry-leading performance and enterprise-class scalability, the highest level of security, and a broad business intelligence platform-delivering all of this at a lower total cost of ownership (TCO).

· Partner Systems

· Scalability

· Security

· High effectiveness

· Business Intelligence

· Total cost of ownership (TCO)

Partner Systems

SQL Server supports your business with a large resource network and a wide range of partner systems. SQL Server is the enterprise's preferred database, with 74.7% of the enterprise currently using SQL Server.

· Microsoft has the largest number of developers and support teams in the world. MySQL has about 70 developers and 50 support staff.

· Approximately 15,000 ISVs support SQL Server. There are less than 400 ISVs that support MySQL.

· Approximately 2,000 solution partners in data management accreditation in the global Microsoft Collaboration program are capable of providing support for your business. and MySQL's smaller partner system can only provide limited resources.

Scalability

SQL Server supports industry-leading performance and enterprise-class scalability. SQL Server provides a foundation build that can be developed with your enterprise, and it is proven to handle large workloads.

· Performance validated by industry standard benchmarks, including TPC and SAP. MySQL does not have industry-standard performance benchmarks. MySQL performance requirements are generally based on configurations that may not be appropriate for your organization.

· A rich feature, high-performance, and cost-based query optimizer improves the efficiency of complex queries. MySQL has only one basic query optimizer, and the optimizer is not optimized to provide the best performance, and thus only provides limited scalability.

· Distributed partitioned views extend scalability. MySQL does not have a distributed partitioned view.

Security

SQL Server provides the highest level of security. According to the National Vulnerability database, SQL Server has not seen a vulnerability in the past three years. MySQL users are subject to a number of security challenges during the same period.

Research organization confirms SQL Server's high security

· Security innovations found that SQL Server on Windows is more secure than MySQL on Linux, with a 46% reduction in vulnerabilities and a 48% reduction in risk days.

· ESG reports that MySQL has more vulnerabilities than Microsoft SQL Server, Sybase, and IBM DB2 in 2004, 2005, and 2006.

SQL Server provides:

· Reliability: A wealth of security features protect data and network resources.

· Third-party assessment, the preferred way for security confirmation: Microsoft obeys Common Guidelines (Common criteria) as a proven and accepted validation process. MySQL does not include common guideline proofs or C2 (NSA).

· Stealth: The SQL Server cluster supports Kerberos authentication on one virtual machine and Windows style policy for standard logons. This makes it possible for all accounts in this domain to use a consistent policy.

· Integrity: SQL Server supports the ability to encrypt in the database and integrates with the Key Management Foundation Build.

· Automatic Updates: SQL Server is integrated with Microsoft Update for security updates. MySQL does not have automatic update patching.

High effectiveness

SQL Server provides the highest level of effectiveness. Always on (always online) technology provides a comprehensive enterprise-class choice that minimizes system downtime, protects your data from high human errors, and maintains application effectiveness at the appropriate level. MySQL does not provide the following features, all of which are included in SQL Server: Database mirroring, failover clustering, database snapshots and snapshot isolation, log shipping, and online operations to prevent downtime.

Reliable upgrades to meet the requirements

· Camstar found that SQL Server 2005 Enterprise Edition (64-bit) can be upgraded to meet the needs of most infrastructure builds.

· Temenos, a major provider of core banking solutions for global financial institutions, uses Microsoft SQL Server 2005 Enterprise Edition (64-bit) and Quad-core Intel with Microsoft and Intel Xeon technology to test the performance of the Temenos T24 Bank model.

Business Intelligence

SQL Server provides a comprehensive, out-of-the-way business intelligence platform-no third-party vendors are required. SQL Server, the first online analytical processing (OLAP) server on the market, contains Analysis Services, ETL, and Reporting Services for building and managing print and online reports. MySQL does not provide business intelligence features.

Total cost of ownership (TCO)

SQL Server delivers high quality at a low TCO, providing a comprehensive, out-of-the-way data platform solution for your business without the need for expensive plug-ins.

· A set of world-class tools and an integrated debugging environment help reduce development costs.

· SQL Server Management Suite, designed to help create a self management system that helps reduce employee costs.

· Reduced TCO and faster development time, and common engineering policies that are implemented across Windows server products. Also, Microsoft offers a number of SQL Server licenses and price choices, each of which provides strong support.

MySQL provides its community Server free of charge. But the company warns that MySQL does not provide support and is regularly updated. If you do not have the technical experience to rely on yourself to provide these services, then the purchase of these services is very expensive.

· Product support requires MySQL Enterprise to subscribe $599 to $4,999 per server each year (MySQL Enterprise selection).

Building a comprehensive solution on MySQL requires additional Third-party tools, such as backup and business intelligence, many of which are fee-based.

The above is reproduced, tell some personal experience, my company's virtual host management system is I developed, using the. Net and SQL Server scenarios, and because it is operating a virtual host, so the two common database contact is very many, encountered many problems.

　　Generally speaking, performance, the data volume is relatively small, the data table structure is simpler, such as 100,000 data, then the efficiency of MySQL in the first retrieval time will be faster than SQL Server, note that I mentioned the first search, because the two types of database hosting platform is different, The Windows system might also show several g of free physical memory after one year, and the Linux/unix may take over all of the physical memory when it is started, and it's confusing when you're just learning Unix. So in Linux/unix, the process to use memory is less than the process under Windows wait for a step, so the memory page exchange overhead will be relatively small, and windows to see the rest of the physical memory, enough to get, not enough to write the page file ( In fact, in memory management and scheduling the difference between the two systems is very large, here is just a simple example, so Windows memory scheduling performance is certainly not linux/unix good. SQL Server is very interesting to run, a friend who has done server maintenance should note that the SQL Server needs to be given the upper limit of the physical memory that can be invoked, otherwise, after a period of time (how much time, depending on the load situation, may come up in a few seconds) SQL The server will eat all the physical memory, our company has taken over two days to visit tens of millions of sites, are SQL Server database, Dell's 1950, two Xeon 3.0,4g memory running more difficult, because too much concurrency. SQL Server is a typical memory-swapping performance, and careful friends will find that a 500M database, if you do count or sum or any other operation that needs to retrieve the entire table, the physical memory that SQL Server occupies will jump up to more than 500 m, Here you can see his performance source, is to buffer the database in memory, the efficiency of the memory operation is higher than the disk, so his high performance is very normal, plus he is a commercial database, the new version of SQL Server 2008 performance has been exaggerated, followed by detailed data. MySQL's memory footprint is satisfying, small footprint, and no matter how I adjust, I see that the upper limit of MySQL's memory footprint is only 1.1G, at that time was also a very extreme situation, a user's site was attacked, more than 30,000 concurrent requests for PHP page, is a forum, the following hook up Mysql,iis program pool CPU has caused a core loaded with a processor (quad core, He used 25%, and MySQL has used 60%, more than two core load, MySQL process footprint memory close to 1.1G, because his forum posts more, the impression is more than 3 million or more than 8 million posts, the system load is very high, MySQL obvious reaction slow, or even retrieve time out. Front mentioned firstThe problem of the second retrieval, also explained the reason, SQL Server for the first time because to buffer into memory, so need to read the entire database physical files, so the speed of the first retrieval is the disk to stall, in fact, most of Windows programs first startup will be very slow, The same is true for asp.net pages, but the first time, the speed is significantly improved, and MySQL is consistent, the same, the first and tenth times the same speed. But through the monitoring system IO operation can be learned that MySQL always have to read and write the physical files frequently, so the speed is naturally not high SQL Server. Of course, the difference is not only this, but we usually rarely involve tens of millions of data above the search, you can see this.

To talk about security, the above also mentioned the official and third-party data, that MySQL does not have SQL Server security, in fact, including the operating system, the Windows system has a high risk of vulnerabilities less than Linux/unix, and the number of IIS vulnerabilities come over, Apache has a lot of holes in it. So someone always said Linux how safe, I do not know his argument, based on what. I've worked on information security, and the list of vulnerabilities is a habit, and in recent years the number of linux/apache/mysql vulnerabilities is absolutely twice times that of Windows/iis/sql server. Another security issue not mentioned above, is the physical file of the database, as we all know, the security of the physical file determines everything, SQL Server file operation is very safe, at least for more than 10 years I have not encountered SQL Server inexplicably physical file corruption, and MySQL encountered many times, inexplicably index is broken, sometimes can repair, sometimes still can not repair, forcing us to develop automatic backup system ...

Finally, tell a story about yourself. For some reason, I grabbed the Sohu a son station of all articles, more than 150,000 or more than 160,000, the number of words in 1.5G, when the whim of comparison of the performance of the two databases. All correctly inserted into the database after the fuzzy search, contrast speed (MySQL less than a few articles, because the escape characters too much, lazy to check). On SQL Server I first made a massive fuzzy search, let him mount memory, and then sum (like) a nearly every article has a keyword, run three times, the worst one is 49 milliseconds, that is 0.049 seconds. Then do MySQL, Test 5 times, each time out of the results, wait a few minutes did not respond, kill process restart, and then come ... The last direct process collapsed, and I was in no mood to study.

Based on your experience with server management over the years, SQL Server's robustness, performance, and stability are much higher than MySQL's (haven't mentioned that MySQL has a few inexplicable crashes), if your application is more complex, the volume of data is large, and is a "critical" application, Then choose a business database is correct, but if you are strong, have their own research and development capabilities, can do like Yahoo and Google as a cluster, or even the ability to transform their own database, then use MySQL is suitable for you. If your application is relatively simple, the amount of data is not large, and the structure of the data table is relatively simple, then MySQL for you to use, but please do backup measures, MySQL's robustness is not flattering.