Microsoft SQL Server

What is Microsoft SQL Server?

Microsoft SQL Server (popularly called SQL Server or MSSQL) is Microsoft’s implementation of the RDBMS philosophy and used to build, maintain and run relational databases in an enterprise environment. A relational database is one where the tables in the database are related to each other through common data fields (i.e. columns or attributes) that have keys defined on them. The primary key which is local to a table and the foreign key which is the primary key of a table but foreign to the referring table.  The keys help one table to access and pull the data of another table so that the combined data becomes a collective unit and can be manipulated collectively.

The chief competition to Microsoft SQL Server comes from MySQL and Oracle Database with Microsoft SQL Server placed ahead of Oracle Database and a bit behind MySQL. To understand the place of Microsoft SQL Server and appreciate it we will go briefly through its history and then do a comparison with one of its chief competitor MySQL. The comparison will help us understand it strengths and limitations and the motivation and effort that has gone into the product over the years to make it a world class client server RDBMS solution across the world.

The Microsoft SQL Server architecture has two main components – the database engine (which includes the single storage engine and query processor) and the operating system SQLOS. The storage engine is responsible for data storage and retrieval. The query processor is responsible for generating query execution plan, handling batch tasks, thread management and managing IO, memory and buffer for the executing queries. SQLOS is responsible for providing operating system services of IO and memory management, interrupt and exception handling and synchronization services.

History and Evolution

SQL Server was initially jointly developed by Microsoft with Sybase and the first version called SQL Server 1.0 was released for the OS/2 operating system in April 1989. Later on, Sybase sold the product to Microsoft and it became Microsoft SQL Server. Microsoft SQL Server is a 31-year-old product and there have been 15 versions of the product so far with the latest being Microsoft SQL Server 2019 which was released in November last year. Till 2016 Microsoft SQL Server only worked in the Microsoft Windows operating system. Post 2016 support was added for Linux and now it also supports MacOS via docker. The code of Microsoft SQL Server is powered by C, C++.

Comparison

The following table is a broad-brush comparison of Microsoft SQL Server and MySQL for a better comprehension of what MSSQL brings to the table.

Comparison ParameterMSSQLMySQL
OwnershipMicrosoft CorporationOracle Corporation
OS supportPrimarily compatible with Microsoft Windows but also supports Linux and MacOS (only through docker)Supports almost all operating systems
DistributionThere are two free editions – Microsoft SQL Server Developer Edition (not for production use) and Microsoft SQL Server Express Edition (can be used in medium workload production environments). The remaining 3 editions are paid editions and include Microsoft SQL Server Standard Edition, Enterprise Edition, Web Edition. So it has more customization and variety for customers to choose from as per need.There is a free production use community edition which is distributed under GNU GPL. The remaining editions are paid and include MySQL Standard, Enterprise and Cluster Grade Edition (CGE)
PricePaid versions are significantly costlier than MySQL paid versionsPaid versions are significantly cheaper than MSSQL paid versions
Installation and SetupInstallation is through installer exe and easy and straightforwardInstallation is equally easy through RPM albeit a little complicated to MSSQL installation.
Language SupportIt is available in a multitude of languages including English, Japanese, French, Spanish, Chinese etc.It is available only in English
Programming language supportPrimarily compatible with .NET languages like C#, Visual Basic but supports other major programming languages including Java, PHP, Python, Ruby, Delphi, Go and RSupports all programming languages supported by MSSQL and additionally Perl, Scheme, Tcl, Haskel and Eiffel
Development LanguageDeveloped in C and C++Developed in C and C++
BackupIt can back up the database without blocking it and hence database operations are not affectedBlocks the database during backup making it unavailable. Takes more time to backup and restore
Storage engineIt uses a single Microsoft developed storage engineSupports multiple storage engines and even allows different tables in the same database to be managed by different storage engines
Performance and ScalabilityPerformance wise it is very good and scales better than MySQLPerformance wise it is equally good though it is a bit weaker in terms of inserts and deletes. Scalability is also bit of an issue and performance is seen to drop on scaling.
IDE SupportThe primary IDE is SQL Server Management Studio (SSMS) which is more feature rich and robustPrimary IDE is MySQL Workbench which is not as good as SSMS
Filtering capabilitiesFiltering is better and easierFiltering is difficult and has to be done separately by running multiple queries individually on the object to filter
Securityit is proprietary, supports Microsoft Baseline Security Analyzer (MBSA) for security baselining and does not allow direct access and edits to database files which make it more secureIt does not have any inbuilt security tool like MBSA, allows access and edits to database files while it is online and is open source which makes it less secure.
TriggersIt supports triggers on views and multiple triggers on a tableIt does not support triggers on views and multiple triggers on a table
Product SupportSupport is mostly paid and costlyVery good community support which makes paid support redundant even though it is available
SQL supportIt follows the ANSI SQL standard more completely and extends it through Transact-SQL (T-SQL) and is syntactically easierIt does not follow the full ANSI SQL standards and is syntactically relatively difficult
DBaaS offeringsNone availableAvailable as Azure Database for MySQL and ScaleGrid for MySQL
Query cancellationIt allows running query cancellationIt does not allow running query cancellation
BI capabilitiesIt has better BI capabilities with tools like BIDS and SSDT for data analysis, reporting and ETLIt has less inbuilt capabilities though there are third part tools which support BI functionality for MySQL

From the above comparison we can see that Microsoft SQL Server is a very strong RDBMS product and which can hold its own and even prevail over other such products including its chief competitor in the market.

Advertisements

Leave a Reply