In the fast-paced world of data management and analytics, staying ahead of the curve is not just preferred; it's essential. SQL Server 2017 brings a host of new features and enhancements, expanding the capabilities of one of the industry's leading database platforms. For SQL developers, database administrators, and technology enthusiasts, understanding and leveraging these updates can mean the difference between a good system and a great one, between a secure database and a compromised one. This post takes a comprehensive look at the standout features of SQL Server 2017 and examines how they can be a game-changer for your organization.
Whats New In SQL Server 2017 on Linux
Perhaps the biggest headline of the SQL Server 2017 release was its newfound compatibility with Linux operating systems. For a platform that had remained tethered to Windows from the beginning, this was a seismic shift. But it wasn't just a marketing ploy; the move to Linux is a response to the growing demand for cross-platform solutions and provides users with more flexibility than ever before.
Cross-Platform Benefits
Running SQL Server on Linux isn't just about diversity; it's about delivering the best possible performance for your specific infrastructure. By removing the dependency on Windows, organizations have more opportunities to optimize their server setups. For developers, it means writing code that can be deployed across different environments without significant modifications.
Considerations and Deployments
While the migration to Linux is relatively straightforward, there are always new wrinkles to consider. Deploying SQL Server on Linux may require adjustments in terms of administration, system resource management, and even the tools used to monitor performance. However, with the right knowledge and preparation, the transition to a new OS can be smooth and ultimately beneficial.
Adaptive Query Processing
In the never-ending challenge to improve query performance, SQL Server 2017 introduces Adaptive Query Processing (AQP). This suite of features focuses on providing more efficient ways to process queries, adapt plans during execution, and improve system performance.
AQP in Action
A hallmark of AQP is its ability to learn from past executions to make informed adjustments in the future. Batch mode memory grant feedback, for example, can reduce resource contention for complex queries. Interleaved execution is another key component, reordering data joins on the fly for better parallelism and performance.
Impact on Performance and Efficiency
The implications of AQP are significant. It's a step toward a future where database systems are more self-regulating and dynamic, fine-tuning their operations with minimal input. For developers and administrators, this means enjoying a system that can adapt to real-world workloads with precision and agility.
Automatic Tuning
Database tuning has traditionally been a manual, time-consuming endeavor, but with SQL Server 2017's Automatic Tuning capabilities, the system can now take a more active role in managing performance.
The Hands-Off Approach
Automatic plan correction can identify inefficient query plans and implement better ones automatically. Similarly, automatic index management can detect and address the need for new indexes or the removal of redundant ones, all without the user's intervention.
Maintaining Optimal Performance
These features aren't just about convenience; they're about maintaining a high-performing database environment consistently. By automating these typically human-driven processes, SQL Server 2017 can deliver better performance with lower overhead, freeing up time and resources for other critical tasks.
Resumable Online Index Rebuilds
The ability to pause and resume index rebuilds might sound simple, but it's a powerful tool for minimizing downtime and managing resources more effectively in the SQL Server environment.
Flexible Housekeeping
Index maintenance is a critical component of database health, but it can be disruptive. Resumable Online Index Rebuilds allow administrators to schedule these operations more flexibly and to respond to sudden workload changes without compromising system availability.
Downtime Prevention
In a world where 'always-on' is the gold standard, any tool that prevents downtime is invaluable. With Resumable Online Index Rebuilds, SQL Server reaches a new level of resilience and availability that directly translates to a better end-user experience.
Graph Database Support
For applications that need to model complex relationships, the relational model has its limitations. Graph databases offer a more natural fit for these use cases, and SQL Server 2017 now includes native support for graph tables and queries.
Complex Relationships, Simplified
Graph databases model relationships as first-class citizens, making it easier to represent and query network-like structures. This is particularly useful in areas like social networking, fraud detection, and network topology, where traditional queries can become unwieldy.
New Analytical Vistas
For data analysts, the introduction of graph database support opens up new avenues for exploration. By leveraging this functionality, analysts can uncover insights and patterns that may have been obscured by the constraints of a purely relational model.
Python Integration
The ability to execute Python scripts directly within SQL Server queries elevates the platform from a mere data repository to a powerful analytical tool.
Opening the Analysis Toolbox
With Python integration, SQL Server 2017 becomes a gateway to a robust ecosystem of data science packages and tools. From machine learning to natural language processing, the possibilities are as vast as the Python community itself.
A Unified Environment
Developers and analysts no longer need to switch contexts or tools to harness the power of Python. With SQL Server 2017, Python scripts can be integrated seamlessly into their existing Transact-SQL workflows, creating a more streamlined and efficient environment for advanced analytics.
Enhanced Security Features
In today's data-driven world, security is paramount, and SQL Server 2017 comes equipped with several new features to strengthen your database's defenses.
Always Encrypted with Secure Enclaves
This feature keeps your most sensitive data safe not only at rest but also in use, using a secure enclave to process encrypted data without exposing the keys, even to administrators.
Row-Level Security
With SQL Server 2017, you can now implement security policies that restrict access to specific data rows based on the user’s permissions, providing a more granular level of control over data access.
Compliance Tools
SQL Server 2017 includes tools and features that help maintain compliance with various regulatory standards, such as the General Data Protection Regulation (GDPR), making it easier to manage global data protection compliance requirements.
Conclusion
SQL Server 2017 is more than an upgrade; it's a testament to Microsoft's dedication to enhancing the database experience for developers and administrators alike. By familiarizing yourself with these new features and embracing them in your projects, you can ensure that your systems are not just keeping pace with industry standards but surpassing them.
In the dynamic world of data technologies, those who innovate can thrive. As you consider the move to or the upgrade of SQL Server 2017, remember that each new feature is an opportunity for you to innovate within your organization, to create more robust and efficient systems, and to better protect and utilize your most valuable asset—your data.
What's New In SQL 2017
Internal Links