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.

Installation of SQL Server 2016 Express:

Steps to Accomplish

Downloading SQL Server Express
If running Windows Server 2012 or later, please perform the following steps:

1.Download and execute the Microsoft SQL Server 2016 SP2 Express installer from Microsoft’s website.
2.Choose the Custom option when prompted since SELECTserver utilizes features not enabled by default in the Basic installation.

3.The program will download the installer to the specified location.

Installing SQL Server Express
Once the installer appears, proceed through the following steps:

1.Choose the “New SQL Server stand-alone installation” option highlighted in the screenshot.

2.Accept the licensing terms, and click the Next button.

3.To obtain the latest updates from Microsoft, turn on Microsoft Update

4.If any updates are found, apply them.

5.Once any updates have been applied and potential problems have been investigated, click the Next button

6.On the following screen, click the Next button to proceed with default selections unless using alternate installation locations

7.In most cases, the default instance name will be sufficient. For additional clarity, the instance can optionally be named SELECTserver. Click the Next button to proceed

8.Default Service Account settings will be sufficient if SQL Server Express is being installed on the same machine where SELECTserver will reside. Click the Next button.

9.Choose the “Mixed Mode” radio button in the Authentication Mode section. Also enter a strong password containing uppercase and lowercase characters and either numbers or symbols. Click the Next button to continue. The installer will display an error if the password is not strong enough or if the two password fields do not match.

10.The installer may prompt for a restart of the machine if any files could not be updated immediately. If the below message appears, restart the server when possible following the installation.

11.An installation summary will appear once SQL Server 2016 Expresss has been installed. Click the Close button, and close the SQL Server Installation Center window.

The Express edition is a free version of SQL Server that is intended for small databases with a low number of users. In the 2016 version Express is limited to four cores, 1GB of ram per instance and a maximum database size of 10 GB. Although it is a reasonable size it is not large enough for most production database applications, but it is useful for development work if your development instance does not require many resources and is small, and if you are not developing any functionality that depends on Standard or Enterprise-only features..

Benefits of Express for development:
Performance problems will show up more conspicuously in development
There are no license complications for development work
It is suitable for both individual and shared development.
Network services can be configured just as with the full editions, so you can connect with tcp from remote machines and can be used by the SQL Browser service to allow connections using instance name
It can be freely downloaded and installed

Negatives of Express for development:
Does not include the full feature set of one of the production versions
Requires a full standard installation to create a new instance, this takes time to install and upgrade and quite a lot of disk space per instance
Should not be used for load or performance testing

SQL Server 2016 Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications. It is the best choice for independent software vendors, developers and hobbyists building client applications.

SQL Server Express LocalDB, a lightweight version of Express that has all of its programmability features, yet runs in user mode and has a fast, zero-configuration installation and a short list of prerequisites.

SQL Server 2016 Express edition is ideal for deploying small databases in production environments and makes leading innovation available to any public cloud like SQL Server 2016 Express edition on AWS marketplace  and Google Cloud ( GCP ).

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

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

Click on the respective cloud provider tab for technical information.

Default password for sa is set as Passw@rd123 for SQL Server.
Please change the password after first login.

SQL Server 2016 Express on Windows Server 2016

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

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

Major Features of SQL Server 2016 Express Edition

• Core: Maximum number of cores is 4.
• Memory: Maximum buffer pool size per instance is 1410 MB
• Memory: Maximum Column store segment cache per instance is 352 MB
• DB Size: Maximum database size is 10 GB
• Advanced Security: Always Encrypted, row-level security, data masking, fine-grained auditing

Installation Instructions For Windows

Installation Instructions for Windows

Note: How to find PublicDNS in AWS

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

1) Connect to the 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

Note: Please change the password after the first login.

Step 4) Other Information:

1.Default ports:

• Windows Machines:  RDP Port – 3389
• sql server port :1433: By default, this is blocked on Public interface for security reasons.

Configure custom inbound and outbound rules using this link

Installation Instructions For Windows

Installation Instructions for Windows

Step 1) VM Creation:

1. Click the Launch on Compute Engine button to choose the hardware and network settings.

2.You can see at this page, overview of Cognosys Image as well as some estimated costs of VM.

3.In the settings page, you can choose the number of CPUs and amount of RAM, the disk size and type etc.

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

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

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

 SQL UserName : sa

Step 1.For getting password , Please follow below.

Step 2: Click on Custom Metadata for sa Password details.

Step 3: Connect to SQL Management Studio Using the credentials as provided in Custom Metadata above.Please change password at earliest.

Step 5) Other Information:

1.Default ports:

• Windows Machines:  RDP Port – 3389
• sql server port :1433: By default, this is blocked on Public interface for security reasons.

SQL Server 2016 Express on Windows Server 2016

Introduction To Microsoft SQL Server 2016