SQL Server 2017 on Cloud

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

Overview

Sql Server is a boundless tool for data analysis, specifically when talking about large amounts of data. There are many versions of Sql Server.SQL Server 2017 has come up with more new features that offer faster processing, more flexibility of use, and greater cost savings as a result. SQL Server 2017 promises all that and much more of what corporate customers need at all levels. Database performance has reached a new peak with adaptive query processing, new flexibility with cross-platform capabilities, new integrations for statistical and data science analysis, and SQL Server versions on Linux®, Ubuntu® operating systems, or Docker®. The new version adds solid technology with cost savings. SQL Server 2017 represents a major step towards making SQL Server a platform that gives you choices of development languages, data types, on-premises or cloud, and operating systems by bringing the power of SQL Server to Linux, Linux-based Docker containers, and Windows.

Express Edition. This edition is free to use but you only have a Maximum database size of 10 GB. I don’t recommend using this edition in production, especially when doing data analysis because your databases might seem not big at start but after a few months (depending of your business) you will need to manage large amounts of data and you will need to migrate your database to a better edition.

Developer Edition. When you are not working on a production environment and you want to have almost everything that enterprise edition has to offer with no cost. This version is very useful. You can use it when you want to prepare for a Sql Certification or you want to develop software using Sql Server without any restriction. It used to cost 50 dollars before but since version 2014 Microsoft decided to make it free.

Standard Edition. This edition is specially design to cover your basic Sql Server functions on a production environment (license is not free), this version lack some BI tools and some analytical advanced functions. An advantage over express edition is that you don’t need to worry about your database size because max database size for this edition is 524 PB (Petabytes). If you only want to analyze data with your team using Sql Server or developing apps using this Database at work without some advance analytical tools, this is your version.

Enterprise Edition. This is an advance edition of Sql Server, it is complete and it contains everything Sql Server has to offer you. This version is optimized and it has a lot of functionalities and tools that Standard Edition lacks, price is a lot more than Standard Edition but it worth it. This version is more likely to use by a mid to big size company for price and functionality reasons. If you want to use some advance Sql Server functions this version is for you.

 SQL Server 2017 Master Data Services (MDS)

  • Experience and performance are improved when upgrading from SQL Server 2012, SQL Server 2014, and SQL Server 2016 to SQL Server 2017 Master Data Services.
  • You can now view the sorted lists of entities, collections and hierarchies in the Explorerpage of the Web application.
  • Performance is improved for staging millions of records using the staging stored procedure.
  • Performance is improved when expanding the Entitiesfolder on the Manage Groups page to assign model permissions. The Manage Groups page is located in the Security section of the Web application. For more information about the performance improvement,

SQL Server 2017 Analysis Services (SSAS)

SQL Server Analysis Services 2017 introduces many enhancements for tabular models. These include:

  • Tabular mode as the default installation option for Analysis Services. (CTP 2.0)
  • Object-level security to secure the metadata of tabular models. (CTP 2.0)
  • Date relationships to easily create relationships based on date fields. (CTP 2.0)
  • New Get Data(Power Query) data sources, and existing DirectQuery data sources support for M queries. (CTP 2.0)
  • DAX Editor for SSDT. (CTP 2.0)
  • Encoding hints, an advanced feature for optimizing data refresh of large in-memory tabular models. (CTP 1.3)
  • Support for the 1400 Compatibility levelfor tabular models
  • A modern Get Dataexperience for tabular models at the 1400 compatibility level.
  • Hide Membersproperty to hide blank members in ragged hierarchies. (CTP 1.1)
  • New Detail Rowsend-user action to Show Details for aggregated information. SELECTCOLUMNS and DETAILROWS functions for creating Detail Rows expressions. (CTP 1.1)
  • DAX INoperator for specifying multiple values

SQL Server 2017 Reporting Services (SSRS)

SQL Server Reporting Services is no longer available to install through SQL Server setup.

  • Comments are now available for reports, to add perspective and collaborate with others. You can also include attachments with comments.
  • In the latest releases of Report Builder and SQL Server Data Tools, you can create native DAX queries against supported SQL Server Analysis Services tabular data models by dragging and dropping desired fields in the query designers. See the Reporting Services blog.
  • To enable development of modern applications and customization, SSRS now supports a fully OpenAPI compliant RESTful API. The full API specification and documentation can now be found on swaggerhub.

Machine Learning in SQL Server 2017

SQL Server R Services has been renamed SQL Server Machine Learning Services, to reflect support for Python in addition to the R language. You can use Machine Learning Services (In-Database) to run R or Python scripts in SQL Server, or install Microsoft Machine Learning Server (Standalone) to deploy and consume R and Python models that don’t require SQL Server.

SQL Server developers now have access to the extensive Python ML and AI libraries available in the open-source ecosystem, along with the latest innovations from Microsoft:

  • revoscalepy– This Python equivalent of RevoScaleR includes parallel algorithms for linear and logistic regressions, decision tree, boosted trees and random forests, as well as a rich set of APIs for data transformation and data movement, remote compute contexts, and data sources.
  • microsoftml– This state-of-the-art package of machine learning algorithms and transforms with Python bindings includes deep neural networks, fast decision trees and decision forests, and optimized algorithms for linear and logistic regressions. You also get pre-trained models based on ResNet models that you can use for image extraction or sentiment analysis.
  • Python operationalization with T-SQL– Deploy Python code easily by using the stored procedure sp_execute_external_script. Get great performance by streaming data from SQL to Python processes and using MPI ring parallelization.
  • Python in SQL Server compute contexts– Data scientists and developers can execute Python code remotely from their development environments to explore data and develop models without moving data around.
  • Native scoring– The PREDICT function in Transact-SQL can be used to perform scoring in any instance of SQL Server 2017, even if R isn’t installed. All that’s required is that you train the model using one of the supported Revocable and revoscalepy algorithms and save the model in a new, compact binary format.
  • Package management– T-SQL now supports the CREATE EXTERNAL LIBRARY statement, to give DBAs greater management over R packages. Use roles to control private or shared package access, store R packages in the database and share them among users.
  • Performance improvements– The stored procedure sp_execute_external_script has been optimized to support batch mode execution for columnstore data. 

Step by step installation of SQL Server 2017

You have 3 main options. The trial evaluation, Developer edition and Express edition.

The main question is which SQL Server edition should I use?

  • The SQL Server trial evaluation is the free edition that includes all the features. If you want to check out any/all of the features, this is a complete version. It is a 180 days trial, but you can buy an Enterprise or Standard license later. Use this version if you plan to buy a license later.
  • SQL Server Developer edition is free, but you cannot use it in production. For this tutorial, you can use any of them. I recommend this one if you use it for development purposes or for training purposes. It includes all the features also.
  • The SQL Server Express edition is free and can be used in production, but it has a limitation in Storage (10 GB) and it does not include several features (SQL Server Agent, DTA, etc.). I recommend this edition if you do not have too much disk space or resources in your machine.

There are other editions like the SQL Server Standard and Web edition. They are cheaper versions than the enterprise. The Web edition contains the features necessary to work in a Web hosting environment. The Standard edition is like the Enterprise edition, but it has some limitations like the Transparent Data Encryption, Partition Table parallelism, mirrored backups, online indexing, etc. I recommend checking the features that are not included in this edition to check if it is a good idea to buy them to save money.

Once downloaded, run the setup file of the SQL Server installer. The Basic option will install mainly the Database Engine with basic components. We will use the Custom option to learn some other features included in SQL Server. Download Media is used to install later or to install in other machines:

You can choose the installation location. It requires 9,000 MB of free space. Once selected, press Install:

While installing, you will find useful URLs. For example, the following SQL Server forums. 

Also, it will show the GitHub which contains sample databases, code samples and more.

The installer will install in the Planning section. There are many nice resources here like the Hardware and Software requirements to verify if you have all the hardware and software requirements. You also have security documentation, System Configuration Checker, the Data Migration Assistant (DMA), Online installation documentation, Failover and Upgrading documentation:

We will go to the Installation section and select the option New SQL Server stand-alone installation. Note that SQL Server Reporting Services is installed separately:

In product key, you can specify a license or select a free edition. In this example, we will install the developer edition:

License Terms will show the terms to install it. Accept the license terms:

In Microsoft updates, you can verify if there are updates in the installer:

The Install Rules will verify the Active Template Library, the registry keys and if the computer is not a domain controller:

In the context of the Feature Selection, Data Engine Services is the database itself.

  • Replication is useful if you want to replicate your data in another server or another SQL Server instance.
  • Machine Learning Services (In-Database). You can install R or Python. SQL Server 2016 included the R Services. Now, you can have R and/or Python.
  • Full-Text and Semantic Extractions for Search used for full-text queries.
  • Data Quality Services is used to enrich, standardize and avoid duplicate data.
  • Another nice feature is PolyBase used to query NoSQL Data.
  • Analysis Services is used in Business Intelligence (BI) to generate cubes to create Enterprise Reports with a multi-dimensional technology:

You have Machine Learning Services (In-Database) and Standalone. The first option is to install the SQL Server Database Engine. The Standalone does not require a database engine.

Data Quality Client is to perform quality operations using standalone tools. In Integration Services we have the option to install Scale Out Master and Scale Out Worker. With this feature you can distribute the packages in different Servers to provide better performance. The master is the responsible for the operations and the Workers receive the tasks. Client tools backward compatibility includes tools to work with earlier SQL Server versions like deprecated tools, discontinued tools and breaking changes in SQL tools.

Client tools SDK contain resources for programmers.

Distributed Replay Controller is the feature in charge to administer the distributed Replay clients. The Distribution Replay is used to capture a trace, verify performance, security, for upgrades or test environments it is like the SQL Profiler, but distributed Replay can trace multiple servers.

You can install the Distributed Replay client to simulate workloads.

SQL Client Connectivity SDK will install SQL Server OLEBD and ODBC connectors that can be used to connect to SQL Server using.NET, Java, PHP or other programming languages.

Finally, the Master Data Services are used to organize your data into models, creates rules for the access to the data and control who is using it:

If you select to install PolyBase, the setup will ask for the Oracle JRE. 

You can have multiple SQL Server instances in SQL Server in the same server. This can be useful to simulate and practice replications, mirroring or to have separate instances for different purposes. By default, you can install the default instance that is usually the Server computer name:

In PolyBase you can now install as a standalone instance or using a PolyBase scale-out group. The Scale-out group is used when you need to query massive data and you need to distribute the job across multiple SQL Server instances. This option requires to enable ports and enable the MSDTC:

There are account names created automatically for each service. It is a good practice to keep them. Do not grant administrative privileges to these accounts unless there is no other choice:

There are two options in SQL Server to authenticate. Windows authentication will use the Windows Account to authenticate in SQL. Mixed Mode allows creating internal logins and passwords inside SQL Server. You can Add accounts here using the Add Current User or by pressing the Add button:

In the Data Directories tab, you can choose the location of your data file and log file. It is a good practice to have them in separated drives to improve performance and increase security for disaster recovery.

 

The TempDB tab is used to configure the TempDB database. This is a system table used to store temporary data. Check our article to improve your database performance by configuring this database correctly:

  • The tempdb database, introduction and recommendations

FILESTREAM allows to store non-structured data like documents, images in the database:

The Analysis Services Configuration allows installing a Multidimensional and Data Mining Mode. This is a multidimensional cube that allows creating fast queries for enterprise reports. Another option is to Install Tabular Mode. The Tabular databases are dependent on the RAM. If your database will have several TB of information, the Multidimensional option is better. If you require Data Mining services, the Multidimensional option is better. For more information about Multidimensional vs Tabular Models.

PowerPivot can be used in Excel or if you can use it in SharePoint. PowerPivot for Excel can be used to create your reports. If you need to share with multiple people you can use SharePoint. You can add users with administrative permissions:

Integration Scale-Out Configuration – Master Mode will be used to define the port to communicate between the master and worker nodes. You will also be able to create the certificate here or use an existing one:

In the Integration Services Scale Out Configuration – Worker Node, you need to specify the endpoint of the Master Node, which is the master node name and the port. You can also specify the SSL certificate here:

You will receive a question if you want to install R. Press Accept and then next. R is a very popular language used for machine learning and other uses:

You can also accept to install Python which is a competes with R and offers many useful capabilities

Once that you have everything installed, you can verify the configurations. If everything is OK, press Install:

After 15-45 min, you will have your database installed:

Once installed the database and other components, open the installer. In the Installation section select the option to Install the SQL Server Management Tools:

It will go to the SSMS web page, download the product:

Once downloaded, install the product:

In the Windows menu, open the Microsoft SQL Server Management Studio:

Select the SQL Server name and press Connect:

Select a database. Right click and select New Query:

We will run a system stored procedure. The sp_who that will show the sessions, users connected and processes:

Another tool that is installed separately is the SQL Server Data Tools. This tool will help you if you want to work with BI tools like SSAS, SSIS or SSRS:

The link will go to the SSDT web page you can download the last version here:

 

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

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

Click on the respective cloud provider tab for technical information.

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

 

Features

SQL Server 2017 is measured a major release in the history of the SQL Server life cycle for various reasons. SQL Server 2017 is an interesting release. Some of SQL Server 2017 interesting features are mentioned below:

SQL Server 2017 …

  1. is now on the platform of or choice; SQL Server 2017 on several Linux distros, that can run on Docker containers, and SQL VM in Azure, along with the good ol’ SQL Server on Windows.
  2. expands its reach to support Graph database technology
  3. includes automatic database tuning
  4. supports Machine Learning by adopting Python
  5. contains new dynamic management views and functions
  6. has new string functions

 Time to crack in

 The file size of the SQL Server executable, sqlservr.exe, is ~3 MB in SQL Server 2017, compared to ~64 MB in SQL Server 2008 R2. The SQL Server team realized the importance of decoupling and modularizing the surface area code into much smaller components. The entire architecture is broken down into multiple smaller micro-services so that the deployments and integration are made on each of the respective components, and that the process is seamless.

Starting with SQL Server 2017, Microsoft is no longer using the old model of release under which cumulative updates (CUs) were released every two months and service packs (SPs) containing all the fixes from the preceding CUs were released once a year. This new model allowed shortening the release cycle and features getting added to SQL Database and SQL Server faster than ever before. Most recently, the SQL Server engineering team managed to ship SQL Server 2017 along with the new cross-platform support within 15 months after the release of SQL Server 2016. This is in contrast with the legacy three-to-five-year shipping cycles of the past.

One would feel why SQL Server2016 and 2017 are such big releases in a matter of a difference of just a few months. However, starting with 2017, Microsoft is moving to a rapid-release cadence with enhanced functionality and new features being rolled out annually instead of having to wait two or three years between versions.

SQL Server 2017 on Linux

, two of the biggest changes with SQL Server 2017 are its support for Linux and Docker containers, both of which could have a big impact on the database trends of the future. The release of SQL Server 2017 for Linux has proven to be a step towards extending the capabilities to a high-performance platform, which has already set some TPC benchmark records.

In this release, we can look forward to running SQL Server instances on Linux computers. Many of our previous articles show you how to get started by creating a RedHat virtual machine in Microsoft Azure, as well as installing SQL Server on a CentOS machine. Here are some of the articles.

Python support in ML (Machine Learning)

The next great feature that’s new in SQL Server 2017 is the addition of support for the Python scripting language, which is in addition to R, support for which was added in 2016, to form a new machine-learning services package. There’s no doubt that AI is now among the hottest buzzwords in IT. It seems like almost every IT product is now suddenly AI-enabled, and SQL Server is no exception in that regard. In April 2017, SQL Server was officially touted as “The first RDBMS with built-in AI!”

Graph Databases

This section explores the integration of Graph database components in SQL Server 2017. The pointer at the end of this section includes a working data set of relational and graph databases, and data models. In addition, it explains how to integrate graph databases with relational databases and how to convert the relational tuples into a graph data set.

A key concept of graph databases are edges and nodes. The relationships allow entities to be linked together directly, and in many cases retrieved with one operation. In some cases, due to stringent data modeling design, the relational data model may not be a good fit as it incurs costly join operations. In the past, the lack of viable alternatives and the lack of support made the graph model a difficult alternative, but that is no more the case.

We’ll also look at the integration of graph database support to help model many-to-many relationships as nodes and edges, rather than with the traditional relational model.  The footprint of the graph database technology in the IT industry is clearly visible today, and for good reasons such as adding efficiency to social recommendations, IT network analysis, fraud detection, product recommendation, and so on.

 

String functions

One of the major capabilities of modern computers is processing the human language. Basically, the strings are transformed into the code which is then processed by a machine. The built-in string functions have always been efficient in handling string literals. It is possible to find and alter string values using various options. However, string functions and string manipulation consume most of the query execution time in decoding the various parts of the character literals. SQL Server 2017 offers various new string manipulation functions, which have been in the talk for many of its features that simplify a developer’s life; no more writing long T-SQL statements with temporary tables and complex logic, only to manipulate and aggregate strings.

Here are some of the new string manipulation functions present in SQL Server 2017:

  • CONCAT_WS
  • TRANSLATE
  • TRIM
  • STRING_AGG

DMVs and DMFs

With the advent of system objects, the metadata of various pieces of SQL Server have been exposed for a better understanding of the entire system. These system objects, for the most part, are a cumulative collection of data, or the aggregation or accumulation of the various counter values or different data-structures of SQL Server. Most importantly, the data is real time, and obviously dynamic in nature. In most of the cases, the DMVs are used, and they define the baseline or pressure points of various metrics that determine the performance of the system. The DMFs provide cumulative statistics of the requested parameters.

 

The new DMVs introduced in SQL Server 2017 are as follows:

  • sys.dm_tran_version_store_space_usage
  • sys.dm_db_stats_histogram (Transact-SQL) 
  • sys.dm_exec_query_statistics_xml
  • sys.dm_os_host_info 
  • sys.dm_os_sys_info
  • A new column modified_extent_page_count introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database.
  • Identify the free disk space using the new DMV sys.dm_os_enumerate_fixed_drives

1

SELECT * FROM sys.dm_os_enumerate_fixed_drives;

The new DMFs introduced in SQL Server 2017 are as follows

  • sys.dm_db_log_info 
  • sys.dm_db_log_stats

Resumable Online Index Rebuild

SQL Server 2017 is the first commercial RDBMS to support pause and resume functionality for index maintenance operations. Most administrators consider index rebuild on VLDB’s a daunting task to manage. Many critical database solutions don’t permit to perform offline database maintenance operations. In most cases, the database design plays an important role in that regard. SQL Server 2017 provides index maintenance tasks with great flexibility as an alternative solution to managing the maintenance operations. There are situations where database administrators might need to temporarily free up system resources. For example, what if a priority task needs resources and a lower-priority index rebuild is eating away the resources? We’d rather have the index rebuild operation run in some other available maintenance window—but at the same time, what if the rebuild was halfway? We do not want to lose that state either. In such a case, the operation can be paused, and resumed during a maintenance window. This can be a termed as “piecemeal index maintenance operation.”

  1. Resume: Resume an index-rebuild operation, after a failure.
  2. Pause: Pause the rebuild operation (and resume it at a later point).
  3. Rebuild: Rebuild large indexes with minimal log space usage.

Automatic database tuning

Monitoring the databases for optimal query performance, creating and maintaining required indexes, and dropping rarely-used, unused, or expensive indexes is a common database administration task. SQL Server 2017 can now assist database administrators in performing these routine operations by identifying problematic query execution plans and fixing SQL plan performance problems. Automatic tuning begins with continuously monitoring the database, and learning about the workload that it serves. Automatic database tuning is based on Artificial Intelligence; AI is now providing great flexibility in managing and tuning the performance of database systems.

SQL Server 2017 include the following features:

 

SQL Server on Linux

SQL Server is no longer just a windows-based relational database management system (RDBMS). You can run it on different flavors of the Linux operating systems. You can also develop applications with SQL Server on Linux, Windows, Ubuntu operating systems, or Docker and deploy them on these platforms.

Resumable online index rebuild

This feature resumes an online index rebuild operation from where it stopped after events such as database failovers, running out of disk space, or pauses.

SQL Server machine learning services

SQL Server 2016 integrated the R programming language, which can be run within the database server and can be embedded into a Transact-SQL (T-SQL) script, too. In SQL Server 2017, you can execute the Python script within the database server itself. Both R and Python are popular programming languages that provide extensive support for data analytics along with natural language processing capability.

Query processing improvements

SQL Server 2017 adapts optimization strategies to your application workload’s runtime conditions. It includes adaptive query processing features that you can use to improve query performance in SQL Server and SQL Database.

Automatic database tuning

This feature notifies you whenever a potential performance issue is detected and enables you to apply corrective actions, or it enables the database engine to automatically fix performance issues caused by the SQL plan choice regressions. Thus, the database can dynamically adapt to your workload by finding what indexes and plans might improve performance of your workloads and what indexes affect your workloads. Based on these findings, the automatic tuning process applies actions that improve the workload performance. In addition, the database continuously monitors performance after any change made by automatic tuning to ensure that it improves the workload performance. Any action that doesn’t improve performance is automatically reverted

empDB file size improvements

SQL Server 2017 setup now enables you to specify the initial TempDB file size up to 256 GB (262,144 MB) per file, with a warning if the file size is set greater than 1GB without instant file initialization (IFI) enabled. It is important to understand that, depending on the initial size of TempDB data file specified, not enabling IFI can cause setup time to increase exponentially.

Smart differential backup

A new column modified_extent_page_count is introduced in sys.dm_db_file_space_usage to track differential changes in each database file in the database. The new column modified_extent_page_count allows DBAs, the SQL community, and backup independent software vendors (ISVs) to build smart backup solutions, which perform differential backups if the percentage of changed pages in the database is below a threshold (approximately 70-80%). Otherwise, they perform a full database backup. With a large number of changes in the database, the cost and time to complete differential backups is similar to taking a full database backup, so there is no real benefit of taking differential backup in this case. However, it can certainly increase the restore time of database. By adding this intelligence to the backup solutions, you can now save on restore and recovery time by using differential backups.

Smart transaction log backup

A new Dynamic Management Function (DMF), sys.dm_db_log_stats (database_id), was released. This function exposes a new column, log_since_last_log_backup_mb, which empowers DBAs, the SQL community, and backup ISVs to build intelligent T-log backup solutions to take backups based on the transactional activity on the database. This T-log backup solution intelligence ensures that, if the T-log backup frequency is too low, the transaction log size doesn’t grow due to a high burst of transactional activity in a short time. It also helps to avoid a situation where the scheduled transaction log backup creates too many T-log backup files even when there is no transactional activity on the server. If that happened, it would add unnecessarily to the storage, file management, and restore overheads.

Improved SELECT INTO statement

In SQL Server 2017, you can provide the filegroup name on which to create a new table by using the ON keyword with the SELECT INTO statement. The table is created on the default filegroup of the user by default. This functionality was not available in previous versions.

Distributed transaction support

SQL Server 2017 supports distributed transactions for databases in availability groups. This support includes databases on the same instance of SQL Server and databases on different instances of SQL Server. Distributed transactions are not supported for databases configured for database mirroring.

New availability groups functionality

This functionality includes clusterless support, the Minimum Replica Commit Availability Groups setting, and Windows-Linux cross-OS migrations and testing.

This functionality includes the following features:

  • Availability groups can now be set up without an underlying cluster (Windows Server Failover Cluster or WSFC) and across mixed environments (instances on Windows and Linux or Docker).
  • The new Minimum Replica Commitsetting enables you to dictate a certain number of secondary replicas. You must commit a transaction before committing on the primary.

 

New dynamic management views

The dynamic management views (DMVs) include the following elements:

  • dm_db_log_statsexposes summary level attributes and information on transaction log files and is helpful for monitoring transaction log health.
  • dm_tran_version_store_space_usageenables you to see the impact on version store usage, grouped by each database. As a result, you can use this to profile your workload in a test environment (before and after the change) and to monitor the impact over time–even if other databases are also using version store.
  • dm_db_log_infoexposes virtual log file (VLF) information to monitor, alert, and avert potential transaction log issues.
  • dm_d_stats_histogramis a new dynamic management view for examining statistics, as shown in the following image:
  • dm_os_host_infoexposes things like platform, distribution, service pack level, and language.
  • dm_os_sys_infowas expanded, revealing CPU information (such as socket count, core count, and cores per socket).
  •  

In-memory enhancements

The in-memory changes in SQL Server 2017 include the following enhancements:

  • Computed column, and indexes on those columns, are now supported.
  • CASE expressions, CROSS APPLY, and TOP (N) WITH TIES are now supported in natively-compiled modules.
  • JSON commands are now fully supported in both check constraints and in natively-compiled modules.
  • The system procedure sp_spaceusednow properly reports space for memory-optimized tables.
  • The system procedure sp_renamenow works on in-memory tables and natively-compiled modules.
  • The limitation of eight indexes on memory-optimized tables has been eliminated.
  • Memory-optimized filegroup files can now be stored on Azure storage.

Security enhancement

You can now grant, deny, or revoke permissions on database-scoped credentials such as CONTROL, ALTER, REFERENCES, TAKE OWNERSHIP, and VIEW DEFINITION permissions. Also, ADMINISTER DATABASE BULK OPERATIONS is now visible in sys.fn_builtin_permissions. 

High availability and disaster recovery

Gain mission-critical uptime, fast failover, easy setup, and load balancing of readable secondaries with enhanced Always On functionality in SQL Server 2017. This is a unified solution for high availability and disaster recovery on Linux and Windows. You can also put an asynchronous replica in an Azure virtual machine for hybrid high availability.

Performance improvements

SQL Server 2017 introduces the following changes to the way queries and statistics are collected and displayed:

  • A new DMV dm_exec_query_statistics_xmlallows you to correlate sessions to plans, as long as query profiling is enabled. The following image illustrates this:
  • Showplan XML now includes information about the statistics used for a plan and, for actual plans, runtime metrics and the top 10 wait statistics experienced by that plan. These wait statistics are also now being tracked in the query store.
  • A new dynamic management function dm_db_stats_histogramenables you to access histogram information programmatically, without databases console commands (DBCC).

———————————————————————————————————————————————————-

Major Features of SQL Server :

  • Security and compliance Protect data at rest and in motion with a database that has the least vulnerabilities of any major platform for six years running in the NIST vulnerabilities database (National Institute of Standards and Technology, National Vulnerability Database, Jan 17, 2017). Security innovations in SQL Server 2017 help secure data for mission-critical workloads with a layers of protection security approach, adding Always Encrypted technology along with row-level security, dynamic data masking, transparent data encryption (TDE), and robust auditing.
  • High availability and disaster recovery Gain mission-critical uptime, fast failover, easy setup, and load balancing of readable secondaries with enhanced Always On in SQL Server 2017—a unified solution for high availability and disaster recovery on Linux and Windows. Plus, put an asynchronous replica in an Azure Virtual Machine for hybrid high availability.
  • Corporate business intelligence Scale your business intelligence (BI) models, enrich your data, and ensure quality and accuracy with a complete BI solution. SQL Server Analysis Services help you build comprehensive, enterprise-scale analytic solutions—benefiting from the lightning-fast performance of in-memory built into the tabular model. Reduce time to insights using direct querying against tabular and multidimensional models.
  • End-to-end mobile BI on any device Gain insights and transform your business with modern, paginated reports and rich visualizations. Use SQL Server Reporting Services to publish reports to any mobile device—including Windows, Android, and iOS devices—and access reports online or offline.
  • Simplify data big and small Combine relational data and big data with PolyBase technology that queries Hadoop using simple T-SQL commands. JSON support lets you parse and store JSON documents and output relational data into JSON files. Now in SQL Server 2017, manage and query graph data inside your relational database.
  • In-database advanced analytics Build intelligent applications with SQL Server Machine Learning Services using R and Python. Move beyond reactive and into predictive and prescriptive analytics by performing advanced analytics directly within the database. By using multi-threading and massively parallel processing, you’ll gain insights faster than using open source R and Python alone.
  • Real-time hybrid transactional/analytical processing Combine in-memory columnstore and rowstore capabilities in SQL Server 2017 for real-time operational analytics—fast analytical processing right on your transactional data. Open up new scenarios like real-time fraud detection without impacting your transactional performance.
  • Now on Windows, Linux and Docker Develop once and deploy anywhere with our consistent experience from on-premises to cloud. Now with support for Windows and Linux as well as Docker containers.
  • Consistent data platform from on-premises to cloud Get a consistent experience from on-premises to the cloud—letting you build and deploy hybrid solutions for managing your data investments. Benefit from the flexibility to run SQL Server workloads in Azure Virtual Machines, or use Azure SQL Database to scale and further simplify database management.
  • Easy-to-use tools and connectors Use the skills you already have, along with familiar tools like Azure Active Directory and SQL Server Management Studio, to manage your database infrastructure across on-premises SQL Server and Microsoft Azure. Apply industry-standard APIs across various platforms and download updated developer tools from Visual Studio to build next-generation web, enterprise, business intelligence, and mobile applications.

AWS

Installation Instructions For windows

Installation Instructions for Windows

Step 1) RDP  Connection: To connect to the deployed instance, Please follow Instructions to Connect to Windows  instance on AWS Cloud

1) Connect to virtual machine using following RDP credentials:

  • Hostname: PublicDNS  / IP of machine
  • Port : 3389

Username: To connect to the operating system, use RDP and the username is Administrator.
Password : Please Click here to know how to get password .

Step 2) SQL Connection: To Connect Microsoft SQL Server Management Studio in windows server, Please follow Instructions to Connect Microsoft SQL Server Management Studio

Step 3) Database Credentials: You can Login by below SQL Database credentials

      SQL UserName : sa ||  Password : Passw@rd123

Note: You can reset ‘sa’ password by using windows authentication to connect to local SQL instance.Please use localhost in the server name when connecting from inside the RDC

Please change the password after the first login.

Step 4) Other Information:

1.Default ports:

  • Windows Machines:  RDP Port – 3389
  • 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

a
b
c
d
e

Azure

Installation Instructions For windows

Installation Instructions for Windows

Step 1) RDP  Connection: To connect to the deployed instance, Please follow Instructions to Connect to Windows instance on Azure Cloud

1) Connect to virtual machine using following RDP credentials:

  • Hostname: PublicDNS  / IP of machine
  • Port : 3389

Username: To connect to the operating system, use RDP and the username is Administrator.
Password : Please Click here to know how to get password .

Step 2) SQL Connection: To Connect Microsoft SQL Server Management Studio in windows server, Please follow Instructions to Connect Microsoft SQL Server Management Studio
Local SQL Server sa password has been set as Passw@rd123

Step 3) Database Credentials: You can Login by below SQL Database credentials

      SQL UserName : sa ||  Password : Passw@rd123

Note: You can reset ‘sa’ password by using windows authentication to connect to local SQL instance.Please use localhost in the server name when connecting from inside the RDC

Please change the password after the first login.

Step 4) Other Information:

1.Default ports:

  • Windows Machines:  RDP Port – 3389
  • Http: 80
  • Https: 443
  • SQL Server Port: 1433 this is by default not allowed on the firewall for security.

2. VSCode and debug utilities  have been installed 

Google

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 initialize local DB server 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 the Public interface for security reasons.

Videos

SQL Server 2017 Ent with Vulnerability Assessment

https://www.youtube.com/watch?v=c6My_tdsyDU
https://www.youtube.com/watch?v=qZmUmF0F8f8
SQL Server 2017 on Cloud

Related Posts