SQL Server Databases and GitHub Administration

VIDEO TRANSCRIPT | Recorded: 2025-12-18 | Verify against current system state

Abstract¶

This session covers two major topics: SQL Server database architecture and GitHub organization administration. Jeff explains the database landscape including Aptify, AMPDB, CraftCMS MySQL, and Salesforce databases, the historical view abstraction layer for legacy applications, backup/recovery procedures across Aptify Cloud and Azure, and DBAmp license renewal requirements. The GitHub section covers organization structure, team-based permissions, Copilot licensing, vendor access patterns, branch protection, secrets management, and Dependabot security alerts.

Key Procedures¶

SQL Server & Databases¶

• Database locations: Aptify Cloud (production) and Azure (Bestline servers with "AZ" prefix)
• Primary databases: Aptify (AMS backend), AMPDB (legacy apps), Salesforce (DBAmp cache), CraftCMS MySQL (Cloudways)
• Aptify database access: Read-only recommended; updates should go through Aptify API/framework
• Exception process: Document exceptions and submit to Aptify Cloud Help Desk for large direct SQL changes
• VPN required for all database connections; can hop through web server if direct SQL connection fails
• SA credentials in 1Password; most developers use Active Directory authentication
• Aptify Cloud backups: Managed as part of hosting; IT Support receives daily success/failure email - check weekly
• Database restore for QA refresh: Contact Aptify Cloud Support; verify licenses and smart client after restore
• Aptify Cloud support hours: 8-5 Eastern; after-hours response may be slower for complex issues
• Azure/Bestline backups: Veeam backup includes both server and SQL database; stored in 3 locations (Azure, blob, off-site)
• DBAmp license renewal: ANNUAL - new license number provided via email; must apply before old license expires or sync fails
• DBAmp restart: Use desktop configuration app → Stop → Restart after license update
• Log table purge: SQL job maintains log tables with varying retention (2 weeks to 3 months depending on table)
• View abstraction: Legacy tables renamed to __legacy, views created with original names pointing to Aptify
• vw_PersonsWithBestAddress: Useful but slow - best for small result sets, not entire database queries

GitHub Administration¶

• Login: IT Support account from 1Password for organizational admin access
• Switch to organization: After login, click to switch from personal account to "AMPIT" organization
• Archived repositories: Only visible when logged in as IT Support
• Seats: 9 of 10 used; includes full members and outside collaborators
• Team-based permissions: Create teams (e.g., "Salesforce Auditors" = read-only, "Developers" = full access)
• Vendor access pattern: Create team named "[Vendor] Developers", add members, grant repo access to team
• GitHub pricing: \(4/seat/month base; Copilot additional ~\)20/seat/month (3 users currently)
• Review usage: Remove users not logging in to GitHub
• Branch protection: Consider for critical repos (account, conference reg, Salesforce metadata) - require PR for main branch
• Two-factor requirement: Not enabled yet - enabling will remove users without 2FA configured
• GitHub Secrets: Organization level for Mighty Citizen Buddy sync and Bitbucket; repository level secrets exist but harder to audit
• Personal Access Tokens: Settings → Developer Settings → Fine-grained or Classic tokens
• Dependabot alerts: Consider enabling only for critical repos (account, conference reg) to avoid alert flood
• Nightly sync: GitHub Actions pull from Bitbucket (I2C) and Buddy (MightyCitizen) repositories

Notable Statements¶

• 0:03:36 "Aptify has set it up so they really strongly recommend developers only have read-only access to the Aptify database."
• 0:08:23 "View persons with best address is very often used... it's solid, but it has really slow performance."
• 0:16:13 "Most of the time when you do this restore, I think y'all would agree, there's always problems afterwards."
• 0:27:02 "DBAmp will give you a new license number, like a completely new license number... you have to apply the new license before the old license expires."
• 0:31:48 "Without Aptify support, it's our responsibility to have database backups... index maintenance, statistics updates, integrity monitoring, performance tuning, security patching."
• 0:39:23 "What we did was we changed some of these tables to views. We renamed the original table to underscore underscore legacy."
• 0:46:15 "Those views... some of them are fairly complex and should never be changed. They are what they are at this point."
• 0:51:36 "It's much easier to put people in teams and then revoke access based on the team."
• 1:04:03 "You got to get me out of these repositories come January 1."

Systems & Configurations¶

Systems Mentioned¶

• SQL Server 2019 (Aptify Cloud and Azure)
• Aptify (AMS - desktop and web client)
• AMPDB (legacy database)
• Salesforce (DBAmp cached data)
• CraftCMS MySQL (Cloudways)
• DBAmp/CData (Salesforce-SQL integration)
• GitHub (version control, organization)
• Azure (Bestline servers)
• Veeam (backup solution)
• 1Password (credentials)
• Entity Framework, NHibernate, Dapper (ORMs)

Specific Configurations¶

Credentials/Access Mentioned¶

• SA credentials in 1Password for SQL Server
• Active Directory authentication for most developer access
• IT Support GitHub account for organizational admin
• DBAmp license numbers provided annually via email
• Personal Access Tokens for API/MCP connections
• GitHub Secrets for Buddy and Bitbucket sync credentials

Errors & Troubleshooting¶

• Issue: DBAmp sync stops working
• Cause: Annual license expired; shows as "trial license" in error
• Resolution: Apply new license number from email in DBAmp desktop config app; restart service

• Timestamp: 0:27:02

• Issue: Cannot connect directly to Aptify SQL Server

• Cause: VPN or network issue
• Resolution: Connect to Aptify Cloud web server first, then RDP from there to SQL Server

• Timestamp: 0:12:09

• Issue: QA database restore causes downstream issues

• Cause: Restore doesn't automatically reconnect databases, restore licenses
• Resolution: After restore, verify: database connections, smart client works, licenses restored

• Timestamp: 0:16:13

• Issue: vw_PersonsWithBestAddress query is slow

• Cause: Complex view not optimized for full table scans
• Resolution: Use for small, filtered result sets only; avoid selecting all US persons

• Timestamp: 0:08:23

• Issue: Losing Aptify process flows after migration to Azure

• Cause: Aptify async processor, scheduled tasks, process flows won't run without Aptify
• Resolution: Business logic that ran on save in Aptify must be handled differently in Salesforce
• Timestamp: 0:30:52

Transcript Gaps & Quality Notes¶

• Recording is from a live Teams meeting with Matt Mackowski and Keith Milleson asking questions
• Two major topics (SQL/GitHub) covered in one session - comprehensive but dense
• Historical context provided for AMPDB → Aptify migration and view abstraction pattern
• AMP Core concept discussed as future architecture direction
• Recommendation: Consider fractional DBA service post-Aptify Cloud
• SQL Server maintenance solution (Ola Hallengren) referenced for self-managed scenarios
• Action item mentioned: Remove Jeff Sikes from GitHub repositories by January 1
• Presenter: Jeff Sikes
• Duration: ~68 minutes
• Audio quality: Good