Author: SQL Padre

Problem at work

SQL Padre SQL

Sometimes, real-work scenarios are the best teachers. Without going into too many details or specifics of how we got here, this is my problem:

I need to get a “as near real-time” Read-Only copy of one of my largest production databases on a specific server, in another data center, on another subnet, with an entirely different active directory domain. I have to replicate Cluster A/AG-A1 to Server B with read only access. Here is the game plan to attemp to make this possible.

  • Data Center A –> Domain A –> Cluster A –> 6 nodes –> SQL 2022 Enterprise –> 3 AGs — 8 TB of databases
  • Data Center B –> Domain B –> Server B –> SQL 2022 Standard –> 4 TB of existing databases

Here are the knowns: full way trust between Domain A and B. Networking has full control over both subnets and VMWare in both data centers allow easy cloning, snap shots, etc. to ensure I don’t truly messup the production servers.

  1. Upgrade Server B to Enterprise
  2. Create Clusterless AG on Server B
  3. Create Distributed AG between AG-A1 and AG-B with AG-B being Read Only

Luckily I am able to POC this toplogy to enusre that it is going to work. So we cloned Server B and I created a new Cluster A for just this purpose. I hope to document all my steps in making this happen.

Pre-Check: SQL Deployments

SQL Padre SQL , ,

Following the introductory post of my series Optimize SQL Server Deployment with PowerShell and DBA Checks, after much trial and error I have a working set of deployment PowerShell scripts that I would say “do their job”. My deployment process is laid out into 3 scripts: Pre-Check, Install and Post-Check. These are modular based inteneded to be executed one script at a time. In the future, I may wrap these in a callable function if the need arises.

Pre-Check in my mind is preparing the OS layer for SQL Installation. This include OS level settings, things needed at the OS level and ensuring volumes are correctly configured for SQL Server.

These steps are what my company does, not necessarily what is best for everyone; but it is what is best for us. Because my copmany uses “privledged accounts” and the requirement for Local admin rights of PowerShell, my be method of implementing these scripts is to be logged into a “jump” server with my Privledged Account which has Local Admin rights to execute these scripts against a new server.

  1. Remotely install PowerShell Management Scripts
    • SQL Server
    • DBA Tools
    • Install Windows Updates
  2. Check OS for any pending updates
  3. Validate attached volumes intended for SQL Data, Log and Temp database files are formated 64K
$ServerName = 'MyTestServer'
$PSSession = New-PSSession -ComputerName $ServerName
Invoke-Command -Session $PSSession -ScriptBlock{
    Install-PackagePRovider -Name NuGet -MinimumVersion 2.8.5.201 -Force
    Set-Repository -Name PSGallery -InstallationPolicy -Trusted
    Install-Module -Name PSWindowsUpdate -Scope Allusers -Force
    Install-Module -Name SqlServer -Scope Allusers -Force
    Install-Module -Name Dbatools -Scope AllUsers -Force
}
Invoke-COmmand -Session $PSSession -ScriptBlock {
     Install-WindowsUpdate -MicrosoftUpdate -AcceptAll -Confirm -AutoReboot
}

Get-CimInstance -ComputerName $ServerName -ClassName Win32_Volume|Select-Object Name,Filesystem,Label,BlockSize

Sneaux Day in South Louisiana

SQL Padre SQL

For those that are not aware, I live in South Louisiana. We don’t typically have snow. and if we do, it is usually a few flakes and when it hits the ground it melts. Today was different. Today we recorded over 5″ of snow that will stick around for a couple of days because tonight’s low is predicted to be 17 degrees!!!

Pray for us, we aren’t OK. ha

New Year, new me ??

SQL Padre SQL

Happy New Year to all my readers (all select few of you)!!!

I have come to the realization (again); blogging is hard work. I don’t think it is the subject or finding a topic for me, it is a matter of being disciplined enough to actually remember to write a post.

That being said, I hope to do better in 2025 with more posts on things I am doing at work. Powershell is still very much at the forefront of all my learing right now as well as the concepts and strategies of a “data architect”.

Here’s to the new year!!