SQL Server 2016 Standard edition on AWS / Azure /  Google cloud delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premise and cloud — enabling effective database management with minimal IT resources.

SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft.

Similar to other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interacting with the relational databases. SQL server is tied to Transact-SQL, or T-SQL, the Microsoft’s implementation of SQL that adds a set of proprietary programming constructs.

SQL Server works exclusively on Windows environment for more than 20 years. In 2016, Microsoft made it available on Linux. SQL Server 2017 became generally available in October 2016 that ran on both Windows and Linux.

Introduction

Microsoft SQL Server 2016 provides faster transactions and queries, deeper insights on any device, advanced analytics, new security technology, and new hybrid cloud scenarios. Along with these features, comes the new installation setup. Although it is similar to the installation of SQL Server 2014 and other older versions, there are few notable differences:

• System requirements have changed
• SQL Server Management Studio has its own installation process, separated from installation of other services
• New features have been added, with their respective setup options

System Requirements

There are several hardware and software requirements that should be taken into consideration before installing new SQL Server 2016:

1. 64 bit processor, with at least 1.4 GHz clock speed. Older 32 bit processors are not supported by the SQL Server 2016.
2. At least 512 MB of system memory is required, but be aware that some of the features require much more.
3. As for the hard drive – at least 6 GB of free space is required, and it heavily depends on additional features that could be installed. Detailed information on disk space requirements for each feature could be found here.
4. FAT32 filesystem is supported, but it is strongly recommended to avoid it, and use an NTFS formatted drives instead. FAT32 filesystems proved to be less secure then NTFS systems when using SQL Server 2016.
5. Setup cannot be performed on Read-only, mapped or compressed drives.
6. Installing SQL Server 2016 on domain controllers is not recommended, since it can cause security conflicts.

Systems that are currently supported for SQL Server 2016 installation are:

• Windows 8 and 8.1 (All 64 bit editions)
• Windows 10 (All 64 bit editions)
• Windows Server 2012 and 2012 R2

Older systems like Windows 7 and Windows Server 2008 are not supported by SQL Server 2016.

Installation types

There are four ways to install Microsoft SQL Server 2016. The easiest way to perform it is to use the installation wizard. There is also an option to install the program using Command Prompt, Configuration File or SysPrep. No matter which installation type is chosen, the installation files need to be downloaded from Microsoft’s website first. In order to get the download link, either create new Microsoft account, or sign in with the existing one. Choose the file type that will be used for the installation: ISO, CAB, or Azure file. For this demonstration, the ISO file will be used.

After clicking on “Register to continue” button, additional information is required: name, email address, language, and country. There are some more fields that can be filled, but only the fields marked with * are necessary in order to access the download link. Click the Continue button, and download the file to the hard drive.

Installation process

Burn or mount the downloaded ISO image (SQLServer2016-x64-ENU.iso), and start the installation process.

1. Starting the Autorun, or running the Setup.exe will initiate the SQL Server Installation Center. First tab that opens by default is Planning. Many useful links that are found here, lead to pages with more info on the product and installation instructions.
2. It is always a good idea to run System Configuration Checker. It only takes a minute or two to complete, and can save you from possible issues in the future. If the system passes all the tests in System Configuration Checker, proceed to the next step.
3. Clicking on the Installation tab in the upper left corner displays the standard installation options. It is noticeable that the options for SQL Server Services and SQL Server Management Tools stand separated, which was not the case in previous versions of SQL Server. To start the installation, click on New SQL Server Stand-alone installation or add features to an existing installation.
4. Choose which SQL Server 2016 version to install. The Evaluation version gives access to all of the SQL Server 2016 features, as well as trial license limited to 180 days. Other versions can be chosen as well – but they require the activation key to be entered in the field beneath. In this example, the Evaluation version is used. Click Next to proceed.
5. Read and accept License Terms. Check the box in front of I accept the license terms, and click Next.
6. In this step, set the options for Microsoft Automatic Updates. Check the box to enable automatic updates, or leave it unchecked if you prefer to install them manually. Click Next.
7. Wait for the Install Setup Fileand Install Rules to complete. It is possible to get the Windows Firewall warning after Install Rules check is completed. The warning occurs if port 1433 is closed by the Windows Firewall. The warning can be ignored at the moment, but the 1433 port will have to be configured later. There is also an option to specify another port(s) that will be used by SQL Server instance. Detailed instructions on how to configure Windows Firewall to enable Database Engine Access can be found here.
8. On a Setup Role, choose to install All Features with defaults, or perform custom installation with features that you specify. In this example, the custom installation is chosen.
9. In the Feature Selection step, select the features of the SQL Server that need to be installed. You can check the description for each feature on the upper right, as well as prerequisites for all selected features in the box bellow. On the lower right, the Disk Space Requirements are displayed. In comparison to older versions of SQL Server, it is noticeable that Management Tools feature (which installs SQL Server Management Studio) is missing, but it can be installed separately later. For the instance to work, it is only necessary to install Database Engine Services. The latest SQL Server 2016 feature, PolyBase Query Service for External Data, can also be installed, but be aware of additional requirements. At least 4 GB of RAM is required to use this feature, and the installation of Java Runtime Environment 7 Update 51 in the next step will be required. If needed, set Instant root directory and Shared feature directory paths manually on the bottom of the menu. When finished with the selection, click Next.
10. If PolyBase Query Service is chosen in the previous step, the wizard will display the warning about failed test for Java Runtime Environment. Just click on the failed link on the right, and it should open the JRE download page.
11. In the Instance Configuration step, choose the default, or named instance, and specify the Instance ID. Click Next to proceed.
12. In Server configuration step, specify the accounts that can start SQL Server services. Set the startup options for any of these services to Manual, Automatic, or Disabled. Click Next when done.
13. In Database Engine Configuration, under Server Configuration tab, specify the authentication mode and Administrator account(s) for the server. As in earlier versions, Windows Authentication mode and Mixed Authentication mode are available.
14. After checking Feature Configuration Rules, the wizard proceeds to Ready to Install  All of the settings that are specified earlier, can be reviewed here. Check the settings one last time, and click Install to start the installation.
15. Wait for the installation to finish. The process may take some time, depending on hardware specifications.
16. Optionally, install SQL Server Management Studio, so you could easily manage your databases. Start the autorun or setup.exe again, and under the settings tab, choose Install SQL Server Management Tools. This will open the download page for the latest version of SSMS. Click on the download link, and save the installation to the hard drive.
17. Once the download completes, run the file to start SSMS setup. Just click on the Install button, and the installation should complete without any further setup options. Depending on the system, the computer restart might be required.

Advantages of MS SQL Server

Installation Is Streamlined

It can be installed via a setup wizard and the prerequisite updates are detected and downloaded by the installer automatically. The complexity of installing the software is minimized significantly because of automatic installation of updates. Other components such as analytical and database services can be installed separately afterward. Automatic updation also reduces maintenance costs quite significantly.

Security Features Are Better

SQL Server 2008 uses Policy-Based Management to detect security policies that are non-compliant. This feature allows only authorized personnel access to the database. Security audits and events can be written automatically to log files.

Enhanced Performance

The MS SQL server has built-in transparent data compression feature along with encryption. Users don’t need to modify programs in order to encrypt the data. The MS SQL server has access control coupled with efficient permission management tools. Further, it offers an enhanced performance when it comes to data collection.

Lower Cost Of Ownership

SQL server includes effective data management and data mining tools along with disk partitioning. Your server’s optimum maintenance can be ensured by following effective data management practices. These practices also help you ensure the availability and recoverability of data.

Database Engine

The core component of the SQL Server is the Database Engine. The Database Engine consists of a relational engine that processes queries and a storage engine that manages database files, pages, pages, index, etc. The database objects such as stored procedures, views, and triggers are also created and executed by the Database Engine.

Relational Engine

The Relational Engine contains the components that determine the best way to execute a query. The relational engine is also known as the query processor.

The relational engine requests data from the storage engine based on the input query and processed the results.

Some tasks of the relational engine include querying processing, memory management, thread and task management, buffer management, and distributed query processing.

Storage Engine

The storage engine is in charge of storage and retrieval of data from the storage systems such as disks and SAN.

SQLOS

Under the relational engine and storage engine is the SQL Server Operating System or SQLOS.

SQLOS provides many operating system services such as memory and I/O management. Other services include exception handling and synchronization services.

Microsoft SQL Server Services and Tools

Microsoft provides both data management and business intelligence (BI) tools and services together with SQL Server.

For data management, SQL Server includes SQL Server Integration Services (SSIS), SQL Server Data Quality Services, and SQL Server Master Data Services. To develop databases, SQL Server provides SQL Server Data tools; and to manage, deploy, and monitor databases SQL Server has SQL Server Management Studio (SSMS).

For data analysis, SQL Server offers SQL Server Analysis Services (SSAS). SQL Server Reporting Services (SSRS) provides reports and visualization of data. The Machine Learning Services technology appeared first in SQL Server 2016 which was renamed from the R Services.

Microsoft SQL Server Editions

Microsoft SQL Server  on cloud has four primary editions that have different bundled services and tools. Two editions are available free of charge:

Microsoft SQL Server Developer edition for use in database development and testing.

Microsoft  SQL Server Expression for small databases with the size up to 10 GB of disk storage capacity.

For larger and more critical applications, Microsoft  SQL Server offers the Enterprise edition that includes all SQL server’s features.

Microsoft SQL Server Standard Edition has partial feature sets of the Enterprise Edition and limits on the Server regarding the numbers of processor core and memory that can be configured.

For the detailed information on the SQL Editions, check it out the available Server Server 2017 Editions.

In this tutorial, you have a brief overview of the SQL Servers including its architecture, services, tools, and editions.

Microsoft  SQL Server is a relational database management system (RDBMS) developed and marketed by Microsoft. As a database server, the primary function of the SQL Server is to store and retrieve data used by other applications.

Getting Started with Microsoft SQL Server

This section helps you get started with the SQL Server quickly. After completing this section, you will have a good understanding of the SQL Server and know how to install the SQL Server Developer Edition for practicing.

Microsoft SQL Server Basics

The SQL server basics section shows you how to use the Transact-SQL (T-SQL) to interact with SQL Server databases. You will learn how to manipulate data from the database such as querying, inserting, updating, and deleting data.

Microsoft SQL Server Views

This section introduces you to the SQL Server views and discusses the advantage and disadvantages of the database views. You will learn everything you need to know to manipulate views effectively in SQL Server.

Microsoft SQL Server Indexes

In this section, you will learn everything you need to know about the SQL Server indexes to come up with a good index strategy and optimize your queries.

Microsoft SQL Server Stored Procedures

This section introduces you to the SQL Server stored procedures. After completing the section, you will be able to develop complex stored procedures using Transact-SQL constructs.

Microsoft SQL Server User-defined Functions

In this section, you will learn about SQL Server user-defined functions including scalar-valued functions and table-valued functions to simplify your development.

Microsoft SQL Server Triggers

SQL Server triggers are special stored procedures that are executed automatically in response to the database object, database, and server events.

Microsoft SQL Server Aggregate Functions

This tutorial introduces you to the SQL Server aggregate functions and shows you how to use them to calculate aggregates.

Microsoft SQL Server Date Functions

This page lists the most commonly used SQL Server Date functions that allow you to handle date and time date effectively.

Microsoft SQL Server String Functions

This tutorial provides with many useful SQL Server String functions that allow you to manipulate character string effectively.

Microsoft SQL Server System Functions

This page provides you with the commonly used system functions in SQL Server that return objects, values, and settings in SQL Server.

Microsoft SQL Server Window Functions

Microsoft SQL Server Window Functions calculate an aggregate value based on a group of rows and return multiple rows for each group.

Cognosys Provides Hardened images of SQL Server 2016 Standard Edition  on the cloud ( AWS marketplace, Azure and Google Cloud Platform).

Deploy SQL Server 2016 Standard Edition  securely on cloud i.e. AWS marketplace, Azure and Google Cloud Platform (GCP)

Core data management and business intelligence capabilities with minimal IT resources.

Major Features of SQL Server 2016 Standard Edition

Features of SQL

SQL is one of the most demanding skill in the current world. Every day a huge amount of data is collected and one have to deal with these databases to make an insightful information. Hence it is important for us to learn SQL as it is a special-purpose database programming language which help to generate useful strategies from a database and can easily interact with large and massive database, no matter what is the size. These features of SQL make SQL a most powerful tool. Hence, here are some of the major SQL features which makes it a successful database programming language:

Characteristics of SQL

• High Performance
  • SQL provide high performance programming capability for highly transactional, heavy workload and high usage database system. SQL programming gives various ways to describe the data more analytically.
• High Availability
  • SQL is compatible with databases like MS Access, Microsoft SQL Server, MySQL, Oracle Database, SAP HANA, SAP Adaptive Server, etc. All of these relational database management systems support SQL and it is easy to create an application extension for procedural programming and various other functions which is additional features thus converting SQL into a powerful tool.
• Scalability and Flexibility
  • SQL provides Scalability and Flexibility. It is very easy to create new tables and previously created or not used tables can be dropped or deleted in a database.
• Robust Transactional Support
  • With SQL programming can handle large records and manage numerous transactions.
• High Security
  • It is very easy to provide permissions on tables, procedures, and views hence SQL give security to your data.
• Comprehensive Application Development
  • SQL is used by many programmers to program apps to access a database. No matter what is the size of organization, SQL works for every small or large organization.
• Management Ease
  • SQL is used in almost every relational database management system. “Select“, “Create”, “Insert”, “Drop”, “Update”, and “Delete” are the standard and common SQL commands that helps us to manage large amount of data from a database very quickly and efficiently.
• Open Source
  • SQL is an open-source programming language for building relational database management system

Simplify big data
Take advantage of non-relational data with PolyBase technology built in that allows you to query structured and unstructured data with the simplicity of T-SQL.

Mission critical intelligent applications
Deliver real-time operational intelligence by combining built-in advanced analytics and in-memory technology without having to move the data or impact end user performance.

Highest performing data warehouse
Scale to petabytes of data for enterprise-grade relational data warehousing—and integrate with non-relational sources like Hadoop—using scale out, massively parallel processing from Microsoft Analytics Platform System. Support small data marts to large enterprise data warehouses while reducing storage needs with enhanced data compression.

Mobile BI
Empower business users across the organization with the right insights on any mobile device.

Easy to use tools

Use the skills you already have, along with familiar tools like Azure Active Directory and SQL Server Management Studio, to manage your database infrastructure across on-premises SQL Server and Microsoft Azure. Apply industry-standard APIs across various platforms and download updated developer tools from Visual Studio to build next-generation web, enterprise, business intelligence and mobile applications.

Installation Instructions For windows

Installation Instructions for Windows

Step 1) RDP  Connection: To connect to the deployed instance, Please follow Instructions to Connect to Windows  instance on AWS Cloud

1) Connect to virtual machine using following RDP credentials:

• Hostname: PublicDNS  / IP of machine
• Port : 3389

Username: To connect to the operating system, use RDP and the username is Administrator.
Password : Please Click here to know how to get password .

Step 2) SQL Connection: To Connect Microsoft SQL Server Management Studio in windows server, Please follow Instructions to Connect Microsoft SQL Server Management Studio

Step 3) Database Credentials: You can Login by below SQL Database credentials

      SQL UserName : sa ||  Password : Passw@rd123

Note: Please use localhost in the server name when connecting from inside the RDC

Please change the password after the first login.

Step 4) Other Information:

1.Default ports:

• Windows Machines:  RDP Port – 3389
• Http: 80
• Https: 443
• SQL Server Port: 1433 this is by default not allowed on the firewall for security.

Configure custom inbound and outbound rules using this link

Installation Step by Step Screenshots