During the planning phase of a software development project, the most critical component is the database and its design. Among the several relational databases available, developers usually prefer MySQL and SQL Server. While both are capable of supporting enterprise projects, their subtle differences will determine the way you design your application code. You should know these variations before implementing a development plan because changing to another platform will significantly alter your timeline.

Open Source vs. Closed Source

SQL Server has been around since the 1980s when Microsoft provided only closed-source software, and it had no significant competition until MySQL launched in the 1990s. Oracle, which acquired MySQL shortly after its release, also has a closed-source database platform popular in business environments. Until recently, SQL Server, MySQL, and Oracle have been the leading contenders for database clients.

Cost Differences

The open-source community offers free software to individuals and enterprises, earning revenue through support services. An individual working on a personal project can use MySQL to find answers to common questions on the Internet. However, a corporation needs a higher level of support, which is available for a subscription fee. MySQL costs nothing to download and install, but you pay for business support.

Microsoft SQL Server has a free Express version exclusively for developers, but it can’t support large projects. You also must be licensed to use SQL Server; the cost depends on the version installed and the number of users connected to the database. Although initially expensive, it can be economical for those who have administrators who fully understand how to support and manage a SQL Server database. For significant issues, however, Microsoft charges an hourly fee for support.

Development Language and Environment

Some development languages work more efficiently with MySQL; others work better with SQL Server. MySQL is popular with PHP development and other open-source frameworks. The two environments work together smoothly and with minimal effort, and developers don’t need to deal with bulky installs to connect software to the database.

MySQL works in a .NET environment, but not without some problems. SQL Server integrates seamlessly into a .NET environment, including VB.NET and C#, both of which function better with a SQL Server installation than with MySQL. However, you can use either as long as you can work through any issues.

Syntax Differences

SQL Server and MySQL have differences in syntax, which, although minor, could be significant enough to slow down development for programmers unaccustomed to the one you choose for your project. Both are relational databases, each with unique limitations, syntax differences, and maintenance specifications. Most programmers can learn the language during development, but those who are unfamiliar with the right optimisation methods might need a review before any stored procedures get published to production. A poorly designed code can also make it difficult to scale your software in the future, which limits the number of features you can add to your software without overhauling the existing code and database design.

Here is an example of a basic MySQL query that gets the first 10 records from a “customer” table:

SELECT * FROM customer LIMIT 10;

Compare that to a SQL Server query that gets the same 10 records:

SELECT TOP 10 * FROM Customer

Notice the small differences in syntax. SQL Server doesn’t require the terminating semicolon, and the number of records to return is at the beginning of the statement instead of the end. “TOP” is a SQL Server query directive, whereas MySQL places “LIMIT” at the end of the report.

Developers unversed in SQL syntax will take longer to design the database portion of your project. If the job has a hard deadline, it’s better to stick with a database with which developers are familiar.

Canceling Queries

One of the most significant downsides to MySQL is that it cannot cancel queries. If your query affects millions of records, any mistake will affect them all irrevocably. Although developers should test a query before executing it, sometimes they don’t notice the error until after the query starts.

If a bug is present, stopping a query will limit the damage. Doing so on a SQL Server, however, can corrupt a few thousand records versus a few million on MySQL. Canceling queries is useful not only for developers but also for administrators who work with large datasets.

IDE Software

Developers who have years of experience with MySQL are accustomed to using command-line statements to manage the database. The people who developed MySQL also created Workbench, an integrated development environment (IDE) that facilitates database design, maintenance, and programming. Although buggy in its initial release for Windows developers, Workbench is now an excellent tool that makes it easy to maintain a database as well as export and import data, and create backups and stored procedures.

Microsoft also has an IDE, called SQL Server Management Studio. It’s a separate download, so if you install any version of the database, make sure you download the IDE as well. Both Management Studio and Workbench are comparable, and both are great tools for DBAs or programmers.

Both database platforms are viable options, but you should choose the one that your developers are most comfortable with, that will make programming and maintenance easy, and that fits your budget. The wrong choice can mean thousands of dollars in additional expenses if you need to switch later on during development.

If you need support when it comes to the choice or the implementation of your database, Four’s IT consultants are here to help. We can play a key role in the strategic direction of your business.  Our consultancy solutions are all based around partnership; we believe in getting to know the people we’re working for and creating long-term relationships, based on deep understanding of the issues and challenges within an organisation. When you come to Four Business Solutions, you’ll find individuals who hope to become colleagues, and a team that listens and responds, not simply a software sales catalogue.