Introduction to SQL Server
Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. It is a highly scalable product that can be run on anything from a single laptop, to a network of high-powered cloud servers, and anything in between.
Of course, by "anything", it still needs to satisfy the usual hardware and software requirements, but these requirements are reasonably modest, considering what SQL Server is capable of.
According to Microsoft, SQL Server is the number 1 most-used database in the world. According to various other sources, it's more like number 2, 3, or 4. In any case, it's one of the most popular database management systems in the world.
Since the release of SQL Server 1.0 in 1989, SQL Server has evolved to become a true, enterprise information platform. While it's core function is that of an RDBMS, SQL Server has become much more than that. SQL Server 2016 includes built-in business intelligence tools, as well as a range of analysis and reporting tools. This is on top of the database management tools such as database creation, backup, replication, security, and more.
This tutorial focuses more on the database management tools.
Database Management Tools
SQL Server comes with a number of tools to help you with your database administration and programming tasks.
Some typical database administration and programming tasks could include:
- Create & maintain databases
- Create & maintain tables
- Create & maintain other database objects such as stored procedures, views, etc
- Create & maintain and schedule data backups
- Replication (eg, create a copy of the database)
- Create & maintain users, roles, etc
- Optimization tasks
These are some of many tasks that a database administrator (DBA) might need to perform. SQL Server provides the means for performing these tasks.
Client/Server Database Systems
SQL Server is a client/server DBMS, as opposed to a desktop system such as Access.
Client/server systems are designed to run on a central server - or servers - so that multiple users can access the same data simultaneously from across a network. Users normally access the database through an application.
For example, a web-based corporate CRM could be used by employees in various cities, or even countries, all reading and updating data via their browser.
Generally, it's the CRM application that provides the functionality to these users (for example, the CRM could be built using say, ColdFusion, HTML, and JavaScript). It is the database that stores the data and makes it available. However, SQL Server does include some useful features that can assist the application in providing its functionality.
Other client/server databases include MySQL, Oracle, and PostgreSQL, to name a few.
SQL Server 2016 Enhancements
SQL Server 2016 includes a number of enhancements from its predecessor, SQL Server 2014. It would probably take a whole new tutorial to list them in detail, but in a nutshell, the improvements in SQL Server 2016 over 2014 include:
- Better security
- With Always Encrypted, Row-Level Security, and Dynamic data masking
- Higher availability
- Including AlwaysOn Availability Groups, Cloud Witness, Storage Spaces Direct, Workgroup clusters
- Improved database engine
- TempDB enhancements, Query Store, Stretch Database,
- More analytics
- Tabular enhancements, R integration)
- Various improvements to reporting
- Search, custom branding, optimization for modern browsers, mobile, etc
SQL Server 2016 Editions
SQL Server 2016 comes in five editions:
- Enterprise
- Standard
- Developer
- Web
- Express