SQL Server is available, and supports both clustering and cloud computing to exceed the performance and reliability of any single platform. SQL Server is an indemand, capable database system.  SQL Server Express is quite usable for many of our classesAlthough Express has limitationon database size, computing capacity, and available features,the core SQL engine is the same as with other SQL Server versions, and it provides for an easier install and a lighter load on your computer. Microsoft has mostly maintained upward compatibility for SQL between SQL Server versions for many years, so if you have ready access to an older SQL Server installation, version 2008R2, 2012 or 2016, you will also be able to use it.

SQL Server Express is a freely-available small-brother version of Microsoft’s enterprise system, SQL Server. It is also a huge improvement over it, especially regarding the user interface design and the included feature-set. Its main features are:

• Database size limit of 4GB. This excludes logfiles and can be overridden by using multiple databases.
• Support for only one CPU (but multiple cores).
• No SQL Server Agent.

SQL Server Express offers a number of advantages. The first and arguably most important is the complete scalability and integration with SQL Server. This allows you to seamlessly upgrade your database as your needs also grow (remember SQL Server Express is limited to a database size of 4GB). In fact the database upgrade is so smooth that even with no prior experience it can be done in about 10 minutes for a 3GB database.

The SQL Server Express interface is much improved over its MSDE predecessor, and offers such GUI tools like the SQL Server Management Studio Express, the SQL Configuration Manager, the SQL Business Intelligence Development Studio and SQL Server Reporting Services.

SQL Server Express also has a strong family heritage. Being a Microsoft product, one can expect such features as excellent integration with Windows (for example domain authentication), support for dotNET, ODBC and XML, and comprehensive help and support from both the mother company and a large community of users on the web.

SQL Server Express is a bright report of primary relational database processing system (RDBMS), the SQL Server. Fundamentally, the SQL Server is a database management system that can be used to deposit and entry the mention stored in many vary databases. SQL Server comes in non-natural of delightful range of features besides matter insight, reporting, and in-depth campaigner analytics. The enterprise edition of SQL Server training competes nearby enterprise-oriented systems considering Oracle Database (DB) and MySQL. SQL Server Enterprise comes loaded later features and can be too costly for smaller sized companies to retain

Microsoft SQL Server 2016 is a relational database management system (DBMS) for Windows platforms that can be used for building, deploying and managing applications located on premises or in the cloud. The latest version, SQL Server 2016, was released in June 2016. It provides a bevy of and improved capabilities, including:

• A stretch database feature, which can be used to store some data on premises and send infrequently used data to Microsoft’s Azure cloud. Applications can access all data, regardless of where it’s stored.
• The Always Encrypted feature makes it possible to encrypt data at rest and in at the column level.
• Polybase, which integrates SQL Server with Hadoop’s Distributed File System and enables Hadoop data to be queried with SQL and joined with native relational data.
• In-database advanced analytics provides the capability to incorporate the R programming language into SQL Server applications by wrapping it in stored procedures.
• Dynamic data masking can be used to obfuscate data, protecting actual data values from unauthorized personnel for regulatory and compliance purposes.
• Support for temporal data enables automatic tracking of historical changes to data over time.

The latest version also provides a storage format, row-level security and Transact-SQL improvements for -optimized tables.

There are four main editions of SQL Server 2016 :

1. Enterpriseis for mission-critical applications, advanced analytics and data warehousing/business intelligence workloads. This edition is configured to provide high performance and availability for your most important applications. It supports an unlimited number of cores, and usage is constrained by only the operating system.
2. Standardis for core database capabilities, reporting and analytics. Compute capacity can’t exceed 24 cores; maximum usage is 128 GB per instance.
3. Developeris designed for application development purposes (as opposed to production database usage). Its core and support is the same as for the Enterprise edition.
4. Expressis a free edition geared primarily for learning and basic introductory usage. Express edition uses the same database engine and offers compatibility with the main SQL Server editions.

SQL Server Express is the most basic offering available. It is a full database engine you can deploy to a server or embed into an application. Express is free and comes with many of the same features as the enterprise edition. SQL Server Express is probably most suited to supporting production applications for smaller to midsize businesses. A typical SQL Server Express use case would be a deployment by developers who do not want to create applications with a database hosted on a server. Using Express, they would be able to develop apps with their SQL Server database.

SQL Server Express Benefits:

• Free:One huge advantage of SQL Server Express is that it is free. Your only expenditure is the time investment you make downloading and setting up the system. If you only want to learn how to use SQL Server, then Express is for you. There is nothing to lose by downloading the system and getting used to how it works.
• Scalability:SQL Server Express is an idyllic starting point for smaller independent software vendors (ISVs) since it can be used with any smaller application. The licensing allows Express to be included as part of an app or product. While there are limitations on memory and socket usage, they are not as restrictive as some might think. Express is not limited to a single user which is a commonly held misconception. There is a 10GB database restriction, but that is a maximum size per database meaning you can have multiple databases that store up to 10GB of data.
• Security:Within SQL Server Express there is the option of free online backup that will help to protect your valued business data if anything goes wrong. Administrators should still follow security best practices like limiting access to backup folders and following Windows password policies.
• Features:While Express is the “lite” version of SQL Server, there is still an impressive range of features that you would have to pay for with other systems. Express supports Full-Text Search, native XML, and the SQL Common Language Runtime. Other key features include a reporting component and report designer enabling custom report creation.

SQL Server Express Limitations:

• 1GB maximum memory used by the SQL Server Database Engine
• The maximum size of each relational database is 10GB
• SQL Agent is not included in Express. The SQL Agent is a background tool which enables administrators to automate tasks like backing up data, database replication setup, job scheduling, user permissions, and database monitoring.
• The limit on the buffer cache for each instance is 1MB of RAM.
• The relational database engine is restricted to the lesser of 1 socket or 4 cores.

Versions of SQL Server Express

SQL Server 2016 SP1 Express: This is the core express offering and comes with the features we have already laid out. In addition, capabilities like dynamic data masking and row-level security are now available in Express.

SQL Server 2016 Express with Advanced Services: This edition of Express contains the most features. Included are the relational database engine, Management Studio, reporting, and full-text search. Ideal for developers working on smaller applications which need to implement reporting on their applications.

SQL Server 2016 Express LocalDB: This edition is mainly used as a development tool. It is a lighter version of Express that runs as a local app. It does support all the main programmability features of the other editions. The big advantage of LocalDB for developers is that it allows them to create a local environment that is much easier to install and manage. Users can just start up an instance of LocalDB runtime as they need it and do not need to install a service.

Microsoft SQL Server 2016 licensing

• Online retailers, which offer full-packaged product licenses of SQL Server software;
• OEMs, which offer preinstalled licenses with their hardware systems; and
• Licensing solutions partners and enterprise software advisors, which offer SQL Server software through Microsoft Volume Licensing programs for end-customer organizations.

There are two main licensing options for SQL Server 2016:

• Server + CAL, which enables customers to license users and devices at a lower cost, adding to their SQL Server deployment as needed; or
• Per Core, which offers a more precise and consistent measure of computing power, regardless of whether SQL Server is deployed virtually, on physical servers, on premises or in the cloud.

A free, full-featured trial version of SQL Server 2016 is available for 180 days. Microsoft offers Premier Mission Critical Support for SQL Server 2016 that’s based on an organization’s specific usage and requirements.

Data types supported

In addition to the standard numeric, character and date/time data types supported by all relational DBMSes, Microsoft SQL Server 2016 supports money, XML and variable binary data types, as well as user-defined types. Support for system-versioned temporal data was added in the latest version.

Five reasons to run SQL Server 2016 on Windows Server 2016

1.Always On Availability Groups: Enhanced capabilities supporting new scenarios

Always On Availability Groups have been at the center of SQL Server availability since the 2012 release. Availability Groups establish a relationship between a set or group of databases and replicas of that group of databases on one or more replicas. This means all the databases in the group can move as a unit, eliminating the need for complex scripting solutions to do this task.Up to now, with Windows Server Failover Cluster solutions, all nodes in the Availability Group had to reside in the same Active Directory domain. However, many organizations have multiple domains that can’t be merged, and they want to span an Availability Group across such domains. In other situations, organizations may have no Active Directory domains at all, yet still want to host disaster recovery replicas.To give these organizations a solution, the SQL Server and Windows Server teams delivered Windows Server 2016 Failover Clusters (WSFC). Now, all nodes in a cluster no longer need to reside in the same domain—and indeed the nodes are no longer required to be in any domain at all. Instead, you can form a WSFC cluster with machines that are in workgroups.

SQL Server 2016 is able to deploy flexible Always On Availability Groups in environments with:

• All nodes in a single domain
• Nodes in multiple domains with full trust
• Nodes in multiple domains with no trust
• Nodes in no domain at all

With SQL Server 2016 and Windows Server 2016, Always On availability groups can include up to eight readable secondaries and can span multi-domain clusters. In addition, Active Directory authentication is no longer required. All this innovation opens up new scenarios and removes previous blocks that prevented migration from deprecated Database Mirroring technology to Always On Availability Groups.

2.Hybrid Backup and Stretch Database provide online cold data availability in Azure

SQL Server 2016 and Windows Server 2016 are architected to work smoothly with the Microsoft Azure cloud in a hybrid environment. Microsoft hybrid cloud technology provides a consistent set of tools and processes between on-premises and cloud-based environments. This means that SQL Server 2016 is designed to work in a hybrid cloud environment in which data and services reside in various locations. You get faster hybrid backups and disaster recover that lets you back up and restore on-premises databases to Azure and place SQL Server Always On secondaries in Azure. The figures below show how Stretch Database works.

With this flexibility come new ways to save money and address business needs. For example, storing data is a critical business requirement that can be very expensive. To reduce this cost, SQL Server 2016 introduced Stretch Database. It allows production databases to offload older (cold) data to the Microsoft Azure cloud without losing access to the data. Many enterprises need reasonably quick access to their cold data for compliance reasons, and they can now push that data to the cloud to save money on storage costs while still having ready access for compliance audits. (Blog 5 in this series will discuss SQL Server running in a Windows Server infrastructure-as-a-service virtual machine on Azure.)

This means you no longer need to rely on extremely expensive dedicated solutions from storage vendors. In SQL Server 2016, Stretch Database lets you keep as much data as you need for as long as you need, without risking business service level agreements or the high cost of traditional storage. Database administrators need only to enable the database for stretch, and the endless storage and compute capacity of Azure ensures that your data is always online.

In addition, with SQL Server Backup to URL, you can easily back up directly to Microsoft Azure Blob Storage. You no longer need to manage hardware for backups, and you get the benefit of storing your backups in flexible, reliable, and virtually limitless cloud storage.

3.Storage Replica delivers inexpensive high availability and disaster recovery

Storage Replica is a new feature in Windows Server 2016 that offers new disaster recovery and preparedness capabilities. For the first time, Windows Server delivers the ability to synchronously protect data on different racks, floors, buildings, campuses, counties, and cities. If a disaster strikes, all data will be at a safe location. Before a disaster strikes, Storage Replica lets you switch workloads to safe locations if you have a few moments warning—again, with no data loss.

Storage Replica enables synchronous and asynchronous replication of volumes between servers or clusters. It helps you take more efficient advantage of multiple datacenters. When you stretch or replicate clusters, you can run workloads in multiple datacenters so that nearby users and applications can get quicker data access. In addition, you can better distribute load and compute resources. Most important, you can implement this built-in functionality on commodity hardware and use it with emerging technologies such as Flash and SSD (as Danske Fragtmaend did) to build cost-effective, high-performance storage solutions that can work with existing SAN/NAS implementations—or even replace dedicated SAN/NAS solutions at a fraction of the cost.

4.Rolling, in-place upgrades and less downtime

Customers often tell us they want to use the latest releases of SQL Server and Windows Server, but they need the upgrade process to be less time-consuming and complex. Now they can take advantage of rolling, in-place upgrades from previous versions to SQL Server 2016 and Windows Server 2016—while dramatically minimizing downtime.Windows Server 2016 Cluster OS Rolling Upgrade lets you upgrade the operating system of the cluster nodes from Windows Server 2012 R2 to Windows Server 2016 without stopping the Hyper-V or the Scale-Out File Server workloads. Not only can you upgrade the OS in place, but Cluster OS Rolling Upgrade works for any cluster workload, including SQL Server 2016.

For SQL Server customers, this is important because you want to move the base OS without having to reinstall and reconfigure SQL Server. Now, in a rolling approach, you can move a cluster node, perform an in-place upgrade and do a clean install while other databases are being serviced by other nodes. The in-place upgrade preserves SQL Server backup and restore history, preserves permissions and group settings, and saves about 20‒30 minutes of upgrade time per node in the cluster. You can achieve this with minimal or no interruptions to the workload that’s running on the cluster, so you can upgrade the cluster in place. With a Hyper-V or Scale-Out File Server Workload, there’s zero downtime, which means you don’t need to buy new hardware.

5.Better together adds up to the best database reliability at a great price

For mission-critical workloads, you can’t settle for anything less than the best—and most cost-effective—data platform running on the OS that has built-in synergy to ensure database uptime and reliability with advanced disaster recover across domains and sites. Without spending vast amounts of your budget on third-party storage solutions, you can get the functionality you need built into SQL Server 2016 and Windows Server 2016.

SQL 2016 – New Features

1.Always Encrypted

As the word suggests, ‘Always Encrypted’ feature of SQL 2016 ‘Always’ keeps your sensitive data ‘Encrypted’ either at rest (local environment) or at remote (Cloud/Azure). It will help to protect data from people who may play around it like DBAs, Cloud operators, high-privileged but unauthorized users.

How It Works

You can set Always Encrypted to individual column (where your sensitive data resides). While configuring columns, you need to specify encryption algorithm and cryptographic keys for data protection. There are basically two keys you need to define:

1. Encryption Key for column data encryption (It will be used to encrypt data for specific column)
2. Master Key: for Encryption of column encryption keys

So basically, it’s a double encryption protection, only program can access it, client application will automatically encrypt and decrypt data before fetching data from database.

2.JSON Support

SQL 2016 gives direct support to JSON (Java Script Object Notation), SQL has the facility to read JSON format data, load them in table, support index properties in JSON columns.

JSON data will be stored in NVARCHAR type. Due to NVARCHAR type, an application has the following benefits:

• Already stored JSON data (as text) can be easily migrated on new feature.
• As NVARCHAR is supported by all SQL components so is the JSON too.

You can easily fetch data FOR JSON from SQL with the below syntax:

SELECT column, expression, column as alias
FROM table1, table2, table3
FOR JSON [AUTO | PATH]

It is a SELECT command so when we fire the above query, SQL will format each row/cell value and return as JSON object.

SQL has also provided in-built functions for JSON.

3.Dynamic Data Masking

This is again one of the security features of SQL 2016. As the name suggests, it just wraps MASK on your data, in short, it hides your confidential data that you don’t want to display. It just avoids disclosure of your sensitive data.

After masking, SQL User with limited rights will not view original text, he can view only Masked Text, SQL has pre-defined masking functions you just need to apply it on different columns, see below:

To apply this on specific columns, you just need to ALTER column with ‘MASKED WITH‘ function name, see below syntax:

//here I used function as Default(), you can change it to any of the above types

ALTER TABLE tablename ALTER COLUMN columnname MASKED WITH (FUNCTION=‚default()‘)

4.PolyBase

It’s a multi-connection functionality, in which we can connect to all relational and non-relational data from single point, it helps you to connect Hadoop database and Azure Blob storage. Basically, PolyBase creates a bridge between a data that is outside SQL scope, while querying on Hadoop or Azure storage no additional knowledge or installation is needed. Simply, you can Import and Export data To and From Hadoop or Azure storage. Additionally, you can integrate it with Microsoft’s business intelligence.

5.Row Level Security

This is again one of the security features of SQL 2016. It allows you to secure your data row wise, in short you can define a row, that will be viewed by a particular SQL user only. So depending upon the SQL user access permission, we can restrict row level data, e.g., we can ensure if employees can view only their department data though department table is the same.

To implement Row level security, you need to define Security policy with a predicate and function.

Security policy: We need to create a policy for security, here is simple syntax:

CREATE SECURITY POLICY fn_security ADD [FILTER | BLOCK] PREDICATE FunctionName ON TableName

In the above syntax, FILTER and BLOCK are the predicates that will either FILTER rows and display only those that are available for read or BLOCK rows for write operation.

Function: Function is a simple user defined function, but here are some restrictions for user defined function that are used in Row Level Security syntax:

• Database modification operations are not allowed
• OUTPUT INTO clause is not allowed in function
• Multiple result set should not be returned from function

6.Stretch Database

As the name suggests, it gives flexibility to the user. In short, we can store portion of database to remote (Here, we can say cloud/Azure). The portion of data can be called as COLD DATA. (It is useful for those where transactional data needs to be keep for long time as industry requirement.) So we can say it’s a cost-effective solution for COLD data storage, your data is available anytime for query and manage. You can access your data without changing queries either it is present on local or at stretch database.

To configure it, you need an Azure account and database instance that you need to stretch. The following snap will clear your idea.

7.Multiple TempDB

It is always a good practice to have a Multiple Temp data files, if you are working on a big crucial data, up till now (SQL 2014), you need to manually add temp db files to your database but SQL 2016 provides you temp DB configuration settings, in which you can configure Number of TempDB files at the time of SQL installation. Default number of files are 8 with default size of 64 MB will be given.So you no longer need to configure/create it manually.

8.Query Store

Up till now, to check Query plan and execution statistics, we need dynamic management views in SQL but neither will it give you Query plan that executed by past/old queries nor will it store them anywhere so that you can review, but SQL 2016 provides you ‘Query Store’ that takes you through query plan, statistics and Query execution for current and past queries.To enable it, just right click on database (obviously, you need SQL 2016 SSMS), go to properties. You will see ‘Query store’ at the left corner, select it and click on Enable ‘true‘ or you can do it using Query as follows:

ALTER DATABASE [Database1] SET QUERY_STORE = ON

9.Temporal Table

Do you want to store history of your SQL table? So you want to review your old records after table updation? Then you can go with this features. SQL 2016 provides record version facility in which it keeps a history of changed record and maintains it for timely analysis. This feature can be useful for Audit, checking data trend, accidental update/delete data and many more.

How It Works

Basically, the system keeps pair of a table for history and adds two additional columns in it named ‘SysStartTime‘ and ‘SysEndTime‘ for start time and end time for row respectively. Live table contains current record of row, whereas history table contains previous record of row. We can fetch data from History table, with the following query:

SELECT * FROM table1 FOR SYSTEM_TIME   
BETWEEN date1 AND date2
WHERE condition;

10.R Introduction

Have you stored statistical data in SQL? Want to use R to analyze it? You export data each time from SQL to R? Then your headache will now be covered in SQL 2016, because it is now with R. You can run R script on SQL. You need to install this feature at the time of SQL setup.

–Major Features of SQL Server :

• Security and compliance

  Protect data at rest and in motion with a database that has the least vulnerabilities of any major platform for six years running in the NIST vulnerabilities database (National Institute of Standards and Technology, National Vulnerability Database, Jan 17, 2017). Security innovations in SQL Server 2016 help secure data for mission-critical workloads with a layers of protection security approach, adding Always Encrypted technology along with row-level security, dynamic data masking, transparent data encryption (TDE), and robust auditing.

• High availability and disaster recovery

  Gain mission-critical uptime, fast failover, easy setup, and load balancing of readable secondaries with enhanced Always On in SQL Server 2016—a unified solution for high availability and disaster recovery on Linux and Windows. Plus, put an asynchronous replica in an Azure Virtual Machine for hybrid high availability.

• Corporate business intelligence

  Scale your business intelligence (BI) models, enrich your data, and ensure quality and accuracy with a complete BI solution. SQL Server Analysis Services help you build comprehensive, enterprise-scale analytic solutions—benefiting from the lightning-fast performance of in-memory built into the tabular model. Reduce time to insights using direct querying against tabular and multidimensional models.

• End-to-end mobile BI on any device

  Gain insights and transform your business with modern, paginated reports and rich visualizations. Use SQL Server Reporting Services to publish reports to any mobile device—including Windows, Android, and iOS devices—and access reports online or offline.

• Simplify data big and small

  Combine relational data and big data with PolyBase technology that queries Hadoop using simple T-SQL commands. JSON support lets you parse and store JSON documents and output relational data into JSON files. Now in SQL Server 2016, manage and query graph data inside your relational database.

• In-database advanced analytics

  Build intelligent applications with SQL Server Machine Learning Services using R and Python. Move beyond reactive and into predictive and prescriptive analytics by performing advanced analytics directly within the database. By using multi-threading and massively parallel processing, you’ll gain insights faster than using open source R and Python alone.

• Real-time hybrid transactional/analytical processing

  Combine in-memory columnstore and rowstore capabilities in SQL Server 2016 for real-time operational analytics—fast analytical processing right on your transactional data. Open up new scenarios like real-time fraud detection without impacting your transactional performance.

• Now on Windows, Linux and Docker

  Develop once and deploy anywhere with our consistent experience from on-premises to cloud. Now with support for Windows and Linux as well as Docker containers.

• Consistent data platform from on-premises to cloud

  Get a consistent experience from on-premises to the cloud—letting you build and deploy hybrid solutions for managing your data investments. Benefit from the flexibility to run SQL Server workloads in Azure Virtual Machines, or use Azure SQL Database to scale and further simplify database management.

• Easy-to-use tools and connectors

  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: You can reset ‘sa’ password by using windows authentication to connect to local SQL instance.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

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 Azure 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
Local SQL Server sa password has been set as Passw@rd123

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

      SQL UserName : sa ||  Password : Passw@rd123

Note: You can reset ‘sa’ password by using windows authentication to connect to local SQL instance.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.

2. VSCode, FTP Client, etc utilities have been installed