Category: SQL Server

SQL Server AlwaysOn Rolling Upgrade with Powershell & DBATools

goforebroke480Leave a comment

Upgrading SQL Server in High Availability and Disaster Recovery (HADR) environments is a routine task for database administrators. More importantly, these upgrades often need to be performed with minimal or no downtime. šŸ˜Š Fortunately, Microsoft introduced rolling cluster upgrades in Windows Server 2016, allowing the operating system in a Windows cluster to be upgraded without stopping cluster services.

Additionally, rolling cluster upgrades can be combined with SQL Server AlwaysOn rolling upgrades, enabling database administrators to upgrade SQL Server HADR environments with minimal downtime. In this post, we will demonstrate how to upgrade a Windows Server 2016/SQL Server 2017 AlwaysOn cluster to Windows Server 2019/SQL Server 2019.

Prerequisites

Before Proceeding, ensure you have the following:

Instructions

Part 1. Add Windows 2019 servers to cluster

Part 2. Add SQL 2019 instances to availability group

Part 3. Perform Rolling upgrade

Update SQL Server with Desired State Configuration (DSC)

goforebroke4802 Comments

DSC (Desired State Configuration) is a PowerShell-based management platform that enables you to manage IT and development infrastructure using configuration as code.

Prerequisites

  • Oracle VirtualBox is downloaded and installed on Windows 10.
  • Familiarity with Desired State Configuration (DSC).
  • Working knowledge of SQL Server command-line installation options.
  • Working knowledge of Active Directory.
  • A Windows Server 2019 or 2016 Domain Controller, installed and configured.
  • A Windows Server 2019 or 2016 file server, installed and configured.
  • A Windows Server 2019 or 2016 machine to host SQL Server, installed and configured.

Instructions

Part 1: Setup SQL Server Media Share

Part 2: Download CertificateDsc

Part 3: Update SQL Server with DSC

Install SQL Server with Desired State Configuration & DBATools

goforebroke480Leave a comment

DSC (Desired State Configuration) is a PowerShell-based management platform that enables you to manage IT and development infrastructure using configuration as code.

Prerequisites

  • Oracle VirtualBox must be downloaded and installed on Windows 10.
  • Basic knowledge of Desired State Configuration (DSC).
  • Familiarity with SQL Server command-line installation options.
  • Understanding of Active Directory.
  • A Windows Server 2016 or 2019 Domain Controller must be installed and configured.
  • A Windows Server 2016 or 2019 server must be installed and configured to serve as a file share server.
  • A Windows Server 2016 or 2019 server must be installed and configured to host SQL Server.

Instructions

Part 1: Setup SQL Server media share

Part 2: Download SQLServerDSC and DBATools

Part 3: Install SQL Server with DSC & DBA Tools

SQL Server & Group Managed Service Accounts (gMSAs)

goforebroke480Leave a comment

Managed Service Accounts (MSAs) were introduced to eliminate the overhead of managing and updating passwords for application service accounts. By shifting password management to Active Directory (AD), MSAs ensure that passwords are automatically generated, secured, and rotated every 30 days (by default). Additionally, updating an MSA password does not require a restart of SQL Server. This delegation of password management relieves the DBA/SQL team of the cumbersome task of manually updating service account passwords and restarting SQL Server.

However, MSAs have limitationsā€”they are restricted to a single computer account, making them unsuitable for failover clustered instances where the SQL Server instance can run on any node in the cluster. In large SQL environments, this restriction can result in the creation of numerous MSA objects in AD, adding to administrative overhead.

To address these limitations, Group Managed Service Accounts (gMSAs) were introduced. gMSAs provide the same automated password management benefits as MSAs while allowing multiple SQL Server installations to share a single account. This significantly reduces the number of service account objects in AD and simplifies management in large environments.

Prerequisites:

  • Oracle VirtualBox must be downloaded and installed on Windows 10.
  • Basic knowledge of Windows Server 2016 or Windows Server 2012 R2.
  • Familiarity with SQL Server 2014, 2016, or 2017.
  • Understanding of Active Directory.
  • A Windows Server 2016 or Windows Server 2012 R2 Domain Controller must be installed and configured.
  • SQL Server 2014, 2016, or 2017 database server(s) must be installed and configured.

Instructions

Part 1 – Create the security group and the gMSA

Part 2 – Install Active Directory cmdlets on servers hosting SQL Server

Part 3 – Configure the SQL Server instance to use the gMSA

Oracle 12c (PDB) Replication to SQL Server 2016

goforebroke480Leave a comment

If you need to replicate data from Oracle to SQL Server, I highly recommend Oracle GoldenGate for its reliability and robust replication capabilities. In this demonstration, Iā€™ll showcase data replication from Oracle 12c Multitenant to SQL Server 2016 using Oracle VirtualBox, the Oracle Developer Pre-Built VM, Oracle GoldenGate, Windows Server 2016, and SQL Server 2016. These VirtualBox machines will run on a Windows 10 system, which serves as the base ā€œhomeā€ environment.

Prerequisites

Oracle VirtualBox is downloaded and installed on Windows 10.
Working knowledge of Oracle 12c Multitenant.
Working knowledge of SQL Server 2016.

Instructions

Part 1 – Install Windows Server 2016 in VirtualBox

Part 2 – Install SQL Server 2016 on Windows Server 2016

Part 3 – Import Oracle/Linux Pre-Built Developer VM into VirtualBox

Part 4 – VM Network Settings

Part 5 – Install GoldenGate for SQL Server on Windows

Part 6 – Install GoldenGate for Oracle on Linux

Part 7 – Configure GoldenGate for Oracle on Linux

Part 8 – Configure GoldenGate for SQL Server on Windows

Part 9 – Putting it all together

Part 10 – Monitoring Replication