September 20, 2019 by Mike Hillwig
Earlier this year, I made the decision to go back to school and finish get my bachelor’s degree in Data Analytics at Southern New Hampshire University. This term, and taking Introduction to Quantitative Analytics and IT-204:Intro to Data and Info Mgmt. One of our assignments was a group task, where we had to define the pros and cons of a specific database platform. My group was assigned SQL Server. This was my blog post to share with my group. My audience was a group of college students, so I tried to avoid getting too technical.
While I’m thinking about it, let me lay out the pros and cons that I know about SQL Server. This comes from my years of experience with the platform and my years of educating others. I’m a former Microsoft SQL Server/Data Platform MVP, so I’ve got a little bit of credibility on this topic. Please keep in mind that I’m very passionate about this technology and acknowledge that I have a bias.
SQL Server typically runs on Microsoft Windows Server, which tends to be more accessible to most smaller IT organizations. SQL Server standard edition running on Windows standard edition, tends to be very cost effective. Better yet, SQL Server on CentOS Linux is even more cost effective. Using an IDENTITY column as a primary key (via a clustered index), makes uniqueness very simple. It also makes JOINs very fast. Microsoft was one of the earliest adopters of putting their database technology in the cloud. Thus, Azure SQLDB can be both cost effective and easy to manage. SQL Server has a very passionate community supporting it, called PASS. PASS provides free and low-cost training for technology professionals. If you ever want to learn more about SQL Server, look up SQLSaturday in your city. I was the organizer of SQL Saturday Boston for several years. The SQL community is very active on Twitter with the #sqlhelp hashtag. SQL Server’s BI stack is built right into the platform. Since SQL 2016, Microsoft has supported SQL Server on both Windows and major Linux distributions. SQL Server includes built-in maintenance tools for backups and index management. The two GUIs for SQL Server (Management Studio and Azure Data Studio) allow you to manage the database without learning a lot of command line tools. Caution: This is a double-edged sword. SQL Server has two tools for disaster recovery, log shipping and availability groups. Both are available to standard edition. Unlike Oracle, SQL Server’s options for any edition are included in the price. Oracle makes you pay extra for individual features, such as Dataguard and RAC.
There are a few cons as well.
SQL Server has an 8K limit for data pages. This means that SQL Server is very good with narrow tables, but the 8K limit can be a challenge for wide tables. Some of the more advanced features that require Enterprise Edition (such a high availability Always On and in-memory tables) can get very expensive. Sometimes, the UI can be too easy. This leads inexperienced people to find configuration options that allow them to create their own disasters. SQL Server is closed-source commercial software. That’s important if your company is used to using open source software. Not all of SQL Server’s tools are available on Linux platforms. This includes Analysis Services and Reporting Services.
If anyone has questions, I’m happy to answer them.