Efficient SQL Server Patching Using DBATools.IO

By bradyupton in Maintenance, Security

Maintaining SQL Server infrastructure can be time-consuming, especially when it comes to patching multiple servers. A manual approach of remoting into each server individually, checking versions, and applying updates takes significant effort and is prone to errors. This is where DBATools.IO, a powerful open-source PowerShell module, comes in handy. It simplifies and automates SQL Server tasks, including patching. In this post, we’ll walk through a PowerShell script that uses DBATools.IO to patch SQL Servers, saving time and effort compared to manual methods.

What is DBATools.IO?

DBATools.IO is a collection of PowerShell cmdlets designed to make SQL Server management easier for DBAs. It provides over 500 functions for database migration, backups, performance tuning, patching, and more. For those managing multiple SQL Servers, DBATools.IO is an essential tool in automating repetitive tasks, improving efficiency, and reducing human error.

Breaking Down the PowerShell Script

Let’s look at each part of the script and understand what it’s doing:

Set-ExecutionPolicy RemoteSigned

This line allows PowerShell scripts to run on your machine. The RemoteSigned policy permits scripts downloaded from the internet to run if they are signed by a trusted publisher, while local scripts don’t need a signature.

#Servers
$ServerList = 'Server01', 'Server02', 'Server03'

Here, a list of SQL Servers that need to be patched is specified. Instead of remoting into each server one by one, this list tells the script which servers it needs to patch, simplifying the process.

$Credential = Get-Credential -UserName .\Brady -Message 'Enter Password'

The Get-Credential cmdlet prompts you to enter your credentials (username and password), which will be securely passed into the subsequent commands. This allows the script to authenticate when accessing the SQL Servers without exposing passwords directly in the script.

import-module DBATools
import-module sqlserver

These commands import the necessary modules. DBATools provides SQL Server management functions, while sqlserver allows interaction with SQL Server instances via PowerShell.

Test-DbaBuild -Latest -Update

This command updates the local index of SQL Server builds to ensure you are working with the most up-to-date version information. It checks for the latest SQL Server cumulative updates and stores that information locally for reference. Read more about this command and available parameters here.

$version = '2022CU15'

Here, the desired SQL Server version is set, in this case, 2022CU15. This variable is used later to specify which version to patch the servers to.

Get-DbaBuildReference -Update

This command updates the build reference for SQL Server patches, ensuring the script uses the most current information available for the version you’re updating to. Read more about this command and available parameters here.

Update-DbaInstance -ComputerName $ServerList -Version $version -Path '\\fileshare\DBA\SQL Server Installs\Patches\2022' -Credential $Credential -Restart

This is the heart of the script: the actual patching process. The Update-DbaInstance cmdlet performs the following tasks:

  • -ComputerName $ServerList: Specifies the servers to patch (as listed in $ServerList).
  • -Version $version: Defines the SQL Server version to patch to, which was previously set as 2022CU15.
  • -Path: Specifies the network path where the patch files are located, in this case, a fileshare (\\fileshare\DBA\SQL Server Installs\Patches\2022).
  • -Credential $Credential: Provides the credentials (username/password) to access and update the servers.
  • -Restart: Ensures that the servers are automatically restarted after the patch is applied, as needed.

Final look at the entire script:

Set-ExecutionPolicy RemoteSigned

#Servers
$ServerList = 'Server01', 'Server02', 'Server03'

#Credential to pass into the Update-DbaInstance command
$Credential = Get-Credential -UserName .\Brady -Message 'Enter Password'

import-module DBATools
import-module sqlserver

#Update the index of Builds
Test-DbaBuild -Latest -Update

#Set the version that you want to update to
$version = '2022CU15'

# Update the build index
Get-DbaBuildReference -Update

# Patch SQL Server
Update-DbaInstance -ComputerName $ServerList -Version $version -Path '\\fileshare\DBA\SQL Server Installs\Patches\2022' -Credential $Credential -Restart

Time-Saving Benefits of Using DBATools.IO for SQL Patching

Traditionally, patching multiple SQL Servers would require manually remoting into each server, applying the updates, and rebooting if necessary. This is not only tedious but also introduces the possibility of human error, such as applying the wrong patch version or missing a server in the process.

With this DBATools.IO script:

  • Automation: The entire patching process is automated, eliminating the need for manual intervention. You can patch multiple servers with a single command.
  • Consistency: The same patch is applied across all servers, ensuring version consistency across your environment.
  • Efficiency: Instead of remoting into each server, the script performs the patching in parallel, saving time and reducing downtime.
  • Error Reduction: By automating the process, the risk of missing a step or applying the wrong patch is greatly reduced.
  • Credential Management: The script securely passes credentials without needing manual authentication on each server.

In a production environment where uptime is critical, and efficiency is key, using a single PowerShell script to patch multiple SQL Servers at once can significantly cut down on maintenance time. DBATools.IO is a powerful ally in making SQL Server management smoother, faster, and more reliable.

By leveraging tools like DBATools.IO, DBAs can streamline repetitive tasks, allowing them to focus on higher-value activities like optimization and troubleshooting.

Performance Tuning Series – Regular Maintenance

By bradyupton in Backup/Restore, Dynamic Management Views, Extended Events, Maintenance, Monitoring, Performance Series, Performance Tuning

SQL Server performance isn’t just about the initial setup or database design—it requires continuous maintenance to ensure it runs smoothly over time. Neglecting regular maintenance can lead to fragmentation, slow queries, data integrity issues, and ultimately, downtime. By implementing a comprehensive maintenance strategy, you can ensure that your SQL Server databases remain optimized and healthy, allowing them to perform efficiently even as workloads and data volumes grow.

Why Regular Maintenance is Critical

Over time, SQL Server databases accumulate various inefficiencies that can degrade performance. These include fragmented indexes, outdated statistics, growing transaction logs, and unused or bloated data. Regular maintenance tasks help to mitigate these issues, ensuring that SQL Server can continue to execute queries quickly, handle transactions efficiently, and maintain data integrity.

Key Regular Maintenance Tasks for SQL Server

1. Index Rebuilding and Reorganization

Indexes can become fragmented over time as data is inserted, updated, or deleted. Fragmentation occurs when the logical order of pages in an index no longer matches the physical order on disk. This leads to slower reads, as SQL Server must perform additional I/O to retrieve scattered data.

  • Rebuild Indexes: Index rebuilds recreate the index from scratch, removing fragmentation and improving query performance. This process locks the table, so it should be scheduled during periods of low activity.
  • Reorganize Indexes: Reorganizing indexes is a less intrusive process that defragments them without locking the table. This can be done during regular operations but is less effective than a full rebuild.

Best Practice:

  • Schedule regular index maintenance based on the level of fragmentation. Use SQL Server’s sys.dm_db_index_physical_stats DMV to check fragmentation levels:
    • 0-10% fragmentation: No action needed.
    • 10-30% fragmentation: Use index reorganization.
    • Above 30% fragmentation: Perform a full index rebuild.
  • Automate index maintenance using SQL Server Agent jobs or a dedicated maintenance tool to ensure this task is performed regularly without manual intervention.

2. Update Statistics

SQL Server uses statistics to estimate the distribution of data values in a table, which helps the query optimizer choose the most efficient execution plan. As data is modified, these statistics can become outdated, leading to suboptimal query plans and slower performance.

  • Best Practice:
    • Regularly update statistics on your tables and indexes to ensure that the query optimizer has the most accurate information. Use the UPDATE STATISTICS command or enable SQL Server’s auto-update statistics feature.
    • For large tables, use sampled statistics to balance performance with accuracy. Full scans of very large tables can be resource-intensive.
    • If queries slow down unexpectedly, manually update statistics to resolve potential performance issues caused by outdated statistics.

3. Backup and Recovery Management

Regular backups are essential for data protection and business continuity. However, improper backup strategies can lead to bloated transaction logs, excessive disk usage, and even performance degradation during peak times.

  • Best Practice:
    • Implement a full backup strategy based on your business’s recovery point objectives (RPO) and recovery time objectives (RTO). Schedule full backups regularly (e.g., daily) and transaction log backups more frequently (e.g., every 15-30 minutes) for critical databases.
    • Use differential backups between full backups to reduce the load on storage and improve recovery times.
    • Regularly test your backups by restoring them to a separate environment to ensure that they can be successfully recovered when needed.
    • Ensure that backup schedules avoid peak activity times to prevent any impact on performance.

4. Transaction Log Management

SQL Server’s transaction log records every modification made to the database. If not properly managed, the transaction log can grow excessively large, consuming valuable disk space and degrading performance.

  • Best Practice:
    • Use the Full Recovery Model for critical databases to ensure point-in-time recovery, but regularly back up the transaction logs to prevent them from growing too large.
    • For less critical databases or databases that don’t require point-in-time recovery, consider using the Simple Recovery Model, which automatically truncates the transaction log after each checkpoint.
    • Monitor transaction log size and schedule log backups frequently to avoid excessive growth. Use the sys.dm_db_log_space_usage DMV to monitor log space consumption.

5. Integrity Checks (DBCC CHECKDB)

Database corruption can occur for various reasons, such as hardware failures or improper shutdowns. SQL Server provides the DBCC CHECKDB command to detect and repair corruption in your databases.

  • Best Practice:
    • Run DBCC CHECKDB regularly to ensure data integrity. This process checks for physical and logical corruption in database files.
    • Schedule DBCC CHECKDB during off-peak hours to avoid performance impacts, as this operation can be resource-intensive.
    • If DBCC CHECKDB identifies corruption, address the issue immediately. Use repair options like REPAIR_ALLOW_DATA_LOSS as a last resort, and restore from backups when possible.

6. Tempdb Maintenance

Tempdb is a shared system database that is heavily used by SQL Server for temporary objects, intermediate query results, and sorting. Over time, tempdb can become a performance bottleneck if it is not properly managed.

  • Best Practice:
    • Ensure that tempdb has multiple data files, especially in high-concurrency environments. Best practice is to configure one data file per logical CPU core (up to 8 cores), which helps reduce contention on system pages (like PFS, GAM, and SGAM).
    • Place tempdb on fast storage (preferably SSD or NVMe) to handle its high I/O workload.
    • Regularly monitor tempdb space usage to avoid running out of space, which can lead to system crashes. Use sys.dm_db_task_space_usage and sys.dm_db_session_space_usage to track space consumption.

7. Cleanup of Unused or Outdated Data

Over time, databases may accumulate unused data, which can increase table sizes and slow down queries. Regularly cleaning up obsolete data ensures your database remains efficient.

  • Best Practice:
    • Implement a data retention policy that defines how long data should be kept before being archived or deleted. This policy should reflect business requirements while keeping database sizes manageable.
    • Periodically archive old data that is not frequently accessed into separate databases or storage systems.
    • Use automated scripts to clean up old or unused records, freeing up space and reducing index bloat.

8. Monitor and Tune Performance

SQL Server provides several tools for monitoring performance, including Dynamic Management Views (DMVs) and Extended Events. Regular monitoring can help identify performance bottlenecks before they affect end-users.

  • Best Practice:
    • Regularly monitor key performance metrics like CPU usage, memory usage, disk I/O, and query execution times to ensure the system is operating within optimal thresholds.
    • Use SQL Server Profiler or Extended Events to capture detailed information about query performance and diagnose slow-running queries.
    • Leverage DMV’s to analyze query patterns and suggest optimizations like new indexes.
    • Continuously review and tune your queries, indexes, and database schema based on real-time performance data.

9. Automating Maintenance Tasks

Manually managing routine maintenance tasks can be time-consuming and prone to error. SQL Server provides built-in automation tools, such as SQL Server Agent, to schedule and manage maintenance operations.

  • Best Practice:
    • Set up automated maintenance jobs for tasks like index rebuilding, statistics updates, transaction log backups, and integrity checks. This ensures that these critical operations are performed consistently and without manual intervention.
    • Regularly review and adjust job schedules to avoid conflicts during peak business hours.
    • Use maintenance plans in SQL Server Management Studio (SSMS) or third-party tools for more advanced scheduling and management of maintenance tasks.

Conclusion

Regular maintenance is essential for keeping your SQL Server database healthy and optimized for performance. Tasks like index rebuilding, updating statistics, managing transaction logs, performing integrity checks, and cleaning up obsolete data all contribute to the overall efficiency and reliability of your SQL Server environment. By automating maintenance operations, monitoring key performance metrics, and regularly tuning the system, you can ensure that your SQL Server databases continue to deliver optimal performance as your workload and data volumes grow.

Maintenance Windows for Azure SQL

By bradyupton in Azure, Maintenance

The cloud has revolutionized the way we manage and maintain databases, offering new features and capabilities to enhance reliability and minimize disruptions. One such feature that has garnered significant attention is the maintenance window for Azure SQL Database.

Microsoft’s maintenance window feature allows database administrators to schedule planned maintenance events for Azure SQL Database resources during non-peak hours, effectively reducing the impact on mission-critical production workloads. By configuring a maintenance window, we can ensure that updates, patches, and other maintenance activities occur at a time that minimizes disruptions to our business operations.

The true power of this feature lies in its flexibility and customization options. While Azure SQL Database follows a default maintenance window designed to align with typical business hours, administrators can tailor the maintenance schedule to suit their unique requirements. Whether it’s opting for weekday or weekend maintenance slots, the ability to choose a time frame that aligns with our specific operational needs is a game-changer.

Moreover, the maintenance window feature comes with built-in advance notifications, allowing us to stay informed and prepared for upcoming maintenance events. This proactive approach enables us to plan accordingly, minimizing potential downtime and ensuring a smooth transition during the maintenance process.

It’s worth noting that the maintenance window is available for most service level objectives (SLOs) within Azure SQL Database, ensuring that organizations with diverse performance and availability requirements can benefit from this feature.

As database administrators, we understand the critical importance of maintaining a reliable and high-performing database infrastructure. The maintenance window feature in Azure SQL Database empowers us to strike the right balance between keeping our systems up-to-date and minimizing disruptions to business operations.

I encourage my fellow database professionals to explore this feature and leverage its capabilities to optimize their maintenance strategies. By doing so, we can enhance the reliability and availability of our database systems, ultimately contributing to the success of our organizations.

Click here to read more…

SQL Server DBCC CHECKDB Overview

By bradyupton in Maintenance 0 Comment

SQL Server database corruption can be a problem and can cause serious damage to a database. If you’re an experienced DBA then you probably have safeguards in place to detect this, but over the years I’ve seen hundreds of SQL Servers with no detection methods at all and this is a problem. There are a few ways to detect database corruption, but this tip will focus more on DBCC CHECKDB.

You may or may not have heard of DBCC (database console commands) statements. These statements are used to perform different operations in your database and can be broken down into four categories: Maintenance, Miscellaneous, Informational, and Validation. I use some of the DBCC statements on a daily basis, but none more than DBCC CHECKDB.

What is SQL Server DBCC CHECKDB

DBCC CHECKDB, from Microsoft MSDN Library, checks logical and physical integrity of all the objects in the specified database by performing the following operations:

  • Runs DBCC CHECKALLOC on the database – Checks consistency of disk space allocation structures for a specified database.
  • Runs DBCC CHECKTABLE on every table and view in the database – Checks the integrity of all the pages and structures that make up the table or indexed view.
  • Runs DBCC CHECKCATALOG on the database – Checks for catalog consistency within the database.
  • Validates the contents of every indexed view in the database.
  • Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
  • Validates the Service Broker data in the database

If you’ve ever ran DBCC CHECKDB you know it takes some time for large databases. Now that you know all of the steps that are run, you can see why it takes time to complete.

How can SQL Server DBCC CHECKDB help me?

Data corruption is bad. It can cause all sorts of issues within the database that may include incorrect data results, failed SQL statements, and in some cases can take down the entire SQL instance. DBCC CHECKDB warns you of corruption so that you can fix it before (hopefully) it gets too bad.

How do I use SQL Server DBCC CHECKDB?

DBCC CHECKDB is pretty straightforward. There are a few options you can use with the statement and we’ll go over some of those in the next section, but the basic syntax looks like this:

DBCC CHECKDB ('DatabaseName')

Pretty simple.

Automate SQL Server DBCC CHECKDB

Obviously, you don’t want to log in every morning and run this statement on each database, so you can automate this process using a few different methods:

  • SQL Server Maintenance plans – Maintenance plans are part of SQL Server out of the box (unless you’re running Express Edition). I don’t like using maintenance plans for the most part, but I don’t mind using them for this type of task. In the Maintenance Plan toolbox you’ll need to use the Check Database Integrity task. The only configurable option is to include indexes so it’s not really user friendly, but in some cases this is all you need. Again, we’ll talk about other options in the next section.

Custom scripts – Custom scripts are usually what I use and offer the best flexibility as far as adding the options you want. My go-to scripts are already created and free to use from Ola Hallengren. He’s done a wonderful job of creating these and sharing them to the world. Thanks Ola!

Click here to view the rest of this post.