SQL Server Partition Manager

A production-grade Windows Forms application for managing Microsoft SQL Server table partitioning. Built for DBAs who need to discover, create, maintain, and operate on partition functions, schemes, partitioned tables, and related objects.

Features

Discovery & Visualization

• Object Explorer tree with Server > Database > Partitioning hierarchy
• Partition functions with boundary values
• Partition schemes with filegroup mappings
• Partitioned tables with per-partition row counts, sizes, and compression
• Index analysis (aligned vs. non-aligned detection)
• Filegroup inventory
• Search/filter across all objects

Create Operations

• Create Partition Function wizard with data type selection, LEFT/RIGHT range, boundary grid, CSV import
• Create Partition Scheme wizard with single-filegroup or explicit per-partition mapping

Operational Workflows

• Split Range - Add a new boundary value (with NEXT USED filegroup)
• Merge Range - Remove a boundary value
• Sliding Window - Bulk-add future partitions and optionally retire old ones
• Switch Out - Move a partition to a staging/archive table
• Switch In - Move staged data into a partition
• Index Rebuild/Reorganize per partition
• Compression changes per partition (NONE/ROW/PAGE)
• Statistics updates per table/index

Safety & Validation

• All generated T-SQL is previewed before execution
• Prerequisite validation for destructive operations (especially SWITCH)
• Identifier injection prevention via strict validation and QUOTENAME-style quoting
• Transaction wrapping where safe (with clear explanation when not)
• Confirmation dialogs for all executions
• Cancellation support for long-running operations

Script Management

• Generated T-SQL preview with dark-themed editor
• Save to .sql file
• Copy to clipboard
• Dry-run mode (generate without executing)

Logging & Audit

• In-app operation log grid with full detail view
• File-based rolling log (%LOCALAPPDATA%\SqlPartitionManager\Logs\)
• Optional SQL Server logging table (see scripts/CreateLoggingTable.sql)
• Each log entry includes: timestamp, server, database, user, operation type, script hash, execution result, duration

Prerequisites

• .NET 8.0 SDK or later
• Windows 10/11 (WinForms)
• SQL Server 2016 or later
• Visual Studio 2022 (17.8+) recommended

Setup

Build

dotnet restore
dotnet build

Run

dotnet run --project src/SqlPartitionManager

Or open SqlPartitionManager.sln in Visual Studio and press F5.

Test

dotnet test

Test Database

Run scripts/SetupTestDatabase.sql against your SQL Server instance to create a sample database with:

• Two partition functions (date-based and int-based)
• Two partition schemes
• Two partitioned tables with sample data (10K orders, 50K customers)
• An archive table matching the Orders table (for SWITCH testing)

Required SQL Server Permissions

Minimum for Metadata Read

GRANT VIEW DEFINITION ON DATABASE::<dbname> TO <login>;
GRANT VIEW DATABASE STATE TO <login>;
GRANT SELECT ON sys.partition_functions TO <login>;
GRANT SELECT ON sys.partition_schemes TO <login>;
-- Or simply: ALTER ROLE [db_datareader] ADD MEMBER <login>;

For Operations (per operation type)

Operation	Required Permission
Read metadata	VIEW DEFINITION, VIEW DATABASE STATE
Create Partition Function	ALTER ANY DATASPACE
Create Partition Scheme	ALTER ANY DATASPACE
Split/Merge Range	ALTER ANY DATASPACE
Switch Partition	ALTER on both source and target tables
Rebuild/Reorganize Index	ALTER on the table
Update Statistics	ALTER on the table (or UPDATE STATISTICS permission)
Compression Change	ALTER on the table

Recommended Role

For full functionality, the db_ddladmin role provides all necessary permissions. For read-only exploration, db_datareader plus VIEW DEFINITION is sufficient.

Architecture

SqlPartitionManager/
  Models/           # Domain models (PartitionFunctionInfo, etc.)
  Services/         # Business logic layer
    ConnectionManager.cs      # SQL connection management
    MetadataService.cs        # Catalog view queries
    ValidationService.cs      # Prerequisite validation
    ScriptExecutionService.cs # T-SQL execution with logging
    LoggingService.cs         # In-memory + file logging
  Utilities/        # Pure utility classes
    SqlQueries.cs             # All T-SQL queries (centralized)
    IdentifierValidator.cs    # SQL identifier validation & quoting
    ScriptBuilder.cs          # T-SQL generation engine
  Forms/            # WinForms UI
    MainForm.cs               # Main window (splitter, tree, tabs)
    Controls/                 # Tab controls (Dashboard, Functions, etc.)
    Wizards/                  # Operation wizards (Create, Split, Switch, etc.)

Known Limitations

1. No SMO dependency - Uses catalog views directly. Some complex scripting scenarios (e.g., full CREATE TABLE with all constraints for staging tables) generate skeleton scripts that may need manual refinement.

2. SWITCH validation is thorough but not exhaustive. It checks columns, types, nullability, clustered index presence, and row counts. Manual verification of check constraints, foreign keys, and computed columns is still recommended.

3. Sliding Window cadence inference works best with date-based partitions. For irregular boundaries, you may need to manually specify boundary values.

4. No syntax highlighting in the script preview (uses dark-theme colored text as a visual approximation). For full syntax highlighting, save the script and open in SSMS or Azure Data Studio.

5. Transaction wrapping is disabled for operations that SQL Server doesn't support in user transactions (SPLIT, MERGE, REBUILD, REORGANIZE). The UI clearly indicates when and why transaction wrapping is not available.

6. Filegroup validation checks that filegroup names are valid identifiers but does not verify they exist on the server for offline script generation. The server will report errors at execution time.

Security Notes

• No credentials are stored on disk
• Connection strings are built in memory only
• All SQL identifiers are validated against a strict pattern ([\p{L}\p{N}_@#$]{1,128}) and bracket-quoted
• Boundary values for date/string types are properly escaped (single-quote doubling)
• Numeric boundary values are validated as parseable numbers before inclusion in scripts
• The application never concatenates raw user input into executable SQL without validation

License

This project is provided as-is for DBA use. See LICENSE file for details.