SQL SERVER – Choose Right Edition of SQL Server Express for Your Application

SQL Server Express is better alternative of MySQL. I have recently helped quite a few organizations to move to SQL Server Express recently. However, one question keep on coming up quite often regarding which is the right edition for SQL Server Express. SQL Server Express have more than one edition available.

Here is the quick guide to select right edition for SQL Server.

After reading above guide if you are still not sure which edition you should select, leave a comment here or send me email and I will get back to you.

SQL Server 2008 Express with Advanced Services – 546.6 MB

  • SQL Server Database Engine
  • SQL Server Management Studio Basic
  • Full-Text Search
  • Reporting Services

SQL Server 2008 Express With Tools – 230.4 MB

  • SQL Server Database Engine
  • SQL Server Management Studio Basic

SQL Server 2008 Express Runtime Only – 82.5 MB

  • SQL Server Database Engine

SQL Server 2008 Express Management Studio Basic - 38.5 MB

  • SQL Server Management Studio Basic

SQL Server 2008 Management Studio Express is also known as SSMSE.

You can refer my complete reference article SQL SERVER – SQL Server Express – A Complete Reference Guide.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Difference between SQL Server Express and MySQL

Both SQL Server express and MySQL are two of the Relational Database Systems (RDBMS) available today. Both are freely available and meant for running smaller or embedded databases, yet there are also significant differences between them.

SQL Server Express is a freely-available small-brother version of Microsoft’s enterprise system, SQL Server. It is the successor to MSDE (Microsoft Desktop Engine), but it is also a huge improvement over it, especially regarding the user interface design and the included feature-set. Its main features are:

  • Database size limit of 4GB. This excludes logfiles and can be overridden by using multiple databases.
  • Support for only one CPU (but multiple cores).
  • No SQL Server Agent.

SQL Server Express offers a number of advantages. The first and arguably most important is the complete scalability and integration with SQL Server. This allows you to seamlessly upgrade your database as your needs also grow (remember SQL Server Express is limited to a database size of 4GB). In fact the database upgrade is so smooth that even with no prior experience it can be done in about 10 minutes for a 3GB database.

The SQL Server Express interface is much improved over its MSDE predecessor, and offers such GUI tools like the SQL Server Management Studio Express, the SQL Configuration Manager, the SQL Business Intelligence Development Studio and SQL Server Reporting Services. All these work together to make the little RDBMS almost as rich in features as its SQL Server enterprise sibling.

SQL Server Express also has a strong family heritage. Being a Microsoft product, one can expect such features as excellent integration with Windows (for example domain authentication), support for dotNET, ODBC and XML, and comprehensive help and support from both the mother company and a large community of users on the web.

On the other hand, SQL Server Express’s most severe limitation is that it only runs on Windows. Given the large number of organizations and individuals running other operating systems especially Unix and Linux, it is locked out of an important market segment. Others are of course its aforementioned 4GB size and single-CPU handicaps. These, however, are not usually a problem for small databases.

MySQL, on the other hand, is a completely open-source RDBMS, as contrasted to SQL Server Express which is a free ‘lite’ version of a commercial database engine. Its largest arena is as the backend database for about 12 million websites around the world. Many of the world’s high-traffic websites like Yahoo Finance and Slashdot use MySQL as a backend database, a testament to its reliability. MySQL is open-source and its source code is freely available under the GPL (General Public License); so it can be argued that it is in fact a competitor to not only SQL Server Express, but the full-bodied SQL Server as well.

One unusual offering from MySQL is its ability to incorporate different database storage engines depending on the user’s primary need. If the DBA determines that the database will mainly be used for either quick data access, or transaction input speed, or accessing heavily partitioned tables, then he or she can choose the appropriate storage engine to suit this need. Of course, this also adds a new complexity- you must know the right engine to use and how to install and configure it. Examples of these engines are InnoDB, Memory, and NDB Cluster.

Unlike SQL Server Express, MySQL will happily run on almost all operating systems, although unofficial comparison tests run by several independent companies and individuals point to anecdotal evidence that MySQL performance is excellent on Unix and Linux, but not as good as either SQL Server Express on Windows platforms, much less the full SQL Server. This is partly attributable to SQL Server’s optimization within Windows, but also partly to MySQL’s reputedly weak Query Optimizer. In fact it is only recently that MySQL has incorporated advanced RDBMS features such as foreign key support and multiple-stage data commit.

Another important area in which MySQL comes up short is system stability- sudden power loss on a server hosting MySQL can very well cause severe data corruption. SQL Server and Express are more robust and tolerant to such outages because the data save process goes through multiple checkpoints.

I hope this has provided you with a good overview of the differences between SQL Server Express and MySQL. Even though both are free RDBMS systems and worthy competitors, they have areas of market overlap but also distinct market segments. Experience has taught many database administrators that each system has it strengths and weaknesses, and one would be advised to research thoroughly before settling on one or the other.

You can read the complete reference over here SQL SERVER – SQL Server Express – A Complete Reference Guide.

Please contribute here with your comments and opinion.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Effect of Oracle acquiring MySQL – A Delayed Analysis

On 20 April 2009, Oracle Corporation announced its acquisition of Sun Microsystems in a deal worth about US$ 6 billion. This would have been just another one of corporate mega-deals that sound interesting in the news but really have no effect on your life. Except for the fact that with the purchase, Oracle acquired the world’s most widely used open-source database engine- MySQL. About 12 million small databases, mainly in websites and small businesses, run on the open-source MySQL platform, since it is stable, easily adaptable and most important of all for cash-strapped small companies, free. Note that ‘free’ here means that there is no software license to purchase (unlike commercial database engines like Oracle DB and Microsoft SQL Server), but most customers still have to pay third-party vendors for additional services like user interface design, technical support, bug fixes and so on. This is what makes the open-source model paradoxically both free and profitable.

First of all, why did Oracle buy Sun Microsystems? The main reason for the purchase was to get Sun’s major products- Sun hardware, Solaris and Java. The SunServer+Solaris+Oracle combination (especially the Oracle database), is globally used in mission-critical systems, from banks to hospitals to nuclear power stations, because of its stability and robustness. It is perhaps the most trusted combination of hardware, operating system and database there is. Java is also a key platform and supporting component for several Oracle products, for example Oracle’s Fusion Middleware is wholly built to run on Java.

So the MySQL acquisition was not really the focal point of the purchase, but it was nevertheless an important aside. Sun itself acquired MySQL in 2008 for about US$ 1 billion, Oracle now owns both of them. While Sun was known for its commitment to open source software, Oracle is a corporate giant without a track record for supporting open-source initiatives. So there arose understandable concern within the IT community about what will happen to MySQL. Will Oracle try to kill MySQL so that it does not compete with Oracle’s own offering? Will Oracle help to develop MySQL further as a platform? Will they leave it alone and not be bothered? Will they sell it off to another company? The real answer right now is that nobody knows, except perhaps Oracle senior management.

Major Outcomes

Let us now examine the different outcomes being put forth by pundits. The first one, from insiders like IHL Consulting Group President Greg Buzek, is the glum opinion that Oracle will kill off MySQL because it partially competes with Oracle’s own database engine. Matters are complicated more by the fact that even though Oracle is mainly bought by large organizations (whose chief concern is system stability, vendor robustness and support, and not the software license fee), while MySQL is primarily used by small companies and small websites, its development has recently scaled up and MySQL can now offer enterprise-class computing, which then becomes a major headache for Oracle.

Another reason given for Oracle deciding to bury MySQL is that the company is not really a supporter of the open-source model, instead preferring the tried and tested pay-per-license route. In fact Oracle is viewed with some suspicion in the open-source community- a sort of mega-corporation bent on world (software) domination. This is the chief cause for concern for the MySQL community. However, Oracle does have some redeeming acts in its interaction with open-source products. For example, the company has fully supported development of its software products to run on Linux.

The second possible outcome is that Oracle will continue to encourage and support MySQL development. One argument for this is that MySQL, as part of the Sun purchase together with Java and Solaris, offers Oracle CEO Larry Ellison a weapon to fight his main rival Microsoft. Also, don’t discount the fact that Oracle is keenly aware that MySQL is open-source, meaning its source code and original developers are still around. So even if Oracle were to try and kill MySQL, either by stopping development or by licensing and charging for it, the development community can simply start working on an open-source, similar clone application. This is how Linux itself was originally conceived- as a free spin-off of commercial Unix operating systems.

A third possibility is that Oracle executives may decide that MySQL is simply too much bother, and decide to sell off the company to someone else. Sun Microsystems acquired MySQL for about a billion dollars, and Oracle may decide that since MySQL was not the main reason they bought Sun anyway, they might as well dispose of it for roughly the same amount.

A fourth possible outcome is that Oracle may elect to offer support contracts and consulting on MySQL to companies that need a lighter-weight solution than Oracle’s full, and at times bloated, database products. In this way, they offer the comfort of their large-vendor status to the potential clients who may be worried about using open-source software- a shrewd market-capturing move.

But that same decision may have its own potential pitfall. Oracle’s main reason for buying Sun was to integrate and sell whole computing platforms. But IT managers might choose to avoid this one-vendor offering, from server to database application to support services, because it can also result in a single point of failure or arbitrary price changes in the platform’s licensing.

Conclusion

In conclusion, the truth is that simply don’t know what Oracle will do with MySQL. There are several interesting alternatives, some more viable than others. But it is safe to assume that in the short-term, Larry Ellison will not take any drastic steps that may alienate his huge client base. Only time will tell.

My Opinion

In many of the application, I do not need full featured SQL Server Enterprise Version. I just need something simple and free. This is when I use SQL Server Express Edition. You can read the complete reference over here SQL SERVER – SQL Server Express – A Complete Reference Guide.

Please contribute here with your comments and opinion.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Server Express – A Complete Reference Guide

SQL Server Express is one of the most valuable products of Microsoft. Very often, I face many questions with regard to SQL Server Express. Today, we will be covering some of the most commonly asked questions.

Q: What is the cost of SQL Server Express?
A: SQL Server Express is a FREE product from Microsoft.

Q: Where can I find more details about SQL Server Express?
A: On official Microsoft Site: http://www.microsoft.com/express/sql/default.aspx

Q: Why should I use SQL Server Express when I have full version available?
A: Usually, I install only the license version product on my system. When I do not have to use all the features of SQL Server, I install SQL Server Express.

Q: What is the maximum size per database for SQL Server Express?
A: SQL Server Express supports a maximum size of 4 GB per database, which excludes all the log files. 4 GB is not a very large size; however, if database is properly designed and the tables are properly arranged in a separate database, this limitation can be resolved to a certain extent.

Q: Can MySQL be compared with SQL Server Express as both of them are free products?
A: Yes. Both are free and have their own space. However, thus far, I have never faced a situation wherein the task that can be accomplished by using MySQL is not accomplished by SQL Server Express.

Q: Does SQL Server have sufficient help available online?
A: Yes. There are so many online help resources available for SQL Server Express that user will find strongest community for any FREE product.

Let me give you quick list of the online help resources. Even though I took some time to build this reference list, I am sure that all of you will appreciate this and will share with others, which is worth the effort!

Overview

Getting Started

Installation

Quick Start

Misc

Blogs

Webcasts

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – SQL Server 2008 Migration White Papers

Quite often I get project when I am asked to migrate different database to SQL Server. Microsoft has excellent white papers written for this series.

Guide to Migrating from MySQL to SQL Server 2008
In this migration guide you will learn the differences between the MySQL and SQL Server 2008 database platforms, and the steps necessary to convert a MySQL database to SQL Server.

Guide to Migrating from Oracle to SQL Server 2008
This white paper explores challenges that arise when you migrate from an Oracle 7.3 database or later to SQL Server 2008. It describes the implementation differences of database objects, SQL dialects, and procedural code between the two platforms. The entire migration process using SQL Server Migration Assistant (SSMA) 2008 for Oracle is explained in depth, with a special focus on converting database objects and PL/SQL code.

Guide to Migrating from Informix to SQL Server 2008
This white paper explores challenges that arise when you migrate from an Informix 11 database to SQL Server 2008. It describes the implementation differences of database objects and procedural code between the two platforms. Emulation of system functions is also discussed.

Guide to Migrating from Sybase ASA to SQL Server 2008
This white paper explores challenges that arise when you migrate from a Sybase Adaptive Server Anywhere (ASA) database of version 9 or later to SQL Server 2008. It describes the implementation differences of database objects, SQL dialects, and procedural code between the two platforms.

Guide to Migrating from Sybase ASE to SQL Server 2008
This white paper covers known issues for migrating Sybase Adaptive Server Enterprise database to SQL Server 2008. Instructions for handling the differences between the two platforms are included. The paper describes how SQL Server Migration Assistant, the best tool for this type of migration, can help resolve various migration issues.

Files can be downloaded here.

Abstract courtesy : Microsoft

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Proposed eGov Standards Policy – Benefit for All or Only A Chosen Few

Does the proposed eGov Standards Policy benefit all or only a chosen few?

As a wider audience comes to accept new technology, so the technology itself grows. The recent debate in India on the eGov Standards policy has been a point of contention for some time. I would like to start our discussion on this topic by posing two questions:

Question 1: Should government mandate single standards for a given technology domain?

The obvious answer would appear to be “Yes”, but the considered answer is actually “No”.

The stipulation of a “single standard” would unnecessarily restrict the technology choices for the Government and would result in the exclusion from Government projects of Indian ISV’s who do not conform to the standard. In the Indian IT ecosystem, not many software companies offer their products for free. This is not only an Indian phenomenon, but a global one too. Software is not generally free.

Question 2: Should royalty free IP in standards be used in eGovernment solutions as suggested in section 5.1.1?

Once again, the obvious answer would appear to be “Yes”, but the considered answer is actually “No”.

If everything is free, who will pay the bills? We all know that in life there is no such thing as a free lunch. To build a sustainable standard takes time, people and money. The Ethernet standards, developed in the IEEE in the IEEE 802 series, provide us with a good example of this. If everything has to be royalty-free, how is the inventor to be paid for his efforts? It is simply impractical to expect everything to be free.

Open Standard vs. Open Source

The real eGovernment debate revolves around understanding the true meaning of “Open Standards”. Wikipedia is a great source on the subject and provides various points of view.

A clear understanding of the differences between Open Standards and Open Source is necessary. Open Standards can be used both in Open Source and paid technologies. For example, UNICODE is standardized under RAND terms and is used in both Windows and Linux. Sadly, Open Source has become a tool of exploitation in hands of the few who usurp the claim on Open Standard.

Facing Reality

For Government procurements, the lowest bidder is generally awarded the contract. The “artificial” imposition of a single royalty-free standard would preclude Government Departments from selecting solutions on a value-for-money basis. Many eGovernment Interoperability Frameworks across the globe do not impose such stipulations for the very reason that they have adverse effects on interoperability.

A number of organizations offer their software for free, but profit by charging surreptitiously for support. To my mind, it is better to be upfront and open about the real costs. Many have found themselves locked into agreements, only to be shocked by the actual long-term costs. This is what most Open Source implementations end up doing.

By towing the “free line”, effectively the Government is siding with the Open Source philosophy and killing vendors who charge for their software upfront. This is not in the best interests of the emerging software economy, nor the development of intellectual property.

The debate on standards is NOT purely academic in nature. If this standard is passed, Open Source commercial vendors stand to gain millions in Government contracts. Please refer to section 7.4 on this point. The debate is also NOT about what is good or bad. It is really a debate on how the proponents of a single standard will secure their profits from the Government.

The proponents of a single standard have a loud voice, and they appeal loudly to higher authorities and institutions. I believe that this should come to and end. The real issue, i.e. the confusion between Open Standards and Open Source needs to be clarified before a crucial error is made.

Suggestion for Better eGovernment Standard Policy

As we have seen, the term “open standard” means different things to different bodies. While I support the Indian Government’s goals to achieve interoperability for e-government, I recommend the following amendments to the eGov Standards policy:

  1. Remove section 5.1.4, because to prove that a particular standard is better than all other existing standard is virtually impossible to do objectively.
  2. Remove all preferences in section 5.2 for Open Source as they only favor commercial Open Source companies.
  3. If preference needs to be given at all, give them to software developed in India by Indian companies. As an added advantage, the Government can more tightly regulate these companies.
  4. To let the taxpayer’s money be utilized most effectively and to meet the objectives detailed in section 3.4, the committee should think of including a “lowest cost preference” clause in section 5.
  5. Define Open Extensions and/or Open Subsets clearly.

I sincerely urge the intellectuals of this country to climb down from “socialist” viewpoints and ensure that the IT the ecosystem is best leveraged to the benefit of the nation.

To better e-governance!

Reference : Pinal Dave (http://blog.sqlauthority.com)