SQL Server 2016 on Windows 2016

1-click AWS Deployment 1-click Azure Deployment         1-click Google Deployment

Overview

SQL Server consistently leads in the TPC-E OLTP workload, the TPC-H data warehousing workload, and real-world application performance benchmarks. Get record-breaking performance now on Windows and Linux.

Cognosys Provides Hardened images of SQL Server 2016  on the cloud ( AWS marketplace, Azure and Google Cloud Platform) with multiple utilities to help in easy deployment.

Definition of SQL Server Express

SQL Server Express is a free version of Microsoft’s primary relational database management system (RDBMS) – the SQL Server. Essentially, the SQL Server is a database management system that can be used to store and access the information stored in many different databases. SQL Server comes with an impressive range of features like business intelligence, reporting, and in-depth advanced analytics.

The enterprise edition of SQL Server competes against enterprise-oriented systems like Oracle Database (DB) and MySQL. SQL Server Enterprise comes loaded with features and can be too expensive for smaller sized companies to maintain. Microsoft does, however, offer SQL Server in some different editions including:

  • SQL Server Enterprise
  • SQL Server Business Intelligence
  • SQL Server Standard
  • SQL Server Web
  • SQL Server Express

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

Some benefits come with an SQL Server Express deployment.

  • Free: One huge advantage of SQL Server Express is that it is free. Your only outlay 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 ideal 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. If you are an ISV and your company experiences high growth rate resulting in increased database demands, then you can only upgrade to a paid version of SQL Server.
  • Security: Within SQL Server Express there is the option of free online backup that will help to protect your valuable business data if anything goes wrong. Administrators should still follow security best practices like restricting 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:

Before deploying SQL Server Express, you should make yourself aware of its limitations which include:

  • 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

The 2016 version of SQL Server Express was released in three different editions:

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 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.
    Component Requirements
    Minimal Recommended
    Processor Speed 1.4 GHz on x64 Processor 2 GHz or faster
    System Memory (RAM) 512 MB 1024 MB
    Hard Disk Space 6 GB 8 GB or more
    Operating System Windows 8, Windows Server 2012 Windows 8 or later

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 File and 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 step. 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.

Highest performing data warehouses

Get support for small data marts to large enterprise data warehouses while reducing storage needs with enhanced data compression. Scale to petabytes of data for enterprise-grade relational data warehousing—and integrate with non-relational sources like Hadoop.

SQL Server 2016 SP2 Std w/Vulnerability Assessment

Features

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.

  • Always Encrypted

The Always Encrypted feature protects data and enables the SQL Server to perform encrypted 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.

  •  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.

  •  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.

  •  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 columnstore.

  •  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.

  •  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.

  •  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.

  •  Enhancements to AlwaysOn

The SQL 2016 version improves the ability of the AlwaysOn 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.

  •  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.

  •  JSON Support

The Java Script Object Notation feature allows you to interchange JSON data between the SQL Server database engine and various other applications. The SQL 2016 can analyze JSON formatted data and even convert relational data into JSON format for convenient storage.

The expertise 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 while pursuing the “Implementing a Microsoft SQL 2016 Data Warehouse” course. This 5-day course will help you move ahead of your contemporaries by acquiring the necessary skills for excelling as a Business Intelligence Developer so that you can climb the ladder of success by improving your skill set.

AWS

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
  • 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

a

b

c

d

e

Azure

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
  • SQL Server Port: 1433 this is by default not allowed on the firewall for security.

2. VSCode and debug utilities  have been installed 

 

Videos

SQL Server 2016 SP2 Std w/Vulnerability Assessment

New in SQL Server

SQL Server 2016 on Windows 2016

Related Posts