Microsoft SQL Server Technical Documentation

The mssql-python driver is a native Python driver for SQL Server featuring a DDBC architecture (Direct Database Connectivity), which eliminates the need for an external ODBC driver installation.

Key Capabilities:

• DB-API 2.0 Compliance: Standard Python database interface.
• Authentication: Supports SQL, Windows, and 7 Microsoft Entra ID methods (including ActiveDirectoryDefault, ActiveDirectoryInteractive, ActiveDirectoryMSI, etc.).
• Connection Management: Built-in connection pooling and TLS encryption.
• Execution: Supports execute(), executemany(), and batch_execute().
• Transactions: Supports manual commit/rollback, autocommit mode, and isolation level control.

The go-mssqldb driver is a pure Go implementation of the TDS (Tabular Data Stream) protocol designed for Microsoft SQL Server. It integrates with the standard Go database/sql interface and does not require CGo or external C libraries (like ODBC).

It supports connecting to:

• Microsoft SQL Server (all versions)
• Azure SQL Database
• Azure SQL Managed Instance
• SQL database in Fabric
• Fabric Data Warehouse
• Azure Synapse Analytics

It is compatible with Windows, Linux, and macOS.

The mssql-python driver is Microsoft's official Python driver for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric.

Key characteristics:

• Direct Database Connectivity (DDBC): Connects without requiring an external driver manager.
• PEP 249 Compliance: Implements the standard Python Database API Specification 2.0 (connect, cursor, execute, fetch*).
• Python 3.10+ Support: Requires Python 3.10 or later.
• High Performance: Supports Apache Arrow for zero-copy result sets and native bulk copy operations for large data loads.
• Security: Supports Microsoft Entra ID (formerly Azure AD) authentication, managed identities, and TDS 8.0 encryption via Encrypt=strict.

To monitor performance in Azure SQL Database and Azure SQL Managed Instance, you should monitor CPU and IO resources relative to your selected service tier and performance level.

Monitoring can be performed via:

• Azure portal: For resource metrics, database advisors, and Query Performance Insight.
• SQL Server Management Studio (SSMS): For engine-level monitoring.
• Dynamic Management Views (DMVs): For deep engine-level diagnostics.
• Query Store: For historical query performance analysis.
• Database watcher (preview): For in-depth workload monitoring and estate-wide visibility.

The dtexec command prompt utility is used to configure and execute SQL Server Integration Services (SSIS) packages. It provides access to all package configuration and execution features, including parameters, connections, properties, variables, logging, and progress indicators.

Supported Package Sources:

• SSIS server
• .ispac project files
• SQL Server databases
• SSIS Package Store
• The file system

Azure SQL Managed Instance is a fully managed Platform as a Service (PaaS) database engine. It provides near 100% compatibility with the latest Enterprise Edition SQL Server Database Engine while automating management tasks such as upgrading, patching, backups, and monitoring.

Key Use Cases

• Lift and Shift: Migrate on-premises or IaaS SQL Server applications to the cloud with minimal changes.
• High Security & Programmability: Ideal for workloads requiring high security (via native VNet support) and a rich programmability surface.
• Reduced Management Overhead: Focus on domain-specific optimization while Microsoft handles underlying infrastructure, OS patching, and SQL updates.

Migration Options

• SQL Server migration experience in Azure Arc
• Managed Instance link

Dev Container Templates provide preconfigured, containerized development environments for Azure SQL Database. They use the Development Container Specification (devcontainer.json) to ensure a consistent environment across local machines (via Docker and VS Code) and the cloud (via GitHub Codespaces).

Workflow Lifecycle

1. Initialization: Start with a template containing necessary configurations.
2. Environment Setup: Docker builds the container based on devcontainer.json.
3. Coding and Testing: Develop within the consistent environment to ensure compatibility.
4. Deployment: Use CI/CD pipelines like GitHub Actions to deploy to Azure (e.g., Azure Static Web Apps, Azure Web App Service, or Azure SQL Database).

You can automate and schedule the execution of SQL Server Integration Services (SSIS) packages using SQL Server Agent. This allows you to run packages that are stored in:

• The SSIS server
• The SSIS Package Store
• The file system

Note that this guide focuses on on-premises scheduling. For Azure cloud or Linux (using cron), refer to the specific platform documentation for SSIS.

The mssql-django backend provides several specialized features for SQL Server integration:

• Drop-in Django backend: Works with Django's ORM, migrations, admin, and management commands.
• Microsoft Entra ID authentication: Supports passwordless connections via extra_params (Managed Identity, Service Principal, etc.).
• JSONField support: Uses native nvarchar(max) storage with Django lookup support.
• Always Encrypted: Supports client-side encryption for sensitive columns.
• Bulk operations: Optimized bulk_create and bulk_update with sensible batch sizes.
• inspectdb: Ability to generate Django models from existing SQL Server schemas.