Sql server 8 Node cluster Migration with minimal downtime by Distributed always on concept

Migrating an 8-node SQL Server cluster with minimal downtime using Distributed Always On is a complex task that requires careful planning and execution. Here’s a high-level overview of the steps involved:

1. Assessment and Planning:

• Assess the existing SQL Server cluster architecture, including hardware specifications, network configurations, and database dependencies.
• Evaluate the feasibility of using Distributed Always On for the migration based on the business requirements and technical constraints.
• Develop a migration plan outlining the sequence of steps, resource requirements, and expected downtime.

1. Preparation:

• Ensure that all nodes in the existing cluster are running compatible versions of SQL Server and have the necessary prerequisites installed.
• Set up a new SQL Server environment in the target location, including the installation of SQL Server instances and configuration of network settings.
• Configure Distributed Always On between the existing cluster and the new target environment, ensuring proper network connectivity and firewall rules.

1. Database Migration:

• Take full backups of all databases in the existing cluster to ensure data integrity and recoverability.
• Restore the backups onto the new target SQL Server instances in the Distributed Always On configuration.
• Set up database mirroring between the primary nodes in the existing cluster and the corresponding target nodes in the new environment.
• Synchronize the databases between the primary and secondary nodes using database mirroring, ensuring data consistency and minimizing downtime.

1. Failover and Validation:

• Perform failover tests to validate the failover capabilities of the Distributed Always On configuration.
• Monitor the migration process closely, addressing any issues or performance bottlenecks that may arise.
• Conduct post-migration validation tests to ensure that the databases are functioning correctly in the new environment and that data integrity is maintained.

1. Finalization:

• Decommission the old SQL Server cluster once the migration is complete and the databases are fully operational in the new environment.
• Update application connection strings and DNS records to point to the new SQL Server instances.
• Document the migration process, including any issues encountered and lessons learned, for future reference.

1. Post-Migration Support:

• Provide training and support to database administrators and end-users on managing and accessing databases in the new environment.
• Monitor the performance and stability of the Distributed Always On configuration, making adjustments as needed to optimize performance and ensure availability.

By following these steps and leveraging Distributed Always On, you can migrate an 8-node SQL Server cluster with minimal downtime and ensure a seamless transition to the new environment.

Log Rate mesument is required prior to Migrate the sql server Database azure MI instance

Azure SQL Database Managed Instance stands as a fully managed iteration of the SQL Server Database engine, hosted within the Azure cloud, offering inherent maintenance operations such as automated backups.

Ensuring comprehensive coverage of changes across all databases, Managed Instance commits to taking database backups every 5–10 minutes, specifically log backups. To accomplish this, it imposes log rate limits per instance, tailored to catch up with updates efficiently:

• General Purpose instance: Up to 22 MB/s
• Business Critical instance: Up to 48 MB/s

It is advisable to verify these values in the Managed Instance documentation as they may undergo changes over time.

These limits may impact the performance of certain log-intensive operations, including:

1. Bulk loads that introduce substantial data into the database.
2. Index rebuilds that recreate indexes at a rate exceeding the instance’s log limits.

If you suspect performance issues, it is crucial to assess whether these limits are being reached.

To identify log rate limits on Managed Instance, examination of Query Store and DMVs is one approach. Alternatively, using the Query Performance Insight helper library, specifically designed for Managed Instance, simplifies the analysis. Though not mandatory, adding the library’s procedures and views to your database facilitates the process.

To initiate analysis, follow these steps:

1. Execute the qpi.snapshot_wait_stats procedure to capture wait statistics, subsequently viewable through qpi.wait_stats.
2. Identify the presence of “Log Rate Governor/INSTANCE_LOG_RATE_GOVERNOR” at the top of wait statistics, indicating potential log rate limit issues.

Additionally, file statistics help verify that the total log rate across all files aligns with the instance limit. Establish a baseline snapshot with EXEC qpi.snapshot_file_stats and then calculate the sum of throughput values for all log files:

SELECT instance_log_throughput_MBps = SUM(write_mbps)
FROM qpi.file_stats
WHERE type = 'LOG';

If the obtained value approximates the specified limits, it suggests hitting the instance log limit cap. Note that variations may occur due to workload spikes, as the query returns average values within a given interval.

Unfortunately, there are no direct corrective actions to avert this issue, as the log rate limit serves as a necessary trade-off to consistently ensure timely backups. For those migrating from SQL Server to Managed Instance, repeating this assessment on the SQL Server instance can gauge compatibility and performance under similar circumstances, using the appropriate version of the Query Performance Insight library.

How to resolve SQL server replication Data missmatch

Identifying and fixing data mismatches in SQL Server replication can be challenging, but there are several steps we can follow.

1. Identify the Data Mismatch:

First, determine which tables or columns are affected and what type of mismatch is occurring (e.g., missing rows, different values).

2. Check the replication configuration:

Verify the replication agent settings, subscription properties, and distribution database settings to ensure that the replication configuration is correctly configured.

3. Check the replication status:

Use SQL Server Management Studio (SSMS) or replication monitoring tools to monitor replication status and look for any errors or warnings.

4. Compare Source and Destination Data:

Compare the data in the source and destination tables using tools such as Redgate’s SQL Data Compare or other data comparison tools to identify discrepancies.

5. Check for conflicts:

Check the replication conflict tables for conflict information and resolve it accordingly if the same row is updated on both the publisher and subscriber.

6. Verify schema changes:

Ensure that schema changes (e.g., table structure, constraints, etc.) are correctly replicated to the subscriber. Misconfiguration of the schema can lead to data mismatches.

7. Check Data Transformation:

You should review any data transformations that are used during replication (e.g., custom stored procedures, triggers) for potential issues that could lead to data mismatches.

8. Check Data Types and Collations:

Make sure that the data types and collations are consistent between the source and destination databases. Mismatched data types can result in conversion errors.

9. Validate Primary Keys and Indexes:

It is important to ensure that primary keys and indexes are replicated correctly. If primary key constraints are not replicated correctly, data mismatches may occur.

10. Restart the Replication Agents:

Restarting the replication agents may resolve transient issues causing data mismatches in some cases.

11. Resynchronize the subscriber:

You may consider reinitializing the subscriber from a fresh snapshot of the publisher’s data if the data mismatch is significant.

12. Analyze replication logs and error messages:

Inspect the SQL Server Error Logs and replication-specific logs for any error messages or warnings that may provide insight into the cause of the data mismatch.

Remember to take proper backups of your databases before making any significant changes to avoid data loss.

So far, from my experience, I have seen that the data validation errors are reported for a mismatch in row count for the table(s).  And, it is fairly easy to determine the error and fix when it comes to resolving it. Without wasting much time, let’s talk about various ways to deal with this very error.

The most common, and indeed planned for, problem with replication is that of LATENCY. Latency in this context simply refers to the delay between publisher and subscriber (via distributor) and taken in isolation with no other symptoms can be perfectly normal. Latency can be caused by various factors, including geographical distance between nodes; network traffic or bandwidth constraints; transactional load on the publisher; and system resources. Less common causes, but by no means rare, are problems such as stalled agents, failed jobs and unexpected locking / other resource conflicts.

Here’s one such query you can run which uses some of these tables. The following query will pull all information about distribution agents that are actively distributing

transactions (in progress) and will provide useful information such as the delivery rate (txs/sec). I’ve added WITH (NOLOCK) from habit when dealing with system tables but it’s not strictly necessary in this case since this is a history table. Feel free to amend and add/remove columns or filters:

SELECT      da.name, da.publisher_db, da.subscription_type,

dh.runstatus, dh.delivery_rate, dh.start_time, dh.duration

FROM        dbo.MSdistribution_history dh WITH (NOLOCK)

INNER JOIN  dbo.msdistribution_agents da WITH (NOLOCK)

ON          dh.agent_id = da.id

WHERE       dh.runstatus = 3 — 3 means ‘in progress’, table explanation here:

— http://msdn.microsoft.com/en-us/library/ms179878.aspx

AND         dh.start_time BETWEEN DATEADD(dd,-30,GETDATE()) AND GETDATE()

ORDER BY    dh.start_time DESC

Fixing Data Validation Errors in Replication:

• Skip missing Transaction
• Ignore Data Consistency Issue
• Drop and Republish articles which are out of sync

Basically, when one has data validation errors in replication it is about inconsistency or out of sync articles. First, one has to find out what exactly the error is. Based on the error reported, use the tablediff utility to compare the records and find out the mismatch records, this utility will also create tsql statement that can help fix the data validation. Row count can be manually validated as well in case the article is small. Once a difference is found, manually insert the record(s) and it will fix the issue.

When a validation or inconsistency reported due to the key violation of duplicate value, find out the offending records. And then, one can create another agent profile and use –skiperrors parameter to resolve this error. Also, one can also drop and republish the article that has reported an error and fix the issue.

I hope this tips will help you fix the data validation issue.

SQL server database capacity planning TSQL Script

To perform capacity planning for a SQL Server database using T-SQL, you can utilize system views and functions to gather information about your database and workload. Here’s an example of T-SQL queries you can use:

1. Determine Current Database Size:

SELECT 
    DB_NAME() AS DatabaseName,
    SUM(size * 8 / 1024) AS DatabaseSizeMB
FROM sys.master_files
WHERE type = 0 AND DB_NAME(database_id) = DB_NAME()
GROUP BY database_id;

2. Estimate Growth Rate:

SELECT 
    DB_NAME() AS DatabaseName,
    (SUM(size * 8 / 1024) - MAX(size * 8 / 1024)) / COUNT(*) AS GrowthRateMBPerDay
FROM sys.master_files
WHERE type = 0 AND DB_NAME(database_id) = DB_NAME()
GROUP BY database_id;

3. Calculate CPU Usage:

SELECT 
    DB_NAME() AS DatabaseName,
    MAX(total_worker_time) / DATEDIFF(second, GETDATE(), GETDATE() + 1) AS CPUUsagePercent
FROM sys.dm_exec_query_stats
WHERE DB_NAME(dbid) = DB_NAME()
GROUP BY dbid;

4. Estimate Memory Requirements:

SELECT 
    DB_NAME() AS DatabaseName,
    COUNT(*) * 8 AS BufferPoolSizeMB
FROM sys.dm_os_buffer_descriptors
WHERE DB_NAME(database_id) = DB_NAME();

5. Assess Storage Requirements:

SELECT 
    DB_NAME() AS DatabaseName,
    SUM(size * 8 / 1024) AS TotalDataSizeMB,
    SUM(CASE WHEN type = 0 THEN size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
    SUM(CASE WHEN type = 1 THEN size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = DB_NAME()
GROUP BY database_id;

6. Analyze Query Performance:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS AvgCPUTime,
    qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
    qs.total_logical_writes / qs.execution_count AS AvgLogicalWrites,
    t.text AS QueryText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE DB_NAME(t.dbid) = DB_NAME()
ORDER BY AvgCPUTime DESC;

By running these queries and analyzing the results, you can gain insights into the current database size, growth rate, CPU usage, memory requirements, storage needs, and query performance. This information can help you in estimating the capacity and making informed decisions for capacity planning in your SQL Server database.

SQL server database capacity planning based on transaction per second

SQL Server database capacity planning involves estimating the required resources based on transaction per second (TPS) and other workload characteristics. Here are some steps to help you with the capacity planning process:

1. Understand the Workload:

• Analyze your application’s transaction patterns and identify the peak TPS.
• Determine the average transaction size and complexity (in terms of query complexity and data manipulation operations).

1. Estimate TPS:

• Collect performance metrics or use load testing tools to measure the TPS under typical or expected workload conditions.
• Consider factors such as concurrent users, data volume, and anticipated growth.

1. Calculate Resource Requirements:

• CPU: Estimate the CPU requirements based on the number and complexity of transactions per second, as well as the processing required for each transaction. Consider the CPU utilization percentage you want to target (e.g., 70-80%).
• Memory: Estimate the memory requirements by considering the amount of memory needed to cache frequently accessed data and query plans. SQL Server’s buffer pool is a critical component for efficient data retrieval.
• Storage: Estimate the storage requirements based on the expected data growth rate, the size of each transaction, and the need for indexing, temporary storage, and log files.
• Network: Evaluate the network bandwidth required for data transfer between the database server and application servers, especially if your workload involves significant data transfer.

1. Plan for Scaling and Growth:

• Consider the expected growth rate in terms of TPS and data volume over a defined period (e.g., 1-3 years).
• Evaluate options for scaling up (vertical scaling) by adding more resources to the existing server, such as increasing CPU cores, memory, or storage capacity.
• Assess options for scaling out (horizontal scaling) by distributing the workload across multiple servers or using technologies like SQL Server Always On Availability Groups or database sharding.

1. Monitor and Adjust:

• Continuously monitor the actual TPS and resource utilization on your SQL Server.
• Compare the observed performance with the planned capacity to identify potential bottlenecks or the need for additional resources.
• Adjust your capacity plan periodically based on real-world usage and any changes in workload or application requirements.

Remember that capacity planning is an iterative process, and it’s important to regularly review and revise your estimates as your application evolves and user demands change.

How we can incresae the trasaction per second in SQL server tables

Increasing the transaction per second (TPS) in SQL Server tables involves optimizing various aspects of your database system. Here are some strategies you can consider:

1. Database Design:

• Normalize your database structure to reduce redundancy and improve query performance.
• Use appropriate data types and indexes to ensure efficient data storage and retrieval.
• Partition large tables to distribute data across multiple filegroups or disks, reducing contention.

1. Indexing:

• Analyze query patterns and create indexes on frequently accessed columns.
• Remove unused or duplicate indexes to minimize overhead.
• Regularly update statistics to ensure accurate query optimization.

1. Query Optimization:

• Analyze and optimize your queries using tools like the SQL Server Query Optimizer.
• Rewrite complex queries to simplify execution plans and reduce resource consumption.
• Use appropriate join types and indexing strategies to minimize table scans.

1. Hardware Considerations:

• Ensure your server hardware meets the demands of your workload, such as having sufficient memory, CPU power, and disk I/O capabilities.
• Use RAID or other disk configurations to improve read/write performance.
• Consider using solid-state drives (SSDs) for data and log files to increase I/O performance.

1. Server Configuration:

• Adjust SQL Server configuration settings, such as max server memory, to optimize resource allocation.
• Configure parallelism settings to take advantage of multiple CPUs for query processing.
• Optimize tempdb configuration for efficient temporary object storage.

1. Transactions and Locking:

• Minimize transaction duration to reduce contention and improve concurrency.
• Use appropriate transaction isolation levels to balance data consistency and concurrency requirements.
• Review and tune locking mechanisms to prevent excessive locking and blocking.

1. Performance Monitoring and Tuning:

• Monitor the performance of your SQL Server using tools like SQL Server Profiler and Performance Monitor.
• Identify and address performance bottlenecks, such as long-running queries, resource contention, or disk I/O issues.
• Regularly review and tune your database system based on monitoring data and performance benchmarks.

Remember that the effectiveness of these strategies may vary depending on your specific workload and environment. It is advisable to thoroughly test any changes in a non-production environment and closely monitor the impact on performance.

SQL Server 2022 DBA features

SQL Server 2022 introduces several new features for database administrators (DBAs) to improve the manageability, performance, and security of SQL Server instances. Here are some of the key DBA features in SQL Server 2022:

1. SQL Server on Kubernetes: SQL Server 2022 introduces native support for Kubernetes container orchestration, allowing DBAs to deploy and manage SQL Server instances as Kubernetes pods. This feature provides greater flexibility, scalability, and portability for SQL Server deployments.
2. Data Classification: Data classification is a new feature that allows DBAs to tag sensitive data and manage it in a more secure and compliant manner. SQL Server 2022 includes built-in classification rules for GDPR and other data privacy regulations, as well as the ability to create custom rules.
3. Always Encrypted with secure enclaves: Always Encrypted is a feature that allows DBAs to encrypt sensitive data at rest and in transit, while still allowing applications to query and manipulate the data. SQL Server 2022 introduces support for secure enclaves, which provide an even higher level of encryption security and protection against attacks.
4. Database recovery acceleration: SQL Server 2022 includes improvements to the database recovery process, allowing DBAs to recover databases faster and with less downtime. This feature can be especially useful for large databases or mission-critical systems.
5. Intelligent performance: SQL Server 2022 introduces several new features to improve query performance and optimize resource usage. These include a new intelligent query processing engine, improved memory grant feedback, and better support for in-memory OLTP.
6. Database compatibility level: SQL Server 2022 introduces a new database compatibility level (CL) of 170, which provides the latest T-SQL language features and improvements. DBAs can set the compatibility level of a database to control which language features are available and ensure compatibility with previous versions of SQL Server.
7. Automatic plan correction: SQL Server 2022 includes a new feature called automatic plan correction, which can automatically detect and correct poorly performing queries without human intervention. This feature uses machine learning to analyze query performance and suggest improvements to the query plan.

Overall, SQL Server 2022 introduces several new features and enhancements that can help DBAs to manage and secure SQL Server instances more effectively and optimize query performance for their applications.

SQL query to Identify the most resource intensive queries in sql server database

You can use the “sys.dm_exec_query_stats” dynamic management view in SQL Server to identify the most resource-intensive queries. This view contains performance statistics for cached query plans, such as CPU time, execution count, and logical reads. Here’s an example SQL query that uses this view to identify the top 10 most resource-intensive queries:

SELECT TOP 10 
    SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, 
    ((CASE qs.statement_end_offset
        WHEN -1 THEN DATALENGTH(qt.TEXT)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1) AS [Query Text], 
    qs.total_worker_time AS [Total Worker Time],
    qs.execution_count AS [Execution Count], 
    qs.total_elapsed_time AS [Total Elapsed Time],
    qs.total_logical_reads AS [Total Logical Reads]
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY 
    qs.total_worker_time DESC;

In this query, the “sys.dm_exec_query_stats” view is joined with the “sys.dm_exec_sql_text” view using a CROSS APPLY function to get the query text associated with each cached query plan. The columns returned by the query include the query text, total worker time, execution count, total elapsed time, and total logical reads. The results are sorted in descending order by total worker time, which is a measure of the CPU time used by each query. The TOP 10 clause limits the output to the top 10 most resource-intensive queries.

Step By to setup the resource governor in sql server

SQL Server Resource Governor is a feature that allows you to manage and allocate resources such as CPU and memory to different workloads or applications in a SQL Server instance. Here are the steps to create a Resource Governor:

1. Enable Resource Governor: First, you need to enable Resource Governor. To do this, run the following command:

ALTER RESOURCE GOVERNOR RECONFIGURE;

2. Create a Resource Pool: Next, create a Resource Pool that defines the maximum amount of resources that can be allocated to a workload group. Use the following syntax to create a Resource Pool:

CREATE RESOURCE POOL [PoolName] WITH
(
    MIN_CPU_PERCENT = 0, 
    MAX_CPU_PERCENT = 100,
    MIN_MEMORY_PERCENT = 0,
    MAX_MEMORY_PERCENT = 100
);

In this example, you create a Resource Pool named “PoolName” with a minimum and maximum CPU and memory percentage set to 0 and 100, respectively. You can adjust these values as per your requirements.

3. Create a Workload Group: A workload group defines a set of requests or queries that share the same resource requirements. To create a workload group, use the following syntax:

CREATE WORKLOAD GROUP [GroupName] USING [PoolName];

In this example, you create a workload group named “GroupName” that uses the Resource Pool created in step 2.

4. Create a Classifier Function: A classifier function maps a connection or session to a workload group based on predefined criteria. To create a classifier function, use the following syntax:

CREATE FUNCTION [ClassifierFunction] ()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @GroupName SYSNAME
    IF (some_condition)
        SET @GroupName = 'GroupName1'
    ELSE
        SET @GroupName = 'GroupName2'
    RETURN @GroupName
END

In this example, you create a classifier function that returns a group name based on a condition. You can define multiple conditions and assign different workload groups accordingly.

5. Configure Resource Governor: Finally, configure the Resource Governor to use the classifier function. To do this, run the following command:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [ClassifierFunction]);

In this example, you assign the classifier function created in step 4 to the Resource Governor.

Once you have completed these steps, SQL Server Resource Governor is configured and ready to use. You can monitor the Resource Governor using the “sys.dm_resource_governor” DMV and make adjustments as needed to optimize performance.

Step By Step How to use DPA

step-by-step-dpa-tool-1Download