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.
The top 5 reasons to upgrade to SQL Server 2016
- 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.
- 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.
- 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.
- 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).
- 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).
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
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.
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.
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 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.
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.
SQL Server 2016 is now support in-database advance analytics. Using this feature we can execute R code inside the SQL Server database engine.
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.
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.
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
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”
- 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
Step 1) VM Creation:
- Click the Launch on Compute Engine button to choose the hardware and network settings.
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 ”
- 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