What is Microsoft SQL Server?
Microsoft SQL Server has had a presence in the marketplace for more than two decades and as a result is a well supported database that offers ease of integration with other Microsoft products. SQL Server has native integration with many components in the Microsoft BI stack which means SQL Server can be used to enable your analytics from data loading (ETL) to reporting. SQL Server has seen renewed focus from Microsoft with the release of SQL Server 2016 which includes new capabilities with a focus on an hybrid approach to on-premise/cloud and advanced analytics such as R.
The characterization here is that with open source you have to choose between a rich feature set (Postgres) and robust performance (MySQL). With SQL Server you have advanced functions, integrations with .NET, integration with R, combined with super fast performance. Only thing is it costs a lot.
SQL Server is still being actively developed today, and SQL Server 2016 includes many long awaited advancements to make it a competitive database option. The new features include always encrypted, dynamic data masking, JSON support, PolyBase, Query Store, row level security. In addition new capabilities such as stretch database that allow you to seamlessly access your historical data regardless if it is on-prem or stretched to Azure cloud, and advanced analytics extensions now allow users to execute scripts written in a supported language such as R have been introduced.
SQL Server pricing & considerations
Integration with Microsoft ecosystem
SQL Server integrates seamlessly with other components of the Microsoft stack, and makes it extremely easy to scale to Microsoft’s cloud data warehousing solution, Azure SQL Data Warehouse as the need for analytical workloads increases.
Mature offering and high availability of skilled professionals
SQL Server has a well-established track record and large installed base. Way more features.
Enterprise-grade development and support
SQL Server offers such enterprise-grade options for transaction control, ACID compliance, backup management, and fault tolerance.
What use cases is SQL Server really great for?
OLTP environments (specifically .NET environments)
If you are looking for a database backend for an application written in .NET, SQL Server is an ideal choice. It will give you a proven enterprise grade OLTP backend that will work well with all components in your stack, and may be cheaper to own and maintain when bundled with a variety of other Microsoft components.
Operations running a Microsoft stack
Microsoft’s ecosystem is massive, so rather than using one database with a bunch of 3rd-party services, you can invest completely in Microsoft’s platform. This means that you can leverage Microsoft’s extensive integrations with other Microsoft data technologies such as SSIS (SQL Server Integration Studio) and SSRS (SQL Server Reporting Services).
Organizations looking for the speed of MySQL with the extensibility of Postgres
Microsoft SQL Server contains the distributed architecture of MySQL that enables extremely fast performance and high throughput, which the SQL Support and extensibility of Postgres. It’s a full service solution, designed for an organization looking for a full-featured enterprise transactional database.
The Express Edition of SQL Server is free and ships with a smaller feature set (appropriate for small blogs, intranet sites or ad hoc relational storage). The full edition can range in price because of upfront costs and ongoing license/maintenance fees based on a wide range of factors. Choosing to run SQL Server in the cloud also gives you the opportunity to purchase per-minute licensing for SQL Server.
However, the price for SQL server can often be drastically reduced or eliminated when bundled with other enterprise Microsoft offerings such as Forefront Identity Manager, Visual Studio Team licenses, Outlook, Exchange server etc. If you are investigating in using SQL server it may make a large difference in the final cost/benefit calculus if you are planning on using a variety of other Microsoft technologies.
SQL Server architecture
SQL Server’s architecture is similar to MySQL in that the database is typically run as a clustered configuration that is, distributed files across a number of individual machines. SQL Server is also similar to MySQL in that the storage components of database are distinct from the computation components of the database.
SQL Server thus has two distinct components: the relational engine and the storage engine.
The relational engine is responsible for for breaking down a SQL query into individual logical components, and estimating the cost associated with executing a variety of steps to retrieve the data necessary to complete the query. The engine then chooses the query execution plan that is the most efficient (that is, has the lowest cost), and then executes each logical step within that query plan.
The storage engine on SQL Server is not only responsible for distributing the individual files to be stored on the database across nodes in the cluster, it’s also responsible for retrieving and reading those files when they are accessed by the relational engine. Additionally, the storage engine manages concurrency, deciding when to lock the database when simultaneous reads / writes are occurring.
Types of data
Full support for standard SQL data types, as well as proprietary data types like money that help with standardizing certain types. Users are also capable of creating their own data types with SQL Server.
Maximum recommended size of data
SQL Server has a list of maximum capacity specifications available here.
SQL Server performance tuning and management
Implementing SQL Server
You can implement SQL Server one of two ways: you can either install it on commodity hardware that you already own, or spin up a virtual machine with SQL Server already installed (essentially run SQL Server in the cloud).
SQL Server on-premise
To install SQL Server on-premise, you’re going to want to reference SQL Server 2016’s Hardware and Software requirements and then purchase commodity hardware that fits these requirements. You’re then going to want to reference Microsoft’s installation guide to help you install the software.
SQL Server on Azure
Installing SQL Server in the cloud is a lot easier. If you already have an Azure account, you can simply click a link to create an instance of SQL Server in the cloud. More considerations around creating an instance of SQL Server on Azure can be found here.
Process for new data
SQL Server comes natively with a very good flat file/data source importer, which is based on SSIS. The SSIS service works well for visually designing complex ETL schemes and data enrichment processes.
Generally these services are designed to integrate extremely well with other technologies within the Microsoft ecosystem and therefore are particularly well-suited to important data into SQL Server from Microsoft Access and Microsoft Excel.
One of the benefits that comes with SQL Server being part of a large enterprise ecosystem is that there are a lot of tools within the Microsoft ecosystem that allow you to monitor and tune your database. In addition to the software, Microsoft also provides a comprehensive guide to best practices around monitoring and tuning SQL Server for optimal performance.