What is SQL Server

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.

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. Enterprise is 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. Standard is for core database capabilities, reporting and analytics. Compute capacity can’t exceed 24 cores; maximum usage is 128 GB per instance.
3. Developer is designed for application development purposes (as opposed to production database usage). Its core and support is the same as for the Enterprise edition.
4. Express is 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.

The Standard edition of SQL Server 2016 proposes many features with limits. This edition is planned to provide smaller organizations with all the required tools and features at a lower cost and less need for IT staff. The Standard edition is a highly effective version for business customers looking for budget freedom. However, there are those customers who may need to further examine SQL Server 2016 features in order to effectively make choices between the two editions. This is where a careful examination of all the features coupled with licensing can save some companies cost, while others may quickly find they need far more database computing power. Since the SQL Server 2016 database engine is designed for far faster performance, the needs of an organization fall into the specifics designed into each edition. Digging further into the details can provide an assessment which enables Microsoft customers to make the best-informed decision for both immediate and long-term needs. Here are some highlights when to consider among the many options and features of SQL Server 2016 when choosing an edition

Scale Limits

The Standard edition has a variety of memory limitations which must be considered when designing database applications. It is most important to remember that Standard edition is limited to the lesser of four sockets for 24 cores when it comes to the database engine, analysis services or reporting services. Other components may also be limited in the Standard version so it’s important to check for those limitations. But it is also an important to realize that both Standard and Enterprise editions have a maximum relational database size of 524 PB. 

RDBMS High Availability

There are a number of features that are not available in the Standard edition, some of the most important being the lack of always-on availability groups, online page and file restore, online indexing, online schema change, fast recovery, mirrored backups, and hot add memory and CPU.  

RDBMS Performance and Scalability

Almost all of the RDBMS performance and scalability features are available in both Enterprise and Standard editions with the exceptions being: resource governor, partition table parallelism, NUMA aware and large page memory and buffer array allocation, and I/O resource governor.

RDBMS Security

SQL Server 2016 Standard edition strongly mirrors RDBMS security from that of the Enterprise edition except for transparent database encryption and extensible Key management.

Replication

The Standard edition also comes with many of the same replication features as that of the Enterprise edition, the exceptions being: Oracle publishing, peer-to-peer transactional replication, and transactional replication updateable subscription. 

Management Tools

The management tools available in the Enterprise edition are exactly the same within the Standard edition.

RDBMS Manageability

Standard Edition possesses many of the same RDBMS manageability features, though there are some which are not included that are available in the Enterprise edition. These features are: parallel indexed operations, automatic use of indexed view by query optimizer, parallel consistency check and SQL Server Utility Control Point. 

Development Tools

SQL Server 2016 Standard and Enterprise editions provide the exact same development tools which is a strong benefit either way.

Programmability

Almost all of the available programmability features of the SQL Server 2016 Enterprise edition are available in the Standard edition with the exception of advanced R integration and R server (standalone). This means there are a wide array of programming options available even in the Standard edition. 

Integration Services

Basic integration services are available in both additions, but advanced sources and destinations as well as advanced tasks and transformations are not available in the Standard edition 

Master Data Services

Master data services are only available in the Enterprise edition, making the higher cost a consideration for those organizations in need of these features. Contact one of our specialists for more details. 

Data Warehouse

There are several integration services features not available in the Standard edition which include: star join query optimizations, scalable read only analysis services configuration, parallel query processing on partitioned tables and indexes, and global batch aggregation. 

Analysis Services

The Standard edition does not provide support for scalable shared databases or synchronize databases, while AlwaysOn failover cluster instances only supports two nodes. 

BI Semantic Model (Multidimensional)

Standard edition of SQL Server 2016 does support many of the same business intelligence semantic model (multidimensional) features as the Enterprise edition. Check with one of our specialists for more details.

BI Semantic Model (Tabular)

Business intelligence is an important inclusion within the Standard edition of SQL Server 2016. The Standard edition supports all of the same tabular models as the Enterprise edition except for Perspectives, Multiple partitions, and Direct Query storage mode. 

Power Pivot for SharePoint

The Standard edition of SQL Server 2016 does not provide any support for Power Pivot for SharePoint so if this is a major consideration for an organization the Enterprise edition may be the choice since SharePoint is a powerful Microsoft product in wide use. 

Data Mining

While the Enterprise edition of SQL Server 2016 does support a wide array of data warehouse features, the Standard edition only supports standard algorithms and data mining tools (Wizards, Editors, Query Builders). 

Spatial and Location Services & Additional Database Services

All features and options for spatial and location services as well as additional database services are the same between SQL Server 2016 Enterprise and Standard editions. 

Other Components

Stream Insight HA is not available in the Standard edition.

Choosing between SQL Server 2016 Standard and Enterprise editions includes a wide collection of factors. It is very important to remember that with the 2016 version, Microsoft included Business Intelligence features for the Standard edition to provide non-enterprise class customers with these useful options at a restricted accessibility. Similarly, the Standard edition widely mirrors the Enterprise edition in terms of available features, only with limitations to scalability

SQL Server 2016 Standard edition on 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.

10 new features of SQL Server 2016 

1. Always Encrypted

This feature protects data and allows the SQL Server to perform encoded data operations so that the owners can protect their confidential data by using an encryption key. This feature ensures that your important data stored in the cloud managed database remains encrypted and protected. Encryption works, but many companies do not or cannot implement it all the way through the stack, leaving some layer data available for the taking as plain text. SQL Server has long supported both column-level encryption, encryption at rest, and encryption in transit. However these all had to be configured independently and were frequently misconfigured. Always Encrypted is new functionality through the use of an enhanced client library at the application so the data stays encrypted in transit, at rest and while it is alive in the database. Also given Microsoft’s push towards the use of Azure, easy encryption makes for a much better security story.

2. Dynamic Data Masking

This feature gives an obscured version of your confidential data to some people and allows only authorized users to view it by defining masking rules. It is popularly used for managing credit card information and similar databases.

3. Stretch Database

The Stretch Database permits you to extend your database to Azure where the data that is not accessed regularly are moved to the cloud so that you can enjoy high-performance applications while benefiting from the low-cost Azure store as only the frequently accessed data stays on premise. One common idiom in recent years, is how cheap storage is. While it may be cheap to buy a 3TB drive from Amazon, if you are buying enterprise-class SAN storage or enterprise SSDs, you will know that storage is still very expensive. Microsoft is trying to help reduce your storage (and processing costs) with a hybrid feature called “Stretch Database.” The basics of Stretch Database are that some part of your tables (configurable or automated) will be moved into an Azure SQL Database in the cloud in a secure fashion. When you query those tables, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly. The query processing on the Azure rows takes place in Azure so the only latency is for the return of the rows over the network. As an additional enhancement, you are only charged for the SQL Database in Azure when it is used for queries. You do, however, pay for the Azure Blob storage, which, generally speaking, is much cheaper than enterprise storage

4. Real-time Operational Analytics

The real-time Operational Analytics of SQL 2016 prepare your system for optimal transactional performance and helps to increase workload consistency by combining in-memory OLTP with in-memory column store.

5. Polybase

This feature works as a connector between SQL Server and Hadoop so that your regular data processing involving large text files can be stored conveniently in Azure Blob Storage or Hadoop. This technology bridges the gap between SQL Server and Hadoop to make data storage easy. Hadoop and Big Data have been all the rage in the last several years. I think some of that was industry hype, but Hadoop is a scalable, cost-effective way to store large amounts of data. Microsoft had introduced Polybase, a SQL Server connector to Hadoop (and Azure Blob Storage) to its data warehouse appliance Analytics Platform System in 2015. But now Microsoft has incorporated that functionality into the regular on-premises product. This feature will benefit you if your regular data processing involves dealing with a lot of large text files — they can be stored in Azure Blob Storage or Hadoop, and queried as if they were database tables. A common scenario where you might use this would be an extract, transform and load (ETL) process, where you were taking a subset of the text file to load into your database.

6. Query Store

The Query Store feature of SQL 2016 version deals with the problem faced by previous servers in tracing the history of your execution plans as they only showed plans that are active in the plan cache. However, the inclusion of this feature in SQL Server 2016 allows you to track previous execution plans and performance by tracing your queries over a long period of time. One common problem many organizations face when upgrading versions of SQL Server is changes in the query optimizer (which happen from version to version) negatively impacting performance. Without comprehensive testing, this has traditionally been a hard problem to identify and then resolve. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed. The Query Store is configured at the individual database level.

7. Row Level Security

The Row Level Security feature is a major development in this database management system as it restricts some users to view data in tables by using an SQL Server login. It allows you to implement row level security so that new users will not be able to detect whether the rows of data were filtered for restricting data. A feature that other databases have had for many years, and SQL Server has lacked natively is the ability to provide row-level security (RLS). This restricts which users can view what data in a table, based on a function. SQL Server 2016 introduces this feature, which is very useful in multi-tenant environments where you may want to limit data access based on customer ID.

8. Enhancements to Always On

The SQL 2016 version advances the ability of the Always On feature to have around three synchronous replicas in order to support availability and disaster recovery functions. It also relies on Distributed Transaction Coordinator support for load balancing and manages automatic failover. SQL Server 2014 introduced the concept of in-memory tables. These were optimally designed for high-speed loading of data with no locking issues or high-volume session state issues. While this feature sounded great on paper, there were a lot of limitations particularly around constraints and procedures. In SQL Server 2016, this feature is vastly improved, supporting foreign keys, check and unique constraints and parallelism. Additionally, tables up to 2TB are now supported (up from 256GB). Another part of in-memory is column store indexes, which are commonly used in data warehouse workloads. This feature was introduced in SQL 2012 and has been enhanced in each version since. In 2016 it receives some enhancements around sorting and better support with AlwaysOn Availability Groups

9. Revamped SQL Server Data Tools

The Revamped SQL Server Data Tools re-consolidate the data tools that had to be downloaded separately in the earlier versions as SQL Server 2016 presents a compact package to assist easy working on this database management system.

10. JSON Support

This feature allows you to exchange JSON data between the SQL Server database engine and various other applications. The SQL 2016 can evaluate JSON formatted data and even change relational data into JSON format for suitable storage. The skill to develop a data warehouse by using these advanced features of Microsoft’s SQL Server 2016 can be developed by getting proper mentoring and guidance.

 SQL Server 2016 Standard Edition

• Standard edition is priced in per core licensing model. Per core costs US$3,717
• The standard edition supports only up to 24 processor cores.
• Standard edition can use the server’s memory up to 128 GB.
• Reporting services can use the memory up to 64 GB.
• Similar to enterprise edition a database in standard edition can grow up to 524 PB.
• It does not have the complete business intelligence modules or the advanced security features like the transparent database auditing or the extensible key management available in enterprise edition.

———————————————————————————————————————————————————-

Major Features of SQL Server 2016 Standard Edition

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.

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

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

Click on the respective cloud provider tab for technical information.

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

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