Finding Tables Suitable for Stretch Databases in SQL Server 2016

SQLServerF1

Most of the DBAs must be familiar with SQL Server upgrade advisor, which we run during planning phase of upgrading to higher SQL Server versions inorder to identify any objects or features that will get affected during or after the instance or database upgrade. With every new release of SQL Server, some old features may be deprecated or retired and behavior of some features might change in new versions. So, running upgrade advisor during planning phase on a test server where the production database is restored, will give us good idea on what objects are going to break during or after upgrading to higher version of SQL Server. In previous releases of SQL Server, the Upgrade Advisor tool was made available only few days before the release of the new SQL Server version, but with SQL Server 2016, the upgrade advisor has been released way early giving options to test the instances or databases and then to proceed with testing on RC releases of the SQL Server. Although, we may be very familiar with using the upgrade advisors for SQL Server 2005, 2008 R2, 2012 and 2014, but there have been significant changes to SQL Server 2016 Upgrade Advisor in look and feel and its usage.

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

We can identity the tables which can be part of Stretch Database using SQL Server 2016 upgrade advisor, SQL Server 2016 upgrade advisor has different options to analyse a regular database and to analyze for Stretch Database. To get started, first download and install SQL Server 2016 upgrade advisor on a test server. Launch the SQL Server 2016 upgrade advisor and select “Scenarios” option -> choose ‘Run Stretch Database Advisor” -> Choose “Select Databases to Analyze” -> Provide SQL Instance name and make the successful connection to the SQL instance against which we want to run the analysis -> Select one or more databases from the list of databases list provided -> Run the analysis. It will take a while for the analysis to complete, after which we will be presented with a summary page of the analysis. We can save the results to HTML file or CSV file or other options as and when available.

But there are many limitations to the tables to be part of Stretch Database at this point of time. Below are some of the limitations.

– Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure SQL table that contains the migrated data.
– Insert/Delete operations are not supported and also insert is not allowed through linked servers as well.
– Views cannot be created on the stretch enabled tables. Filters on SQL Server indexes are not propagated to the remote table.
– There are many other limitations on which data types cannot be used for the tables columns, some database properties are not supported like filetables, Memory-optimized tables, etc.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Revamped SQL Server 2016 Upgrade Advisor

SQLServerF1

Most of the DBAs must be familiar with SQL Server upgrade advisor, which is used to run during planning phase of upgrading to higher SQL Server versions inorder to identify any objects or features that will get affected during or after the instance or database upgrade. With every new release of SQL Server, some old features may be deprecated or retired and behavior of some features might change in new versions. So, running upgrade advisor during planning phase on a test server where the production database is restored, will give us good idea on what objects are going to break during or after upgrading to higher version of SQL Server. In previous releases of SQL Server, the Upgrade Advisor tool was made available only few days before the release of the new SQL Server version, but with SQL Server 2016, the upgrade advisor has been released way early giving options to test the instances or databases and then to proceed with testing on RC releases of the SQL Server. Although, we may be very familiar with using the upgrade advisors for SQL Server 2005, 2008 R2, 2012 and 2014, but there have been significant changes to SQL Server 2016 Upgrade Advisor in look and feel and its usage.

We should be able to download the latest version of SQL Server 2016 Upgrade advisor from official Microsoft link here.
As per Microsoft documentation “SQL Server 2016 Upgrade Advisor Preview is a standalone tool that enables users of prior versions to run a set of upgrade rules against their SQL Server database to pinpoint breaking and behavior changes and deprecated features as well as providing help with the adoption of new features such as Stretch Database.
Once we download the SQL Server 2016 Upgrade Advisor, we will find SqlAdvisor.msi in the downloaded location. Run this msi installer by choosing run as administrator and you will need to have local administrator permission on the server where you are installing this tool. It is always recommended to install Upgrade Advisor on a test server and restore a copy of production database on the test server for analysis.

Once the installation of SQL Server 2016 upgrade advisor is completed successfully, launch the program and you will first see a What’s New screen which has some information regarding What’s new in SQL Server 2016 Upgrade Advisor. After that we will find a title bar with SQL Server 2016 Upgrade Advisor and next to it, you will see two main options “Scenarios” and “Active”. Apart from these two options, there are Notifications, Send Feedback and Settings which allows us to see new notifications for the product and to send any feedback to Microsoft for any bugs or new feature requests. Settings option has a gear icon which is used to choose any settings, as of now there is not much you can do here.

Inorder to start the upgrade analysis process, we need to choose “Scenarios” menu -> Run Database Upgrade Advisor -> Select databases to analyze -> Select Instance -> Select the instance from the drop down list if any available or create a new connection -> If we need to specify port number as well inorder to connect to the instance, then we can specify the port number by clicking on advanced options -> Once we successfully made connection to the SQL Server instance, next we will see list of databases to choose for the upgrade analysis -> Select one or more databases based on your requirement -> Run the upgrade analysis by clicking on Run button. It will take a while for the analysis to be performed. Once analysis is complete, you will be presented with a summary page. We will not only see the analysis for SQL Server 2016 instance, but we will find it for other versions as well based on what is the SQL Server version where upgrade advisor was run against, like if you run it against SQL Server 2008 R2 instance, then you will see the analysis for SQL Server 2012, 2014 and 2016. We can drill down for each of the versions and see the affected objects and the details about the impact and recommendation.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Dynamic Data Masking Feature in SQL Server 2016

SQLServerF1

Microsoft SQL Server 2016 has introduced many new features and Dynamic Data Masking is one such feature useful in some cases. Dynamic data masking main purpose is to limit the exposure of sensitive data by masking it to non-privileged or low privileged users. Dynamic data masking feature helps in preventing unauthorized access to sensitive data by enabling users to only view the sensitive data required with minimal impact on the application layer. This basically hides the data from the user trying to query the data, instead zeros or other symbols are returned instead of actual data. The underlying data does not change, but only the results are not visible to under privileged users. This Dynamic data masking feature is very easy to implement with the existing applications, since masking rules are applied only in the query results. Many applications can mask sensitive data without modifying existing queries.

It is important to understand that this Dynamic Data Masking Feature in SQL Server 2016 is not at all replacement of any of the security features like TDE, Cell-level encryption, SSL, etc, because in Dynamic Data Masking, the underlying data is not converted into another form, rather it is saved in the same format and can be viewed by users with appropriate permissions without any issues or without any requirement of decryption or performing any transformations. The use case of this feature is more for obfuscation of personally identifiable information (PII) from lesser-privileged users. This can also be be used to apply the masking constraints to development or test environments where this information should not be visible to the developers or testers. In the initial CTP releases of this Dynamic Data Masking feature, there were many bugs identified by the community where some tricks could easily reveal the sensitive data to under privileged users, like using alias or temp tables or variables could reveal the sensitive data. Later versions of CTP releases had fixes released to counter the bugs, but still this feature is only has limited usability, but strictly should not be considered as replacement for encryption of data.

Dynamic Data Masking feature supports different types of masks to some or all of the columns in a table which helps in protecting the privacy of the data from low-privileged/normal (non-sysadmin) users. There are four different types of masks currently supported Dynamic Data Masking in SQL Server 2016.
default() – This is for strings to show x for each character up to 4, for numeric types it will show 0, and for dates this will show 2000-01-01.
email() – This only shows the first character, then replaces the remaining characters with XXX@XXXXX.com
partial() – With this option, we can define a custom string to represent the mask, including how many leading and trailing characters to show from the original string.
random – A random masking function for use on any numeric type to mask the original value with a random value within a specified range.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

How to View the Data Encrypted Using Always Encrypted in SQL Server 2016

SQLServerF1

Always Encrypted is a new feature introduced with SQL Server 2016. This has been introduced to secure sensitive data in a better way which can port very well with SQL Azure databases data as well. Although there have been other features available prior to SQL Server 2016 for encryption like TDE, SSL and cell level encryption, but the new Always Encrypted feature ensures critical data is not visible to anyone, either SQL Server DBAs, System Administrators, Network Administrators or hackers listening on the network between client and server. This is achieved by Always Encrypted as it encrypts the data both at rest and and also in motion. Important components of Always Encrypted feature as listed below,
Column Master Key – It is an encryption key that protects the column encryption keys. We must have at least one master key before encrypting any columns.
Column Encryption Key – It is the encryption key that actually protects our encrypted columns data.

Most important thing, which allows us to view or work with the Always Encrypted data is the Connection string.
Connection string – For a client driver to understand that column encryption is in use, the connection string must have the attribute Column Encryption Setting = enabled;
As, we must have seen different connection strings which are used to connect to SQL Server from applications and has different options depending up on the type of authentication, additional parameters in case of any database mirroring features being used. So, for Always Encrypted as well, we need to use this additional parameter in the connection string to work with the Always Encrypted data which is Column Encryption Setting = enabled;
Another useful thing with Always Encrypted is that the application code does not need to changed to access this encrypted data, once we use this parameter in the connection string.
We can find the Column Master Key and Column Encryption Key metadata under DatabaseName -> Security -> Column Master Key Definitions -> Column Encryption Keys.

At this point, this feature is not supported by all client libraries. The only provider that currently works with Always Encrypted data is the ADO.NET 4.6, so we will need to ensure that the .NET Framework 4.6 is installed on any machine that will run the client application that interfaces with Always Encrypted data.
– It is important for many developers to be able to view We can use SQL Server Management Studio (SSMS), so when connecting to SSSMS, we can go to Options which will bring additional tabs, then we need to use additional Connection Parameter where we need to specify “column encryption setting=enabled” and once we make the connection successfully to SSMS, we now will be able to view the data.
– But when we try to read the Always Encrypted data, we may receive erros, where encrypted data could not be decrypted, because on the system we are launching the SSMS may not have the certificate which can decrypt the data.
We need to get the certificate installed on the local system with both public and private keys, after which we will be able to successfully view the encrypted data.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Failed to Decrypt A Column Encryption Key Error in SQL Server 2016

SQLServerF1

There have been many new features introduced with SQL Server 2016 and you may get different errors while you use these new features. One such issue and errors DBAs might get while they try to use new SQL Server 2016 feature Always Encrypted data is as below.
Msg 0, Level 11, State 0, Line 0
Failed to decrypt column ‘test’
Msg 0, Level 11, State 0, Line 0
Failed to decrypt a column encryption key using key store provider: ‘MSSQL_CERTIFICATE_STORE’. The last 10 bytes of the encrypted column encryption key are: ‘F1-S3-K2-8E-69-E7-F7-O8-1W-B4’.
Msg 0, Level 11, State 0, Line 0
Certificate with thumbprint ‘664EBAB58B3A8150AC51E2BEF40BDEC17055167C0′ not found in certificate store ‘My’ in certificate location ‘CurrentUser’.
Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.
Parameter name: masterKeyPath

This error occurs if we do not have certificate imported to the local machine’s certificate store. Always Encryped has two main keys, one is column master key, which is stored in the trusted key store and its information about column master keys, including their location, is stored in the database in system catalog views. Another important part is Column encryption keys, which actually encrypts the sensitive data. For clients to be able to access the data, there needs to be a certificate which needs to be installed on the client systems, with out which we will get the errors related to the certificate. This is to allow only authorized users with the certificate to be able to access and decrypt the sensitive data.

Apart from this error for not importing the certificate, there can be other errors too returned with Always Encrypted even when the certificate in installed or imported on the client system like below,
Failed to decrypt a column encryption key using key store provider: ‘MSSQL_CERTIFICATE_STORE’.
The last 10 bytes of the encrypted column encryption key are: ‘F1-S3-K2-8E-69-E7-F7-O8-1W-B4’. Certificate specified in key path ‘CurrentUser/My/664EBAB58B3A8150AC51E2BEF40BDEC17055167C0’ does not have a private key to decrypt a column encryption key. Verify the certificate is imported correctly.
Parameter name: masterKeyPath

The above error occurs even when we import the certificate because the imported certificate only has a public key, which is not enough to decrypt the data. We will also need a private key to be able to successfully decrypt the data.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Always Encrypted Feature in SQL Server 2016

SQLServerF1

It is important for every organization to protect their important customers or company’s data and ensure that no one should be able to view or update the data. There are many security mechanisms in SQL Server which will ensure only authorized users can see the data or make changes to it, like logins and database users to restrict the permissions on what they can do with the data. However, still there are many ways, where in unauthorized users still try to gain access to the data, like tapping the network between SQL Server and client system, trying to hack someone else account and gaining access, etc. So, only having security at login/database user level is not enough, so there are other mechanisms available like SSL certificates to encrypt the data that gets transmitted over the network, encryption of data at individual cell/column level or table level or at database level. Prior to SQL Server 2016, database/cell level encryption, still lets the database administrators or other users with high permissions to still gain access to the encrypted data, as the encryption keys are mostly stored in the database and are managed by the DBAs. This feature will be helpful for Microsoft to promote the SQL Azure databases more to the customers showcasing that their data will only be accessible to the clients, but no one else.

Starting with SQL Server 2016, new feature Always Encrypted has been introduced to safeguard the sensitive data from high privilege users as well as unauthorized users. Always Encrypted feature has been designed to protect sensitive data, such as credit card numbers or national identification numbers which are mostly stored in SQL Azure Database or on-premise SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine like SQL Database or SQL Server. Due to this, Always Encrypted provides a separation between those who own the data (who can view it) and those who manage the data (DBAs, but should have no access to sensitive data). This ensure that on-premises database administrators, cloud database operators, or other high-privileged, or unauthorized users cannot gain access to the encrypted data. This will help in for comfortable delegation of on-premises database administration tasks to third parties or to reduce security clearance requirements for the DBA staff.

Always Encrypted features mainly uses two types of keys which are, column encryption keys and column master keys.
Column master keys – are to protect the keys used to encrypt column encryption keys. It is important for the Column master keys should be stored in a trusted key store. The information about column master keys, including their location, is stored in the database in system catalog views.
Column encryption keys – are used to encrypt sensitive data stored which is stored in the database columns. All the values which are in a column can be encrypted using a single column encryption key. The encrypted values of column encryption keys are stored in the database in system catalog views. We need to store column encryption keys in a secure/trusted location for backup.

Another important component of the Always Encrypted feature which plays a key role is Always Encrypted enabled driver which ensures the transparency of encryption to client applications. This Always Encrypted enabled driver calls a server i.e., makes a roundtrip for each query with parameters to retrieve information on how to encrypt query parameter and whether they should be encrypted. This driver then calls a key store provider to decrypt the encrypted column encryption key value. The resultant plaintext column encryption key values are cached. Query results containing data from encrypted columns are accompanied by encryption metadata to enable transparent decryption. So, when an Always Encrypted enabled client driver queries encrypted columns, SQL Server sends the information about encryption settings for the queried columns, including encryption type information. The encrypted value of column encryption keys are used to protect the data in the queried columns, and also the location of the corresponding column master keys. The driver uses that information to, contact the key store containing each column master key and decrypt the column encryption keys, encrypted with the given master key. If for some reason, Always Encrypted is not enabled on the client side, the driver returns encrypted values and the values have the varbinary(max) data type.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Enhancements to T-SQL in SQL Server 2016

SQLServerF1

Transact-SQL is the key to interact with SQL Server, it acts as medium of communication between SQL Server and DBAs or Developers. Although SQL Server provides great GUI features to administer SQL Server, but underlying, the actions performed on GUI are translated into T-SQL queries or commands. Over a period, with many releases of SQL Server, there have been lot of changes, new additions supporting new features, enhancements to TSQL to support existing features and new commands introduced to support the requirements of Developers and DBAs. So, just like any other SQL Server release, even with SQL Server 2016, there have been some great enhancements which are worth talking or knowing about which include TRUNCATEE TABLEE with partitions, NO_PERFORMANCE_SPOOL, DROP IF EXISTS, T-SQL queries for new features like JSON and temporal tables, enhancements to FORMATMESSAGE, etc.

Truncatee Tablee – Everyone must be aware of the functioning of this command, which will remove data from entire table, although what happens internally is a different story, but from user perspective, all the data in the data will not be found anymore. Now, starting with SQL Server 2016, there has been improvement made to this command, where in now we can truncate only one partition, instead of entire table. This will be beneficial in making the maintenance easier with large partitioned tables and this operation will be faster too compared to deleting all the data from a partition.
– Another enhancement is that now ALTER TABLEE can now alter many columns, while the table remains online for which we can use WITH ONLINE = ON|OFF options.
– There has been new query hint NO_PERFORMANCE_SPOOL added, which will prevent spool operators from being added to the query plans. This is to help improve the performance when many concurrent queries are running the spool operations.

– Starting with SQL Server 2016, MAXDOP option has been added to use with DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP, which will limit the degree of parallelism as per the requirement of the DBA in specific environments to reduce the load of integrity checks and to can avoid using resource governor for this purpose.
– There has been improvement in the FORMATMESSAGE where we can now supply our own message string. In prior versions of SQL Server, FORMATMESSAGE used to construct a message from an existing message in sys.messages, but now with this new enhancement, we can supply our own messages too, which are not part of sys.messages.
– There have been new T-SQL commands added for supporting new features like JSON, Temporal tables, session context, etc.
– Another new and useful enhancement is introduction of DROP IF EXISTS, which simplifies the checking of table using longer queries through an IF condition.
– The maximum index key size for NONCLUSTERED indexes has been increased to 1700 bytes.
– Another enhancement include, support for Advanced Analytics Extensions, which allow us to execute scripts written in a different supported language such as R. Transact-SQL starting with SQL Server 2016 now supports R by using the sp_execute_external_script stored procedure, and with the external scripts enabled Server Configuration Option.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New SESSION_CONTEXT() Support in SQL Server 2016

SQLServerF1

There are many application which have requirement to store and retrieve session context information from SQL Server. Session context information is very useful in cases which can be used to store information specific to each user or their current state of the application. This is required in some applications to control the logic in Transact-SQL statements. Prior to SQL Server 2016, application developers used to rely mostly on CONTEXT_INFO function or sometimes on TSQL session variables, but both these have their own disadvantages like, Transact-SQL variables, scope is limited to that current Transact-SQL batch, stored procedure, trigger, or user-defined function. SQL/Application developers were using the CONTEXT_INFO function for retrieving the session context for the current session. Also, CONTEXT_INFO allows to retrieve session context values for all current sessions and batches from the context_info columns in the sys.dm_exec_requests or sys.dm_exec_sessions dynamic management views. Inorder to run queries against these views, required SELECT and VIEW SERVER STATEE permissions on the SQL Server instance, but these permissions were not required anymore when we use the CONTEXT_INFO function.

But this CONTEXT_INFO function has its own limitations too like, It is a single and a binary value, therefore is very difficult to work with because we will need to convert the data from binary to human readable format and also the involves the complexity of storing multiple values in a single binary representation, which will be difficult to use, another limitation include the size supported was only 128 bytes for the connection which was not liked my many developers, this CONTEXT_INFO also has another security concern too as it allows the users to overwrite the data at any time, and also this does not work well with SQL Azure databases. So, upon the feedback from the developer community, Microsoft has introduced new built-in function called SESSION_CONTEXT() starting with Microsoft SQL Server 2016. This is not a new innovation by Microsoft, but there are equivalents in other RDBMS like Oracle.

The way SESSION_CONTEXT function is different from CONTEXT_INFO is that the SESSION_CONTEXT uses key-value pairs to store the session data and the keys are of SYSNAMEE type where as the values are SQL_VARIANT type. Now, the size of the data that can be store has been increased/set to 256 KB, which is significantly greater than the size of data allowed with CONTEXT_INFO. Also, the security concerns has been addressed, wherein we can set the key as read only, so the value will not be able to be changed once it has been established. Also, as this has been introduced after SQL Azure was launched, Microsoft has kept the SQL Azure databases in mind and made this work similarly on both on-premise and on SQL Azure databases. We need to set the key-value pairs using the stored procedure called as sys.sp_set_session_context.
@key – is the key which is being set, and is of type sysnamee. The maximum key size allowed 128 bytes.
@value – is the value for the specified key, and it is of type sql_variantt. Setting this value of NULL will free the memory. The maximum size is 8,000 bytes.
@read_only – This allows zero or one. This is a flag of type bit. If 1 is set, then the value for the specified key cannot be changed again on this logical connection. If 0 (default), then the value can be changed.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

JSON (JavaScript Object Notation) Support with SQL Server 2016

SQLServerF1

There have been many new features introduced with SQL Server 2016 and many enhancements and additional support to new features, etc. One such interesting change with Microsoft SQL Server 2016 is native support for JSON. JSON stands for JavaScript Object Notation. If you are wondering what is this JSON (JavaScript Object Notation), is is an open and text-based data exchange format, that provides a standardized data exchange format better suited for Ajax-style web applications. In other words, JSON is a data interchange format that has become popular for moving data between systems. There was no support for JSON feature prior to SQL Server 2016, so the application developers had to fall on to use third party tools or workarounds to achieve the output in this format.

One of the popular language which many of us are aware for exchanging data between different heterogeneous systems is XML. Since many versions of SQL Server has been supporting XML by allowing queries which deal with XML data to store, retrieve or manipulate the XML data using SQL Server TSQL queries, specifically designed to handle XML data. JSON is another such data format like XML, which is also used to exchange data between different heterogeneous systems. Initially JSON was developed specifically for javascript, but later it has become as an independent language. As Microsoft wants to spread the usage of SQL Server across different platforms, there has been many changes like support to running SQL Server on linux and support for languages like JSON. Although, SQL Server 2016 supports JSON language, but that does not mean, XML is no more supported. Microsoft SQL Server newer versions will continue to support both XML and JSON languages and in future, we may see even more languages added to this list, this will help SQL Server scale new heights and can be used with any applications.

Format of JSON looks like below

JSON
[ 
   { "name": "Test", "skills":["SQL","C#","Azure"] },
   { "name": "Mike", "surname": "Taite" }
]

SQL Server offers many built-in functions and operators that lets us do the things like,
– Parse JSON text and read or modify the values.
– Convert arrays of JSON objects into table format.
– Use any TSQL queries on the converted JSON objects.
– Format the results of TSQL queries in JSON format.

Some of the key capabilities that SQL Server provides related JSON include,
– Use the JSON_VALUE function to extract a scalar value from a JSON string
– Use JSON_QUERY to extract an object or an array
– Use the ISJSON function to test whether a string contains valid JSON.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

DATABASE SCOPED CONFIGURATION in SQL Server 2016

SQLServerF1

Microsoft SQL Server 2016 has many new features and enhancements to existing features. Also, Microsoft claims break through improvement in the performance of queries in SQL Server 2016, compared to its previous versions. Database Scoped Configurations is one of the interesting enhancement made in SQL Server 2016. In previous versions of SQL Server, we can open database properties and can view various details about the databases and also make changes to some of the database properties to alter the behavior of the database. Starting with Microsoft SQL Server 2016, in database properties window, options tab, new section has been added with the name “Database Scoped Configurations”, which includes different options which can be configured.

Legacy Cardinality Estimation – This options allows us to set the query optimizer cardinality estimation at an individual database level, which is different from the database compatibility level. In SQL Server 2016 there are a lot of enhancements that come when we set Compatibility Level 130 for a database in SQL Server 2016, such as performance improvements with Columnstore and In-Memory OLTP and all the Query Optimizer enhancements done over the years under Trace Flag 4199. But there can be situations where you want to benefit from the new compatibility, but still want the application to work well and run under old compatibility mode, then we can enable this option. Once we set the Compatibility Level of database to 130, then we will get all the performance benefits for query execution that come with SQL Server 2016, while still using old CE.
Legacy Cardinality Estimation For Secondary – This option can be set to PRIMARY or SECONDARY. This option can be set only the secondaries and indicates that the same configuration will be used which are set on the primary. If the configuration for the primary changes, secondary will also adjust to the same value. PRIMARY is the default setting for the secondaries. While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases will use the same configuration. Different settings cannot be configured for individual secondaries.

MAXDOP – In previous versions of SQL Server, we can set MAXDOP at an instance level or at batch/stored procedure/query level, but not at the database level. But starting SQL Server 2016, we can set Maximum degree of parallelism(MAXDOP) for an individual database. SP_Configure MAXDOP setting will be overwritten by this database setting for queries ran against this database.
MAXDOP For Secondary – This option can be set to PRIMARY or SECONDARY. This option can be set only the secondaries and indicates that the same configuration will be used which are set on the primary. If the configuration for the primary changes, secondary will also adjust to the same value. PRIMARY is the default setting for the secondaries. While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases will use the same configuration. Different settings cannot be configured for individual secondaries.

Parameter Sniffing – Using this option, we can enable or disable parameter sniffing at database level. In previous versions of SQL Server, we could turn on or off using the trace flag, but now it is made simple with just changing the option from database properties.
Parameter Sniffing For Secondary – This option can be set to PRIMARY or SECONDARY. This option can be set only the secondaries and indicates that the same configuration will be used which are set on the primary. If the configuration for the primary changes, secondary will also adjust to the same value. PRIMARY is the default setting for the secondaries. While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases will use the same configuration. Different settings cannot be configured for individual secondaries.

Query Optimizer Fixes – In previous versions of SQL Server, there are many query optimizer hotfixes enabled by some trace flags. In SQL Server 2016, we can choose to either enable or disable query optimization hotfixes for an individual database.
Query Optimizer Fixes For Secondary – This option can be set to PRIMARY or SECONDARY. This option can be set only the secondaries and indicates that the same configuration will be used which are set on the primary. If the configuration for the primary changes, secondary will also adjust to the same value. PRIMARY is the default setting for the secondaries. While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases will use the same configuration. Different settings cannot be configured for individual secondaries.
– Starting SQL Server 2016, we now have the ability to clear individual Database Plan Cache using command ALTER

DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 
1 2