What is SQL Server
SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft.
Similar to other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interacting with the relational databases. SQL server is tied to Transact-SQL, or T-SQL, the Microsoft’s implementation of SQL that adds a set of proprietary programming constructs.
SQL Server works exclusively on Windows environment for more than 20 years. In 2016, Microsoft made it available on Linux. SQL Server 2017 became generally available in October 2016 that ran on both Windows and Linux.
SQL Server Architecture
The following diagram illustrates the architecture of the SQL Server:
SQL Server consists of two main components:
- Database Engine
The core component of the SQL Server is the Database Engine. The Database Engine consists of a relational engine that processes queries and a storage engine that manages database files, pages, pages, index, etc. The database objects such as stored procedures, views, and triggers are also created and executed by the Database Engine.
The Relational Engine contains the components that determine the best way to execute a query. The relational engine is also known as the query processor.
The relational engine requests data from the storage engine based on the input query and processed the results.
Some tasks of the relational engine include querying processing, memory management, thread and task management, buffer management, and distributed query processing.
The storage engine is in charge of storage and retrieval of data from the storage systems such as disks and SAN.
Under the relational engine and storage engine is the SQL Server Operating System or SQLOS.
SQLOS provides many operating system services such as memory and I/O management. Other services include exception handling and synchronization services.
SQL Server Services and Tools
Microsoft provides both data management and business intelligence (BI) tools and services together with SQL Server.
For data management, SQL Server includes SQL Server Integration Services (SSIS), SQL Server Data Quality Services, and SQL Server Master Data Services. To develop databases, SQL Server provides SQL Server Data tools; and to manage, deploy, and monitor databases SQL Server has SQL Server Management Studio (SSMS).
For data analysis, SQL Server offers SQL Server Analysis Services (SSAS). SQL Server Reporting Services (SSRS) provides reports and visualization of data. The Machine Learning Services technology appeared first in SQL Server 2016 which was renamed from the R Services.
SQL Server Editions
SQL Server has four primary editions that have different bundled services and tools. Two editions are available free of charge:
SQL Server Developer edition for use in database development and testing.
SQL Server Expression for small databases with the size up to 10 GB of disk storage capacity.
For larger and more critical applications, SQL Server offers the Enterprise edition that includes all SQL server’s features.
SQL Server Standard Edition has partial feature sets of the Enterprise Edition and limits on the Server regarding the numbers of processor core and memory that can be configured.
For the detailed information on the SQL Editions, check it out the available Server Server 2017 Editions.
In this tutorial, you have a brief overview of the SQL Servers including its architecture, services, tools, and editions.
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 :
- 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.
- Standard is for core database capabilities, reporting and analytics. Compute capacity can’t exceed 24 cores; maximum usage is 128 GB per instance.
- 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.
- 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
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.
SQL Server 2016 Standard edition strongly mirrors RDBMS security from that of the Enterprise edition except for transparent database encryption and extensible Key management.
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.
The management tools available in the Enterprise edition are exactly the same within the Standard edition.
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.
SQL Server 2016 Standard and Enterprise editions provide the exact same development tools which is a strong benefit either way.
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.
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.
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.
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.
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.
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.
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.
- Launch the SQL Server installer from CD or file download.
- 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.
- 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.
- Click OK when done to return to the “SQL Server Installation Center” window.
- In the “SQL Server Installation Center” window, click Installation then New SQL Server stand-alone installation or add features to an existing installation
- Enter your product key and click Next.
- Check the I accept the license terms. checkbox and click Next.
- Wait for the Rule Check to run.
- 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.
- At the “Product Updates” step leave all defaults and click Next.
- Wait for the “Install Setup Files” step to complete. Check that all operations pass. When the installation is successful, click Next.
- The only feature in “Feature Selection” necessary for Secret Server is “Database Engine Services.”
- Unless you are using Geo-Replication, you can leave everything else unchecked. Leave the directory locations unchanged and click Next.
- At the “Instance Configuration” step leave “Default instance” selected.
- Choose a name (Instance ID) for your SQL Instance, then click Next.
- At the “Server Configuration” step leave the defaults and click Next.
- 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.
- 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.
- Wait for the setup wizard to progress to the “Ready to Install” step and click Install.
- Wait for installation to complete. This may take several minutes. Click Close.
- Back in the “SQL Server Installation Center” window, click Installation then Install SQL Server Management Tools
- 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).
- 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).
- Click Install when the SQL Server Management Studio installer starts.
- 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.”
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:
- Open SQL Server Management Studio by searching for it from the Windows Start screen.
- Connect to your SQL Server instance.
- Right click the Databases folder and select New Database…
- Enter a name for your database in the Database name field, and then click OK.
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:
- Open SQL Server Management Studio by searching for it from the Windows Start screen.
- Connect to your SQL Server Database.
- Expand the Security folder.
- Right-click the Logins folder and select New Login…
- 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.
- Click User Mapping in the left menu.
- Select the check box next to your Secret Server database.
- In the Database role membership window, select the db_owner check box.
- Click OK.
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.
This feature works as a connector between SQL Server and Hadoop so that your regular data processing involving large text files can be stored conveniently in Azure Blob Storage or Hadoop. This technology bridges the gap between SQL Server and Hadoop to make data storage easy. Hadoop and Big Data have been all the rage in the last several years. I think some of that was industry hype, but Hadoop is a scalable, cost-effective way to store large amounts of data. Microsoft had introduced Polybase, a SQL Server connector to Hadoop (and Azure Blob Storage) to its data warehouse appliance Analytics Platform System in 2015. But now Microsoft has incorporated that functionality into the regular on-premises product. This feature will benefit you if your regular data processing involves dealing with a lot of large text files — they can be stored in Azure Blob Storage or Hadoop, and queried as if they were database tables. A common scenario where you might use this would be an extract, transform and load (ETL) process, where you were taking a subset of the text file to load into your database.
6. Query Store
The Query Store feature of SQL 2016 version deals with the problem faced by previous servers in tracing the history of your execution plans as they only showed plans that are active in the plan cache. However, the inclusion of this feature in SQL Server 2016 allows you to track previous execution plans and performance by tracing your queries over a long period of time. One common problem many organizations face when upgrading versions of SQL Server is changes in the query optimizer (which happen from version to version) negatively impacting performance. Without comprehensive testing, this has traditionally been a hard problem to identify and then resolve. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed. The Query Store is configured at the individual database level.
7. Row Level Security
The Row Level Security feature is a major development in this database management system as it restricts some users to view data in tables by using an SQL Server login. It allows you to implement row level security so that new users will not be able to detect whether the rows of data were filtered for restricting data. A feature that other databases have had for many years, and SQL Server has lacked natively is the ability to provide row-level security (RLS). This restricts which users can view what data in a table, based on a function. SQL Server 2016 introduces this feature, which is very useful in multi-tenant environments where you may want to limit data access based on customer ID.
8. Enhancements to Always On
The SQL 2016 version advances the ability of the Always On feature to have around three synchronous replicas in order to support availability and disaster recovery functions. It also relies on Distributed Transaction Coordinator support for load balancing and manages automatic failover. SQL Server 2014 introduced the concept of in-memory tables. These were optimally designed for high-speed loading of data with no locking issues or high-volume session state issues. While this feature sounded great on paper, there were a lot of limitations particularly around constraints and procedures. In SQL Server 2016, this feature is vastly improved, supporting foreign keys, check and unique constraints and parallelism. Additionally, tables up to 2TB are now supported (up from 256GB). Another part of in-memory is column store indexes, which are commonly used in data warehouse workloads. This feature was introduced in SQL 2012 and has been enhanced in each version since. In 2016 it receives some enhancements around sorting and better support with AlwaysOn Availability Groups
9. Revamped SQL Server Data Tools
The Revamped SQL Server Data Tools re-consolidate the data tools that had to be downloaded separately in the earlier versions as SQL Server 2016 presents a compact package to assist easy working on this database management system.
10. JSON Support
This feature allows you to exchange JSON data between the SQL Server database engine and various other applications. The SQL 2016 can evaluate JSON formatted data and even change relational data into JSON format for suitable storage. The skill to develop a data warehouse by using these advanced features of Microsoft’s SQL Server 2016 can be developed by getting proper mentoring and guidance.
SQL Server 2016 Standard Edition
- Standard edition is priced in per core licensing model. Per core costs US$3,717
- The standard edition supports only up to 24 processor cores.
- Standard edition can use the server’s memory up to 128 GB.
- Reporting services can use the memory up to 64 GB.
- Similar to enterprise edition a database in standard edition can grow up to 524 PB.
- It does not have the complete business intelligence modules or the advanced security features like the transparent database auditing or the extensible key management available in enterprise edition.
Major Features of SQL Server 2016 Standard Edition
Simplify big data
Take advantage of non-relational data with PolyBase technology built in that allows you to query structured and unstructured data with the simplicity of T-SQL.
Mission critical intelligent applications
Deliver real-time operational intelligence by combining built-in advanced analytics and in-memory technology without having to move the data or impact end user performance.
Highest performing data warehouse
Scale to petabytes of data for enterprise-grade relational data warehousing—and integrate with non-relational sources like Hadoop—using scale out, massively parallel processing from Microsoft Analytics Platform System. Support small data marts to large enterprise data warehouses while reducing storage needs with enhanced data compression.
Empower business users across the organization with the right insights on any mobile device.
Easy to use tools
Use the skills you already have, along with familiar tools like Azure Active Directory and SQL Server Management Studio, to manage your database infrastructure across on-premises SQL Server and Microsoft Azure. Apply industry-standard APIs across various platforms and download updated developer tools from Visual Studio to build next-generation web, enterprise, business intelligence and mobile applications.
Cognosys Provides Hardened images of SQL Server 2016 Standard Edition on the cloud ( SQL Server 2016 Standard Edition on AWS marketplace, SQL Server 2016 Standard Edition on Azure and SQL Server 2016 Standard Edition on Google Cloud Platform).
Click on the respective cloud provider tab for technical information.
Core data management and business intelligence capabilities with minimal IT resources.
Installation Instructions for Windows
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:
- 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