This repository contains scripts, configurations, and documentation for standing up a secure, high-performance on-premise infrastructure with virtualized environments and robust SQL Server database administration. It includes CI/CD automation, backup strategies, ETL pipelines, and performance monitoring practices.
- Virtualization Setup: Templates and configuration for provisioning VMs with Hyper-V, VMware, or Proxmox
- Secure Networking: VLAN and firewall configuration for isolated, compliant environments
- Database Deployment: SQL Server setup with filegroups, collation, containment, RBAC, and encryption
- ETL Design: Pipeline structure with validation and error logging
- CI/CD Integration: Ready-to-use YAML files for GitHub Actions and Azure Pipelines
- Backups & DR: Strategy for full/differential/log backups and restore testing
- Documentation: Flowcharts, configs, and access control matrices for audit and scale
- This README.md file provides an overview of the project, its structure, and how to set up the environments.
- The
docsdirectory contains architecture diagrams, checklists, IP plans, and guidelines. - The WIKI is used for detailed, step-by-step guides, best practices, troubleshooting tips and knowledge sharing.
- System and Database Administration tasks are logged on this Trello board.
├── virtualization/
| ├── ansible/ # Playbooks for post-VM configuration
│ | ├── inventory/ # Host/group inventory
│ | ├── roles/ # Ansible roles for SQL, AD, IIS, etc.
│ | └── site.yml # Entry playbook
| ├── packer/ # Packer templates to build golden images
│ | ├── windows-server-2022.json
│ | └── scripts/
│ | ├── install-sql.ps1
│ | └── enable-rdp.ps1
| ├── terraform/ # Infrastructure provisioning (if cloud or nested ESXi)
│ | ├── main.tf
│ | ├── variables.tf
│ | └── outputs.tf
| ├── tests/ # Automated tests (e.g., Inspec, Pester, PowerShell)
│ | ├── validate-domain.ps1
│ | └── test-sql-service.ps1
| ├── docs/ # Architecture, checklist, config
│ | ├── VM_Setup_Checklist.xlsx
│ | ├── network-plan.md
│ | └── vm-topology.drawio
| ├── scripts/ # General provisioning scripts
| │ ├── create-vms.ps1
| │ ├── set-static-ip.ps1
| │ └── join-domain.ps1
├── database/
│ ├── schema/
│ │ ├── create_databases.sql
│ │ ├── create_tables.sql
│ │ ├── create_indexes.sql
│ │ └── stored_procedures/
│ ├── encryption/
│ ├── backups/
│ │ ├── backup_strategy.md
│ │ └── restore_tests/
│ └── access_control/
│ └── access_control_matrix.xlsx
├── etl/
│ ├── pipelines/
│ ├── logging/
│ └── validation_rules/
├── cicd/
│ ├── github/
│ │ └── main.yml
│ ├── azure-pipelines.yml
│ └── secrets_template.md
├── docs/
│ ├── workflow_flowchart.png
│ ├── hardening_guidelines.md
│ └── architecture_overview.md
└── README.md
This document outlines all the tools and accounts required to use this repository for on-prem virtualization, SQL Server database administration, CI/CD automation, and infrastructure documentation.
- Hypervisor (choose one):
- Hyper-V (Windows Server)
- VMware ESXi
- Proxmox VE
- VM Management Tools:
- Hyper-V Manager / vSphere Client / Proxmox Web UI
- PowerShell (with Hyper-V or VMware modules)
- SSH client for Linux VMs
- SQL Server:
- SQL Server 2019+ installed on your VMs
- SQL Server Management Studio (SSMS)
- Scripting & Testing:
- Azure Data Studio (optional, cross-platform)
sqlcmdCLI utility
- SQL Server Integration Services (SSIS) or Azure Data Factory
- Python or PowerShell for scripting ETL validation and automation
- Git
- GitHub CLI (optional)
- YAML-aware IDE or editor (e.g., VSCode)
- GitHub Actions or Azure DevOps Pipelines
- GitHub Secrets configured for:
- SQL Server connection strings
- Admin credentials
- API tokens or secret keys
- (Optional) Azure Key Vault or HashiCorp Vault for advanced secret handling
| Tool / Platform | Account Requirement |
|---|---|
| GitHub | Free or Pro account to host the repo & run Actions |
| Azure DevOps | Microsoft account + DevOps organization |
| SQL Server | Local or licensed installation + admin access |
| VMware/Proxmox | Admin access to hypervisor or test lab environment |
| Windows Admin Center | Admin rights to manage Hyper-V or Windows Server Core |
| Cloud Storage (optional) | Azure Blob or AWS S3 bucket for backup replication |
| Tool | Download Link |
|---|---|
| SSMS | https://aka.ms/ssms |
| Azure Data Studio | https://learn.microsoft.com/en-us/sql/azure-data-studio |
| Proxmox VE ISO | https://www.proxmox.com/en/downloads |
| VMWare Workstation | https://support.broadcom.com/group/ecx/free-downloads |
| Windows Server 2022 ISO | https://www.microsoft.com/en-us/evalcenter/evaluate-windows-server-2022 |
| Git | https://git-scm.com/downloads |
💡 Tip: Ensure all tools are installed on the machine(s) where you plan to provision VMs, run pipelines, or manage your databases.
Clone this repository to your local machine using:
git clone https://github.com/your-username/SQLServer-VENV.git-
Provision Hypervisor
- Start in
/virtualization/to configure your hypervisor, network, and base VM templates.
- Start in
-
Set Up Virtual Networks
- Use VLAN design files and firewall rules to isolate environments.
-
Deploy Databases
- Run SQL scripts in
/database/schema/from your preferred management tool or pipeline.
- Run SQL scripts in
-
Configure Access & Backups
- Modify the RBAC stored procedures and encryption settings to fit your org’s policy.
-
Run ETL Jobs
- Develop and test pipelines in
/etl/. Logs and validations are built in.
- Develop and test pipelines in
-
Deploy CI/CD
- Choose a pipeline (GitHub Actions or Azure DevOps) and connect secrets for auto-deployment.
- Do not check in real secrets. Use
/cicd/secrets_template.mdas a guide. - All production machines should follow
/docs/hardening_guidelines.md.
MIT License
Vesper Annstas
Data Analytics Manager & Infrastructure Engineer
Contact: LinkedIn