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.

SQL 2016 Standard Installation

The following steps walk you through setup and configuration for SQL Server 2016 Standard Edition as an example. For the most up to date resources on installing SQL see Microsoft’s Technical Documentation> SQL.

At the completion of this article you will have:

• Installed a basic stand-alone instance of SQL Server 2016 Standard with the minimum features necessary for SQL Server.
• Installed SQL Server Management Studio for managing the local database.
• Created a database in SQL for your Thycotic product
• Created a new SQL Server user login for your SQl database

Note: This document uses Thycotic’s Secret Server product as example in the instructions, but the same steps apply for Privilege Manager advanced installs.

Installing SQL Server 2016

1. Launch the SQL Server installer from CD or file download.
   • 
2. Click System Configuration Checker. This will launch and run a tool that checks for conditions on your server that could prevent SQL Server from installing.
3. When the tool launches, click the Show details button. A successful scan should look like the one shown below. If you encounter any issues, look at the detailed report, resolve the reported issues, and rerun the scan.
   • 
4. Click OK when done to return to the “SQL Server Installation Center” window.
5. In the “SQL Server Installation Center” window, click Installation then New SQL Server stand-alone installation or add features to an existing installation
   • 
6. Enter your product key and click Next.
   • 
7. Check the I accept the license terms. checkbox and click Next.
   • 
8. Wait for the Rule Check to run.
9. At the “Microsoft Update” step, check Use Microsoft Update to check for updates (recommended) unless your software update process does not utilize automatic updates from Microsoft and click Next.
   • 
10. At the “Product Updates” step leave all defaults and click Next.
11. Wait for the “Install Setup Files” step to complete. Check that all operations pass. When the installation is successful, click Next.
    • 
12. The only feature in “Feature Selection” necessary for Secret Server is “Database Engine Services.”
13. Unless you are using Geo-Replication, you can leave everything else unchecked. Leave the directory locations unchanged and click Next.
    • 
14. At the “Instance Configuration” step leave “Default instance” selected.
15. Choose a name (Instance ID) for your SQL Instance, then click Next.
    • 
16. At the “Server Configuration” step leave the defaults and click Next.
    • 
17. In the Database Engine Configuration window, you have the choice to select either Windows authentication mode or Mixed Mode. Select the option that will work best for your environment. See descriptions below:
    • a.      Mixed Mode (for easiest configuration)   Mixed Mode is required if you intend on using a SQL Server account to authenticate Secret Server to your SQL Server instance. We recommend using Mixed Mode if you are setting up a test or demo environment. Selecting this option will also require you to set a password for the SQL Server system administrator (sa) account. See Adding a SQL Server User (section below) for instructions on adding more users.
    • b.      Windows Mode (recommended for best security)  This will prevent SQL Server account authentication. We recommend using Windows Mode for production environments. Whatever user or group assigned will have administrative access to your SQL instance. According to Best Security Practices, limit this number to as few users as possible.
    • Note: If choosing Windows Mode you will also need to set up a service account to run your Thycotic product IIS Application Pool(s) later in the installation process.
    • 
    • Note: **Select an Administrator account to manage your SQL Server Database.
18. You can leave the options in the remaining tabs at their default values or change the file locations in the “Data Directories” and “TempDB” tabs if you wish to store the database and log data in a different drive or directory. Click Next.
19. Wait for the setup wizard to progress to the “Ready to Install” step and click Install.
    • 
20. Wait for installation to complete. This may take several minutes. Click Close.

​Installing SQL Server Management Studio

1. Back in the “SQL Server Installation Center” window, click Installation then Install SQL Server Management Tools
   • ​
2. Wait for the web page to load then click the Download SQL Server Management Studio 17.5 link (note: actual version # may be different if a newer version is available).
3. After the download is complete, click Run (if using Internet Explorer to download the file; if using a different browser, follow the conventions for that browser to run the file or open the download folder and run the file).
4. Click Install when the SQL Server Management Studio installer starts.
   • 
5. Wait for the installer to complete. This may take several minutes. When the installation has completed, click Restart if prompted. Otherwise, click Close and then close “SQL Server Installation Center.”
   • 

Creating the SQL Server Database

To install Secret Server or Privilege Manager, the Thycotic installer will create the SQL database for you if it does not exist and if the user account has permission to create a new database (this requires the dbcreator server role).

If not using the Thycotic Installer, use the following steps to create a database manually through SQL Server Management Studio:

1. Open SQL Server Management Studio by searching for it from the Windows Start screen.
2. Connect to your SQL Server instance.
3. Right click the Databases folder and select New Database…
   • 
4. Enter a name for your database in the Database name field, and then click OK.
   • 

Adding a SQL Server User

According to Security Best Practices, limit the number of users with access to your SQL database as much as possible.

Use the following instructions to add a SQL Server account for Secret Server to use to access the SQL database:

1. Open SQL Server Management Studio by searching for it from the Windows Start screen.
2. Connect to your SQL Server Database.
3. Expand the Security folder.
4. Right-click the Logins folder and select New Login…
   • 
5. Select a method of authentication:
   • a.      SQL Server Authentication   Use this option to create a new SQL Server account (this requires Mixed Mode to be enabled). To create the account, enter a new username and password and then deselect the Enforce password policy check box to prevent the account from expiring.
   • 
   • b.      Windows Authentication   Use this option to add access to SQL Server for an existing Windows account. To add the account, enter the login name or click Search to find the account. It is recommended to use a domain account rather than a local Windows account.
6. Click User Mapping in the left menu.
7. Select the check box next to your Secret Server database.
8. In the Database role membership window, select the db_owner check box.
   • 
9. Click OK.

The top 5 reasons to upgrade to SQL Server 2016

1. Seamless step-up without rewriting apps. . If you switch from Express to Standard, or Standard to Enterprise, you don’t have to rework code to take advantage of additional features. Time saved! In addition, the change brings access to innovative features across performance, security, and analytics not previously available in Express or Standard—a great reason to upgrade applications that run on those editions. The Enterprise edition of SQL Server 2016 continues to set the industry benchmark in terms of price, performance, and scalability at unparalleled TCO.
2. Take back your weekend. With SQL Server 2016 you won’t have to wait for weekends or after-hours to run analytical workloads. You don’t have to wait until you can extract, transform, and load (ETL) the data to your Enterprise Data Warehouse, either. Now you can run your analytics workloads simultaneously on your operational data, without losing performance, by using in-memory OLTP tables and in-memory columnstore together. This process can provide real-time operational analytics, also known as hybrid transactional/analytics processing (HTAP). Get even more from your data with in-database advanced analytics using R statistical language, so you can model and score quickly and at scale with native integration in SQL Server’s T-SQL query language.
3. Unparalleled level of data security. Rest easy, you don’t need to lose sleep over potential breaches when your data is Always Encrypted—whether at rest or in motion. With SQL Server 2016 and Azure SQL Database, your database data remains encrypted at all times; at rest, during computation, and while processing queries. This is in addition to row-level security and new dynamic data-masking capabilities built-in. This 2-minute video explains how Always Encrypted works.
   
4. Free your users from their desks. Take a trip, work from home, or linger over lunch—Mobile BI in SQL Server 2016 has got you covered. You can get your critical business insights in rich and beautiful reports anywhere, anytime, online and offline, and on any device (IOS, Android, and Windows).
5. Upgrade without headaches. Upgrading from older versions doesn’t need to be painful. We’ve created a painless Data Migration Assistant for upgrading to SQL Server 2016 as well as Azure SQL Database. But the short version is that you can now migrate your data from an old SQL Server version to a new one, plus get help finding and fixing breaking changes from earlier versions.

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

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

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

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

SQL Server 2016 Standard Edition on Win 2012 R2

10 New Features of SQL Server 2016

Always Encryption

This feature of SQL server enables us to encrypt the SQL Server database. This means that data is always in encrypted form in SQL Server. Access of this encrypted data is available to calling application. SQL server is now supported both column level as well as table level encryption. It allows client application to access which has encryption key. This encryption key is not passed to the SQL Server. This feature is also worked with cloud.

JSON Support

Java Script Object Notation is now supported by SQL Server 2016. Now we are able to interchange JSON data between application and database engine. SQL Server engine has ability to parse JSON data. Some new functions have been added to provide support for querying JSON data. It is very similar way to XML support, there is built in with “FOR JSON” and “OPENJSON” functions.

Row Level Security

Row level security enables database engine to restrict access to row data which is based on server login. By using filter predicates that defining inline table value function, restricting rows will be done. This is ensuring by Security policies that filter predicates get executed for each and every SELECT or DELETE operation. With this feature of SQL Server 2016, user need not to take care for restrict data from some login by writing code. This will maintain database engine itself.

PolyBase

Big Data and Hadoop have been introduced for handling large amount of data. Microsoft has introduced PolyBase which allows us to query distributed data sets. Using PolyBase, we are able to use T-SQL to query Azure blob storage and Hadoop. PolyBase enables us to write adhoc queries to join semi-structured data stored like Azure blob storage and Hadoop to relational data from SQL Server. This allows us to retrieve data from semi-structured data stored without knowing it.

Temporal Table

Temporal Table are tables that hold the old version of rows within base table. If database has a Temporal Table, SQL engine automatically moves old version of row to the temporal table whenever it is updated. The temporal table is physically a different table than the base table, but it is internally linked with the base table.

Dynamic Data Masking

If we want to restrict someone from seeingour confidential data, this feature of SQL Server 2016 is very important. Using Dynamic Data Masking, we can mask the confidential columns of data in a table for user who is not authorized to see these data. For example, if we stored mobile number or email address of the user in our table but want to restrict user to see whole mobile number or email address and it might be able to see some part of mobile number or email address. At this time we can use Dynamic Data Masking. By setting up this feature, we need to setup the masking rules so unauthorized logins can only read some part of mobile number or email address and authorized logins can see whole mobile number or email address.

Multiple TempDB Database support at the time of installation

Best practice says it would be good have multiple tempDB files if database server running on multi-core machine. Up to SQL Server 2014, we need to create additional tempDB data files manually once installation done. In SQL Server 2016, we can configure the number of tempDB files at the time of installation.

Query Store

SQL Server 2016 database engine now maintains a history of query execution plans with performance data and it also able to find queries that gave slower performance recently. Prior to this version we can examine execution plans by using DMVs (dynamic management views) but it only allows us to see the plans which are active in cache. This feature is now enabling us to examine historical execution plans and also save query statistics.

R-integration

SQL Server 2016 is now support in-database advance analytics. Using this feature we can execute R code inside the SQL Server database engine.

Stretch Database

This feature of SQL Server 2016 allows us to dynamically archive data in a secure manner from our local database to Azure SQL database hosted in the cloud. When we enable a database to stretch the older data is dynamically move over to the Azure SQL database. When we run query on database, it would access active record first (local database) and then historical data from stretched database. This feature enables us to archive data with any change in application code.

In-Memory Enhancements

SQL Server 2014 introduced the concept of “in-memory” tables. These were designed for high speed loading of data with no locking issues or high-volume session state issues. This feature has a lot many limitations. In SQL Server 2016, this feature is enhanced to support foreign keys, check, unique constraints and parallelism. SQL Server 2014 is support in-memory table up to 256 GB but it now extend to 2 TB.

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

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 installation path:  “C:\Program Files\Microsoft SQL Server 2016”

2.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) VM Creation:

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

2.You can see at this page, an overview of Cognosys Image as well as estimated cost of running the instance.

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

The below screen appears after successful deployment of the image.

i) Please connect to Remote Desktop as given in step 2
ii) You can use SQL server instance as localhost. The SQL Server instance name to be used is “localhost” Connect to SQL Management Studio with username as sa and password provided in Custom Metadata.

If you have closed the deployment page you can also get the sa password from VM Details “Custom metadata” Section.

Step 5) Other Information:

1.Default installation path:  “C:\Program Files\Microsoft SQL Server ”
2.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

SQL Server 2016 Standard Edition on Win 2012 R2