New Features in SQL Server 2022 for DBAs

SQL Server 2022 brings a wealth of new features that empower DBAs to enhance database performance, security, and usability. By leveraging these capabilities, organizations can optimise their data management strategies, ensuring better compliance, improved efficiency, and advanced analytics capabilities. The introduction of intelligent query processing, enhanced security features, and support for serverless architectures positions SQL Server 2022 as a robust choice for modern data solutions.

Here’s a summary of the key features and their benefits:

1. Intelligent Query Processing Enhancements

Overview: SQL Server 2022 expands on the Intelligent Query Processing (IQP) capabilities introduced in SQL Server 2019. New features include:

• Parameter Sensitive Plan Optimization: This feature allows SQL Server to create multiple execution plans for a single query based on the specific parameter values used during execution, leading to better performance.

Case Scenario: In a scenario where a stored procedure is called with highly variable input parameters (e.g., a sales report for different regions), the parameter-sensitive optimisation ensures that SQL Server selects the best execution plan tailored to the input, reducing execution time and resource consumption.

2. SQL Server Ledger

Overview: The SQL Server Ledger feature provides blockchain-like capabilities, ensuring data integrity and immutability through ledger tables. This feature uses cryptographic methods to verify data integrity, making it ideal for applications requiring audit trails.

Case Scenario: For financial institutions that need to maintain accurate records of transactions, SQL Server Ledger enables the creation of a ledger table where each transaction is logged. Any modifications are tracked, ensuring an immutable record that can be audited for compliance purposes.

3. Enhanced Security Features

Overview: SQL Server 2022 includes several security enhancements:

• Always Encrypted with Secure Enclaves: This allows more operations to be performed on encrypted data without exposing it in plaintext, enhancing security for sensitive information.
• Dynamic Data Masking Enhancements: Provides more flexible masking options, allowing DBAs to control how sensitive data is displayed to different users.

Case Scenario: In a healthcare application, patient data can be stored securely using Always Encrypted. The application can perform queries and calculations on encrypted data without revealing sensitive information to unauthorised users.

4. Query Store Enhancements

Overview: The Query Store feature has been enhanced to provide deeper insights into query performance over time. It now includes capabilities for identifying and managing query performance regressions.

Case Scenario: DBAs can utilise the enhanced Query Store to track performance changes after application deployments. If a new release introduces slow-running queries, the Query Store can help identify regressions, allowing quick remediation by reverting to previously optimised execution plans.

5. Built-in Machine Learning Services

Overview: SQL Server 2022 integrates built-in support for machine learning services, allowing DBAs to run R and Python scripts directly in the database engine. This feature enables data scientists and analysts to perform advanced analytics without moving data outside the SQL Server environment.

Case Scenario: A retail company can analyse customer purchasing patterns using machine learning algorithms stored in SQL Server. DBAs can schedule these analyses to run during off-peak hours, optimizing resource usage while delivering timely insights.

6. Serverless SQL Database

Overview: SQL Server 2022 introduces serverless capabilities for databases, allowing automatic scaling and cost-effective resource usage based on demand. This feature is ideal for workloads that experience variable usage patterns.

Case Scenario: A startup that experiences fluctuating traffic on its web application can benefit from serverless SQL databases, which automatically scale up during peak traffic and scale down during low usage periods, ensuring cost-efficiency.

7. Enhanced Availability Groups

Overview: New features for Always On Availability Groups improve failover performance and management. This includes automatic failover for databases in availability groups without the need for manual intervention.

Case Scenario: In a mission-critical application, the DBA can configure availability groups with automatic failover capabilities, ensuring minimal downtime during server maintenance or unexpected failures, thus enhancing application reliability.

8. Improved Performance Insights and Monitoring Tools

Overview: SQL Server 2022 provides enhanced monitoring tools with better insights into system performance. Features like the Database Health Monitor offer real-time health checks and performance tuning recommendations.

Case Scenario: DBAs can utilize the Database Health Monitor to receive alerts and recommendations for performance tuning based on workload analysis, enabling proactive management and optimization of database performance.

9. Support for JSON and XML Enhancements

Overview: SQL Server 2022 introduces improvements to support for JSON and XML data types, including better indexing and querying capabilities.

Case Scenario: A content management system that relies on JSON documents can leverage the new indexing features to improve query performance on JSON fields, allowing faster data retrieval and reporting.

That's all for now.

Knowing and Protecting Your Data

Computer circuitry has crept into nearly everything we use nowadays, and almost all of which gather information from us and about us. As a result, the present is immersed in copious amounts of data, which is stored somewhere and, most of the time, not securely protected. 

Today we are almost entirely reliant on IT departments at work; practically all organisations depend on enterprise-wide applications to support numerous key business processes which create a mountain of information. Needless to say, IT infrastructure and business applications are increasingly — and usually, unnecessarily — more complex; complexity is the worst enemy of security — and sometimes, of performance too. As a consequence, we are losing more control of storage and, therefore, security. The more data we share, the bigger the security risk is. 

Companies always strive to successfully harness the power of data, but unfortunately, this is not the case when it comes to securely accessing data. There are laws about protecting information, but most people are either blissfully unaware of them or careless about them. Many of us know what could happen when our data end up in the wrong hands.

Cryptology vs. Cryptography vs. Cryptanalysis. What's the Difference?

Although the words cryptology, cryptography and cryptanalysis are used interchangeably — strictly speaking — they mean different things. Nowadays, we only use the word cryptography for everything; it's indeed a catch-all for a broad range of intertwined topics. Today’s post not only aims to point out the differences among them but also to show their connections to each other.

To begin with, cryptology is the mathematics, algorithms, and the applications of formulas that underpins cryptography and cryptanalysis. The world of cryptology goes from basic foundations in cryptography (code-making) to modern algebraic cryptanalysis (code-breaking). So, cryptology is clearly divided into two major parts: cryptography and cryptanalysis; with strong connections to each other, which include cryptographic applications, types of cryptography and their algorithms, code-breaking techniques, information theory, number theory and mathematical applications to encrypt data and also break ciphers. 

Curious About Cryptographic Boolean Functions?

In order to have a good understanding of cryptographic Boolean functions, let's get started from scratch, that's, having a look at the very basic concepts. To begin with, all modern computers are composed of very basic logic circuits using very basic gates, called operators which only apply to binary numbers, in other words, 0 and 1. Each type of gate implements a Boolean operation. The finite field ${\mathbb F}_{2}=\{0,1\}$ is also called binary field, and it is of special interest because it is particularly efficient for implementation in hardware or on a binary computer. Using these gates, the rules of Boolean algebra may be applied to design circuits that perform a variety of tasks. For example, integrated circuits. Then, these circuits are all put together to build into more powerful modern computers. 

Exploring What a PhD Is Like

Some weeks ago, it was my pleasure, and honour, to be invited to participate as a panellist in the online event Explore What a PhD is Like organised by PhD Social Support Network from Loughborough University. The event aimed to give undergraduate and postgraduate students a summary of Doctoral Researchers’ lives and an idea of what it’s like to do a PhD at Loughborough University.

With so much confusing and contradictory information out there, it becomes a bit difficult to have a good understanding of many things before embarking on doing a PhD. So I decided to write down my answers for those of you who could not attend the event and are interested in doing a PhD. Because we were four panellists, I just answered some questions, but in this post, I share my answers to all questions, which mainly depend on my personal circumstances and experiences at Loughborough University — And I do hope to help clear some things up. In case that you have any other questions, please let me know in the comment section.

Don't Place the Blame on SQL Server

I have never worked for Microsoft, but SQL Server has given me a lot in terms of learning, community and opportunities, all these together have helped me do a great job as a Database Administrator (DBA) for many years. Since I started working with SQL Server nearly 15 years ago, I have heard a lot of complaints about SQL Server being nowhere near as good as Oracle. Much as I would have liked to ignore these fruitless discussions, I couldn't see the point of comparing products in such a compulsive way. Is it not true that our skills are more crucial than the technology itself? — Or perhaps some people just try to find something to blame. Whatever the case, I am convinced that we, as database professionals, are compelled to make the most out of any specific database technology. 

No matter what technology we are working with, we are at the wheel — technology is just a tool — so it is not the best to blame technology on the ground of one's inefficiency.

Cryptography is Everywhere

Historically, cryptography was used for secret communication by exclusive sectors only — such as governments, military and spies — since it was crucial and affordable to them. They have long been aware of the consequences of their messages falling into the wrong hands; therefore, this situation has motivated the development of techniques for disguising a message so that only the intended recipient can read it. 

The huge desire for secrecy led nations, kings, and queens to make all-out efforts to ensure the security of communications by inventing the best possible secret codes and ciphers. 

A lover in Victorian times

As the public also became aware of the need to protect personal messages of a highly sensitive nature, they also became comfortable with encipherment. They began to express their cryptographic skills in a variety of ways — for example, young lovers in Victorian England were often forbidden from publicly expressing their affection, and could not even communicate by letter in case their parents intercepted and read the contents. This resulted in lovers sending encrypted messages to each other via the personal columns of newspapers, more specifically, via the classified ads. 

Do You Want to Be a Cryptographer?

Alan Turing

I've always been interested in information security since 2003, but it wasn't until I enrolled on the cryptography module — while studying for a Master in Advanced Computer Science in England in 2018— that I started getting more keen on the mathematical side of cryptography; perhaps it was in part because I cherish mathematics and had the right cryptography teacher. What's more, I admire Alan Turing since he strove to do good work in difficult conditions during World War II; his work saved millions of lives. All of these things together inspired me to immerse myself in the world of cryptography.