Tag Archives: Microsoft SQL Server

Standard

Posted by

Posted on

July 26, 2024

Posted under

SQL Server 2022

Comments

Leave a comment

SQL Server 2022: A Comprehensive Overview

SQL Server 2022 is Microsoft’s latest release in its line of database management systems, and it comes packed with exciting new features and improvements. Whether you’re a database administrator, developer, or data analyst, SQL Server 2022 has something to offer to enhance your workflow and data management capabilities. Let’s dive into what’s new and improved! 🚀

1. Azure Integration and Hybrid Capabilities ☁️

One of the standout features of SQL Server 2022 is its deep integration with Azure, providing a seamless hybrid environment. This includes:

  • Azure SQL Managed Instance Link: Easily link your SQL Server instance to Azure SQL Managed Instance for disaster recovery and cloud bursting scenarios.
  • Azure Synapse Link: Instantly replicate your SQL Server data to Azure Synapse Analytics, enabling real-time analytics without impacting operational workloads.
  • Managed Disaster Recovery: Automatic management of failover to Azure in the event of an outage, ensuring business continuity.

2. Performance Enhancements 🏎️

SQL Server 2022 introduces several performance improvements that make it faster and more efficient:

  • Intelligent Query Processing (IQP) Enhancements: Building on previous versions, IQP now includes new features like Parameter Sensitive Plan Optimization (PSPO) to handle queries with varying parameter values more effectively.
  • Accelerated Database Recovery (ADR) Improvements: ADR now supports more complex scenarios, reducing recovery time in case of failure.
  • TempDB Optimization: Significant improvements in TempDB management help in reducing contention and improve overall performance.

3. Security and Compliance 🔒

Security remains a top priority in SQL Server 2022, with new features to protect your data:

  • Ledger Tables: A new feature that provides cryptographic attestations for sensitive data, ensuring data integrity and compliance.
  • Always Encrypted with Secure Enclaves: Enhanced to support more complex operations, making it easier to protect sensitive data.
  • Azure Active Directory Integration: Streamlined integration with Azure AD for more secure and manageable identity and access management.

4. Developer and DBA Productivity Tools 🛠️

SQL Server 2022 includes several enhancements aimed at boosting productivity for developers and DBAs:

  • Query Store Improvements: The Query Store now supports read-only replicas, giving DBAs better insights into query performance across their environment.
  • Enhanced Error Messages: More descriptive error messages help developers quickly identify and fix issues.
  • New T-SQL Enhancements: New T-SQL features like JSON enhancements and new functions make it easier to work with complex data types.

5. Big Data and Analytics 📊

SQL Server 2022 continues to support big data and analytics workloads with new features and integrations:

  • PolyBase Enhancements: Now supports more data sources and offers improved performance, making it easier to integrate with various big data ecosystems.
  • Azure Synapse Link for SQL: Enables real-time analytics by synchronizing data between SQL Server and Azure Synapse Analytics.

6. Operational Enhancements ⚙️

Operational improvements in SQL Server 2022 make management and maintenance more efficient:

  • Always On Availability Groups Enhancements: New features like availability group lease mechanism and better integration with Azure for hybrid scenarios.
  • Improvements in TempDB and Storage: More efficient use of TempDB resources and better storage performance.

7. Integration with Other Microsoft Services 🤝

SQL Server 2022 integrates seamlessly with other Microsoft services, enhancing its capabilities:

  • Power BI Integration: Improved integration with Power BI for real-time analytics and reporting.
  • Microsoft Defender for SQL: Enhanced security monitoring and threat detection capabilities.

Conclusion 🎉

SQL Server 2022 is a robust and feature-rich release that caters to the needs of modern data-driven organizations. Its integration with Azure, improved performance, enhanced security, and new features make it an excellent choice for both on-premises and cloud-based deployments.

Whether you’re looking to enhance your analytics capabilities, secure your data, or improve your database’s performance, SQL Server 2022 has the tools and features to help you succeed. Upgrade today and unlock the full potential of your data!

For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

February 6, 2024

Posted under

AlwaysON

Comments

Leave a comment

SQL Server Always On Series: Availability group DDL operations are permitted only when you are using the master database


Introduction
During a recent attempt to perform a manual failover from Always On Availability Group from JBSAG1 to JBSAG2 using Microsoft SQL Server Management Studio (SSMS), an unexpected error disrupted the process. The error, specifically identified as Microsoft SQL Server Error 35208, posed a challenge to the manual failover operation. In this detailed account, we’ll explore the encountered issue, outline the steps taken to address it, and ultimately achieve a successful manual failover.

Issue
Upon initiating the Always On Availability Group manual failover, the process encountered a hurdle with the following error:

TITLE: Microsoft SQL Server Management Studio
Manual Failover failed (Microsoft.SqlServer.Management.HadrTasks)

ADDITIONAL INFORMATION:
Failed to perform a manual failover of the availability group ‘JBSWiki’ to server instance ‘JBSAG2’. (Microsoft.SqlServer.Management.HadrModel)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47021.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Availability group DDL operations are permitted only when you are using the master database. Run the ‘USE master’ statement, and retry your availability group DDL statement. (Microsoft SQL Server, Error: 35208)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-35208-database-engine-error

Navigating through this error was the initial challenge, particularly when attempting the failover using the SSMS graphical user interface (GUI). Executing the ‘USE master’ statement in this context presented uncertainties, prompting a reevaluation of the failover approach.

Solution
To address the dilemma, the following steps were taken:

GUI Failover Attempt:

  • Initially, the failover was attempted through the SSMS GUI, raising questions about how to execute the ‘USE master’ statement within the graphical interface.

Scripted Failover Action:

  • The failover wizard was restarted, and instead of concluding the process through the GUI, the failover action was scripted for manual execution. I started the failover wizard again and this time instead of clicking finish at the end, tried scripting the failover action.

Manual Execution of Failover Command:

  • The failover command was manually executed from the SSMS SQLCMD query window, successfully completing the failover process.

Database Context Discovery:

  • Investigation revealed that the database context for a new query window was set to a user database (JBDB) instead of ‘master,’ leading to a pivotal realization. I tried clicking on a new query window and I saw that the database context for that query window was set to an user database JBDB and not master.

Connection Options Adjustment:

  • This is when I realized that I might have connected to the SQL Server instance with an User Database specified on the “Connect to database” in “Options <<” as part of making a connection from SSMS. Please check screenshot below,

After realizing this, I disconnected the existing sessions on SSMS and changed “Connect to database” in “Options <<” to “Master” and connected to SQL server JBSAG1 and JBSAG2.

Success After Correction:

  • Following these corrective actions, subsequent manual failover and failback attempts via SSMS were executed seamlessly.

Summary
In summary, this journey through troubleshooting manual failover with error 35208 underscored the importance of the database context, especially when initiating DDL operations. The solution involved a meticulous adjustment of connection options within SSMS, ensuring a connection to the ‘master’ database before attempting manual failover. This article provides a detailed account of the encountered challenge, the thought process behind the solution, and the successful resolution achieved through careful steps and insights.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.