Month: February 2020

Log Shipping for Migration

The trouble with backing up databases in ‘old production’ then restoring them to ‘new production’ is that it takes time.

And there may be some unforeseen delay switching the front-end apps over.

Resulting in ‘old production’ being updated with new data, and ‘new production’ becoming out of date.

Log-shipping is an ideal, built-in, tool that can be used to keep ‘new production’ in sync with ‘old production’ during that phase between backup/restore and switching the front-end to ‘new production’.

This time around there was no need to script the setting up of log-shipping. There were only 13 databases, so using the GUI did not take long.

The idea is to complete the backup/restore a week or so before the switch-over and set-up log-shipping to keep the data in sync.

Then at the designated switch-over time, it takes only a moment to bring ‘new production’ on-line, as a fully up-to-date copy of ‘old production’.

Here is my crib-sheet …

 

Preparation

  • Primary and secondary servers should have as near as possible the same instance settings eg: max-memory, numa configuration, CLR, max dop, etc
  • Ensure user databases are using full recovery model
  • Create shared folder (on the target ideally)
  • Default backup compression is enabled (ideally)
  • Reduce VLF counts
  • Configure file share folder and connectivity
  • Disable tlog backups on primary

Preparation – Secondary Instance

  • Ensure enough space for databases
  • Matching drive letters for datafiles and logfiles (ideally)
  • Configure file share

Preperation – Monitor Instance

  • Ideally separate from primary and secondary

Security

  • Config login is a sys admin role
  • SQL server service account on primary needs read/write permission on backup directory (for the backup job)
  • SQL server service account on secondary needs read permission on backup share and read/write permission to secondary share (for copy / restore jobs)

Configuring log-shipping

  • Manually backup and restore database (with no recovery)
  • Use notepad to cut and paste connection strings and paths
  • Transfer logins, jobs and linked servers

The switch over

  • Manually execute the backup, copy, and restore jobs a final time
  • Manually restore each database “with recovery”
  • Detach old databases (so there is no chance of them being updated)
  • Point front-end-applications to new back-end server

Post Migration

  • Full backups (the old ones cannot be restored now)
  • Update all statistics
  • Check compatability level
  • Execute dbcc checkdb
  • Enable plan-store (read/write)
  • Monitor health and performance

Installing SQL 2017 on a cluster

Remote onto node-1 with the SQL DVD inserted (or mounted), and Windows Failover Cluster already created with shared drives D, L, T, and S. (and ideally Z for backups, but not in this case).

Preperation

  • Re-boot (if “access denied” end task “Service Host: DCOM Server Process Launcher (4)”)
  • From Failover Cluster Manager run “validate cluster” (expect warnings for software level and only one network)
  • Copy media to local drive (IE: SQL Server, CU, and maybe SSMS)
  • Ensure you have service accounts for Engine and Agent.
  • Take a screenshot of Windows uninstall screen – to aid possible uninstall

Run on each node individually

  • Advanced
  • Advanced cluster preparation
  • Product Key, next
  • Licence Terms, tic, next
  • Microsoft Update, next
  • Prepare Failover Cluster Rules (Windows Filewall Warning), next
  • Feature Selection #1, [Database Engine Services]
  • Feature Selection #2, root = [S:\]
  • Instance Configuration, instance name, Instance ID
  • Server Configuration, Agent and Engine (automatic), svc accounts, Grant perform VMT tasks
  • Feature Configuration Rules, next
  • Ready to Install, install

On Active Node

  • Advanced
  • Advanced cluster completion
  • Cluster Node Configuration, Create AD name for instance
  • Cluster Resource Group, next
  • Cluster Disk Selection, tic all required disks (EG: D, L, T)
  • Cluster Network Configuration, tic IPv4, untic DHCP, choose address within subnet (EG: 10.8.0.1)
  • Server Configuration, next

Shared Settings

  • Database Engine Configuration #1: sa pw, add current user, sql service account.
  • Database Engine Configuration #2: Root = D:\, Data = D:\MSSQL, Log = L:\MSSQL, Backup = D:\Backup
  • Database Engine Configuration #3 (tempdb): Data = T:\MSSQL, Log = L:\MSSQL
  • Ready to Install, Install

Uninstall – if needed

  • If it failed before “Advanced cluster completion” just remove from each node via Contol Panel.
  • If it fails at the very end. You will need to run “Remove node from Cluster”, in the Maintenance tab of the media installation program.