What is MSSQL?
MySQL is an open source, relational database management system (RDBMS) based on structured query language (SQL). MySQL is available on all major operating systems, including Windows, Linux and Solaris. It is free to use for individuals and non-production environments under the GNU General Public License; however, if used commercially, a commercial licence is required.
MySQL, like other relational databases, stores data in tables, columns and rows. Each entry is defined by a unique identifier. Its raison d'être has always been the performance and reliability of the database. MySQL was designed and optimized for the web development arena; it is arguably the most common database used in web server deployments. MySQL works very well with Apache and PHP and is often the go to database for LAMP stack deployments. MySQL powers 9 out of 10 websites on the internet today, and is the database chosen by Facebook, Twitter and Wikipedia.i
Microsoft SQL Server (MSSQL) is widely used in enterprise deployments. MSSQL is a scalable data platform which includes several ETL (Extract, Transform and Load) tools and reporting services where data can be added, modified and queried using a standardized structured query language (SQL). MSSQL is an evolving data platform used for mission critical business and data solutions on premise, in the cloud and on hybrid platforms.i
A Brief History of MSSQL
SQL syntax and MSSQL are related but distinct. SQL syntax is the language used to query databases, and MSSQL is the Microsoft database product suite which uses SQL syntax. The very first versions of SQL server were developed by Ashton Tate, Sybase and Microsoft between 1988 to 1993 and were built on Unix-based operating systems. As Microsoft started to dominate desktop operating systems in the early 1990’s, focus shifted to develop SQL Server for Windows. In 1993, SQL Server 4.21 was released, and this was the first SQL database platform created for Microsoft Windows to take advantage of the graphical user interface.
Up to now, Sybase had licensed their Dbase database technology to Microsoft; this technology was widely used in SQL Server, but the terms of the license did not allow Microsoft to change the source code without explicit consent from Sybase. In 1994, the companies parted ways, and Microsoft shifted development focus to release SQL Server 6.0 (SQL95). They quickly followed in 1996 with SQL Server 6.5, which introduced Internet and Data Warehousing support for the fledgling World Wide Web.ii
In 1998, Microsoft completely rewrote SQL Server 7.0 removing any legacy Sybase functions and adding a vast amount of new features. SQL Server 2000 was released to coincided with Windows Server 2000.iii This started off the SQL Server suite release cycle familiar today, all subsequent versions of SQL have been upgraded with added features and services and generally coincide with an Operating System release date.
Why Choose MSSQL
MSSQL server is an incredibly popular database solution used today, and one of its strongest advantages is its ease of use. MSSQL comes with many excellent tools which make database development a fast and agile process. SQL Server management studio allows any approved user to manage and maintain the databases, run SQL queries, perform backups and analyze performance charts. MSSQL integrates with Visual Studio to give your DevOps team a powerful, familiar platform to create and manage custom applications which seamlessly integrate with MSSQL Server.
MSSQL Main Features
There are many products which make up the SQL Server database platform, but there are 4 key services built into MSSQL which define it and make it a popular choice as a database management system (DBMS). These options are available to install when deploying the MSSQL instance. The latest releases of MSSQL are not just compatible with Windows; more recently, Microsoft has offered SQL for Linux (Red hat and SUSE), as well as Docker container platforms.
Database Engine
The SQL Database engine is the core of the MSSQL product suite. This was the original product which is used to store, process and secure data. The data is stored in one or many database instances. Some of the key database engine features include storing data in instance tables, and the ability to do XML data import, Blob data management (Binary Large Objects), DB Triggers, transaction logs, data compression, data search and maintenance plans, to name a few.
Integration Services (SSIS)
SSIS is a data movement tool which can import and export data from a database. It is widely used to design ETL processes (Extract, Transform, Load). You can extract data from almost any source (for example, other databases, text files or Excel documents), transform it by merging, filtering, sorting fields or aggregating dataiv, and you can load this data into a destination, often a shared folder or even another database/application.v
Reporting Services (SSRS)
SSRS is a comprehensive reporting platform for SQL Server which is used to create and deploy several types of paginated reports. SSRS can report on any data in a database and display it in either simple charts or complex data visualizations. SSRS can report on almost any database source data. Reports can be displayed on the reporting services website or integrated into any application supporting .NET. SSRS statements are commonly used by companies to display KPIs like sales data or number of orders processed per hour and thus are one of the features most highly sought by executive teams.vi
Analysis Services (SSAS)
SSAS is a multidimensional, online analytical processing (OLAP) and data mining server.vii SSAS is a separate database which is fed data via SSIS from any other data source to build cubes of relational data in a data warehouse. This is a highly efficient database allowing deep queries to run in near-instant timing across vast amounts of data. An example of a typical SSAS statements would be the analysis of all sales in the EMEA region for a particular month; SSAS will allow you to do this using MDX expressions.viii
Summary
MSSQL Server is one of the best database solutions available today in the SQL marketplace. If you are looking for a secure, easy to manage and high-performance database management system for your compliant database, then MSSQL should be a serious option to consider. It enables users to analyze data, forecast sales and even predict customer behaviour using business intelligence analytics.
References
iMicrosoft SQL Server - US (English). (2018). SQL Server 2017 on Windows and Linux | Microsoft. [online] Available at: https://www.microsoft.com/en-us/sql-server/sql-server-2017 [Accessed 8 May 2018].
iiSpenik, M; Sledge O (2000). Microsoft SQL Server 2000 DBA survival guide. SAMS. Chapter 2. ISBN 0672324687.
iiiHarris, Scott; Curtis Preston (2007). Backup & Recovery: Inexpensive Backup Solutions for Open Systems. O'Reilly. p. 562. ISBN 0596102461.
ivDocs.microsoft.com. (2018). Install SQL Server Database Engine. [online] Available at: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-database-engine?view=sql-server-2017 [Accessed 8 May 2018].
vDocs.microsoft.com. (2018). SQL Server Integration Services. [online] Available at: https://docs.microsoft.com/en-gb/sql/integration-services/sql-server-integration-services?view=sql-server-2017 [Accessed 9 May 2018].
viDocs.microsoft.com. (2018). What is SQL Server Reporting Services (SSRS). [online] Available at: https://docs.microsoft.com/en-gb/sql/reporting-services/create-deploy-and-manage-mobile-and-paginated-reports?view=sql-server-2017 [Accessed 9 May 2018].
viiWhigham, S. (2018). SQL Server 2008/R2 Analysis Services (Training Course) | LearnItFirst. [online] Learnitfirst.com. Available at: http://www.learnitfirst.com/Course165 [Accessed 9 May 2018].
viiiMehta, S. (2018). SQL Server Analysis Services (SSAS) Tutorial. [online] Mssqltips.com. Available at: https://www.mssqltips.com/sqlserver-tutorial/2000/sql-server-analysis-services-ssas-tutorial/ [Accessed 9 May 2018].
