Sql express restrictions. Connecting to a shared LocalDB instance
A new version of SQL server from Microsoft has been released, namely SQL Server 2016 and now we will look at the new features of this version, compare it with previous releases, and also find out in which editions it is released.
Microsoft SQL Server is a relational database management system developed by Microsoft. As you know, support for SQL Server 2005 ended in April 2016 and currently there are the following versions of SQL Server: SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 and now SQL Server 2016.
What's New in Microsoft SQL Server 2016
And we will start, of course, by considering the new functionality, i.e. the new features that came with SQL Server 2016.
- Always Encrypted(i.e. " Always encrypted") is a functionality that allows you to store some data ( for example personal data) in encrypted form, with the encryption key stored on the client, resulting in a separation between those who own the data and can view it, for example, the client whose personal data is encrypted, and those who control the data but should not have access to them, for example, administrators or SQL Server programmers. This feature ensures data protection both at rest and in motion, i.e. in the process of transfer;
- Real-Time Operational Analytics (« Operational analytics in real time") is the ability of the SQL server to process both the operational OLTP load and analytical queries on the same database tables simultaneously, i.e. in real time. The traditional approach to analytics has several disadvantages, such as: the complexity of implementing ETL processes, the cost of purchasing additional data storage and licenses, and one of the most important disadvantages, which is unacceptable for many enterprises, is data latency. This is due to the fact that an ETL job is usually performed once a day, for example, at the end of the working day, and accordingly this data can only be analyzed the next day. The Real-Time Operational Analytics capability allows in some cases to eliminate all of the above disadvantages, i.e. there is no longer a need for ETL and data warehouses. One of the nuances of Real-Time Operational Analytics is that this feature is aimed at working with a single data source that is involved in both OLTP workload and analytics. If it is necessary to analyze data from several sources, the need for a separate data warehouse does not disappear;
- SQL Server R Services (In-database Analytics) is a new SQL server component that allows you to perform analysis within a database using the R language.
R is an open source programming language and library that is widely used as a tool for data analysis. All the features of the R language, including the ability to use enough open source packages to solve everyday problems, are integrated into SQL Server 2016, eliminating the hassle of integrating them into your applications. In addition, R Services users can use the ScaleR library of algorithms, a set of functions that scale to handle hundreds of billions of rows in parallel, delivering performance that cannot be achieved using common open source packages;- PolyBase is a technology that allows you to send queries to both relational and non-relational data, as well as import and export this data. In other words, SQL Server now has the ability to access data in Hadoop or Azure Blob storage;
- AlwaysOn Availability Groups– SQL Server 2016 has improvements to AlwaysOn Availability Groups, namely: the ability to have up to three synchronous replicas, cyclic load balancing on secondary replicas, support for distributed transactions ( DTC - Distributed Transaction Coordinator), support for groups of managed service accounts ( gMSA - Group Managed Service Accounts), and improved log replication throughput;
- Stretch Database is a technology that allows you to store “cold” data in the Azure cloud with the ability to quickly access this data ( “Cold” data here refers to data that is rarely used or not used at all). In other words, if you have a table or tables in your enterprise database that store historical data reaching terabyte volumes, while this volume is constantly increasing, and you need to ensure the safety of this data so that it can be accessed instantly, then Stretch Database technology will help you with this. The advantages of using Stretch Database are: no need to purchase additional storage systems, instant access to cold data, a common way to access cold data, i.e. there is no need to modify queries and applications, transfer of “cold” lines ( for example, if you have both hot and cold data in the same table, you can migrate only the cold data to the Azure cloud). All you need to start using Stretch Database is SQL Server 2016 and a subscription to the Azure cloud to create a new SQL Server Stretch Database;
- SSRS Mobile Reports– the ability to create reports optimized for viewing on mobile devices has been added to Reporting Services;
- Improvements for Columnstore Indexes- SQL Server 2016 has a number of improvements for columnar indexes, for example: a clustered columnstore index now supports one or more non-clustered rowstore indexes, a table can now have one updatable non-clustered columnstore index, and it is now possible to create one columnstore index on a memory-optimized table;
- In-Memory OLTP improvements– this technology also received new functionality such as: support for UNION, UNION ALL, DISTINCT operators, support for UNIQUE indexes, CHECK and FOREIGN KEY constraints, triggers, as well as nested queries;
- Database Scoped Configurations– This feature allows you to configure a number of database configuration parameters at the individual database level;
- Live Query Statistics- SQL Server Management Studio 2016 provides the ability to view the live execution plan of an active query, in other words, you can now watch in real time how a query is executed. Using this feature, we can easily determine which part of the request will take a long time to complete and adjust the request accordingly based on this, i.e. optimize it;
- Query Store– This feature automatically records the history of queries, plans and statistics during execution, and stores them for review, which allows database administrators to track resource-intensive queries and optimize them;
- System-Versioned Temporal Tables– this is a new type of temporary tables that allows you to save a complete history of data changes and obtain information about this data at any point in time, and not just the current one;
- Native JSON support– in SQL Server 2016 it became possible to process data in JSON format, i.e. Now we can export and import JSON data using standard tools;
- Row-Level Security(RLS) – this technology allows you to control access to rows in a database table based on the characteristics of the user executing the request ( for example, group membership or execution context). In other words, we can make it so that one or more users can see ( and process accordingly) only certain rows in the table;
- Dynamic Data Masking is a dynamic data masking function. The purpose of this feature is to limit the disclosure of sensitive data by preventing users who should not have access to that data from viewing it. This feature does not encrypt data, so it is recommended to use Dynamic Data Masking in combination with other security technologies, such as Encryption or Row-Level Security, to better protect your data;
- Other improvements. More detailed information can be found in the official documentation here is the link - SQL Server 2016 Technical Documentation.
Comparison of SQL Server versions
| Possibility, functionality | SQL Server version | |||
| 2008 R2 | 2012 | 2014 | 2016 | |
| In-memory OLTP | + | + | ||
| In-memory ColumnStore | + | + | + | |
| Real-time operational analytics | + | |||
| Query Store | + | |||
| Always On | + | + | + | |
| Always Encrypted | + | |||
| Transparent data encryption | + | + | + | + |
| Row-level security | + | |||
| Dynamic data masking | + | |||
| Backup encryption support | + | + | ||
| Detailed audit | + | + | + | + |
| JSON support | + | |||
| PolyBase | + | |||
| Stretch Database | + | |||
| Archiving to Azure | + | + | + | |
| Policy-based management | + | + | + | + |
| Mobile business intelligence | + | |||
| Integration services managed as a server | + | + | + | |
| Multidimensional semantic models | + | + | + | + |
| SQL Server R Services | + | |||
Microsoft SQL Server 2016 editions
SQL Server 2016 comes in four main editions: Enterprise, Standard, Developer, and Express. Enterprise and Standard are reactions that are used in industrial operation. The Developer edition is used for application development, Express for training, or just to get acquainted with SQL server. Below is a comparative table of these editions, from which you will find out what capabilities and limitations this or that edition has.
| Functionality, limitation | ||||
| Enterprise | Standard | Developer | Express | |
| Maximum number of cores | Unlimited | 24 | Unlimited | 4 |
| Maximum memory used | Maximum for OS | 128 GB | Maximum for OS | 1 GB |
| Maximum database size | 524 PB | 524 PB | 524 PB | 10 GB |
| SQL Server Management Studio, Policy-Based Management | + | + | + | + |
| In-memory OLTP | + | + | ||
| In-Memory Columnstore | + | + | ||
| Always On Availability Groups | + | + | ||
| Row-level security | + | + | + | |
| Dynamic Data Masking | + | + | + | |
| Always Encrypted | + | + | ||
| PolyBase | + | + | + | |
| Basic reporting and analytics | + | + | + | + |
| T-SQL, JSON, CLR | + | + | + | + |
| SQL Server Integration Services | + | + | + | |
| Mobile Reports | + | + | ||
| Stretch Database | + | + | + | + |
| Basic R integration | + | + | + | + |
| Advanced R Integration | + | + | ||
System requirements for installing SQL Server 2016
Now let's look at the requirements that must be met in order to install SQL Server 2016. The table shows the minimum processor and RAM requirements. It is recommended to use higher specifications, it should also be noted that x86 processors are no longer supported, which means SQL Server 2016 can only be installed on a computer with an x64 processor and full functionality will only be available on 64-bit versions of Windows.
| Characteristic | Microsoft SQL Server 2016 editions | |||
| Enterprise | Standard | Developer | Express | |
| Operating system (64 bit) | Windows Server 2012, 2012 R2 | Windows 8, 8.1, 10, Windows Server 2012, 2012 R2 | Windows 8, 8.1, 10, Windows Server 2012, 2012 R2 | |
| CPU frequency | 1.4 GHz | 1.4 GHz | 1.4 GHz | 1.4 GHz |
| RAM | 1 GB | 1 GB | 1 GB | 512 MB |
| Free hard disk space | 8 GB | 8 GB | 8 GB | 8 GB |
You can read more about all the hardware and software requirements for installing SQL Server 2016 in the official technical documentation here.
I propose to end here, I hope the material was useful to you, bye!
Brief description of Microsoft SQL Server 2016
SQL Server is Microsoft's main data processing product. Version 2016 is the most significant breakthrough in the history of Microsoft data platforms: faster transactions and queries, insights on any device, advanced analytics, new security technologies and new use cases for hybrid cloud. SQL Server 2016 provides advanced features to solve high-value workloads with in-memory operations and built-in operational analytics. Comprehensive security features such as Always On Encryption protect data at rest and active, while world-class high availability and disaster recovery further enhance Always On technology. The new functionality will enable organizations to become more efficient with their data, going beyond business intelligence by performing advanced analysis directly on databases and delivering detailed visualizations for business insights on any device. With new Database Stretch technology that dynamically distributes hot and cold transaction data across Microsoft Azure in a secure manner, you will also benefit from hyperscale cloud with new hybrid use cases. Your data will always be at hand, regardless of its size. Additionally, SQL Server 2016 provides a complete database platform for the hybrid cloud, making it easy to build and deploy solutions that can be managed on-premises or in the cloud.
Platform for the operation of Microsoft SQL Server
To run and run smoothly, Microsoft SQL Server 2018 requires a server operating system – Microsoft Windows Server 2012 or earlier versions. For more details, see the system requirements for the product edition.
Microsoft Windows Server is licensed separately.
Answers to frequently asked questions
|
If the license is "core", how many users can connect to the server? |
|
|
If you buy a core license, do you need to buy a license for the SQL server? |
|
|
For 1C client-server version for 20 users, how many CALs do you need to buy? One or 20, taking into account the fact that only one 1C user USR1CV82 works directly with SQL? |
Tags:
There are several options for limiting memory, some that we configure and not those that Microsoft sets.
Memory limit by SQL Server edition
Let's first talk about what we cannot change, namely the memory limit for the SQL Server edition. These thresholds change from version to version, today I would like to focus on SQL Server 2014, other versions are possible.
As we can see, for loaded solutions, the Standard edition will not be enough, and Enterprise costs significantly more (on average 4 times more when comparing core licenses).
Memory limit for a SQL Server instance
Now let's move on to the limitation that we can influence, namely the instance parameter max server memory. This parameter limits the memory allocated for the instance, but does not cover its entire volume. Max server memory limits only the buffer pool (hereinafter referred to as the buffer pool); without going into details, the buffer pool is the SQL Server memory area responsible for the entire cache of data pages and indexes. The rest of the cache (procedural, backup, dll support, etc.) is used outside the buffer pool. Memory is managed by memory clerks, see all their types and the resources they use. It is for this reason that you could see that the instance often eats up more memory than we set in max server memory.
max server memory can be changed in 2 ways:
- Using T-SQL commands
Transact-SQL
First, enable the advanced configuration option for SQL Server sp_configure "show advanced option", 1 RECONFIGURE GO -- Set the maximum amount of RAM for the buffer pool to 2048 sp_configure "max server memroy", 2048 RECONFIGURE
2. Using SQL Server Management Studion:
Right mouse button on the server > Properties > Memory section > specify the desired value in the max server memory section > OK
Limiting memory using the Resource Governor
We also have the opportunity to go down another level and limit memory for specific connections using the Resource Governor. This component creates resource pools, workload groups, and a function for distributing connections across workload groups (). Using Resource Governor, we can divide the resources of a SQL Server instance into groups and distribute these groups, according to a certain logic, to different users. This functionality is only available in the Enterprise Edition.
Features of determining the actual memory used by SQL Server
You can understand how much memory SQL Server is using using the task manager, but if you have ‘memory page locking’ enabled on your server, then this method will not show the real picture. In this case, to see the amount of memory used, you will need to access the performance counters or write a query to the sys.dm_os_performance_counters view:
- The request will look like this
Nowadays, professionals increasingly have to deal with databases and conduct their work in them. It is simpler, more reliable and more convenient, especially if you choose a database management system (DBMS) wisely. One of the best products at the moment is Microsoft sql server 2016.
The essence and innovations insql server 2016
SQL 2016 was launched on June 1, 2016 and succeeds the 2014 version. Compared to it, a newer version:
- conduct transactions faster and process requests;
- has advanced analytics;
- uses a hybrid cloud, providing access to information on any gadget;
- has a new security and information protection system;
Now database management has become even safer and faster, and if data recovery is necessary, improved AlwaysOn technology will help. And given that this DBMS is accessible and perfectly compatible with Microsoft Azure, program deployment has become even faster and more reliable.
Editorialsql server 2016
They are not much different from the previous version and look like this:
- Enterprise Edition.
It is a full-fledged (in terms of performance) edition that provides fast processing of critical processes, deployment and security of data, and is also a comprehensive solution for high-quality analytics. Has the highest level of service.
- Standard Edition.
Includes tremendous support for business analytics, with minimal use of IT resources. This edition will give access to key parameters of sql server 2016;
- Developer Edition.
A free edition that has the full set of functionality from the Enterprise Edition, but can only implement it for testing and application development.
- Express Edition.
Free edition, with which you can deploy small databases and create applications, but with a disk memory limit of up to 10GB.
There are differences in the licensing of the Enterprise and Standard versions, namely:
- Enterprise Edition is licensed per core only;
- The Standard Edition can be licensed by the number of cores, or the “Server + client license” variation.
If you decide to buy sql server 2016 and license the edition by the number of cores, then you should take into account the number of cores on each server and purchase the number of licenses in accordance with them. But also, be sure to consider in what environment (virtual\physical) the user will work.
This option for obtaining a license is advantageous in that it allows an unlimited number of users to use the edition.
Licensing based on the “Server + Client License (CAL)” principle is usually divided into 2 parts - purchasing a license for the server from which the connection to sql server 2016 will be made. And also client licenses, which are divided per user or device.
If you decide to download sql server express or purchase any other version of the DBMS, make sure that they are downloaded or purchased from official Microsoft representatives before installing and configuring it.
Free PDF Brochure
“3 Ways to Save on Microsoft”
How to buy Microsoft products to benefit your company
The brochure contains answers to the following questions:
- How to get a discount from a Microsoft reseller
- How to get a special price from the Microsoft representative office in Ukraine
- Safety rules when purchasing Microsoft products in online stores
- Licensing nuances that help save up to 80% of the purchase price
- 3 checklists for getting the best prices on Microsoft