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