Microsoft SQL Server is a relational database management system (RDBMS) that supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments. It is one of the three market-leading database technologies, along with Oracle Database and IBM’s DB2.
This software supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL (Transact-SQL). T-SQL is a Microsoft propriety Language known as Transact-SQL. It provides further capabilities of declaring variable, exception handling, stored procedure, etc.
This product is built for the basic function of storing retrieving data as required by other applications. It can be run either on the same computer or on another across a network. Microsoft SQL Server suite aiming to facilitate and ease data warehousing and application development. It is a cloud-ready platform, which means organizations can benefit from protection while deploying and synchronizing data across desktops and modern devices.
SQL Server’s high rate of success is mostly due to its power to store and then retrieve data for third-party software that rely on it to successfully manage information inside databases. Another advantage is brought by the advanced security features that protect critical workloads from unauthorized access.
Built to satisfy a wide array of audiences, Microsoft SQL Server is available in multiple editions, each targeting a different purpose. The most popular are SQL Server Enterprise, which aside from the core engine, includes a rich list of add-ons and extensive processor support, followed by SQL Server Standard, which features support for fewer instances and is addressed to medium level businesses. The most recent of the editions is Business Intelligence, which includes memory analytics and focuses on Self Service.
Microsoft SQL Server Software Features
- Always on Availability Groups— This feature takes database mirroring to a whole new level. Always on, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable and can be used for database backups. The big win is that your DR environment no longer needs to sit idle.
- Windows Server Core Support — If you don’t know what Windows Server Core is, you may want to come up to speed before Windows 8 (MS is making a push back to the command line for server products). The core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported by SQL Server.
- Columnstore Indexes— This a cool new feature that is completely unique to SQL Server. They are a special type of read-only index designed to be used with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.
- User-Defined Server Roles— DBAs have always had the ability to create custom database roles, but never server-wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of these was a good solution. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.
- Enhanced Auditing Features— Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.
- BI Semantic Model— This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It’s a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics
- Sequence Objects— For those folks who have worked with Oracle, this has been a long-requested feature. A sequence is just an object that is a counter — a good example of its use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.
- Enhanced PowerShell Support— Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of its server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.
- Distributed Replay— Once again this is the answer to a feature that Oracle released (Real Application Testing). However, and in my opinion where the real value proposition of SQL Server is, in Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.
- Power View— You may have heard of this under the name “Project Crescent” it is a fairly powerful self-service BI toolkit that allows users to create mashups of BI reports from all over the Enterprise.
- SQL Azure Enhancements— These don’t really go directly with the release of SQL 2012, but Microsoft is making some key enhancements to SQL Azure. Reporting Services for Azure will be available, along with backup to the Windows Azure data store, which is a huge enhancement. The maximum size of an Azure database is now up to 150G. Also, Azure data sync allows a better hybrid model of cloud and on-premise solutions
- Big Data Support— I saved the biggest for last, introduced at the PASS (Professional Association for SQL Server) conference last year, Microsoft announced a partnership with Hadoop provider Cloudera. One part of this involves MS releasing an ODBC driver for SQL Server that will run on a Linux platform. Additionally, Microsoft is building connectors for Hadoop, which is an extremely popular NoSQL platform. With this announcement, Microsoft has made a clear move into this very rapidly growing space.
What’s new in Microsoft SQL Server
- Support to Persistent Memory (PMEM) Devices
- Column store Index Enhancements
- Resumable Online Index Creation
- Up to Five Synchronous Replica Pairs for Availability Groups
- Enable High Availability Configurations for SQL Server Running in Containers
- Better Scale-out with Automatic Redirection of Connections Based on reading/write Intent
- SQL Data Discovery and Classification
- Always Encrypted with Secure Enclaves
- UTF-8 Support
- Certificate Management Functionality in SQL Server Configuration Manager
- Vulnerability Assessment
Benefits Microsoft SQL Server
- Easy Installation: All the Microsoft products are easy to install with the one-click installation procedure and readable GUI with lots of instructions for the layman. MS SQL Server contains all these characteristics and it has an extremely user-friendly installation interface, unlike other database servers that require extensive command-line configurations.
- Improved Performance: MS SQL server contains excellent compression and encryption capabilities that result in improved data storage and retrieval functions.
- Security: MS SQL server is considered one of the most secure database servers with complex encryption algorithms making it virtually impossible to crack the security layers enforced by the user. MS SQL server is not an opensource database server which reduces the risk of attacks on the database server.
- Multiple Editions and Price Variations: A good thing about MS SQL server is that it is available in multiple editions in order to cater to the needs of huge corporate sector organizations to a domestic user. The price range also varies which allows anyone to buy the product which meets their price range.
- Excellent Data Restoration and Recovery Mechanism: MS SQL server is fully aware of the importance of your data. Hence MS SQL Server contains many sophisticated features that allow you to recover and restore the data which has been lost or damaged. Although you cannot recover individual records it is possible to restore the complete database using some advanced recovery tools contained in the MS SQL Server database.
How to Install Microsoft SQL Server 2017
Step 1) Double click on “SQLServer2017-SSEI-Dev.exe”. The below screen will appear with three options: Basic, Custom, and Download files.
Step 2) Choose the basic version by clicking on the ‘Basic’ option, as it has all default configuration required to learn MS SQL.
Step 3) ‘Microsoft Server License Terms’ screen will appear. Read the License Terms and then click ‘Accept.’
Step 4) Below the ‘SQL server install location’ window will appear.
- The default location is C:\Program Files\Microsoft SQL Server.
- Optionally, we can also change the installation location by clicking on Browse.3. Once the location is selected, click the ‘Install’ button.
Once, the download is complete; the system will initiate installing developer edition.
Below ‘Downloading install package’ progress screen will be displayed. Wait until the download is complete.
Below the screen show installation progress.
Step 5) Once installation is completed successfully, below screen will appear.
This setup is self-sufficient for proceeding further with learning the SQL server, and we can ‘Close’ this window.
Pros & Cons
|It is Various supported editions.||There is No native support for source control.|
|It’s Online product documentation.||It is Complex performance tuning features.|
|This software On-premises and cloud database support.||It’s an expensive enterprise edition.|
|It is a free developer edition (with all enterprise features).||It’s a difficult licensing process that’s always changing.|
|Processor||2 GHz or faster processor, 2 cores|
|Memory||At least 4GB RAM|
|Hard Disk Space||6GB available|
|Video Card||Super-VGA 800 x 600 or higher resolution monitor|
|Operating System||Windows: Windows Server 2012, Windows Server 2012 R2, Windows Server 2016, or Windows 8 to Windows 10
Linux: Red Hat Enterprise Linux 7.3, SUSE Linux Enterprise Server 12 SP2, or Ubuntu 16.04, Docker Engine 1.8+ for container-based deployments • 3.25 GB RAM • XFS or EXT4 file system
|Other Requirement||.NET 4.6 (required when selecting Database Engine, Master Data Services, Replication).|
|Tittle||Microsoft SQL Server|
|File Size||4.70 MB|