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.

Get Ahead of Year-End with Bulletproof Database Solutions

By bradyupton in Maintenance, Monitoring, Security, SQL Freelancer

As we approach the final stretch of the year, businesses across all industries are gearing up for the increased demands that come with Q4. From handling higher transaction volumes to meeting year-end reporting requirements, your SQL databases will be put to the test. At SQL Freelancer, we understand the critical role that a robust, secure, and optimized database plays in ensuring your operations run smoothly during this peak season.

Why Your Q4 Success Depends on Your Database

The last quarter of the year can be a make-or-break time for many businesses. Whether you’re dealing with a surge in online orders, closing out the fiscal year, or preparing for tax season, your database needs to perform at its best. Any hiccups in your database can lead to delays, data loss, or even security breaches, all of which can severely impact your bottom line.

This is where SQL Freelancer comes in. Our Q3 marketing campaign, “Prepare for Q4 with SQL Expertise,” is designed to help you proactively address potential issues before they arise. By partnering with us, you can ensure that your SQL solutions are ready to handle whatever Q4 throws your way.

What We Offer:

  • Comprehensive Database Audits: We’ll perform a thorough analysis of your current database setup, identifying any weaknesses or areas for improvement.
  • Security Enhancements: Protect your data from breaches with our advanced security measures tailored to your specific needs.
  • Performance Optimization: Ensure your databases run efficiently under heavy loads, reducing latency and improving user experience.
  • Scalability Planning: Prepare for growth by ensuring your databases can scale seamlessly with your business needs.

Don’t Let Your SQL Databases Fall Behind

As you gear up for Q4, don’t let your SQL databases be an afterthought. With SQL Freelancer by your side, you can be confident that your solutions are secure, optimized, and ready for anything. We’re committed to helping you prepare, protect, and excel—so you can focus on what you do best.

Partner with the Leader in SQL Solutions

At SQL Freelancer, we pride ourselves on being a leader in SQL solutions. Our team of experts is dedicated to providing top-notch service, tailored to your unique business needs. Don’t leave your Q4 success to chance—partner with us and get ahead of the curve.

Ready to get started? Contact us today to learn how we can help you prepare for Q4 with bulletproof database solutions.

Enhanced patching for SQL Server on Azure VM with Azure Update Manager

By bradyupton in Azure, Security 0 Comment

With Azure Update Manager, unlike with the existing Automated Patching feature, you’ll be able to automatically install SQL Server Cumulative Updates (CUs), in addition to updates that are marked as Critical or Important.    

Azure Update Manager is a unified service that helps manage updates for all your machines. By enabling Azure Update Manager, customers will now be able to:   

  • Perform one-time updates (or maybe Patch on-demand): Schedule manual updates on demand 
  • Update management at scale: patch multiple VMs at the same time 
  • Configure schedules: configure robust schedules to patch groups of VMs based on your business needs:  
  • Periodic Assessments:  Automatically check for new updates every 24 hours and identify machines that may be out of compliance  

thumbnail image 1 of blog post titled Announcing public preview of enhanced patching for SQL Server on Azure VM with Azure Update Manager

 

Read more here…

Create Always Encrypted Certificate

By bradyupton in Always Encrypted, Auditing and Compliance, Database Design, Security 0 Comment

In SSMS, browse to Database, Security, Always Encrypted Keys, right click Column Master Keys and click New Column Master Key:

In the New Column Master Key box you can choose from several key stores. The most popular stores are Current User or Current Machine. See below for description.

Local machine certificate store
This type of certificate store is local to the computer and is global to all users on the computer. This certificate store is located in the registry under the HKEY_LOCAL_MACHINE root.

Current user certificate store
This type of certificate store is local to a user account on the computer. This certificate store is located in the registry under the HKEY_CURRENT_USER root.

Be aware that all current user certificate stores inherit the contents of the local machine certificate stores. For example, if a certificate is added to the local machine Trusted Root Certification Authorities certificate store, all current user Trusted Root Certification Authorities certificate stores also contain the certificate.

In this example, we’ll use Local Machine. Type a name for the Master Key, choose Key Store and click Generate Certificate.

Click OK and a new Column Master Key will appear:

Do the same for any other master keys you would like to create. For example, Developer:

To create a Column Encryption Key, right click on Column Encryption Key and choose New Column Encryption key:

Give the key a name and select the Column Master Key that you would like to associate it with:

Do the same for the Developer keys. You should have two column master keys and two column encryption keys:

To view Local Machine certificates:

Open Certlm.msc

Browse to Personal, Certificates:

Double click a certificate and go to the Detail tab where you will see the thumbprint:

Match this thumbprint with the Column Master Key in SSMS:

Right click the certificate in Certlm.msc and choose Properties. Enter a friendly name to identify which certificate belongs with the Column Master Key.

To Export and certificate, right click and choose All Tasks, Export:

SQL Server User Defined Server Roles

By bradyupton in Security 0 Comment

A new feature to SQL Server 2012 is the ability to create user defined server roles and assign server level/scope permissions to these roles. DBA’s have always had the ability to create user defined database roles which act as a security layer at the database level, but we’ve never been able to create roles at the server level until SQL Server 2012.

In this post I will show you how to create user defined server roles using T-SQL and SQL Server Management Studio.

What Permissions Can Be Assigned

First, to view the list of permissions that can be assigned to a user defined server role run the following query:

USE master 
GO
SELECT * FROM sys.fn_builtin_permissions(DEFAULT) 
WHERE class_desc IN ('ENDPOINT','LOGIN','SERVER','AVAILABILITY GROUP','SERVER ROLE') 
ORDER BY class_desc, permission_name
GO

Create a Server Role in T-SQL

To create a server role called “juniordba” use the following:

USE master
GO
CREATE SERVER ROLE juniordba

Next we will create a login called Brady and then add it to the new juniordba role that was created:

USE master 
GO
ALTER SERVER ROLE juniordba ADD MEMBER Brady

We haven’t added any permissions to the server role, so Brady shouldn’t have access. To test this we can login as Brady and run the following query:

SELECT * FROM sys.dm_exec_connections

As you can see we get the following error message:

Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.

Click here to view the rest of this post.

Securing and protecting SQL Server data, log and backup files with TDE

By bradyupton in Security 0 Comment

In this post I’ll show you how to setup Transparent Data Encryption (TDE). TDE is new in SQL Server 2008 and serves as an encryption method that uses a database encryption key (DEK) to protect SQL Server’s data and log files. The DEK is a key secured by a certificate stored in the master database.

To setup TDE we’ll need to run a few scripts: (My test database is named TDE)

The following script will create the master key with a specified password ElephantRhin0:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ElephantRhin0';
GO

Next, we’ll create a certificate named TDECert that will be protected by the master key:

USE master;
GO
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
GO

After creating the certificate we’ll backup the certificate to a specified source:

USE master;
GO
BACKUP CERTIFICATE TDECert TO FILE = 'C:\TDECert_backup' WITH 
PRIVATE KEY ( FILE = 'C:\TDECert_key' ,ENCRYPTION BY PASSWORD = 'ElephantRhin0' )
GO

Once the certificate is backed up we will create the DEK using the AES algorithm and protect it by the certificate:

USE TDE;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

The final step is to set our database to use encryption:

ALTER DATABASE TDE
SET ENCRYPTION ON;
GO

If everything completed successfully then we have officially encrypted our database with TDE, but don’t take my word for it, run the following query to confirm:

SELECT name, is_encrypted
FROM sys.databases
WHERE name = 'TDE'

SQL Freelancer SQL Server Transparent Data EncryptionClick here to view the rest of this post.